Export and Looping

[Mark Hardee]

I am approaching the end of a project and have run accross a (to me) barrier. FM 10 ADV... Couple hundred thousand records. There is a field that identifies the particular store. There are hundreds of stores each with thousands of records..

I have exports working as needed. What I need to do is:

Select from all of the records STORE 1

Export its records to a file

Loop through the entire database and select records by EACH store number and export ITS data...

I will never know which stores are in the current group of data......

Any ideas?

I appreciate any help or direction anyone can point me in....

[comment]

I will never know which stores are in the current group of data......

Not sure what you mean by "current group of data" - esp. when you say:

Loop through the entire database

Do you have another table of Stores, where each store is a unique record?

[Mark Hardee]

Do you have another table of Stores, where each store is a unique record?

Not at the moment, but, I wish I had the data for that.. Currently it is in the flat data that is client supplied, and you KNOW they know what they are doing, right? :-)

After reading my post it wasn't very clear... for example:

20,000 records - a field labeled STORE which contain the store number. ( Possibility of 300 - 400 stores - but not every one places orders each day) Of that 5,000 are for STORE 1, 1700 are for STORE 3, and so on. The number of stores varies with each data import and export scenario.. daily... So, let's say Monday we import 10K records, where the STORE field is populated with "orders" for one of any 200 stores. What I need to accomplish is to export the data for EACH store to an Excel file for further processing (variable data print job)...

So, either manually find and export data for each of the 200 possibilities or be able to evaluate the content of the STORE field, select matching records, export, perform find for next store, export, and so on until all data has been exported. So if "today's" data had 26 stores with orders that happened to have 100 records for each store, I would end up with 26 excel files and each excel file would contain 100 records..

The equivalent of:

FOR STORE = 1

DO WHILE STORE = 1

EXPORT

NEXT STORE

[comment]

let's say Monday we import 10K records

If I understand correctly, you only want to export the 10k records of the last import. This would rule out the option to

perform find for next store

unless you also have a field that identifies the import batch.

I think I would do it this way: after importing, while the found set contains only records of the last import:

Sort Records [ by Store ]

Loop

Exit Loop If [ not Get (FoundCount) ]

Go to Record [ First ]

Set Variable [ $n ; GetSummary ( sCount ; Store ) ]

New Window

Go to Record [ $n + 1 ]

Omit Multiple Records [ Get (FoundCount) - $n ]

Export Records

Close Window

Omit Multiple Records [ $n ]

End Loop

where sCount is a summary field defined as count of Store (or any other field that cannot be empty).

[Mark Hardee]

Thanks... with the help of a saviour, Weetbix, who has pulled my can out of the fire more than once, I got it figured out.. Valuelist...

Ended up with:

Set Variable [ $Stores; Value:ValueListItems ( Get ( FileName ) ; "STORES" ) ] 

Go to Layout [ “IMPORT” (IMPORT) ] 

Show All Records 

Set Variable [ $Total; Value:ValueCount ( $Stores ) ] 

Loop 

Exit Loop If [ Let ( $Counter = $Counter + 1 ; $Counter > $Total ) ] 

#Find all stores for current loop ... 

Enter Find Mode [ ] 

Set Field [ IMPORT::STORE; GetValue ( $Stores ; $Counter ) ] 

Set Error Capture [ On ] 

Perform Find [ ] 

Set Error Capture [ Off ] 

#If we found records for this store, lets export them now... 

If [ Get ( FoundCount ) > 0 ] 

Set Variable [ $export; Value:"file:EXPORTS/STORE_" & IMPORT::STORE &"_"& Year(Get(CurrentDate)) &"_"& Right("0" & Month(Get(CurrentDate)); 2) & Right("0" & Day(Get(CurrentDate)); 2) & "_" & Right("0" & Hour(Get(CurrentTime)); 2) & Right("0" & Minute(Get(CurrentTime)); 2) & ".xlsx" ] 

Export Records [ File Name: “$export”; Use field names as column names; Author: " "; Character Set: “Unicode (UTF-16)”; Field Order: FIELDNAMES OMMITED ] [ No dialog; Format output using current layout ] 

End If 

End Loop 

[bcooney]

comment's method is better, imho, in that it accounts for an export of only the last import. However, we're guessing at your workflow bcs you're not providing that information. How are you avoiding exporting a record twice? Do you delete all recs in the import table after you export? Also, how is the value list "STORES" defined? Also, why are you going to Excel? Seems as if you could stay in FM and do the analysis required.

