Jump to content

Import/Scripting/Design Problem


This topic is 1709 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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..  

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

This topic is 1709 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.