6 Replies Latest reply on Feb 5, 2012 9:44 AM by KennyMeesters

    Automatic creating entries

    KennyMeesters

      Title

      Automatic creating entries

      Post

      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!

        • 1. Re: Automatic creating entries
          philmodjunk

          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.)

          • 2. Re: Automatic creating entries
            KennyMeesters

            Hi PhilModJunk,

            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)

            RequiredDocuments Table:

            RequirementID ProjectID Name
            Req 1 Project 1 Document A
            Req 2 Project 1 Document B
            Req 3 Project 2 Document C

            PersonPartcipatesinProject Table

            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!

            • 3. Re: Automatic creating entries
              philmodjunk

              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----<RequirementsFilled>-----RequiredDocuments

              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"

              • 4. Re: Automatic creating entries
                KennyMeesters

                Dear PhilModJunk,

                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!

                • 5. Re: Automatic creating entries
                  philmodjunk

                  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

                  • 6. Re: Automatic creating entries
                    KennyMeesters

                    Dear PhilModJunk,

                    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.