"that splits it and puts each flavor into a new separate field."
These flavors should be records instead of fields (you have 6; maybe 15; maybe more). Here is a file showing how to turn that text string into related records.
This file assumes the text string is imported into the Products field called TEMP. It then takes the selections for this specific product and creates its child selections. Note that, since I have no real information as to your structure or needs, I assumed there may be different products other than Popcorn and I assumed there may be different types of selections other than flavors so I designed for flexibility.
If I'm missing the mark, please explain a bit more. :^)
If you are looking for only the calculation which turns only the selected items into multiline, it is in the script where I set the $list variable but here it is for quick reference:
start = Position ( Products::TEMP ; ":" ; 1 ; 1 ) + 1 ;
Trim ( Substitute ( Middle ( Products::TEMP ; start ; Length ( Products::TEMP ) - start + 1 ) ; ", " ; ¶ ) )
Thanks for your response...
Actually, it is only for popcorn. We produce popcorn products for fundraisers, etc... different cheese flavors, chocolate covered popcorn, etc...
We track the units who are selling with Filemaker... we track the # of each product sold and the $ sold via Filemaker. However, some customers who would like to buy via the Internet do so through a Dreamweaver type website, which is then sent/imported through a Mail Order Management program and imported into Filemaker. We only sell two size decorative tins in this way. (The vast majority is door to door via Boy Scouts)... Each size tin has up to 3 flavors (out of 15 choices) that can be selected by the customer. (In reality, a customer could also just check the box by one or two flavors and then get a tin full of just one or two types of popcorn).
The import we get from the Mail Order Management site gives us "Flavors selected: X, Y, Z" in one field. We just need to separate the three flavors from one field into separate entities...
As of right now, we import:
Customer #, Scout Code, Last Name, First Name, Address, City, State, Zip, Phone, Card type, Paid, Expire Date, Order Date, Order #, Ship Via, Ship Amt, Price 01, Qty 01, Price 02, Qty 02, Price 03, Qty 03, Price 04, Qty 04, Price 05, Qty 05, Email, Flavors Selected...
Of course, it is Flavors Selected that is giving us fits...
This is all we are selling... 2 sizes of tins... each filled with 3 of 15 flavors... (but could theoretically have only 1 or 2 flavors if the customer only chooses that many).
Hopefully, this makes our issue less vague. If your answer is still unchanged, thanks !
"I'd like to create a calculation that splits it and puts each flavor into a new separate field..."
Why? I ask because the purpose determins the method. If you already have fields such as Price 01, Qty 01 then you are already working with imported data which is not properly normalized. Why 5?
"Price 01, Qty 01, Price 02, Qty 02, Price 03, Qty 03, Price 04, Qty 04, Price 05, Qty 05,"
You can reduce these 10 fields down to 2 and make your life and all work in these tables hereafter much easier by using related records. Is that what you are trying to achieve by splitt the flavors out into fields? Do you want to actually split the flavors or just display the flavors in a different way? Are these choices priced differently? How do you know if Qty 01 is for Cashew Thunder in small tin?
What I explained does what you requested originally (in blue). Are you saying that you have fields called Flavor 01, Flavor 02, Flavor 03 and you want the flavors put into those fields? Again, that is flat-file design and will PIB to administer.
Please explain a bit more. :^)