JillJackson
Qrew Trainee
9 months ago

# DIfference between the same field in a table from the two most recent entries

Hi! My title is incorrect above and I can't seem to edit it-whoops! I am trying to set up a formula field that finds the difference between two fields in two separate entries (for the same customer) and for the two most recent entries only. For example, Entry A field X from 01/01/2022 is 98.72; Y is 50. Entry B field X from 09/06/2023 is 200. This formula field would calculate the difference (always a positive value) between B and A (X-Y) and record 150 in field Y in Entry B. Then, Entry C field X added on 10/24/2024 is 700; the difference between Entry C  X field and Entry B (most recent) Y field is recorded in field Y in Entry C...and so on always focusing on the two most recent entries by Read Date.

I have attached a screenshot of how I would like it to function; I have manually entered the data in this example. Is this possible? Or something similar?

Thank You!

------------------------------
Jill Jackson
------------------------------

• 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.

------------------------------
Mike Tamoush
------------------------------
• 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.

------------------------------
Mike Tamoush
------------------------------
• Mike and Chayce,

Yes- I would like to implement the solution provided by Chayce if it is indeed simpler. The more simple, less upkeep, the better at this stage in my QB learning. Would either or both of you be willing to help implement this in to our system? I did put it in a help ticket, prior to your solution, and they responded that they could not help with it. If so, how would you like to communicate-Teams?

Also, just to clarify- I would be importing 1-2 historic entries(child) through a csv. Here is an example of how this will look:    Each entry is linked to a Customer record(parent) through a field relationship

1st entry with date of 5/5/23 > 2nd entry with date of 5/5/24 (both of these through the import) > 3rd entry with date of 10/5/24 will be manually entered into the app by staff; and it continues, in theory, in sequence by manual entry.

There may be a need to add that "formula query hack" you mentioned Chayce in response to Mike's catch of the possible out of sequence addition.

Thank you again for your help and hope to hear from you soon!

• I'll re-read this when I have time but I know my schedule is swamped. However, if you have a small budget MarkShnier__You is a FANTASTIC consultant and can likely help implement any scenario.