Complex of Relationships and Calculations

[esegal]

I need some help here. I'm a novice at FM and have hit a long-standing snag trying to develop a complex of relationships / calculations.

I have a DB that records construction Change Orders "CO" (work in addition to contract work). Each change order has a summary section that records the following:

1) Original Contract Amount (easy)

2) Sum of Previously Approved COs

3) Contract Sum Including Previously Approved COs

4) #3 Plus the current CO amount

First I have a Master_Table into which my data is entered, then I have a CO_Table that summarizes all CO information entered into the Master_Table. In the Master_Table I have fields that retrieve data (listed above) from the CO_Table via relationships. There's a [Company=Company] relationship and another for previous data [Company=Company] & [CO_Serial

So far it's not working well, or one field starts to work then another will stop.

Can someone on the forum please draft up a schematic that I can follow to straighten this out? I confess I'm a little slow on picking up the relationship aspects of FM. I know portals may be a solution, but to date I've not been successful at setting them up for the purposes above.

[Søren Dyhr]

I can't tell from your dscription how static previously entered rows are, could they be deleted or taken out of the sequence?

If so is a modification of this:

http://www.nightwing.com.au/FileMaker/demos8/demo807.html (remember to read about the plugin)

Best suited for the purpose, but if good bookkeeping practises are enforced could this principle be used:

http://www.filemakerpros.com/PORTFILL.zip

Here grap the above lines content and add the new value, BTW would I usher the content of the portal file into the main file which have been possible for some years now.

--sd

[esegal]

Thanks for the response. I think your first link may be useful.

To clarify the process:

Each CO needs to auto-calculate based on previously *approved* CO's per contractor, so I've been setting up the file to select values from records that have been approved previously.

Because I need a trackable history, no changes to data can be retroactive - it can't update or change previously approved COs.

Each time a CO is entered (per contractor), it's amounts should not update in the record's summary until the record is marked "approved", since it can be revised or voided. Once approved, the summaries in that record must update, but not the previous records, hence the [serial

It's a bit difficult to explain - I don't speak the FM language very well.

So far as being diligent about the bookkeeping, I am trying to set this up for others to use, so it needs to be completely automated and "fool proof".

[comment]

I am not sure why a CO needs to calculate anything that involves its siblings: shouldn't there be a parent record ("original contract") where all these calculations reside?

[esegal]

I basically have that. The concept I have going was to enter all info in the "Master" Table, then sum all COs data in the "Change Order" Table via calculation, then via relationship (company=company and/or serial

I may have my strategy wrong, but it makes sense to me and to a large degree I'm committed to using it. I'm hung up in that it's just not working. I need a fresh set of ideas to clear the cobwebs in my head.

[comment]

I don't quite follow - mainly because "Master" table means nothing to me (unless it's a table of chess players...).

IIUC, a client may contract you to do a job. The job has some initial terms, and subsequent changes may be added.

I think there are two ways to handle this, with basically the same structure:

Clients > Jobs > ChangeOrders

The only difference between the two is where are the initial terms of contract: they could be in the Job record, or they could be in the first ChangeOrder record. Either way, the totals would be in the Job record. No calculations in ChangeOrders need to involve other records in the same table.