6 Replies Latest reply on Dec 22, 2012 8:35 PM by ozgobo

    data entry form that fills multiple tables

    michaelduffy_1

      Title

      data entry form that fills multiple tables

      Post

           New to FM - Have set up a customer table with customer_id as pk -other table is customer_address table with customer_id as fk - want to be able to enter multiple addresses per customer from customer data entry form(i.e. - billing,shipping ..different shop locations) - want to commit one address record then add another until done and then commit customer header record - thx in advance for any help - this is a very cool tool -many years with mysql 

        • 1. Re: data entry form that fills multiple tables
          ozgobo

                

               I have done this, although slightly differently.  I have a customers table and layout.  The layout has a portal object pointing to a customer_address table on the customerid field, and the customer_address table links to the address table with an addressid.

               In the portal object, I mostly display fields from the address table including a text field bound to a list for the address type (billing, shipping etc).

               Remembering I'm new to Filemaker, previously Access, SQLServer and .Net, I have a button to add a new address that runs the attached script.  Please don't judge too harshly :)

               First the script gets the customerid into a variable, goes to the addresses table, creates a new record and stores the ID in another variable, then goes to the addresses_customers table and inserts a new record with both IDs and then goes back to the portal object on the customers screen.

               This is slightly over engineered as I was going to allow an address to be related to more than one customer.

          • 2. Re: data entry form that fills multiple tables
            ozgobo

                 Sorry, I missed your bit about add multiples at once.  In this script, I can add sets of tasks from a template to a customer.  The script waits for the user to select a "task group", then selects task templates that belong to that group.  It then loops through them adding a task to the customer tasks table before returning to the tasks portal object on the customers screen.

            • 3. Re: data entry form that fills multiple tables
              michaelduffy_1

                   I am going to review this - thanks - we ship to customers all over the world that have multiple shops,warehouses, billing.... I am reviewing FM as we have a mobile sales force that use ipads,iphones....and mysql just requires to much work for those platforms - thx may have a few more ? after reviewing

              • 4. Re: data entry form that fills multiple tables
                ozgobo

                     I'll try and help where I can, but I only bought FM about 2 weeks ago.  I am a SQLServer DBA of more than 10 years tho.  I had been using Bento at home for about a year until I outgrew it.  This is my first database in FM.

                     For your reference, I've attached the schema behind those scripts for you to have a look at too. Ignore the invoices bit, I'm still building that.  Also remember that this is being designed as a single user solution for me alone, so this design isn't necessarily effecient for a large scale operation.  That and I'm still developing it ;)

                     Cheers

                     Scott.

                • 5. Re: data entry form that fills multiple tables
                  michaelduffy_1

                       so you have a m-m relationship between customer and address as I see the join table - my customer -address is just 1-m - reviewing the script -thx FM has come a long way - I am an apple guy but never considered FM until looking at some white papers this year - again thanks

                        

                  • 6. Re: data entry form that fills multiple tables
                    ozgobo

                         Sorry to seem like a spammer, but you may also look into the relationship options indicated in the attached as well.  With a 1-M relationship into 1 table, you may be able to set these options appropriately and use portal objects and some different scripting.  I haven't looked into that yet as I haven't needed it.  May be easier for your situation. Anyways, good luck :)