1 2 3 Previous Next 106 Replies Latest reply on Feb 20, 2013 1:29 PM by J.S._1

    One Form to Multiple Tables



      One Form to Multiple Tables


           I have a table called Leads. The layout for leads (as pictured) is a form. I need to come up with the most efficient way to write data in the green outlined area to the table called Accounts, and the data in the blue outlined area to the table called Contacts. The magenta outlined areas will be written locally to the Leads table. The caveat here is that I need there to be a way where the user is prompted to remove assoicated records if they delete the lead.

        • 1. Re: One Form to Multiple Tables

               I forgot to include, I do have a few relationships set up via table occurences.

          • 2. Re: One Form to Multiple Tables

                 What relationships do you have between Leads and Accounts and between Leads and Contacts?

                 If they are one to one relationships (One Leads record matches to one and only one Account record...), you can simply enable "Allow creation of records via this relationship") for Accounts in your relationship in Manage | database | Relationships.

                 If it is one to many (One lead to many accounts) or many to one (One account to many leads). You'll need to add a means for selecting the desired account or accounts if they exist and adding a new account record if it does not.

            • 3. Re: One Form to Multiple Tables

                   I was just sending the relationships screenshot when you posted the reply, so I am not sure if you got to see it.

                   A lead will only have 1 account associated with it, and 1 contact. However, an account can have multiple leads and contacts associated.

              • 4. Re: One Form to Multiple Tables

                     Ok, once the screen updated after posting I see you've documented your relationships in a follow on post.

                     It looks like you first need a direct relationship between Leads and Contacts.

                     You can use two occurrences of the Contacts table to link contacts to Leads and also directly to Accounts and I don't see a purpose to linking Accounts and Contacts by both Account ID and by ContactID. Matching by one field or the other should be sufficient.

                • 5. Re: One Form to Multiple Tables

                       Okay, I have updated the realtionships accordingly...

                  • 6. Re: One Form to Multiple Tables

                         The relevant occurrences for your relationships are:


                    Leads::_fkContact_ID = LeadsContactsLea::__pkContact_ID
                    Leads::_fkContact_ID = LeadsAccounts::__pkAccount_ID

                         I've added _fk and __pk to your field names just to help keep track of which fields appear to be foreign (fk) and primary (pk) key fields. Primary key fields should be auto-entered serial number fields and foreign key fields should not be.

                         What you show indicates that you can have many Leads for one account and many leads for one contact. Is this the case?

                    • 7. Re: One Form to Multiple Tables

                           Yes. Especially in the case of corporate entities. For instance, there could be a company called ABC Hamburger Corp with stores in all 50 states, yet all purchases are handled by one or two people at the corporate location. So the lead is that contact at the corporation, but it could be for any of the locations among the 50 states. Follow?

                      • 8. Re: One Form to Multiple Tables

                             Ok, that's a key distinction. Your layout desing implies that this is not the case.

                             The issue is that when you create a new record for a new lead, if the contact or the account already exists in your database, you don't want to create a duplicate of either record in your account.

                             Here is the simplest way to add something that can manage that issue. I'm only going to describe this in terms of Accounts, but you should be able to apply the same example to contacts as all that changes are the names used:

                             First, I recommend against enabling 'allow creation' for Accounts in this relationship. While that can work, you don't want a user to just start entering data in the account fields--generating a new account without first checking to see if an account already exists.

                             Put Leads::Account_ID on your layout inside the Green rectangle. Format it as a drop down list with a value list that lists values from LeadsAccounts::Account_ID for the first (Primary) field and LeadsAccounts::Company_Name as the secondary field. A user check for an existing account for a given company by pulling down the drop down list and looking for the company by name. If they find it in the list, they can select it and the other fields inside the green border will automatically display the data from the selected account. Put a "new account" button inside this green border and give it this script:

                             Freeze Window
                             Go To layout ["accounts" (LeadsAccounts)]
                             New Record/Request
                             Set Variable [$AcccountID ; LeadsAccounts::Account_ID]
                             Go to Layout [original layout]
                             Set Field [Leads::Account_ID ; $AccountID]

                             This will create a new record in accounts and link it to your current Leads record--which then unlocks the blank fields inisde the green border so that you can enter data into them.

                             Note: this value list, is the simple to set up "beginner level" method for selecting the ID number of an exsiting account. There are a number of different ways that use scripting and different interface designs to make selecting a name faster and less prone to error. These enhanced methods make it much easier to deal with your value lists as the number of values in them increase in number.

                        • 9. Re: One Form to Multiple Tables

                               Thanks! Yes, that seems pretty straightforward.

                               The whole purpose of this project is to automate and cut down on errors. When you speak of enhancing to lessen the likeliness of an error, what do you mean?

                               One of my concerns is with the Account_ID drop down. There could literally be hundreds and hundreds of values.

                          • 10. Re: One Form to Multiple Tables

                                 Company an personal names are not unique, must be entered by hand and subject to change. This can make searching for an existing record by hand a less than perfect experience since the name may not be entered into the record as expected. This, in turn, means that it's easy to get duplicate records for the same company or contact. Maybe they change their name, maybe it was spelled incorrectly when first entered, maybe they have changed their contact information...

                                 Thus, name based search widgets that list multiple matches--perhaps after entering just part of a name and which supply additional information about each possible match, can help reduce the changes of such errors.

                                 Here is a demo file that illustrates several such advanced search techniques for finding and selecting a record by name, but which enters the record's ID number:


                                 If you are using FileMaker 12, launch FileMaker and use Open from the File menu to open this file in order to produce a copy of the file that is converted to the correct file format.

                            • 11. Re: One Form to Multiple Tables

                                   The Pattern Search seems like it would satisfy. Is there any immediate concern with using this in the specified application?

                                   Also, in using it, would the Account ID field be the equivilent of the gsearchfield?

                              • 12. Re: One Form to Multiple Tables

                                     I think the one that uses PatternCount is the best for searching your two tables for pre-existing records also.

                                     I do not know enough about your "specified application" to be able to say.much, but the demo file is simulating how you might select products to add to a line items portal on an invoice. In your case, you'd be searching the Accounts table with a script to copy the selected Account record's ID number into the Lead::Account_ID field to link it to the current Lead record and you'd need a second portal with a second relationship for searching Contacts.


                                          would the Account ID field be the equivilent of the gsearchfield?

                                     It would, absolutely, NOT be the Account ID field

                                     gSearchField is a text field with global storage specified in field options. It is solely used with the portal's filter expression to determine which matching records appear in the portal. You then have to click a portal row (the field in the row has been set up as a button), to perform a script to enter that portal record's ID number into the specified field in the layout's table.

                                     Please note that there are special tricks in use that enable the portal to update keystroke by keystroke as you enter more and more text.

                                • 13. Re: One Form to Multiple Tables
                                   I am assuming when you speak of "special tricks" that you are speaking of the "Update Search" script. Is that correct?
                                  • 14. Re: One Form to Multiple Tables

                                         That is part, not all all of the process. Open Manage | Database | Relationships, note the match fields used and the notes placed next to this relationship about how they were set up--including an auto-enter calculation on one of the match fields.

                                         Ahem, just checked that notes box and discovered that it is incomplete. blush

                                         It now reads:

                                         In order for the filtered portals to update correctly, the global search field must be matched to the ProdRefresh field in the cartesian join relationship. The ProdRefresh field must also refer to the global search field in an auto-enter calculation.

                                         This eliminates the need for Refresh Window [flush cached join results] in the Update Search script that refreshes the filtered portals keystroke by keystroke.

                                    1 2 3 Previous Next