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.
- 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.
- The result must be set into a "% Change" field of the current record.
- This evaluation must apply to all records in the "Data" table.
- 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.