seperating data in one field to multiple fields

[zdub]

Hi I am new to filemaker- I've dabbled with it a few times but now want to build a database for orders, customers and inventory.

I am importing a csv file from my ecommerce platform and one of the fields has the 'product details' in it which include:

"Product ID: 44, Product Qty: 1, Product SKU: xxx-, Product Name: xxx xxx, Product Weight: 0.5, Product Variation Details: Size: Large, Product Unit Price: 39.99, Product Total Price: 39.99 | Product ID: 55, Product Qty: 1, Product SKU: xxx_xx, Product Name: xxx: xxx, Product Weight: 0.5, Product Variation Details: Size: Large, Product Unit Price: 24.99, Product Total Price: 24.99"

Each product is seperated with "|". One order can have multiple products or just one. I would like each detail seperated by a comma to be it's own field- just the value not the name. I have no idea of how to start- any help/ direction would be appreciated.

[doughemi]

Create a utility table Product Details. It will have two fields:

CSV_details_raw (text)

CSV_details_list, calculation = Substitute(CSV_details_raw; "|"; ¶)

Create a new TO of this table and name it Product Details 2. Create a Cartesian (X) relationship between Product Details and Product Details 2

Add a new field in Product Details:

CSV_details_listAll, calculation = List(Product Details 2::CSV_details_list)

Relate Product Details to a Sales table with a Cartesian (X) relationship. The Sales table will have fields for each of the product detail items: Product ID, Product Qty, etc.

Create a script

    Set Variable [ $count; Value:ValueCount ( Product Details::CSV_details_listAll ) ]

    Loop

	Set Variable [ $lineNo; Value:$lineNo + 1 ]

	Exit Loop If [ $lineNo > $count ]

	Set Variable [ $row; Value:Substitute(GetValue ( CSV_details_listAll ; $lineNo ); ","; ¶) ]

	Go to Layout [ “SalesLayout” (Sales) ]

	New Record/Request

	// The calculation for each Set Field step strips the column name from the CSV value

	Set Field [sales::Product ID; GetAsNumber ( GetValue ( $row; 1 )) ]

	Set Field [sales::Product Qty; GetAsNumber ( GetValue ( $row; 2 ))]

	Set Field [ Sales::Product SKU; Let([value =(GetValue($row;3); colonpos = Position( value; ":";1;1)]; Right(value; Length(value)-colonpos))]

	// Etc, with fields containing number information handled like Product ID, and fields containing text info handled like Product SKU

	End Loop

Import the data from the CSV's Product Details into the CSV_details_raw field, and run the script. Note that if you want to import another CSV, you will have to delete all records from the Product Details table first.

I can't take credit for all of this; I modified it from this post, which I found with a Yahoo search for "filemaker parse records from single field".

[zdub]

Thank you so much- I'm learning relationships between tables so I'll follow this with the manual close by. I'll report back. Thanks again!