Question: Is there a way to build an export order while performing a script? I would prefer a FileMaker-native or FileMaker-called AppleScript solution, if one is possible.
Project: The project is a reporting tool which summarizes sales information (units, price, cost) by user-selectable criteria such as: week, quarter, year, location, product, supplier, etc. I would like a way to specify, at runtime, an export based on the user-selected criteria.
Example: If a user selected units sold summarized by supplier per quarter I would like to be able to have the script select:
Group by:
Export Order
There are obviously many permutations, so setting up an export for each individual export for each set of options is infeasible.
Building on Mikhail's and Chuck's suggestions, I think the best method for this particular project is going to be to build the contents of a .csv in a global field and then Export Field Contents. The basic outline of what I'm doing:
Go to the first record
Loop
WriteTheRows (see below), comma delimited, to a global field
Set $thisGroup to the count of records summarized by this summary field
Exit Loop If Get (CurrentRecord) + $thisGroup >= Get (FoundCount)
Go to record [Get (CurrentRecord) + $thisGroup]
End Loop
Export Field Contents [global field]
WriteTheRows is a custom function that does the following: The output I'm trying to write can be sorted by up to 7 different criteria at the same time (for example: I could summarize supplier sales by quarter or I could summarize quarter sales by supplier)
Compare the highest level sort field's value to the last value we found for the highest level sort field.
If they're different WriteALine to the global field for this sort field, the next sort field, all sort fields down to the lowest level.
If they're the same, compare the (highest level sort field - 1) to the stored value for the (highest level sort field - 1)
If they're the same, WriteALine to the global field for the (highest level sort field - 1) on down to the lowest level sort field
... repeat until we're down to the lowest sort field
WriteALine is another custom function which adds the appropriate labels, commas and values using the GetSummary ( revenueSummary ; Evaluate ( "summaryField" & summaryFieldNumber ) as Chuck suggests in his answer.