Can I calculate a value in a field which is dependent on a value of the same field but a different record?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I wish to calculate a value in a field which is dependent on a value of the same field but a different record. 

For example, amount this year = amount last year + (units consumed this year)

How do I refer to the last year's amount?

I have attached an excel file which has information on the scenario I wish to mimic. The amount column is a calculated column and that is the column I wish to calculate in QuickBase.

Photo of Nupur

Nupur

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
In general, you cannot easily do calculations where depend on other records in the same table. BUT, if you are willing, for example to change the Key field of your table to the numeric Year, then you can make a table related to itself and hence be able to do a lookup, just like with any other relationship.

So you example if you made a field with the formula [year]-1 called [Last Year] and made a relationships of the table to itself based on that field, then you would be able to lookup values form the [year]-1 record into the  current year record.

I don't know how complicated you app is and if you have ever changed a Key field before, but if you are at all uneasy, then make a copy of you app so you can test on a copy of your app first.
Photo of Nupur

Nupur

  • 0 Points
Hi Mark,

Thank you for your response. I performed the following steps:
1. Created a new formula field, Last Year, in the same table.
2. Formed a new relationship with the table with itself. On the details table side, "Last Year" is the reference field and the look up value is the amount.

But it doesn't work. I thought once I have the "Last Year" field, I will have it look up the amount of the last year in the current year record.

I am sure I missed a critical step in the process suggested by you. Can you please help me?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
What is the Key field to the Table? Does the [Last Year] field have formula?
You will need to change the Key field of the table to be Year field.  Did you do that.  if not then delete you existing relationship, then change the Key field, then re-do the Relationship.
Photo of Nupur

Nupur

  • 0 Points
Yes, [Last Year] is a formula [Year]-1.

The [Year] in the table is not unique since there are multiple records with the same year but other different information. So I am not sure if I can have [Year] as the key field for this table.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
So, how is QuickBase suppose to know which record to pull the information from?  Do you want to choose the related previous year manually for each record?
Photo of Nupur

Nupur

  • 0 Points
Do you think performing this calculation in a report is a better way to do this? In the report i can have records with unique year information.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
A report cannot look at "other records" unless that information is being pulled in as part of a relationship. How about if you give me more information about what this data really represents and i may have some other solutions.  maybe we just need a table of YYYY's and pull in summary information there which is then looked up down to the detail records.  That is a usual way to go in QuickBase.