3 Replies Latest reply on Dec 22, 2009 8:30 AM by JackRackham

    Combining Fields and Exporting to a text file

    JackRackham

      Title

      Combining Fields and Exporting to a text file

      Post

      Hello,

      I just registered on the forum, so let's talk a little about me: I work for a company in the press business. FileMaker is widely used in the company, mostly for really simple databases. I have some "advanced" experience with FileMaker but I'm more a MySQL/PHP developer.

      I'm actually working on another simple application (single table, 30 fields, no relations) that will allow the final user to make some manual data entry and then export the data into a text file, ready to be imported in Quark Xpress.

       

      > 1. The version of FileMaker Pro you are using.

      FileMaker Pro 10.0v3

       

      > 2. The operating system and version you are using.

      I'm on Microsoft Windows Vista SP2, but the application will be used on Apple Mac OSX Snow Leopard.

       

      > 6. Please indicate whether your database file is being shared over a network

      Actually not, but in the future it will probably be shared to allow multi-user data entry.

       

      This is approximately the structure of my FM database (I will user other names for the fields, because the original names are not in english):

       

      ID

      Genre (actually only 3 different genres are available as a value list)

      Field1

      Field2

      Field(...)

      Field30

      ToBePublished

       

       

      I need to export these fields into a txt file on a single row, so I added another field called "ToBePublished" that actually it's a concatenation of the other fields (Genre & " " & Field1 & " " & Field2 & " " & Field(...) & " " & Field30).

       

      When I export the records, obviously, the field "Genre" is repeated for every record, like this:

       

      Genre1 Field1 Field2 Field(...) Field30
      Genre1 Field1 Field2 Field(...) Field30
      Genre1 Field1 Field2 Field(...) Field30
      Genre2 Field1 Field2 Field(...) Field30
      Genre2 Field1 Field2 Field(...) Field30
      Genre2 Field1 Field2 Field(...) Field30
      Genre3 Field1 Field2 Field(...) Field30
      Genre3 Field1 Field2 Field(...) Field30
      Genre3 Field1 Field2 Field(...) Field30 
       

      What I need to do is to export all the records, grouped by Genre, where the Genre only get published one time for each group. Example:

       

      Genre1
      Field1 Field2 Field(...) Field30
      Field1 Field2 Field(...) Field30
      Field1 Field2 Field(...) Field30
      Field1 Field2 Field(...) Field30
      Genre2
      Field1 Field2 Field(...) Field30
      Field1 Field2 Field(...) Field30
      Field1 Field2 Field(...) Field30
      Field1 Field2 Field(...) Field30
      Genre3
      Field1 Field2 Field(...) Field30
      Field1 Field2 Field(...) Field30
      Field1 Field2 Field(...) Field30
      Field1 Field2 Field(...) Field30

       

      Grouping the results by Genre it's not a problem, I already fixed this.

       

      What I'm not able to do is to export the data formatted as above (the resulting file will be imported into Quark Express with no need for human interaction)

       

      Anyone can help me or point me in the right direction (probably this is an easy one, but I'm kind of confused!)

       

      Thanks a lot for you attention,

      Dario

       










        • 1. Re: Combining Fields and Exporting to a text file
          TSGal

          JackRackham:

           

          Thank you for your post.

           

          When exporting, each record appears on one line.  Therefore, having the Genre field print on one line and the data on a second line is not inherently possible.  Here is a FileMaker solution via a script that uses a second table for exporting purposes.

           

          1. Create a second table, "ONE LINE" with one Text field, Line.

           

          2. Change your "ToBePublished" calculation field to NOT include Genre.  That is:

           

          Field1 & " " & Field2 & " " & Field3 & ....   & Field30.

           

          3. Create the following script, EXPORT, with the following script steps (spaces are put in purposefully for readability):

           

           

          Go to Layout [ <ONE LINE Layout> ]

          Show All Records

          Delete All Records [ No dialog ]

           

          Set Variable [ $gen ; "ZZZZZ" ] 

           

          Go to Layout [ <original layout> ]

          Sort Records [ Restore ]      Note: sort by Genre as usual

          Go to Record/Request/Page [First]

           

          Loop

             If [ Genre ≠ $gen ]

                Set Variable [ $gen ; Genre ]

                Go to Layout [ <ONE LINE layout> ]

                New Record/Request

                Set Field [ ONE LINE:: Line ; $gen ]

                Go to Layout [ <original layout> ]

             End If

             Set Variable [ $lin ; ToBePublished ]

             Go to Layout [ <ONE LINE layout> ]

             New Record/Request

             Set Field [ ONELINE:: Line ; $lin ]

             Go to Layout [ <original layout> ]

             Go to Record/Request/Page [ Next ; Exit after last ]

          End Loop

          Go to Layout [ <ONE LINE layout> ]

          Export Records [ <specify file name, export "Line", etc. > ]

           

          ------------ 

           

          Explanation: We want to start with a fresh file each time.  Therefore, we find all records in the ONE LINE table and delete all the records.  Next, we initialize a variable $gen to a large character value.  This variable is used to compare with Genre.  Then, return to your original table/layout, sort the records by Genre as you normally would and go to the first record.

           

          Now, we enter the loop to process the set of found records.  First, we compare the Genre field with the contents of the variable $gen.  If they don't equal (which they won't for the first record), then we store the contents of Genre to the $gen variable, switch to the ONE LINE table, add a record, and replace the value of the field Line with the contents of the variable $gen.  Then, return to the original layout.

           

          Regardless if the Genre field matches $gen, store the contents of the ToBePublished field into the variable $lin, switch to the ONE LINE table, add a record, and replace the value of the field Line with the contents of the variable $lin, and then return to the original layout.

           

          Skip to the next record and go back to the top of the loop.  If this was the last record, then the loop is exited.

           

          Outside the loop, return to the ONE LINE layout and export the field Line.

           

          Hopefully, you are able to follow along.  If you need clarification for any of the above script steps, please let me know.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Combining Fields and Exporting to a text file
            comment_1
              

            I would do this by exporting as XML with a custom XSLT stylesheet transforming the output to the desired format during the export.

             

            ---

            BTW, this seems a very unusual format - I wonder if Quark won't accept something more standard, e.g. XML. 

            • 3. Re: Combining Fields and Exporting to a text file
              JackRackham
                

              Ok, I think I got the trick, but I will not be able to test it until January. In the meanwhile, I'd like to thank you all for you efforts and I'd also like to whish you some good holidays (and a merry Xmas too! :D)

              Bye!

              Dario