11 Replies Latest reply on Mar 29, 2009 3:49 PM by KBGF75

    how to assign a matching ID

    KBGF75

      Title

      how to assign a matching ID

      Post

      Beginner's question:

      1. My FM7 file has two tables, Contacts and Donations. An additional table (volunteer events) is planned. Records in the two existing tables are related by ID fields: ContactID_pk in Contacts, ContactID_fk in Donations. These two fields have been populated with serialized ID numbers. Related Donation records appear in a portal in a Contacts table layout.

       

      2. Creation of a new Contact record automatically assigns a unique serialized number to the ContactID_pk field.

       

      3. I'm now stuck on how best to create a new Donation record. If I do so by selecting the donor's Contact record, then selecting the lowest (blank) line of the portal and entering the data, a new Donation record is created, the ContactID_pk value is automatically entered into ContactID_fk, and all is well. 

       

      4. But, if I go to a Donations layout and select New Record from the Records menu, the new record's ContactID_fk field remains empty. I could manually enter the correct number in ContactID_fk, but that would make the process too vulnerable to human error. I want to make the process as idiot-proof as possible, ensuring matching IDs and eliminating the possibility of unrelated records floating around in the Donations table. 

       

      5. Is there a way to prevent using Records/New Record to create a new Donations record, such that the user may only create a new record by going to a Contact record and entering data via the portal?  Is there a more sensible fix that ensures matching IDs?  Thanks in advance.

            -Al

       

       

       

       

        • 1. Re: how to assign a matching ID
          Jade
            

          Hi Al,

           

          For what it's worth:

           


          KBGF75 wrote:

           

          1. …These two fields have been populated with serialized ID numbers. Related Donation records appear in a portal in a Contacts table layout.

           


           

           Only one of these fields should be populated with serialized ID numbers: ContactID_pk.  The other field, ContactID_fk is a foreign key in the Donations records that should be related to the matching value in the Contacts.  

           

          I assume that you have created a link on the relationship graph ContactID_pk <= ContactID_fk so that each Contact can make many donations using this portal.

           

           


           

          2. Creation of a new Contact record automatically assigns a unique serialized number to the ContactID_pk field.

           


           

           Good.

           

           


          4. But, if I go to a Donations layout and select New Record from the Records menu, the new record's ContactID_fk field remains empty. I could manually enter the correct number in ContactID_fk, but that would make the process too vulnerable to human error. I want to make the process as idiot-proof as possible, ensuring matching IDs and eliminating the possibility of unrelated records floating around in the Donations table. 

             


           

          Here's one way to achieve this.  It involves creating a drop-down list on the Donations layout that lists the contacts.  When the user picks a contact from this list, the corresponding ContactID_pk value is recorded into the ContactID_fk field of the donations record.

           

          Here's how:

          1. Create a value list of ContactID_pk from the Contacts table.  Setup the value list to also display the secondary field: Contact_name so that the user can make sense of the list.

          2. Add the ContactID_fk field to the Donations layout and define it as a drop-down list using the value list defined in #1 above.

          3. Add a field (Contact::Contact_name) to the Donations layout. Initially, set this field beside the ContactID_fk field.  After you see how this works you can move it.   With a little practice you can overlay this field exactly on top of the ContactID_fk field from #2 above.  Make this field display only in Browse mode so that when the user clicks on it, the drop-down list is displayed instead.

          4. Now, when the user picks a contact from this list, the ContactID_fk value is set to the selected ContactID_pk value. 

           

          There are variations to this technique… 

           


           

          …and eliminating the possibility of unrelated records floating around in the Donations table.  

           


           

           Edit the ContactID_fk field definition in the Donation table. Click the "options" button and choose the Validation tab. Select "Not empty" and "Member of value list: (select the value list from #1 above)" options.


          • 2. Re: how to assign a matching ID
            KBGF75
              

            Thanks, Jade. You said:

                A. Only one of these fields should be populated with serialized ID numbers: ContactID_pk.  The other field, ContactID_fk is a foreign key in the Donations records that should be related to the matching value in the Contacts.

                    Actually, just the ContactID_pk field in 1000+ existing Contact records was populated directly with a series of IDs. I used a script (recommended in a prior post) to insert matching IDs in the ContactID_fk field of 1600+ Donation records.

             

                B. I assume that you have created a link on the relationship graph ContactID_pk <= ContactID_fk so that each Contact can make many donations using this portal.

                    My relationship graph does relate ContactID_pk and ContactID_fk, but the operator is =, not <=.  If I should I use <= , I don't know enough to understand why.  Boxes are checked for Donations to allow creation of records in that table, and to delete Donation records when the related Contact record is deleted.

             

                C. Here's one way to achieve this.  It involves creating a drop-down list on the Donations layout that lists the contacts.  When the user picks a contact from this list, the corresponding ContactID_pk value is recorded into the ContactID_fk field of the donations record.

                    Before I try that, a beginner's comment and a question. I'm concerned that a drop-down list of contact names/numbers (a) would be long and cumbersome, and (b) selecting the right name would increase the probablity of human error (Donald Johnson vs. Dot Johnson vs. Doreen Johnson, etc.).

                   Could the following alternative work? Put a button on a Contact layout that runs a script to create a related Donation record, and make that the only means of creating a new Donations record. (The user would first have to find or create the desired Contact record, but to me that's a straightforward process.) This scheme would seem to require, somehow, disabling the Records>New Record command for a particular table, Donations. I fear that's not possible, but I wanted to ask. If this alternative is a non-starter, does it inspire thoughts of other alternatives?

                 -Al

             

            • 3. Re: how to assign a matching ID
              Jade
                

              KBGF75 wrote:

                      My relationship graph does relate ContactID_pk and ContactID_fk, but the operator is =, not <=.  If I should I use <= , I don't know enough to understand why.  Boxes are checked for Donations to allow creation of records in that table, and to delete Donation records when the related Contact record is deleted.

               


              Yes, the operator should be "=" as you have it.  (The "<=" is just my short-hand for indicating the direction the one-to-many relationship.)
               

              KBGF75 wrote:

              Could the following alternative work? Put a button on a Contact layout that runs a script to create a related Donation record, and make that the only means of creating a new Donations record. (The user would first have to find or create the desired Contact record, but to me that's a straightforward process.) This scheme would seem to require, somehow, disabling the Records>New Record command for a particular table, Donations. I fear that's not possible, but I wanted to ask. If this alternative is a non-starter, does it inspire thoughts of other alternatives?

               


               

               Rather than go to the effort of scripting the button, why not just use the Donations' portal on the Contact layout (as you originally suggested) and restrict the user's access privileges to the Donations layout?   BTW: What version of FMP do you have? 

               



              • 4. Re: how to assign a matching ID
                KBGF75
                  

                      Jade: "Rather than go to the effort of scripting the button, why not just use the Donations' portal on the Contact layout and restrict the user's access privileges to the Donations layout?"  

                 

                      Al: Can you point me toward learning about controlling access privileges for a particular layout? Maybe I should just dive into FMP Help.

                • 5. Re: how to assign a matching ID
                  KBGF75
                     Neglected to say that I have FM9 on PC #1, but I work with people who use FM7 and use it myself on PC #2.
                  • 6. Re: how to assign a matching ID
                    Jade
                      

                    Hi,

                     

                    In the File menu, do you have Manage>Accounts & Privileges?

                     

                    P.S.: Knowing now the number of contacts you have, I agree that a drop-down list is inappropriate. 

                    • 7. Re: how to assign a matching ID
                      KBGF75
                         I have File>Manage>Accounts & Privileges in FM9, but in FM7 it's File>Define>Accounts & Privileges. Looks like the same set of options, but if not just choose one.
                      • 8. Re: how to assign a matching ID
                        Jade
                          

                        Sorry for the delays, I have to switch computers to check the options on FMP 9.  I don't have FMP7.

                         

                        Use the Accounts tab to create one or more users.  Keep at least one user with full access privileges (typically Admin).

                         

                        Use the Privilege Sets tab to create a new privilege set.  Here you can set the Donations layout to no access.

                         

                        Then assign the new privilege set to the user(s). 

                         

                        You can set the file to automatically log on using a default user name.  Select File>File Options… Log in using: {user name & password}.  If you hold down the shift key while opening the file, you will be able to enter your admin name and password.

                        • 9. Re: how to assign a matching ID
                          Jade
                            

                          Hi Al,

                           

                          I just noticed an option in the Privilege Sets layout dialog that sets the access to records via the layout to view only.  I have never used this but it may be the best choice.  If this works as I suspect, your users can then view the Donation layout but not add or change the donations records on that layout.

                          • 10. Re: how to assign a matching ID
                            KBGF75
                              

                            Many thanks, Jade. I need to digest today's material, and I'm learning along the way. I should say that my caution is more focused on reducing the opportunity for error by the database minder (me, and in time a successor) than by colleagues who may occasionally need a look-see.

                             

                            And, I should have explained up front that I use IWP to make the db available to my assistant. She does much of the data entry. We plan a third table for volunteer data, at which time yet another player may be entering data via IWP. Designs that work well for me may not do as well for a person using a browser. Patience is a virtue.

                                 -Al

                            • 11. Re: how to assign a matching ID
                              KBGF75
                                

                              Hi Jade,

                              Getting back to this after a distraction, I like your suggestion that privileges be set such that selected layouts are view-only. In this file I have a batch of Contact-table layouts and just two Donation-table layouts. I set the latter to view only. The user now can search and view in those layouts, but the important Records pull-down choices are grayed out.

                               

                              In the Contacts table layout that shows a portal of Donation records, the user can create a new donation record, and the new ContactID_fk field is automatically made identical to the ContactID_pk field.

                               

                              Problem solved. Thanks much!  Now on to the next problem.

                                  -Al