5 Replies Latest reply on Oct 3, 2011 2:29 PM by philmodjunk

    Script to export report results to excel by TIN

    Shardin

      Title

      Script to export report results to excel by TIN

      Post

       Isn't there a way to write a script to export all the data in a report I've created into an excel spreadsheet by TIN.  Such that the loop would run and export one TIN's worth of data to an individual spreadsheet named as the TIN.  Please let me know if anybody has any suggestions.  Thanks!

        • 1. Re: Script to export report results to excel by TIN
          philmodjunk

          What does the phrase "by TIN" mean? That's a new one on me here....

          • 2. Re: Script to export report results to excel by TIN
            Shardin

             Sorry it's just a field name (Tax ID Number)... I have a single table with multiple records for some of the TIN's (The unique delimiter)... I've gotten a script to export to excel based on the the TIN, but I can't seem to figure out how to get it to export all the records per TIN (if there are more than one) to an individual worksheet.  It's only picking up the first record, and it doesn't pick up the subsummary total (per Tin) either.  This can't be that hard, and I'm just a little under the gun trying to get this done.  I've tried a variety of methods to loop and identify a group of records with the same TIN.  I'm not overly familar with filemaker.  It seems like it should be a relatively easy task.  I'd appreciate any assistance! 

            • 3. Re: Script to export report results to excel by TIN
              philmodjunk

              Export Records exports the current found set. If you only want to export those records with a given Tax ID Number, perform a find for that value and then export your records. Include the summary field in your list of exported fields and it will be the sub total for this group of records.

              • 4. Re: Script to export report results to excel by TIN
                Shardin

                Thanks, but there are hundreds of TIN's... I know this is kindof crazy exporting a database into individual spreadsheets - but this is what has been asked of me.  Do you know how to set up a find to find a matching TIN group, export it to one spreadsheet, then move onto finding another group... in the script.  Attached is the last script I wrote, I know it doesn't work.  Many Thanks!

                • 5. Re: Script to export report results to excel by TIN
                  philmodjunk

                  I'd use this method:

                  Show All Records
                  Replace Field Contents [No dialog ; Orig::Mark; ""]  //clear any currently marked records
                  Loop
                     #Find all records not yet marked
                     Enter Find Mode [] //clear the pause check box
                     Set Field [Orig::Mark ; "x" ]
                     Omit Record //same as clicking omit button in manual finds
                     Set Error Capture [on]
                     Perform Find []
                     Exit Loop if [ Get ( FoundCount ) = 0 ]
                     #Find the next group of records with common TIN value
                     Set Variable [$TIN ; value: Orig::TIN ]
                     Enter FInd mode []
                     Set Field [Orig::TIN ; $TIN ]
                     Perform Find []
                     Sort records [no dialog ; restore ] //use if order of records is important
                     #export this group of records with a commont TIN value
                     Set Variable [$FilePath ; "File: " & $TIN & ".xls"]
                     Export Records [No dialog ; "$FilePath" ; //specify options to export the fields you want in the order you want]
                     Replace Field Contents [no dialog; Orig::Mark ; "x" ]
                  End Loop
                  Show All Records
                  Replace Field Contents [No dialog ; Orig::Mark; ""]  //clear any currently marked records