Calculate Values Between Two Records in Same Table

Discussion created by bee13 on Nov 29, 2017
Latest reply on Nov 30, 2017 by bigtom

Skill Level: Novice to Intermediate

Platform: Macbook Pro

Software Version: FMPA 13


Question: Can numerical values set in the same field but in two records of the same table be mathematically evaluated using a calculation field rather than a script?  If so, how?


This would seem to be as fundamental to databases as it is to spreadsheets, yet the methods I've searched mostly point to scripting and script triggering as the answer, not a calculation.  To me, the better solution would involve an unstored calc field that updates all record pairs as needed, not a script that requires triggering for each record pair.


DB Construct: I presently have two tables; the first contains the data fields and the second has several portals that look at the related data table, each portal is sorted descending by a date field and filtered to show a specific data category.


  1. I need the numerical data from a specified field in the current record to be evaluated with numerical data in the same field but in the record with dated immediately preceding the date of the current record.
  2. The result must be set into a "% Change" field of the current record.
  3. This evaluation must apply to all records in the "Data" table.
  4. The results will be visible in the above mentioned portals.


Discussion: My understanding is that fields placed inside a portal are constrained by the filtering/sorting/etc. established for that portal.  If so, shouldn't a calc field in the related "Data" table, visible inside a filtered/sorted portal in the other table, be able to carry out the calculation from the context of the portal constraints?  Perhaps I misunderstand the contextual relevance of portals in this case.


Summary: I am looking for the simplest way to evaluate field values between two records in the same table and have that same evaluation flow all the way through each (date/immediately preceding date) record pair of the table and display the resultant figures in sorted/filtered portals in a related table.


My thanks if you decide to tackle this.