Forum Discussion

AndreonnaGarret's avatar
AndreonnaGarret
Qrew Assistant Captain
8 years ago

Calculate the daily trend of a numeric field across multiple records.

So I collect a set of data a couple times a week. I have a table called "Magnet Stats". The records on this table are related to a parent record on another table "Inventory". On this table, I have a field called [Helium Fill %]. 

When I enter data I pick the related "Inventory" record and then enter the helium percentage. 

I need to add another field called [Daily Helium Loss], which will calculate the daily loss for each related inventory item. When I recorded this data in Excel the formula for the [Daily Helium Loss] was:

(Current Helium - Last entered Helium) / (Current Date - Last entered Date)

I need to recreate this formula Quick Base style. Essentially I need the current record to pull the helium level from the previously entered record for that related item. Then calculate number of days between those two records. Then find the difference between the two helium levels. And finally use those numbers to determine the amount of helium consumed on a daily basis. 

Any help would be appreciated. 

Thansk!
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    Do you need to know the loss between all measurements days?  i.e. Have a history of the changes.  
    Or do you just want to know the loss between the last 2 measurements?
    • AndreonnaGarret's avatar
      AndreonnaGarret
      Qrew Assistant Captain
      8 years ago
      I need to know the history, so we can track a trend of the loss for each related item. 
  • AndreonnaGarret's avatar
    AndreonnaGarret
    Qrew Assistant Captain
    8 years ago
    So for future reference, I have figured out the solution to this problem. My main issue was that when using the max record ID option, it would change every time I entered a new record. To solve that I added additional fields. 

    So we have the following fields to pull in the last records value:
    [Max Record ID - Helium Fill]
    [Max Record ID - Date]

    Now to hold that value:
    [Latest Helium Fill]
    [Latest Date]

    Use a form rule to say if [Latest Helium Fill] is blank, fill in with the value in [Max Record ID - Helium Fill]
    and one to say if [Latest Date] is blank, fill in with the value in [Max Record ID - Date].

    This will fill the information into the fields, and they will hold the value. 

    Now I can create my calculation using the [Latest Helium Fill] and the Current one and the [Latest Date] field and the current one. 

    This would not work if you were creating records in grid edit. 

    Hopefully, that helps anyone in the future if they have a similar problem.