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

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, 20205 yr by comment

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.

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, 20205 yr by comment

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.

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