I'd use the same table for "required documents" and for linking to the person who supplied the required document. If this second linking field is empty, the required document has not yet been supplied.
A filtered portal (requires FileMaker 11) can then be set up that lists all required documents, but omits all for which this field is empty--thus producing a list of all required documents that have not yet been supplied by a person involved in the project.
PS. I'm not sure that all of these relationships will be one to many. Can't the same person be involved in more than one project? (That requires a many to many relationship and is usually handled with a join table.)
Thank you for your prompt reply. First off, yes, indeed I use multiple project per person, I just simplified it for the sake of overview. In fact the entire DB contains over 25 tables, so I didn't want to post the entire ERD.
Reading your answers I think I may have omitted a crucial part of information. Mainly, that these required documents are required for EACH person in the project. So, the requireddocuments is many-to-many to a project participant (through a linked table called RequirementsFilled) I.e the tables would contain information like this:
(of course ID colums do not contain names, but just to clarify)
RequirementID ProjectID Name Req 1 Project 1 Document A Req 2 Project 1 Document B Req 3 Project 2 Document C
ProjectID PersonID Project 2 John Project 1 Mary Project 1 Phil ;) Project 2 Mary
RequirementsFilled Table (would contain ideally;)
Person ID ProjectID Requirement Flled? John Project 2 Req 3 yes Mary Project 1 Req 1 yes Mary Project 1 Req 2 no Phil ;) Project 1 Req 1 no Phil ;) Project 1 Req 2 yes Mary Project 2 Req 3 no
So in general, each person in a project should have all the documents filled. So the problem is, how do I show rows in a portal for that specific person particpating in that specific project which documents are required, even if the RequiremetnsFilled row has not been created yet (as this row will only exist after its added). in other words, all the rows containing Filled=no, would not exist. For now I create all the rows through a script once a person is added to project.
(the script looks up which documents are required for that proejct and creates those rows in the requirementsfilled Table with Filled? = No)
Is this the best way to do this or a there more 'natural' DB ways of doing this?
Hopefully this is clear? If you prefer a (part of the) ERD, let me know.
Thank you for any assitance!
Note in posting a simplified ERD, you introduced errors that do not exist in your design. It appears that this was your original set up:
Project -----< Participates>----Person----<Contracts (---< means one to many)
Note that Particaptes to Person is many to one instead of one to many, making Participates a join table between Project and Person.
Adding the RequiredFilled and an occurrence of RequiredDocuments gives you:
Person::PersonID = RequirementsFilled::PersonID
RequiredDocuments::DocumentID = RequirementsFilled::DocumentID
Person::PersonID and RequiredDocuments::DocumentID should be auto-entered serial numbers, not names. (Names are not unique and people change their names.)
My original suggestion works for this set up.
You can place a portal to RequirementsFilled on a layout based on Person. This Portal filter will then list only those Requirements that have not been "filled":
RequiredDocuents::Filled = "no"
Thank you once again! Like said in my post, the ID are indeed AutoEnter Serials. I thought this would i make easier to make sense of the ERD. My ERD is indeed setup the way you describe it (including using Serials).
I just have three more additional questions:
1) RequiredDocuents::Filled = "no", this field does not exist in the 'requiredDocuments' table, only in the 'RequirmentsFilled' table (as this is depend on the person and project)
2) Will i be able in your solution to see all the rows from requirementsfilled, including those who have 'RequirmentsFilled::Filled = "yes". So that I have an overview of all the requirements for that person in that project and whether or not that specific person has met which requirement.
3) And consequenly, to be able to change the status of each requirementfilled row, if a document is supplied.
I understand your ERD setup, I'm just curious how the portal will be able to know, which rows to show, if the rows in 'requirementsfilled' do not exist yet for that person in that project.
And thank you for your patience!
Like said in my post, the ID are indeed AutoEnter Serials.
Yet one of your examples lists first names instead of ID's. I wanted to be clear--more for the sake of others reading this thread than on your part.
1) my mistake, the expression should start with the table occurrence name of RequirementsFilled instead of Required documents.
2) If you want that, remove the portal filter. It was my understanding that you only wanted to see a list of those requirements not yet filled.
3) Filtered or not, you'll be able to change "no" to "yes" in this field.
The method I described assumes that the records in the Join table were already created. This can be done with a script that pulls up a list of Required documents ID's and loops through the list creating one record in RequirementsFilled for each required document for each participant. This can be done in a nested loop, "batch update" script for your entire project or in a single loop that is to be run each time a participant is selected. You can also use a script to add the needed record for all participants each time a new required document record is added.
There is an alternative approach possible that can be used where records in Requirements Filled are only added when the participant fills that requirement. This uses a portal to required documents, conditional formatting and scripting so that Label text set up as a button looks and functions like check boxes to created the records in the Requirements Filled table.
See the "checkboxes" technique in this demo file for the details: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html
Yes I indeed, used names, else the example was clutered with only numbers, but I agree with you that for the sake of others it might be unclear. I'll be aware of that in the future.
More importantly, the last part where you talk about scripts answers my question. I understand all the relationships, but i just wanted to verify the need to create the records through a script. That was my main question, to be sure i wasn't an "proper" DB solution for this problem. Your last posts answer that question, and provides some intressting insights howto further enhance the script that I already have in place.
I'll definitely look into the provided document! Thank you for that.
Sorry if my orginal questions was unclear, but you've answered it perfectly.
Thank you again for your quick and accurate replies.