4 Replies Latest reply on May 19, 2017 5:42 AM by mikebeargie

# Calculation based on two value from different records in the same table

I'm looking to perform a calculation where in the table a record is created each week, I want to calculate the difference in value between two values where the date is one week later

E.g. date is Monday last week in record A and date is Monday this week in record B, I want value B to minus value A

• ###### 1. Re: Calculation based on two value from different records in the same table

If the records are always created in sequential order, then you can use the auto-enter function of "use value from last record" to store the date from last week in a new field (say FieldB), then use an auto-enter date field in another field (FieldA) to determine the date the current record was created (or modified to your requirement).

Then it would be a simple matter of FieldA - FieldB as an auto-enter calculation (uncheck "replace if existing value" so it updates whenever A/B is updated), into a new field for your result.

If the records are non-sequential, just turn the FieldB auto-enter off, and figure out how to populate it when the new record is created. Everything else is the same.

• ###### 2. Re: Calculation based on two value from different records in the same table

Use a relationship or ExecuteSQL to reference the data in the previous record with a date of the same day of the week - 7 days. If using ExecuteSQL, you will have to convert the text returned into a date before you subtract. Many use a custom function for this to make the expression a bit simpler.

• ###### 3. Re: Calculation based on two value from different records in the same table

Thanks Phil - I'm sure this is miles out but first attempt...

mon_gross - ExecuteSQL ( "select (fri_gross) from weekly_figures 2 WE2 join weekly_figures WE on WE2.id=WE.id where weekly_figures 2::date=date-7"; ""; ""; date-7)

• ###### 4. Re: Calculation based on two value from different records in the same table

You have spaces in your table names, so you need to escape those spaces for using in sql-syntax.

EG

FROM this is a table name with spaces

Needs to be:

\”this is a table name with spaces\”

The defacto guide for learning all about ExecuteSQL() is here and is a highly suggested read:

https://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/