Export Records in Separate xlsx files

[Smidge500]

I'm trying to write a script to export a found set into multiple excel files based on grouping records by a particular filed. 

So I have a found set, which are all records with todays date

Each record has a client assigned in the CLIENT field. 

I'd like to script a loop to export all records matching the Client name in the first record, then move onto the next Client name found and export those records. Some days there may be 5 different client names and other days there could be 2. 

Any help with advising what the correct step would be for splitting the found set would be great. 

 

thanks

[comment]

I would start by getting a list of clients in the current found set. This could be done by applying the UniqueValues() function on the result of a summary field defined as List of [Client].

Then I would loop over this list doing:

• open a new window;
• constrain the found set to the current client;
• export the records;
• close the window.

I presume you have some naming scheme to prevent the exported files from overwriting one another.

 

[Søren Dyhr]

On 3/21/2024 at 10:48 AM, comment said:

I would start by getting a list of clients in the current found set. This could be done by applying the UniqueValues() function on the result of a summary field defined as List of [Client].

But clients and orders would likely belong two different tables? Here's how I probably would do it:

 

NamedExports.fmp12

[comment]

3 hours ago, Søren Dyhr said:

But clients and orders would likely belong two different tables?

Why the "but"? Where is the contradiction to my suggestion??

 

3 hours ago, Søren Dyhr said:

Here's how I probably would do it:

You could do it that way. I would prefer to do it without adding the extra relationship.

OTOH, I like the idea of sorting and counting the constrained found set. That could eliminate the need for the summary field I suggested. However, it has a side effect of losing the original found set in the process. This is probably not a big deal when the find criteria is only the current date - but it could be a problem in other scenarios. So it might be better to open yet another new window before starting the loop.

 

 

 

Edited March 24 by comment

[LaRetta]

This was an enjoyable teaching thread - thanks guys.  

[Søren Dyhr]

9 hours ago, comment said:

Why the "but"? Where is the contradiction to my suggestion??

No - not you but when in the opening post was written: 

 

On 3/21/2024 at 9:27 AM, Smidge500 said:

Each record has a client assigned in the CLIENT field. 

Which in my book sounds a bit too spreadsheet'ish?

9 hours ago, comment said:

You could do it that way. I would prefer to do it without adding the extra relationship.

I wouldn't know if relationships taxes more than a summary field - but via relations in a new window seems easier ... in a found set scenario (which is pretty unique to the filemaker realm). But then let's take up the challenge and do it with ExecuteSQL instead ... and perhaps even flat-file'ish?

--sd

Edited March 24 by Søren Dyhr

[Smidge500]

Thanks for the suggestions. 

12 hours ago, Søren Dyhr said:

But clients and orders would likely belong two different tables?

I only have one table containing both clients and the "notes" field I'm exporting.

What I need to do is export all the notes for CLIENT1, then the loop goes back and exports all the notes that match CLIENT 2 etc. My found set is based on the date. 

 

On 3/21/2024 at 9:48 AM, comment said:

This could be done by applying the UniqueValues() function on the result of a summary field defined as List of [Client]

I've never used the UniqueValues() function before. How would I use this in the script? 

 

7 minutes ago, Søren Dyhr said:

Which in my book sounds a bit too spreadsheet'ish?

I don't quite understand how this relates? 

 

thanks

[Søren Dyhr]

59 minutes ago, Smidge500 said:

I don't quite understand how this relates? 

Could a consumer only place one single order pr. day? This urges for at one-2-many relationship, if normalization is attemptet? What happens if a customer unique by name have several shops to deliver to? What you are likely to break, is the one fact per field rule - say you simply lists the order in a single text field? the next obvious danger is typos in the customer name giving wrong replies from the database.

Here i my stab to the summary field approach, which then looses the multi-predicate selfjoin relationship, and the GTRR as well. But still exploits "found set" by opening and closing windows....

--sd

NamedExpFewRel.fmp12

Edited March 24 by Søren Dyhr

[comment]

1 hour ago, Smidge500 said:

I've never used the UniqueValues() function before. How would I use this in the script? 

You would do:

Set Variable [ $clients; Value:UniqueValues ( Items::sListClients ) ]

where sListClients is the summary field defined as list of the Client field.

The entire script, using my original suggestion, would look like this:

# FIND THE RECORDS TO EXPORT
// Go to Layout [ “Items” ] 
// Perform Find [Restore]

