I am developing a new section to a current relational
>fmp6 application where I have run into a data design problem and need
>help.
>
>The basic issue is that I am preparing a series of relational tables
>for budget, pay vouchers, and agreements based on approved project
>funding. Our agreements are continuously amended. Each amendment MAY
>provide additional funding for a current project. That part works fine
>as a part of the budget, voucher process...as long as I input each
>amendment as a new record into the agreement table. Problem I have
>though is that in the projects records, the project relates back to the
>original agreement and all its amendments. I set the project table up
>originally as a flat file with up to 3 amendments fields. The problem
>is there may be more than one funding agreement to a project and more
>than 3 amendments to a main agreement. I started to build a join table
>between a project table and the agreement table, but I am not sure yet
>if I that is the answer.
>
>I really should also have the agreement table as a main agreement with
>an agreement line item table to capture the related amendments, but
>then I don't know how to connect those records to the budget records to
>approve the funding based on the specific agreement record. Example:
>One main agreement record may approve to build 2 projects totaling
>$100000. Another amendment the next year will amend that 2 by adding
>another 3 and increase funding by $150,000. The budget would approve
>each as 2 separate agreement activities, but the project record to say
>build 1 of the 2 buildings of the first agreement needs to capture the
>main agreement with any amendments after even though it is 1 project
>record out of a total 5 possible units. I hope that is clear enough.
>
>There are numerous layouts that use the string of main agreement and
>associated amendments. The agreement string needs to be considered.
>
>I appreciate your consideration and look forward to your reply.
>
>Thanks,
>Dave Tollefsrud
>City of Omaha Planning Department

dtollefrud wrote:I started to build a join table
between a project table and the agreement table, but I am not sure yet
if I that is the answer.

Hi. That seems like a good idea.

dtollefrud wrote:I really should also have the agreement table as a main agreement with an agreement line item table to capture the related amendments, but then I don't know how to connect those records to the budget records to approve the funding based on the specific agreement record.

I'm not *completely* sure what you're asking, but you can calculate the sum of these amendments up into the agreements so the total of an agreements is something like this: sum(RelatedBudgetlines::LineAmount) + sum(RelatedAmendmentLines::LineAmount).

Depending on your needs, it might be better to store that information in a number field in the agreement record at the end of each time you edit a budget or amendment line.

HTH.

John