7 Replies Latest reply on Dec 23, 2012 1:24 PM by margotjacqz

    exporting related records to csv

    margotjacqz

      Title

      exporting related records to csv

      Post

      Returning to the question of creating a CSV file with names and associated contact data (for import to other apps) (address book, google, etc) This is not, I think, complicated only tedious. Just doing a gut check on my strategy, and wondering if there is a better way. Thanks for any insights.

      Situation (simplified a bit) has two related tables:

          A: names and related singular data (names, address, company, title)

          B: Contact points linked by a kUID. Table B has two fields: Type label (ie work phone, email personal, etc) and Data (number, address, etc). The label is chosen from a Type value list of about 15 options.

       

      I understand how to create a layout based on table B, with data from A being repeated on different rows, but I am unsure that solves a problem of how to Map the variously labeled fields in the export/import process.

      I can envision a script to

      - create variables:  IF (lable = "work number"; set variable [$work=<phonefield>)  ELSE IF (lable=$home; set ...  etc etc.

      - move to a new table with a separate field for every variable iteration, and other data from Table A, and use look ups and SetFieldByName to create new records.  The way I see it I'll still have multiple records for each UID, but data will be in specific fields (columns).

      How do I loop through the original layout / found set? 

      Is there a way that will create a single record (row) with all the data related to one UID in it's proper field?

       

        • 1. Re: exporting related records to csv
          Sorbsbuster

          What are you trying to achieve with the export?

          Eg:

          Joe Green, Coca Cola, 21 Main Street, 01234 567 89, 987 541 632, 456 789 123
          Jane Doe, Google , 1 High Street, 987 654 321, 874 596 321, 874 741 741
          etc

          or

          Joe Green, Coca Cola, 21 Main Street, 01234 567 89
          Joe Green, Coca Cola, 21 Main Street, 987 541 632
          Joe Green, Coca Cola, 21 Main Street, 456 789 123
          Jane Doe, Google , 1 High Street, 987 654 321
          Jane Doe, Google , 1 High Street, 874 596 321
          Jane Doe, Google , 1 High Street, 874 741 741

          • 2. Re: exporting related records to csv
            margotjacqz

            Option one preferred, in defined order, eg:

               Jane Green, WFO, work number, mobile number, personal number, office, email, skype

             

            option 2 also possible, but the numbers have to land in defined fields so

               jane green, wfo, ,123 456 7890, , , , ,

               jane green, wfo, , , , , jane@gmail, ,

            • 3. Re: exporting related records to csv
              margotjacqz

                   I put this aside for a bit, but have time now to re-think. Is there no further response to the above?

              • 4. Re: exporting related records to csv
                Sorbsbuster

                     Option 1 should be easy if you run the export from Table B, and choose the fields you want in the order you want.

                • 5. Re: exporting related records to csv
                  margotjacqz

                        Can you please expand on that? At the moment an export from Table B looks something like this:

                       joe green, company name, address, home, xxx-xxx-xxxx
                       joe green, company name, address, work, yyy-yyy-yyyy
                       joe green, company name, address, work, joe@email.com
                       joe green, company name, address, asst, mary@comail.com

                       My goal is more like:

                       joe green, company name, address, home, xxx-xxx-xxxx, work, yyy-yyy-yyyy,  other, (empty) , joe@email.com, pers, (empty),asst, mary@comail.com,

                       Using another table, I think I can get to something like this:

                       joe green, company name, address, home, xxx-xxx-xxxx, work,  other,  , work, , asst, ,
                       joe green, company name, address, home, , work, yyy-yyy-yyyy,  other, ,  pers, ,asst , ,
                       joe green, company name, address, home, , work, ,  other, , work, joe@email.com, pers, ,asst, ,
                       joe green, company name, address, home, , work, ,  other, , work, pers, ,asst, mary@comail.com,

                       but am not clear on a way to induce all into a single record for Table A item?

                       thanks.

                  • 6. Re: exporting related records to csv
                    Sorbsbuster

                         I apologise: what you are seeing by exporting from Table B was exactly what I expected, but I can see that it was not what you want.

                         You can export from Table A and so only have one record per person, and it can contain the contents of several related records.  For example, if you use the List function and the Substitute function, you could generate a comma-separated list for:

                    Table A
                         Joe Green

                    Table B related Records
                         Joe Green Home Address
                         Joe Green Company Address
                         Joe Green e-mail work
                         Joe Green e-mail personals

                         To generate:

                         Joe Green, Home Address, Company Address, E-mail Work, E-mail Personal
                         Jim Brown... etc

                         But...

                         The related records are in no particular order (necessarily) so their field sequence would be unpredictable.  But you could get around that by sorting the relationship, so they will at least always run from Address to Zipcode.

                         But I can't yet think of any way to get around the fact that some parent records will have 3 related records and some will have 5.  You would need to create the concept of 'place-holders' for the missing two.  And of course the benefit of using a related table and not fixed fields is that you have the ability to add as many contact records as you want, so it would involve a re-design when you add another category.

                         But then the reason you are almost removing the benefit of such flexibility is that the destination application does not work that way - you have defined them as flat files, with all that associated data in one record.  In that case you could create the flat file in Filemaker.  In Table A define as many fields as there are possible contact types (in your destination application).  For example, if it has target fields for Address Work, Address Home, Spouse, and Birthday, then create those as distinct fields in Table A.  (They will be the same as the Value List that you choose from.)  Also create calculation fields with fixed text in them, equal to your Value list values.

                         Then make each of those fields as a related field (or a lookup) based on its own unique relationship to Table B.  For example:

                         TableA::PersonID = TableB::PersonID
                         and
                         TableA::TextOfAddressWork = TableB::ContactType

                         ...would pick up the Work Address for the person if the text field TableA::TextOfAddressWork was "Work address" and that was the contact record type.

                         It will only pick up one contact of each type, but then is that what your destination application expects.

                         It seems counter-intuitive to use FM in that way, but you are of course accommodating the limiting migration to a flat-file system.  You can then export those fields from Table A and it will always be in the correct order, including spaces.
                          

                    • 7. Re: exporting related records to csv
                      margotjacqz

                           Thanks for the expanded thoughts. I have accepted that I'll have to create a related "flat" record to use for export only. I had tried calcualting fields in A2 but had not figured out how to create the unique relationships and pull in all related data from Table B.  Thanks for pointing the way on that!