Parsing delimited data in a single field on import

[-dp-]

I need to import data from a Google Forms survey. One of the fields is a text field containing multiple semi-colon-delimited values (e.g., "basketball;football;track & field;volleyball"). I need to break these out into separate records in a related table. The only idea I've come up with is to capture the field contents into a variable, count the values (using a method I have not yet figured out), create a related record with the leftmost value (the method of identifying which I have not yet figured out, given that some values contain multiple words and spaces), delete the leftmost value , increment a counter, then loop until the counter reaches a number equal to the number of values originally in the field. I can see lots of ways for this to break. Before I start slogging through it, I wanted to see if anyone has a better idea. Thanks.  

FMPA 18, macOS 10.15.3

[comment]

What you describe is more or less the way to go. Althought it can be a bit simpler if you start by substituting the semicolons with carriage returns. Then you can do something like:

Set Variable [ $parentID ; YourTable::ID ]
Set Variable [ $values ; Substitute ( YourTable::Yourfield ; ";" ; ¶ ) ]
Set Variable [ $n ; ValueCount ( $values ) ]
Go to Layout [ ChildTable ]
Loop 
  Set Variable [ $i ; $i + 1 ]
  Exit Loop If [ $i > $n ]
  New Record
  Set Field [ ChildTable::ParentID ; $parentID ]
  Set Field [ ChildTable::Value ; GetValue ( $values ; $i ) ]
End Loop
Go to Layout [ original layout ]

Of course, this assumes that the values themselves do not contain any carriage returns - otherwise you need to substitute them with a placeholder first.

 

Edited February 27, 2020 by comment

[-dp-]

It's good to know that I was on the right track, but this is much cleaner than where I was going. I was not aware of the ValueCount function. Thank you for educating me.

[comment]

BTW, although proper relational structure is certainly encouraged on these pages, you may also consider just replacing the semicolons with carriage returns and formatting the field as a checkbox set (as I presume it appeared on the original form).

 

Edited February 28, 2020 by comment

[-dp-]

Tempting, but every time I've done something like that in the past, it has eventually come back to bite me. I'll need to produce some charts, so I think that spinning off a separate table is the way to go. But thanks for the idea.

[-dp-]

I just adapted your script into my solution, and it worked perfectly. Thanks very much.