8 Replies Latest reply on Aug 23, 2013 1:17 PM by JimWeeda

    Creating List to export from 2 different tables

    JimWeeda

      Title

      Creating List to export from 2 different tables

      Post

           FileMaker Noob here.  I need to create a customer call list from a couple tables in my filemaker db.  I have my main customer information table which has all land owners and tenants in it.  The way our customer billing system is set up, a piece of property has a location number but can have two different accounts tied to it.  So, my table includes that location number, but can have 2 records under with that same number.  I have another table that I create records in from a .csv file that is exported from another system in the company.  It exports that location number.  I import that .csv into my database and link my two tables with that location number.   What i want to do is create a list that has the customer name and phone number based on the location numbers that i import.  I can't figure out after several hours of dinking around how to create that list.  

           So, lets say i have a location number of 00015.  There is a land owner and a tenant listed under that location number in my main table.  When I import my .csv file, its only going to have 00015 listed one time.  I need my new list to pull both records so that when I export, I have everyones name/phone from that single location number I imported.

           Thanks in advance!  

        • 1. Re: Creating List to export from 2 different tables
          philmodjunk

               If I understand you correctly, you have two tables: MainCustomerInformation and then you have Table2 which contains data imported from a source external to FIleMaker. You want to export data from MainCustomerInformation but to include related data from Table2.

               What relationship have you defined linking these two tables? What is the match field you have used?

               With the correct relationship, you can export your data from a layout based on MainCustomerInformation, but also include any needed fields from Table2 as part of that export.

          • 2. Re: Creating List to export from 2 different tables
            JimWeeda

                 Ok.  I'm not very good at defining relationships because I'm not sure I understand them fully.  I have the location field in table two linked to the location field in table 1.  (The field names are different).  It's just a straight across relationship.    

                 Table 1                      Table 2

                 Location--------------Keylink

                 Name

                 Phone

                 Etc.

            • 3. Re: Creating List to export from 2 different tables
              philmodjunk

                   Then you should be able to export records from a layout based on Table 1 (Customer information) and include fields from Table 2 (Your imported data) as part of the export.

              • 4. Re: Creating List to export from 2 different tables
                JimWeeda

                     Ok.  here is the problem I'm running into.  No matter how I run this export, it just exports all the records.  What i need is the list in Table 2 to dictate what is exported.  As i tried to explain in my first post, when i run the export, I need all the records in table 1 with the same "location number".  If a location has a land owner and a tenant, both of those need to export even tho I only show that location number (Keylink) once in Table 2.

                • 5. Re: Creating List to export from 2 different tables
                  philmodjunk

                       And that means that you have two records in customer information, one for the land owner and one for the tenant correct?

                       So if you export records from a layout based on customer information, you export both the land owener and the tenant records. And both exported records can then include info from the same related location record.

                       Export records does not export "all records" though it can look like that. It exports all records from the current found set--there are any number of ways, such as performing a find, to produce a found set of just a few records from the layout's table so that your export only exports those records.

                  • 6. Re: Creating List to export from 2 different tables
                    JimWeeda

                         Ok.  That helped me get this thing straightened out in my head!  My table 2 was my controlling set up records, so I went to that table and performed my export and it then exported all the records that I wanted it to.  Thanks very much for your help!!

                    • 7. Re: Creating List to export from 2 different tables
                      JimWeeda

                           I think I'm back to the orginal problem tho.  Before i do the export, I need to get my list created because there will be multiple records that have the same land owner and phone number and I'll want to eliminate duplicates.  This is for an automated call list for customers that electric power outages scheduled for their accounts.  :P

                      • 8. Re: Creating List to export from 2 different tables
                        JimWeeda

                             Ok.  I finally figured out what I was doing wrong!  I created a new Layout based on the Table 2 and just created a list showing all related records.  That worked like I wanted.  Then I just wrote the script to sort by phone number and delete duplicate records and it gave me a trimmed down list that I can just export to a .csv and give to the billing department to send out the calls.  

                             Thank you very very much for your help!!!!!