Combining multiple records into one

[zdub]

Hello,

I am new to FM and having some difficulty with combining data.

This is my issue... I am importing my orders from a csv file into my FM database. The CSV file has 3 to 4 rows of information (attribute options) for the same product.

How do I make one record of the product with all the attribute options? Would it be a calculation and if so, what would it be? Any help to point me in the right direction would be greatly appreciated. Thanks in advance

[Vaughan]

It depends on wheat the attributes are.

If they are things like colour and size (where the product can only have one of each) then it may be appropriate to put the data into a field, and there is a field for each attribute. If however the data is something like "features" where a product can have many (eg, product plays CD, DVD MP3, DIVX) then the data should probably go into related records.

[zdub]

Thank you for your response. That's exactly what the fields are (size and color) but the source file list the product twice one with the color the other with the size. I want to combine that info into one order in the target file instead of having the order listed twice or more depending on how many products and attributes are in each order... any ideas?

[comment]

An example of the source CSV would be useful.

[zdub]

Hi comment,

You can view the file here

[comment]

As Vaughan implied, you'll need to import the same file three times. This can be automated by importing into a temp table, then doing the three imports from there.

Use field validation to prevent duplicate records: in the Orders table, validate ORDER_ID as 'Unique, Validate always'. Do the same for the PROD_CODE field in the LineItems table. I am not sure why every line item has an additional row with no attributes, but if that's how it goes, validate the PROD_ATTR field in the LineItemAttributes table as 'Not empty, Validate always'.

[zdub]

Thank you, I will try it that way.