7 Replies Latest reply on Mar 10, 2009 12:18 PM by TSGal

    formating output to tab files

    eekamouse

      Title

      formating output to tab files

      Post

      Is there a way to include a header and footer string with "data" in between using a calculation.

       

      For instance the file content i need to generate has to have the following format

       

      xxxxx&datefield

      data from fields record 1

      data from fields record 2

      data from fields record 3 etc

      xxxx&datefield&totalrecordsfield

       

      so a header as first line with static data and a date variable

      3 lines of data concantated from fields

      and a trailer for last line with some static data a date variable and the total number of records being submitted (in this case 3) between the header and footer

       

      trying lots fo things but can't get it

       

      Thanks

       

      eek

        • 1. Re: formating output to tab files
          TSGal

          eek:

           

          Thank you for your post.

           

          I'm assuming the date variable is coming from the Set Variable script.  If so, then you'll need to store the date information in a global field.  Then, include the static text and global field in the Header.

           

          For the Footer (or trailing grand summary), include the static data, the global field, and a Summary field to sum/count the records.

           

          If you need clarification for any of the above steps, or if you are still having difficulty making this work, let me know.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: formating output to tab files
            raybaudi
              

            Hi TSGal

             

            I think that he wanted to know if it's possible to have a calculated field from which to export.

             

            And, also in that case, the answer is yes.

             

             

            But may be I'm wrong, so I'll wait for his replay. 

            • 3. Re: formating output to tab files
              eekamouse
                

              Yes your are right I am trying to have it export from a calculated field (barring another method) here is an actual example of the file

               

              the 265357 in the footer is the dollar amount billed and the 886 is the number of actual records although I have removed 883 and left only the 3 between the header and footer for this example

               

              2022030902260100000000000000044000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
              4250010902260000000000000000000000000000029950000000000000000000000000000000000000000000000000000400000000000000310370642700000000000000000000000002j00440000000000000010135000
              4250010902260000000000000000000000000000029950000000000000000000000000000000000000000000000000000400000000000000616392622000000000000000000000000002j00440000000000000010135000
              4250010902260000000000000000000000000000029950000000000000000000000000000000000000000000000000000400000000000000310456297100000000000000000000000002j00440000000000000010135000
              2022040902260100000000000000044000000000000000000000000000000000000000000000000000000000000000000000000265357000008860000000000000000000000000000000000000000000000000000000000

              • 4. Re: formating output to tab files
                eekamouse
                  

                Hi TsGal and thanks for responding.

                 

                I want to try have this export from a calculation field and all the variables in the header and footer would come from actual fields (i.e. date field) on the layout or a summary/calculation (to count the records in the file).

                 

                eek

                • 5. Re: formating output to tab files
                  TSGal

                  eekamouse:

                   

                  Thank you for the clarification.

                   

                  Yes, you can export from a calculation field, but getting the header and footer may prove a bit difficult.  Therefore, you may want to put the result into a global field.

                   

                  Your script may look like the following:

                   

                  Go to Record/Request/Page [ First ] 

                  Set Variable [ $counter ; 1 ] 

                  Set Field [ <global field> ; <header information> & "¶" ]

                   

                  Loop

                     Set Field [ <global field> ; <global field> & <calculation field> & "¶" ]

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

                     Set Variable [ $counter ; $counter + 1 ]

                  End Loop

                   

                  Set Field [ <global field> ; <global field> & <footer information including $counter for count of records> ]

                   

                  Show All Records

                  Omit Record

                  Show Omitted Only

                   

                  Export Records [ No dialog ; <file name> ; <global field> field only to export ]

                   

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

                   

                  Let me know if that works for you.

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: formating output to tab files
                    comment_1
                       I'm afraid that won't work well, because all carriage returns in the global field wil be exported as vertical tab characters. This could be avoided by exporting the global field only using Export Field Contents[] - but then the exported file will be encoded as UTF-16, which the target application may not be able to read.

                    IMHO, the best way to handle this is to export records as XML with a custom stylesheet. Incidentally, the same stylesheet could also provide the padding of data to fixed length (as seems to be required).
                    • 7. Re: formating output to tab files
                      TSGal

                      Oops.

                       

                      "comment" is correct (Thank you!).  The best solution would be exporting as XML with a custom stylesheet.  If you are not experienced with XML, then there is another option.

                       

                      Export as I showed in the previous post.  The carriage returns are exported as vertical tab characters (ASCII 11 instead of ASCII 13).  Then, if you open the file in Pages (Mac) or Microsoft Word (Mac or Windows), don't make any changes and save as Text, the vertical tab characters are then saved as carriage returns.  It is a two step process, but you do get the desired output.

                       

                      TSGal

                      FileMaker, Inc.