10 Replies Latest reply on Sep 7, 2011 1:09 AM by willrollo

    Autofill from different table


      Autofill from different table



      Let me explain briefly my database outline. 

      I have 2 tables, Contacts, Invoices 

      Within Contacts, I have a New contact Layout, where the user creates a new record and fills in all data (ie home address, phone, and drop down menu for category of contact - trade, employee, customer etc)

      The invoices table has 4 Layouts, List, Quote, Order and invoice. 

      From the New contact layout, the user can choose from a drop down menu to create a quote or an order, using these contact details. This then sets off a script to go to the corresponding layout. 

      This is where I am having my first issue. I have tried to use the starter template, invoice, to try to emulate the autofill for the fields, In this soultion, the user creates a new record/invoice and then selects from a pop up menu, a record, from the Contact name field. On selecting this, the other fields on the invoice fill out. 

      Question - Is there any way to automatically fill out these fields automatically by using the contact data from which the invoice is created? Ie on my database, i want the user to be able to finish typing out the new contact details and then simply click on "create quote" to be taken to the quote layout and all the details filled out. 
      reason being, if the database ends up with 10,000 contacts, a pop out menu isnt ideal even with autofill; 


      I have a few more questions relating to this but thought best to knock this on the head first!

      Thank you for your time and I hope my blurb made sense...



        • 1. Re: Autofill from different table

          Do you have a relationship linking the Invoices table to the contacts table by contactID?

          This is the first requirement.

          Do you want to copy data from contacts into matching fields in invoices or just display the current values of the fields in this contact record? (This is a business procedures question. Many businesses don't want to see the most up to date contact information on previously created invoices, they want to see what contact info was current at the time it was created.)

          Either way, this simple script creates a new invoice record with the same contact ID as the current record:

          Set Variable [$contactID ; value: Invoices::ContactID ]
          New Record/Request
          Set Field [Invoices::ContactiD ; $contactID ]

          From there, Invoice fields with looked up value settings can copy contact info from the contacts table or fields from the contacts table will update to show the current values for that contact.

          When working with "10,000 contacts", there are ways to get fairly easy to use value lists. If you can organize your contacts into categories, a conditional value list can list just a subset of the total.With scripting, a "search text" field can use an auto-complete enabled drop down list of customer names and then typing a few letters will prune the list down to just a few customers. A script then uses that text to find and enter the customer ID. Portals can also be used with filter expressions and scripting to produce a progressively smaller list of matching customers so that you can select one from the portal by clicking it.

          Here's a demo file of the auto-complet search text and filtering portal methods:  http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

          • 2. Re: Autofill from different table


            Thanks again for hleping me! Yes, I do have a relationshi between the two. I have a contact ID number. It is for a business so will probably follow the recommended route as you sggest in part 1 of your answer...

            The last part of your answer sounds like a fun bit of scripting! I think I will try it out when I have the rest of it working! I have a long way to go anyway - Just A quick quesition - a basic one! Should I have all the shipping fields on the enquiry table or the invoices table? As this may chnage from order to order (ie trade customer may want product sent to their client) Then I suppose that is why the shipping info should stay in the invoice table? THis can all get so confusing especially whn one has been at FM for a few hours non stop! Thanks Again Will

            • 3. Re: Autofill from different table

              This is the first mention of an "enquiry" table. Keep in mind that I only know the details you choose to provide and then only if I read and understand it correctly! Wink

              The way you phrase the question illustrates the method for determining the answer: This may chnage from order to order (ie trade customer may want product sent to their client)

              The design of your tables and relationships have to facilitate the processes you build into your database to (as best possible) to support the needs of the user. I can't dfinitively tell you where to put this data as it depends on the needs of your users.

              If this shipping info is something that you will use on many orders, putting it in a separate table so that you can link to it like you do a contact makes sense so that you do not have to fill in all these fields on every order form. It's possible to link a table of shipping addresses to your contact and then you can select either an existing address associated with that contact or enter a new one.

              This can all get so confusing

              You've got that right. Figuring out the features of FileMaker is easy, analyzing the needs of the user and then putting together an efficient and effective database design both at the data and interface levels is the true challenge to getting a quality database solution up and working.

              • 4. Re: Autofill from different table

                Sorry - I meant "contact' table!

                I was going to implement a "copy invoice address to shipping Address" button, Or let it do automatically with an edit option...

                Luckily, I am copying (sort of) an existing database for the same business, that was designed (by my father) with Access. But I am taling over the business and need to understand how the dtabase works in case I need to alter it in the future and he may not be around to ask . And as I have Pcs, and I would have to learn database programming from scratch, there is no better solution that FM11. And as I amtaking over the business then it is really up to me - But more important that the existin employyes canuse the new system and so have been asking them if there are any tweaks that they would like...

                • 5. Re: Autofill from different table

                  I have attached a screen grab of my script that I have written in my original question/post as could nt find out how to add it ti a reply. Anyway, I should point out that I have confused things by trying to simplfying my solution to you by following reconginsable layout/table names. My contacts table is in fact called enquiries. Other than that all is as I have said.

                  i have put in your script into the first half of my script. As you can see, the script is launched fromthe result of a drop down menu option on my enquiry record. Choice of recording a quote or an Order. I adapted the script you supplied to the 'make a quote' part and tested it but not a lot happened! I was wondering if the fact that I am opening a new layout may be the problem or it could (most likely) be me making a mistake. The enquiry number field links the two tables. I have put this field onto each of the enquiry(from enquiry table) layout and the quote (from invoice table) layout. 

                  I have also added my relationship table to give you an idea of my relationships and to see if they make sense. Originally I used the KP customer ID and KP invoice ID as related fields. 


                  Just found out I Cannot add two images so will post it another way but only if you think it neccessary! Prob replace the scrip image instead once you have looked over it...

                  • 6. Re: Autofill from different table

                    "table occurrence context" is crucial to correct script execution. This is determined by the layout that is current at each step of your script's execution. Change layouts and you are potentially referring to a completely different current record and found set.

                    When you change from the enquiry layout to Invoices, you are now on whatever record is current on your invoices layout. This record may or may not have anything to do with the current enquiries record on your enquiries layout.

                    In any case your steps are out of order from what I originally posted.

                    Go To Layout [ "quote" (invoices)]
                    Set Variable [$Enquiry Number ; Invoices::enquiry number]
                    New Record/Request
                    Set field [Invoices::order type ; "Quote"] //I avoid using "insert" steps except when absolutely necessary
                    Set Field [Invoices::enquiry number ; $Enquiry number]

                    Also, since you are starting from an Enquiry record, you can use this script:

                    Set Variable [$Enquiry Number ; Enquiries::Enquiry Number]
                    Go To Layout [ "quote" (invoices)]
                    New Record/Request
                    Set field [Invoices::order type ; "Quote"] //I avoid using "insert" steps except when absolutely necessary
                    Set Field [Invoices::enquiry number ; $Enquiry number]

                    This avoids issues that can arise when the current record on your Quotes layout is for a different contact than the current record on your Enquiries layout.

                    • 7. Re: Autofill from different table

                      That works perfectly! I can't thank you enough. I have a long way to go yet so please be patient when I ask you some more questions! I shall try to be as concise as possible...

                      Thank you again,...


                      • 8. Re: Autofill from different table

                        New it wouldnt be too long! I have created a new layout using data from the invoice (and enquiry due to relationship via enquiry number) that shows a list of all the reocrds under the invoice table. However, I want to be able to sort the list view between orders, invoices, open invoices etc. 

                        So at the top of the table is a line of buttons that the user clicks and this will then filter the records below to relate to corressponding button.i.e. orders.

                        I want to be also able to click a little arrow by each button so sort the displayed record by date/ serial number etc (depending on the field).

                        The problem I am having is that when click the button to view (eg) orders,  - and then go to that slected order, it shows the wrong layout. It shows the quote layout. 

                        Do you thik I should duplicate the fields in the invoice table and create and orders and quotes table and then link these tables to the existing corressponding layouts. I will also link them via enquiry number all the way from invoices - orders-quotes-enquiry. 

                        Any suggestions? Advice on the filtering/sorting would be great too if you have the time...

                        Thank you again - 


                        • 9. Re: Autofill from different table

                          Do you thik I should duplicate the fields in the invoice table and create and orders and quotes table and then link these tables to the existing corressponding layouts. I will also link them via enquiry number all the way from invoices - orders-quotes-enquiry.

                          Please do not do that. Relational databases are a system intended to prevent such duplication of data.

                          I'd need to see your scripts to understand why you are ending up on the "wrong layout."

                          Simply sorting the records currently listed, shouldn't involve any change in layout.

                          • 10. Re: Autofill from different table

                            Hi Phil

                            You are right of course. Actually, whilst waiting for your response, I had a go at going through all my scripts and noticed that I had the 'quote' layout in the script thatI had set up to view record from the list view. 

                            The list view sort/filter I amhaving a few issues with so I shall post another question and I hope you can help me out on it!

                            Thank you