Ok I don't blog much so here goes.

I have this process where I need to create 4 records for every customer with different dates per month.

I settled on the process to derive this set of values via Execute SQL because of a few factors.

• I need to collect all this data in and have it sorted by date.
  
• Any other method the data would be semi sorted and not naturally in chronological order because of overlap from one customer to the next.
  

But i also needed another UUID that can would be static for the entire set of customers for this cycle but I didn't want to use REPLACE in a field on the Customer table it would update all the modification timestamp field, and that would be more work for Syncing the tables to the iPad.

Here is how i solved this:

When my script starts I set a global field that is the 1st day of the given month. Then I use the wonderful CustomList function to generate 1000 UUID's - even though i only need about 600 I gave myself room - plus they are not adding customers that often.

Set Variable [$$uid_temp; Value: CustomList ( 1 ; 1000 ; "Get ( UUID )" ) ]

Then in the Customer table I have an unstored calculation field called uid_temp which is: GetValue ( $$uid_temp ; Get ( RecordNumber ) )

Now when i perform the script with SQL it will grab that value and use the UNION function to build 4 records per customer including the uuid from the global variable as an ad hoc virtual list so that my records are tied together for the cycle by their own unique key, not just the customer id.

here is the SQL ( thanks to beverly - putting me on the right track )

// Built by SQLExplorer.  Compliments of SeedCode… Cheers!
Let ( [ 
// Define Carriage Return Substitution Character
ReturnSub = "n" ;
// Enable the second line here if you want the header in your results
header = "";
// Define Table variables
aCUST = Quote ( GetValue ( Substitute ( GetFieldName ( Customers::UID ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;

// Define Field Variables
aUID = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Customers::UID ) ; "::" ; ¶ ) ; 2 ) ) ;
aINSP = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Customers::uid_temp ) ; "::" ; ¶ ) ; 2 ) ) ;
aTemp = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Customers::uid_template ) ; "::" ; ¶ ) ; 2 ) ) ;
a1 = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Customers::x1 ) ; "::" ; ¶ ) ; 2 ) ) ;
a2 = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Customers::x2 ) ; "::" ; ¶ ) ; 2 ) ) ;
a3 = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Customers::x3 ) ; "::" ; ¶ ) ; 2 ) ) ;
a4 = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Customers::x4 ) ; "::" ; ¶ ) ; 2 ) ) ;

// Build SQL Query
q = "
SELECT 1 as exam, " & aUID & ", " & aINSP & ", " & aTemp & ", ''||" & a1 & ", " & a1 & " as eDate
FROM " & aCUST & " 
UNION
SELECT 2 as exam, " & aUID & ", " & aINSP & ", " & aTemp  & ", ''||" & a2 & ", " & a2 & " as eDate
FROM " & aCUST & " 
UNION
SELECT 3 as exam, " & aUID & ", " & aINSP & ", " & aTemp  & ", ''||" & a3 & ", " & a3 & " as eDate
FROM " & aCUST & " 
UNION
SELECT 4 as exam, " & aUID & ", " & aINSP & ", " & aTemp & ", ''||" & a4 & ", " & a4 & " as eDate
FROM " & aCUST & "
ORDER BY eDate "
;
// Run SQL Query
result = ExecuteSQL ( q ; "|" ; "|*|" ; "" ) ] ;  

// Clean up carriage returns
List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  ) )  )

In this statement i am using a wonderful technique by using two single quotes and two pipes it casts the date field as text so that it is in the correct format for a FileMaker Date field.

So my end goal with this is to return a number, 3 UIDs and a date all pipe separated. Here is the first few lines of the result:

1|02CCB2BD-9373-4716-BED5-CC105F6E89F6|CCB1EA18-E1FD-48DF-B64D-7659DF020DA5|B5E75592-2E7C-4AFD-A6FF-346197720DBE|9/28/2014|2014-09-28
1|1E367D44-4AF6-4946-ACA9-6B775096B83E|3790B3B0-990A-4A86-936D-09E9E74307DC|A9D888C7-6D4E-4108-AE60-2F89F800BDD9|9/28/2014|2014-09-28
1|323CFBEF-BA26-492B-A9C0-22CC65255916|EE238A0A-00C2-4C4C-B886-E8201CEF6977|B5E75592-2E7C-4AFD-A6FF-346197720DBE|9/28/2014|2014-09-28
1|3329F063-994A-4015-8B31-F2D2C6FD83AA|2A92BC19-30C2-404E-9598-2D78F58E7D0A|B5E75592-2E7C-4AFD-A6FF-346197720DBE|9/28/2014|2014-09-28
1|53C69425-81B1-441F-8C52-16840C72D516|989D6D7D-2C1A-4BC6-BAE9-D26E7DEF81CF|1A0403CB-DD46-4184-BBF1-A70EA46FF345|9/28/2014|2014-09-28

Then the script will loop thru this array explode it out to local variables and create records in the target table setting the values.

Running this script As Perform Script On Server runs very well creating about 2200 records in under a minute.

I am still looking on streamlining the method to get the proper dates but for now the half dozen fields needed to do so seems to work.