Import/Scripting/Design Problem

[Salesman0Gil]

Wow! It's been a while since I posted here... Since FM 11 was new... 

Any way. Working on automated process to bring info together, then export for another process. A new snag I don't know if I have ever solved before. 

I am exporting sales data. So the import file needs a line with customer data followed by multiple lines of product data.

Line 1: Customer 1 Data

Line 2: Customer 1 Product 1 Data

Line 3: Customer 1 Product 2 Data

Line 4: Customer 2 Data

Line 5: Customer 2 Product 1 Data

etc... 

 

I have all the data, but am unfamiliar with a method to script this..  

[Steve Martino]

What do you need as a separator.  

[comment]

15 hours ago, Salesman0Gil said:

I am exporting sales data. So the import file needs a line with customer data followed by multiple lines of product data.

This is quite confusing. I think you mean "the exported file needs ...".? Can't see what role importing plays here.

Also please update your profile to reflect your current version and OS. This could be very relevant to the question at hand. 

[Salesman0Gil]

Ok. Lets see if I can clear this up:

Importing: 

The source data arrives in csv form, this is brought into Filemaker to add additional data such as user data. This will grow into multiple different data sets, for instance monthly file for vendor 1 and a separate table for vendor 2. 

My original plan was to have separate tables for each vendor to modify the data into a uniform set then import into a shared table. 

Ideally, a script would import data from each set into a standard table designed for exporting. This would carry the load of formatting for the export. 

I can handle 99% of this, but I can not see a nice way of having a record that is essentially a header record with customer info, followed by product data records related to header record, then do this across 500 lines of data. 

The export needs to be a csv. 

 

Thanks for the help!

[comment]

If you have a flat table with sales data, and one of the fields is customer name (or something that uniquely identifies the customer), you can produce the text for your export by doing the following:

1. Find the records you want to export;

2. Sort by customer;

3. Go to the first record and loop: 

a. if $customer is not equal to the current record's customer, add a line with customer data to $output and set $customer to the current record's customer;

b. add a line with product data to $output;

c. go to next record (exit after last).

At the end of the loop, the $output variable will contain the text for your export. Now the question becomes how to export it. You could place it in a global text field and export the field's contents - but the result will be UTF-16 encoded and not all applications know how to handle this.

Note also that when creating a .csv output, you need to handle fields that may contain commas and/or quotes.

 

 

 

 

Edited July 25, 2019 by comment