Will you always have a set number of entries, or a relatively small max number of entries? If you have say, 5 entries max or less, you could do it with a number of summary fields. ie:
Summary Field 1: Most Recent Value from where = Entry A
Summary Filed 2: Most Recent Value from where = Entry B
You would do this for the max number you would have, then look them down to the children and have a formula field that does your calculation (If Entry B, then value + Most Recent value A)
However, if you want this to run for any number of entries, I think the only way is with formula queries. I have done something just like this, but I am careful using the values on reports, as the query can stall things out and make it pretty slow. If you can get away with a small number of summary and lookup fields, I recommend that direction, otherwise you are forced into formula queries.
I can help with the query if that is the direction you need to go.
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.
I'll chime in for something a little simpler if you really don't care about legacy records and ONLY care about the most recent entries. In your relationship you can do a Max Record ID# of your child to find the most recent record. Add that field back down to your child table, and then make a second summary field of Max Record ID# where the Record ID# is not the same as the most recent (this gets you the second most recent).
Add that field back down as a lookup field to your child - and then you can add some additional summary fields where you can summarize the actual # value(s) you want - where you summarize that value and filter Record ID# = the Most Recent and Second Most recent records. This gets you value A and Value B in your parent to do you calc, then you can bring it back down as a lookup field, to which a simple formula like If( [Record ID#] = [Most REcent Record],[Calc Value],null)
The above will ensure that in your most recent calc you're getting a value and everything else gets omitted. The drawback though is that you only have a calc for your most recent record and there is no history.
If you want a history - you can also create a looping relationship to your child where you make the 'Prior Child' the parent to your current child. So if you have 3 children under the same parent with record IDs like so:
Record 1 (Most recent): RID = 100
Record 2 (Prior): RID = 92
Record 3 (Prior): RID = 72
You can relate them together like:
Record 1 (Most recent): RID = 100 / Related Prior Record: 92
Record 2 (Prior): RID = 92 / Related Prior Record: 72
Record 3 (Prior): RID = 72 / Related Prior Record: Null
You can use the relationship to find the most recent record created for your parent and when you're entering a new child, use form rules to populate the relationship field with the prior record. Since you now have a relationship, you can create the lookup of the prior value and do the calc ongoing with no issues. This would work going forward, and if you needed to you could also do an Excel import to backfill the data.
Hi Mike,
Thank you for the solution! It does not always start with 50. That was just for the example.
Chayce,
So with the looping relationship, the only time it could fail is if you added a record in, but with a prior date. Suddenly the related field would be incorrect. I suppose that should be rare or not even happen, but maybe a formula query as the related field would work well....though I do hate relying on queries as they are memory hogs. But I really like that looping relationship idea.
------------------------------
Mike Tamoush
------------------------------
In theory yes - with the looping relationship the assumption is that the children are all being added in order and the most recent record is always going to be the one you want associated. That doesn't factor in other filters like saying that an entry may have been canceled so you would want to omit those from being considered in your summary field as a side thought.
If the children are added out of sequence or in a way that you can't relate them this way - then you could explore a formula query that tries to find your most recent record on the fly and create your looped relationship that way. Formula queries can't be sorted but you can kind of hack them by creating a string that represents a number and create a fake sort where the most recent date appears first in the query result and just split the first entry as your related record.
------------------------------
Chayce Duncan
------------------------------
