Thank you for all your answers.
@Mansyno: my problem is I use in the script a connection to a database, with SQL query, and I don't know then how to make a calculation coming from 2 fields from different databases. Here is an example of what I would like to do.
ODBC CONNECT TO [server ];
select * from db1;
select * from db2;
select * from db3;
load
if ( db1.field1<1 , networkdays(db2.field2,db3.field3), networkdays(db2.field4,db3.field5)) as Date,
db1.field6 as Reference;
I know that the syntax is incorrect, this is just to illustrate what I would like. In fact, I would like to add a new calculated field to a database, that's why I load db1.field6, to 'link' it to db1.
I have already tried some things with "map" or "table1: .... resident table1" but I didn't succeed to do anything, my fields weren't found. However these syntax aren't very clear to me when we have to use several references to several databases so I surely made something wrong.
Until now, I made these calculations in expressions and it works quite well, but I will be very happy to make them in the script to make my expressions less complex. As I need to go further, I should calculate something like that (for information, I am working on a report for a factory, and I have to calculate the delays added by each station on each product) :
table1:
InputField Delay;
select * from db1;
load db1.fieldKey, RowNo() as Delay resident table1; // I put RowNo() because the field Delay doesn't exist, but I guess it can be any value
foreach(Reference)
Let Date1= Max({$<db1.field1={1}>} db1.field2)
Let Date2=Max({$<db2.field1={1}>} db2.field2)
Let Result=if( Date1<Date2, networkdays(Date,Date1), networkdays(Date,Date2))
Delay.SetInputFieldValue 0,result // how should I get the offset? something like RowNo(db1.fieldKey)?
end foreach
I know it is quite complicated, but so far it is the only idea I have. I hope this "code" make a little bit sense for you.
I create a input field, because I have to create a new field to store each delay added for each item. So I link it to a field with unique value, db1.fieldKey. Then I make a loop for all the item with Reference, I get the dates I need, I make my calculations (which are simplified here), and then I store the value in my input field. Is the offset needed here? If so, how do I have its value? I also think to put in the code field1Key.select(), and this way there is only one value for Delay, but I don't know if it's a good idea or not.
Any idea/help is welcome (and thank you for reading me until the end).