[comment]

Obviously, you are exporting ALL records in the table - so the import has nothing to do with the issue. I am somewhat puzzled by this part:

...

#If we found records for this store, lets export them now... 

If [ Get ( FoundCount ) > 0 ] 

...

Since you said there is no Stores table, I suppose the value list is based on the Store field in the flat table. Thus it is not possible for a find to return an empty found set.

[Mark Hardee]

comment's method is better, imho, in that it accounts for an export of only the last import. However, we're guessing at your workflow bcs you're not providing that information. How are you avoiding exporting a record twice? Do you delete all recs in the import table after you export? Also, how is the value list "STORES" defined? Also, why are you going to Excel? Seems as if you could stay in FM and do the analysis required.

Avoiding export the record twice by this section:

Set Variable [ $Stores; Value:ValueListItems ( Get ( FileName ) ; "STORES" ) ]  

Go to Layout [ “IMPORT” (IMPORT) ]  

Show All Records  

Set Variable [ $Total; Value:ValueCount ( $Stores ) ]  

Loop  

Exit Loop If [ Let ( $Counter = $Counter + 1 ; $Counter > $Total ) ]  

#Find all stores for current loop ...  

Enter Find Mode [ ]  

Set Field [ IMPORT::STORE; GetValue ( $Stores ; $Counter ) ]  

Set Error Capture [ On ]  

Perform Find [ ]  

Set Error Capture [ Off ]  

#If we found records for this store, lets export them now...  

If [ Get ( FoundCount ) > 0 ]  

Getting the unique value of store "name" by using a valuelist, counting it, performing a find on the first value, exporting it and moving down the list and looping the find and export. When the value list has been looped to the end, all data has been accounted for and exported...

Why go to excel? That's what the client wishes and the exported data will then be imported into a variable data print application. I've done the VDP stuff for years and the basis for a successful workflow is feeding it clean, precise data... that's where Filemaker comes in.. Reports are still generated in Filemaker for analysis, job tickets, workflow verification and checklists..

This whole deal (Value Lists) opens up a whole world of possibilities for segregating data and knowing that you have all and only the data you are seeking as the value list contains ONLY unique values..

[Mark Hardee]

Obviously, you are exporting ALL records in the table - so the import has nothing to do with the issue. I am somewhat puzzled by this part:

...

#If we found records for this store, lets export them now... 

If [ Get ( FoundCount ) > 0 ] 

...

Since you said there is no Stores table, I suppose the value list is based on the Store field in the flat table. Thus it is not possible for a find to return an empty found set.

Yes, I am exporting all of the records although they are exported to multiple files differentiated by the store number.

As I see it, it gives the opportunity to perform the loop.. while we are finding data by looping through and changing the counter values, exports are done. Once it get's to the end of the "Stores" list (no more valuelist entries) it will find "0" records and give the script a way to proceed to the Endif...

As I stated, it works like a MURF! I will try the other method as well... one can never have too many ways to accomplish a given task!

THANKS to all that replied.. I hope one of these days I can attain the knowledge to help out...

[comment]

I think what Barbara meant is that when you repeat the process, the same records are exported again and again. So today you may have exported 25,000 records for Store 1, then you import a batch that contains 3,000 records of Store 1, and now you export 28,000 records - with 25,000 records being repeats of the previous export. Perhaps that's what you need - but the point is not quite clear (even though that was the first question I asked).

[Mark Hardee]

I think what Barbara meant is that when you repeat the process, the same records are exported again and again. So today you may have exported 25,000 records for Store 1, then you import a batch that contains 3,000 records of Store 1, and now you export 28,000 records - with 25,000 records being repeats of the previous export. Perhaps that's what you need - but the point is not quite clear (even though that was the first question I asked).

Oh, I get her meaning now... But, the workflow in this scenario is after the records are exported we are now done with this data. A backup of the Filemaker file is made, ALL records are deleted and the new day's data is imported.. Each day is a , well, a new day!

I see that if we were to want to keep all of the data, we could set a record as exported and then loop through only those records that did not have that flag set.. thus not adding already exported data to the "newly added" data for that day..

Mark

[bcooney]

Yes, i was wondering how you were avoiding duplicate exports.