3 Replies Latest reply on Aug 29, 2011 2:09 PM by LaRetta_1

    How to split 3 popcorn flavors in single field into separate fields?

    straka_sh

      Title

      How to split 3 popcorn flavors in single field into separate fields?

      Post

      The field from an import looks like this... "Flavors selected: Caramel Corn, Cheddar Cheese Corn, Cashew Thunder"

      However, I'd like to create a calculation that splits it and puts each flavor into a new separate field...

      Eg:

      Caramel Corn

      Cheese Corn

      Cashew Thunder

      All imports will have the same layout... "Flavors selected: X, Y, Z" and there are about 15 different flavors that could be selected... but it doesn't matter what order they are in... can I use the ":" or "," in some way to split them? 

      Looking for your expertise!

       

        • 1. Re: How to split 3 popcorn flavors in single field into separate fields?
          LaRetta_1

          Hi Shannon,

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

          http://www.4shared.com/file/Y0wduL_j/Popcorn.html

          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:

          Let (  
           start = Position ( Products::TEMP ; ":" ; 1 ; 1 ) + 1  ;
          Trim ( Substitute ( Middle ( Products::TEMP ; start ; Length ( Products::TEMP ) - start + 1  ) ;  ", " ; ¶  )   )
          )

          • 2. Re: How to split 3 popcorn flavors in single field into separate fields?
            straka_sh

             LaRetta,

            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 !

            • 3. Re: How to split 3 popcorn flavors in single field into separate fields?
              LaRetta_1

              "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. :^)