different price sources, one invoice portal

[cabelo]

I want prices from a set of different tables to appear on an invoice, however, there is no way to predict when a table will need to submit a price.

 

There are several tables based on the type of material being submitted (a text table, translation table, design table, etc). These tables link up to a submission table, which in turn links up to a project table. A submission may consist of text and designs (ex: maintenance for a web page in the company web page project table). Or a submission may be translated text (and nothing else). The reason for this setup: some materials for submission have radically different attributes (putting a support license, consulting service and text for a document all on one table did not seem practical.) The relations along this chain all work well, and I use portals on the project level to set up submissions and make requests for materials that need to be bundled and submitted for a project (and information goes the other way, of course)

 

So now comes time to bill for the submission. Linked down from the project table (next to submission table) are deliverables, and linked down from deliverables is the invoice table. I want the related prices of materials (and their names) in a submission to appear on one invoice, but how do I tell Filemaker when a specific material was provided?

 

As mentioned earlier, one submission might consist of any number and combination of various materials, each different kind of material handled in a separate table.