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

    formating output to tab files



      formating output to tab files


      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



      data from fields record 1

      data from fields record 2

      data from fields record 3 etc



      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





        • 1. Re: formating output to tab files



          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.



          FileMaker, Inc. 

          • 2. Re: formating output to tab files

            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

              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



              • 4. Re: formating output to tab files

                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).



                • 5. Re: formating output to tab files



                  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> & "¶" ]



                     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.



                  FileMaker, Inc. 

                  • 6. Re: formating output to tab files
                       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



                      "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.



                      FileMaker, Inc.