Required documents in projects: tables and relations

[drjake]

Dear fellow filemaker users,

I've been working on a databases containing information regarding a project we do for some people. In short, the relevant part of the database for this question concerns the following ERD:

Project < 1- many > Participates < 1 - many> Person <1 - many> Contracts

In reality this relationship is more complex, but is irrelevant for this questions (seems to me). My question is the following:

We'd like to define which documents are needed for a person in a specific project. So at the project level we would define that Project A requires Contract A, B, C. Now of course this can be easily done by adding another table related to the project (e.g. Required_Docs).

Now we want to keep track if the documents have been supplied by the person in the project. Also this is fairly easy, by adding a table to 'partcipates' or 'person' and linking 'required documents' with 'partcipates'.

Now the tricky bit: using such a relationship will only show portal records if the entry has been created (i.e. the user supplied the document and we entered this in the system). However, you won't be able to see which of the 'required documents' have NOT been supplied.

Now I reckon I could easliy create a script that creates all the entries based on the 'required documents' table in the 'supplied documents' table. My question is actually the following is this the best way to do this? Or is there a 'proper' database feature that I'm missing here?

Just to clarify: we want to define per project which documents are needed for all persons. And then view per person which documents have been supplied and which have not.

Thank you in advance for your help!

[comment]

Assuming I am reading your notation correctly:

RequiredDocs >- Projects -< Persons -< SuppliedDocs

you could define a calculation field cSuppliedDocIDs (result is Text) in the Persons table =

List ( SuppliedDocs::DocID ) & "¶-1"

and use it to define a relationship to another occurrence of RequiredDocs as:



Persons::cSuppliedDocIDs ≠ RequiredDocs 2::DocID





Alternatively, show all required documents in a portal to RequiredDocs, and use conditional formatting or portal filtering to indicate whether they have been supplied or not; this is given by =

IsEmpty ( FilterValues ( RequiredDocs::DocID ; List ( SuppliedDocs::DocID ) ) )