# SORT BY Client
Sort Records [ Restore ]

Set Variable [ $clients; Value:UniqueValues ( Items::sListClients ) ]

Loop
   Set Variable [ $i; Value:$i + 1 ]
   Exit Loop If [ $i > ValueCount ( $clients ) ]
   Set Variable [ $client; Value:GetValue ( $clients ; $i ) ]
   # ISOLATE RECORDS FOR CURRENT CLIENT
   New Window [ Style: Document; Using layout: <Current Layout> ]
   Constrain Found Set [ Restore ]
   # EXPORT
   Set Variable [ $path; Value:$client & ".csv" ]
   Export Records [ File Name: “$path” ]
   # RETURN TO FULL FOUND SET
   Close Window [ Current Window ] 
End Loop

 

1 hour ago, Smidge500 said:

I only have one table containing both clients and the "notes" field I'm exporting.

That could create a problem if you ever get two clients with the same name. Or even if an existing client changes their name; you would need to modify their name in all of their existing records, instead of performing a single change.

I thought your Client field actually holds a numeric ID of the client. If the field is a Text field holding the actual name of the client, you will need to specify an exact match when constraining the found set to isolate the current client. Otherwise you might be looking for John Smith but also find John Smithers - see: https://help.claris.com/en/pro-help/content/finding-text.html.

 

 

[comment]

3 hours ago, Søren Dyhr said:

Here i my stab to the summary field approach,

But the idea is to not add anything to the existing schema. You're still adding a summary field, except it counts instead of listing.

Here is how it could be done without requiring either one:

# FIND THE RECORDS TO EXPORT
// Go to Layout [ “Items” ] 
// Perform Find [ Restore ]

# SORT BY Client
Sort Records [ Restore ]

Go to Record/Request/Page[ First ]
Loop
   Exit Loop If [ not Get ( FoundCount ) ]
   Set Variable [ $client; Value:Items::Client ]
   # ISOLATE RECORDS OF CURRENT CLIENT
   New Window [ Style: Document; Using layout: <Current Layout> ] 
   Constrain Found Set [ Restore ]
   Set Variable [ $n; Value:Get ( FoundCount ) ] 
   # EXPORT
   Set Variable [ $path; Value:$client & ".csv" ]
   Export Records [ File Name: “$path” ]
   # RETURN TO FULL FOUND SET
   Close Window [ Current Window ]
   # OMIT RECORDS OF CURRENT CLIENT
   Omit Multiple Records [ $n ] 
End Loop

 

4 hours ago, Søren Dyhr said:

No - not you

But you quoted me...

 

 

Edited March 24 by comment

[Søren Dyhr]

30 minutes ago, comment said:

But you quoted me...

I did - sorry! 

....then to the approach with the omission of the summary field - excellent! However If we're talking very large files, here could the matter shift to another more pleasant execution time. just like the gains found here: https://filemakerhacks.com/2023/05/30/a-fresh-approach-to-deduplication/

But let's give the SQL method a stab as well? 

--sd

Edited March 24 by Søren Dyhr

[comment]

7 minutes ago, Søren Dyhr said:

the gains found here: https://filemakerhacks.com/2023/05/30/a-fresh-approach-to-deduplication/

Isn't that basically the same as my first script?

 

8 minutes ago, Søren Dyhr said:

But let's give the SQL method a stab as well? 

When the starting point is a found set? That would have to be a very special case for me to consider SQL.

 

[Søren Dyhr]

3 minutes ago, comment said:

Isn't that basically the same as my first script?

😜

--sd

[Smidge500]

1 hour ago, comment said:

The entire script, using my original suggestion, would look like this:

This is brilliant, works perfectly, thanks so much! 

[Søren Dyhr]

5 hours ago, Smidge500 said:

This is brilliant, works perfectly, thanks so much! 

If a "Set Error Capture [On] is missing just before the first Perform Find[ .... will this show up occationally:

--sd

Edited March 24 by Søren Dyhr

[Søren Dyhr]

On 3/24/2024 at 2:02 PM, comment said:

When the starting point is a found set? That would have to be a very special case for me to consider SQL.

I agree it's not really worth it. But I came up with another approach to the matter, we could use filtered portals and picking variables for the scripting with GetLayoutObjectAttribute ( as well as the "Save as Excel" function on an extra layout stripped all irrelevant fields shown.

--sd

NamedExpFiltered.Ffmp12.fmp12

Edited March 26 by Søren Dyhr