Hi Mike
That'd be great. But there is no limit on entries per customer and they often exceed 5. Essentially, anything other than the most recent 2 entries are "legacy fields" for reference only. That is, every time a new entry is added the oldest of the most recent can drop off as far as the calculation or summary fields are concerned. Is there a way to restrict these summary fields' frame of reference just by setting the Read Date sort to "Group latest to earliest" and then restricting the summary to only look at those first two entries? I am new to QB so I am not sure of all its functionality/limitations, but yes-I would appreciate your help setting this up to see how it works within our app.
------------------------------
Jill Jackson
------------------------------
So to do this, I think the only way is with formula queries. Though i will be honest, in your picture I don't understand how you got 50 for Entry A. Does it always start at 50??
Quickbase Junkie has given some amazing resources on how to use queries:
https://www.quickbasejunkie.com/blog/formula-query-functions?utm_source=qb&utm_medium=r&utm_id=0
Essentially, this will take two steps.
Step 1: Use a formula query to Rank your children by date. So earliest date is 0, then 1, then 2,3,4,5 up until the highest date.
Step 2: Use a formula query to get the value you need from the record with the rank one below you, and use that value for your calculation.
For step one, here is QB Junies video on ranking: https://www.quickbasejunkie.com/blog/formula-query-functions?utm_source=qb&utm_medium=r&utm_id=0
But in summary, on your child record you will create a new formula numeric field
[Rank]=
var text Query = "{100.EX.'" & [Related Parent Field] & "'} AND {50.BF.'" & [Date Field] & "'}"; //where 100 is the related parent fid and 50 is the date fid
Size(GetRecords($Query)))
Then, Step 2 will be to get the value of the most recent Y Diff using a formula numeric field:
[Most Recent Y Diff] =
var number PriorRank = [Rank]-1;
var text Query = "{100.EX.'" & [Related Parent Field] & "'} AND {101.EX.'" & $PriorRank & "'}"; //where 100 is the related parent fid and 101 is the [Rank] fid
ToNumber(GetFieldValues(GetRecords($Query),10)) //where 10 is the fid of the Y Difference between most recent entries
Last step is to make your actual field you want:
[Y Difference between most recent Entries] = [X Reading]-[Most Recent Y Diff]
This is untested but I think it should work. Though not sure where the 50 comes from in your example, so you may have to account for that.
