LAST() Function

We recently created a new function in Adesso called LAST().  This function, which is available in the expression editor, was created so that values from records in a child table could be extracted with ease in the parent table.  This is useful for when you want to compare two or more values in a child table against each other and/or calculate something between those values (for example, like the difference between the last two temperature readings recorded from the child table).

The function's syntax is as follows:

 LAST(fieldname or O2M.view.fieldname, record number)

Where the O2M (one to many) view listed in the function is a view that has at least one sorting order assigned to it.  The record number in the function is an integer denoting which record to pull the value from, based on the last record in the child table's view being record number 1, the second to last record being record number 2, and so forth.

Note the expression in the following screenshot:

The expression assigned to the field which I have called LAST1_Temp is:

LAST([Well Sampling > Purging].Included.Temp_Reading, 1)

Note that I have selected a view called "Included" that I built for the o2m grid that is displayed on the parent form.   But the name of this view is something that looks complex..."[Well Sampling > Purging].Included.  The reason for this is that a relationship exists between the parent table Well Sampling, and the table Purging.  I created a o2m relationship between those tables and named that relationship Well Sampling > Purging.  Hence the name of the view.  An easy way to populate this is to select the view right from the top left listbox in the expression editor, as illustrated below:

Once I select the proper view, then I simply select the field I want from the field selection listbox just to the right, as illustrated below:

Please note that what I am doing here is selecting the proper VIEW and then the correct field from the related CHILD table.  In our case here, the child table is called Purging, and the parent table is called Well_Sampling_Events.

Now that I have selected the proper field from the proper view of the related child (many) table, then the last piece of missing information is the record number.  In this particular case, I have assigned it as "1".  This means that the function, which is loaded into a field that is present in the parent table (the Well_Sampling_Events table) and which I have called LAST1_Temp will pick up the value in the field Temp_Reading from the last record in the Purging table, where the last record is based on a particular sort order which was assigned in the Included view.  This sounds like a mouthful, so if you are confused, its understandable.  

So lets look at this another way.  Step by step, this is what you would do:

1. Create a parent table, a child table, and relate the two using a one to many (o2m) relationship.
2. Create a view for the child, or many, table, and make sure you give it a sort order by using the Sort tab in the view designer.
3. Assign that view to a grid control on the parent table's form.
4. Create a new field in the parent table to store the expression and make that field a LOCAL field by checking the "Local" checkbox in the field designer.
5. Make this new field with data type equal to the child field data type, with expression display control, and assign it the expression as listed above:  LAST(fieldname or O2M.view.fieldname, record number)

Once you do these things, then you can use that value to calculate something in another field/expression, etc.  In our case above, we did this to figure out when groundwater from a monitoring well has stabilized based on temperature readings. 

Now, if I want to then store the value of the 2nd to last temperature reading from this table, my expression would look like this: LAST([Well Sampling > Purging].Included.Temp_Reading, 2)
I would assign this expression to a new local field in the parent table, and I would call it something meaningful, like LAST2_Temp.

Once I have those two fields, then I can calculate the absolute difference between the two temperature readings by creating yet another local field (with the same data type as the others being used in the expressions) in the parent table, where the expression would look like this: ABS(LAST2_Temp - LAST1_Temp), where ABS is the absolute value, meaning that the negative sign is dropped off.  What this does, then, is it gives me the numerical change between the last two temperature readings obtained and recorded in the child table, and it does this based on the sort order assigned in the view that I called "Included".  Simple as that.

The following screenshots are of this functionality in runtime mode:

Note that on the screenshot above, there's some textual reference that says "Continue purging".  This is an expression that displays either "Continue Purging" or "Stabilization Complete", and it displays this dependent upon the difference between certain stabilization criteria, of which temperature is one of them.  In our case here, a well is considered "stable" if the change in temperature over the last 3 readings is less than 0.2 degrees Celsius.  So what we did was use the LAST() function to capture the values of the last 3 temperature readings, compared them to each other, and if they all fall below +/- 0.2, then the well is considered stabilized based on temperature.  I have placed a button that opens a hidden tab on the bottom of the form, so that users can quickly see the calculations.  The screenshot below displays this form:

 

Please note that all of this functionality also works on a PDA (although a bit slower than on a Tablet PC).  Screenshots of this application running on PPC is displayed below: