Calculation to find the last entered related record matching a certain criteria..

[Steven Cappiello]

In the attached file, given this current data model, I would like to know the best way to calculate the following two results:

 

From the context of "experiment"

 

1. the amount of the most recent sample of type "BA-A" for each experiment

 

2. the amount of the most recent sample of any type starting with "BS" for each experiment

 

 

 

Additionally, I realize there may be a better way to model this data.. any suggestions on that front?

(note: I am helping a beginner with this and he may not be inclined to change his data

model, so being able to calculate 1 & 2 above would be a big help.. but, if I could give him

suggestions regarding modeling what he's trying to do better, I'm all ears.  I have my thoughts,

but I'd appreciate hearing from you, too)

 

Many thanks in advance.

tissue_samples.fmp12.zip

[Matthew F]

If you simply want to view the most recent data of type "BS" then simply add a filter criterion to your portal.  (Double click and set a filer to [ tissue_sample::type_of_sample = "BS" ].  You already have the relationship set to sort by date and time so the most recent sample of type "BS" will now appear.    Ibid for type "BA-A". 

 

You can achieve the same result through a relationship.  First create another field in experiment (call it "sample_type") and enter the value "BS".  This can be a simple text field if you want the user to be able to select different types, or hard-wire it as a calculated field. Create additional table occurrence of tissue_sample (say select_tissue_sample) in the relationship graph, and use both "experiment_id" and "sample_type" as match fields.   Again sort the relationship by date and time.   Now calculations in the context of experiment can refer to this new table occurrence to get the date or time of the last sample of whichever type you choose. 

[Wim Decorte]

Or create none of those fields and extra relationships and use ExecuteSQL to grab a list of IDs of the matching child records.   If you do not use ORDER BY in your SQL query then the most recent one will be the last one in the list.

[Steven Cappiello]

Thanks much.. I was thinking ExecuteSQL would be a good approach. i was hoping to avoid filtering the relationship.