9 Replies Latest reply on Jul 10, 2014 6:38 PM by jmarshall

    Auto Fill

    mk4482

      Title

      Auto Fill

      Post

      I am trying to create a script to add new records, and once I select, say, the name of the person, I want it to auto fill the rest of their info - like address, city, state, zip, etc.  I am at a point where I can create a new record and go to the field to select the person's name, but I do not know how to get the rest of the information automatically filled in.  I am using FMP 11 Advanced.  Thanks for the help!

        • 1. Re: Auto Fill
          philmodjunk

               Presumably, you have two tables, Main--the table where you want your auto-fill and Contacts--the table from which you want to access the contact information.

               While it is possible to link these two tables by name, names are not unique, subject to being changed and can create issues when you discover you've entered a name incorrectly and then need to correct it. If you base your relationship on an auto-entered serial number, you can avoid these issues.

               Main needs at least one field for this, ContactID, more may be needed depending on what results you want from your auto-fill.

               In contacts you would have:

               ContactID (an auto-entered serial number)
               plus your name and address fields.

               You'd then relate the two tables in Manage | Database | Relationships like this:

               Main::ContactID = Contacts::ContactID

               Format Main::ContactID on your Main layout as a value list that lists contact ID's from Contacts and at least a combined name from Contacts--though you may want to define a calculation field in contacts that combines it with an address or phone number field to help distiguish between two contacts that have the same name. With this value list, you can see the contact's current name, but selecting a value enters the Contact ID.

               What you do next depends on the results you want for your auto-fill.

                 
          1.           You can set this up so that the data that auto-fills automatically updates every time you edit the matching record in the contacts table.
          2.      
          3.           You can also set this up so that the information currently in contacts is physically copied to fields in Main. This copied data will not automatically update when you edit data in the Contacts table so you get a "snapshot" of the data current at the time you selected this contact in a given record. This second option is often used in invoices so that you can see what contact info was current at the time the invoice was completed rather than the contact info currently stored in the contacts table.

                

               If you choose option 1, the implementation is simple. In addition to your Main::ContactID field, you simply add the name and address fields from Contacts to your Main layout. When you select a Contact in Main::ContactID, the info from the matching Contacts record will automatically appear.

               If you choose option 2, you'll need to define corresponding fields in Main and set each to use the Looked Up Value option to copy data from the corresponding field in Contacts.

          • 2. Re: Auto Fill
            mk4482

            This worked exactly as I wanted it to - I needed to use Option 2.  Thanks for your speedy response!

            • 3. Re: Auto Fill
              SibenMadze

              would it be possible to make it so that after selecting the ContactID the matching Contacts fields from that record on the Main layout would wait to fill until a "yes" was selected from a seperate field on the Main layout, and alternately if a "no" was selected the fields would remain empty?

              • 4. Re: Auto Fill
                philmodjunk

                You could if you included that field in the relationship.

                ParentTable::IDfield = LookupTable::IDfield AND
                ParentTable::YesNofield = LookupTable::cYesField

                LookupTable::cYesField would be defined as a calculation field that returns "Yes" to match a choice of yes in the ParentTable::YesNoField.

                • 5. Re: Auto Fill
                  SibenMadze

                  Great!! Thanks for posting so quickly! It worked perfectly except for one part...

                  Is there another function besides LookupTable that allows the user to enter a different value into the field without changing the original field from the related table?

                  also, is it then possible to have it actually update the corresponding field on the related table?

                  ...Can't wait till I've got this down well enough to be the one answering questions for other poeple...

                  • 6. Re: Auto Fill
                    philmodjunk

                    LookupTable is not a function, it's a table occurrence (a box on your relationship graph) that refers to a specific data source table. You'd substitute the names for your table occurrences for mine when you use this in your own solution.

                    also, is it then possible to have it actually update the corresponding field on the related table?

                    The relationship is the same, but if you use option 1 from above and place the actual related fields on your layout, they can be directly editable and any changes you make in the fields would modify the data in the look up table and thus your are directly modifying the related data. It is also possible to write a script that takes the changes you make to a field and copies it back to the matching field in the original lookup table.

                    • 7. Re: Auto Fill
                      SibenMadze

                      Hi there! so I've been working through the last few weeks and making progress but I have a question regarding the dubious elegence of the scripts I've created...

                      I have two tables in seperate files and the sript that I've created uses two scripts in each file to copy/paste the values from the field in one file to a related field in the other.

                      The script is copying the content from a field in the current layout and then firing another script in the other file to paste the values and then goes back to the original layout.

                      However, I am now in a position where I will need to copy the content from many fields in a table in one file to many fields in a related table in another file and with the way that I've figured out I will have to create a seperate script for every field in each file.

                      Is there a way to copy the content from multiple fields on one table to a related table in another file without leaving the current layout, and hopefully without creating a bunch of seperate scripts?

                      Alternately, is there also a way to create a script that does the same thing but using two related tables in the same file?

                      Thanks a ton for all your help, it's been enormously useful!

                      Cheers!

                      • 8. Re: Auto Fill
                        AmjadMiandad

                             very helpful for me thanks Siben and especially Phil.

                        • 9. Re: Auto Fill
                          jmarshall

                               Are you saying that a script has to be used to copy the fields from the record with the fields to the form after choosing the correct field value?  (linking the two fields via a related field). 

                               Remember I know almost nothing about how Filemaker works.  Other databases yes, Filemaker no.