7 Replies Latest reply on Sep 30, 2010 8:25 AM by TimRaines

    Updating one table from another table or...



      Updating one table from another table or...


      As I continue working on getting this DB functional, I have run into an issue I'm not sure how to handle. In my db (a copy of which is at the url below) I have a contacts table that contains site contacts and gatekeepers. This is a short list (will be 200ish) of employees that serve in that capacity. I will also be adding the rest of the contact information for the rest of the employee population (2500 employees) into the db so we have an offline copy of crucial contact information in the event we lose our data center. I will receive a full copy of this information nightly, probably in a delimited format.

      It is important that the contacts db stays up-to-date and that we have access to all employee information.

      Are the following options feasible, do they make sense, or is another option better.

      * Option 1: Keep the "Contacts" table separate from an "All Employees" table. Build a script that imports the nightly employee information into the "All Employees" table, compare the records in the "Contact" table and in the "All Employees" table by Employee ID and copies the contact information over.

      * Option 2: Import the nightly employee information into the "Contacts" table.

      Possible Issues:

      * The site contacts and gatekeepers are popup lists based on the "Contacts" table. Popup list length is an issue

      Any insight into this is greatly appreciated!

      The file can be found at: http://www.netsplus.com/DBClone.fp7.gz

        • 1. Re: Updating one table from another table or...

          If you have a unique employee id that is imported with the data, there's an update matching records option for import records that you may wish to investigate. This might make it possible to import your data nightly into a unified contacts table. Just make a back up before each nightly import.

          To prune your pop up list length down to size, consider using a conditional value list that restricts the number of listed employees listed from the entire table. A conditional value list, could list only those employees that are labeled as "gatekeepers" for example.

          • 2. Re: Updating one table from another table or...

            Having never used conditional value lists I did some research and it seems that the "condition" is based on a related table (at least from all the examples I have found). How do I set the condition on the value of say a gatekeeper field being "Yes"?

            • 3. Re: Updating one table from another table or...

              That's one of two methods, the most flexible and the most steps to set up.

              Here's a simpler but less flexible method.

              In Contacts, Define a calculation field, cGateKeeperIDs as

              If (ContactType = "GateKeeper" ; ContactID ; "" )

              Note: ; "" may be left out and you'll get the same result

              Now simply refer to this field in your value list definition.

              Using a relationship, you'd define a field in the table where you'll have your conditional value list. We'll call it cGateKeeperLabel and set it to return the text: GateKeeper

              Now link your parent table to a separate table occurrence of contacts like this:

              ParentTable::cGateKeeper = GateKeeperContacts::ContactType

              Now you can specify ParentTable as the "starting from" table occurrence and specify GageKeeperContacts::ContactID as the field entered in column 1 of your value list.

              • 4. Re: Updating one table from another table or...

                OK, well I'm just not getting it...

                A contact can have many roles (types). They can be a gatekeeper, site contact, and Security Coordinator, so I have to allow for multiple choices. I setup a multiple choice checkbox field using a list from another table, but I was unable to figure out a way to test that the field contained "Gatekeeper" when other types were checked. It seems I need FM Advanced to load a function to do parse the field for a specific string.

                So, I setup a field just for each role. Let's just focus on the Gatekeepers. I have a field setup name "Gatekeeper" that is a checkbox.  I also have a calculation field called "cGatekeeperTab" that is based on the formula

                If (Gatekeeper = "Gatekeeper"; EmployeeID)

                When I check the Gatekeeper checkbox cGatekeeperTab fills in with the Employee ID. From here on out for the life of me I cannot seem to use this field to limit the list of Gatekeepers listed. I put a file with sample data out at


                for anyone who would look and tell me what to do. I'm sure it is something simple, but...

                Also, if anyone has a thought on how to use a single field to cover all role types (issue in paragraph 2) and still get the same results, I would like to hear that as well. Overall, that seems to be a better design.

                Thank you for all the help!

                • 5. Re: Updating one table from another table or...

                  Didn't know your field stored a list of multiple values. Here's a modified calculation that will work with your original field and no custom function is needed:

                  If ( Not IsEmpty ( FilterValues ( ContactType ; "GateKeeper" ) ) ; EmployeeID ; "" )

                  You seem to have stopped short of the second half of this process:  If the above field is labeled cGateKeeperTag, you should be able to set up your value list to refer to EmployeeID's from this field instead of ContactID's.

                  Define a new value list, Specify column 1 values as coming from cGateKeeperTag, and column 2 values as coming from cFullName and you should have your value list.

                  • 6. Re: Updating one table from another table or...

                    I was able to get the first half working successfully, but after fighting with the second half (and uttering a few ugly words to myself) I backed out the second half and took a break.  I will attempt again and let you know. Thanks!!

                    • 7. Re: Updating one table from another table or...

                      OK, finally got my brain around it and it works great! Thanks!