Calculated fields based on other tables

[nhjelmberg]

Hello,

I'm currently rebuilding my FM 5.5 application to get familiarized with FM 7. The separation model appeals to me so I'm trying to keep the tables in one (data) file and all layouts, relations, scripts etc in another (layout) file. However, I have some calculated fields based on other tables and I can't figure out how to get them to work without adding relations to the data file.

For example, I have a parent table, INVOICE, with two child tables, LINEITEM and PAYMENTS. In my old solution I had a calculated field, OUTSTANDING, in INVOICE that subtracted LINEITEM amounts from PAYMENTS amounts. The field OUTSTANDING was later used for a value list so that whenever a payment was booked the user could easily find an outstanding invoice to match it with.

Of course I could add some relations to the data file as well but I would really like to take advantage of the separation model so any ideas on a workaround are most welcome.

[RalphL]

I have a demo file of a many to many relationship posted in this area. It requires the Troi Text Plug-in which you can download on a trial basis from http://www.troi.com. I have three files Data, Business Rules & Reports, and Interface. All calculations are done in Business Rules & Reports tables. There is a one to one relationship between the Data tables and the Business Rules & Reports tables. There are no tables in the Interface file.

[nhjelmberg]

Thank you, I'll try that!