Convert comma separated values to new child records

We would suggest using a combination of a set of fields to extract out the individual Parts of each string and then an Automation to create the child records.

You would have to make an assumption about the maximum number of individual parts on any data upload.

I am just on my iPhone now so I can�t use the formula but you can use the part Function to extract out for example the first part of that, separated field. Then he would have another field for the second part in the third part all the way up to the 10th part.

Then similar that you would extract out 10 fields for the 10 quantities.

Then you would have an automation to write out those 10 records.

But of course in many cases there will not be the full complement of tan line items to be created. So the very last automation could sweep the line items and delete out line items with a blank part number and a blank quantity.

This was dictated to Siri so I hope it�s understandable. Post back if you have any questions.

Ok so i understand where you are getting at with the first part using the Part formula.  If i assume a maximum of 10 line items, ill create 20 fields (10 for each line item and 10 for each quantity).

The second part will write an automation to have each pair of fields converted into a child record.  not sure how this would look, but i'll check it out.

The last part you have got me though regarding the automation deleting line items, please explain?  Are you saying that if only 3 items are created the 4th automation will overwrite the previous ones?

The 11th step in the Automation will delete any child records with blank descriptions

The parts would be extracted out like

Trim(Part([my comma separated field],1,","))

then the 2nd part would be

Trim(Part([my comma separated field],2,","))

and the numerical quantities would be

tonumber(Trim(Part([my commas separated numerical field],1,",")))

Thanks for this :)

This is easy to do using a Rich Text Formula Field using the 3Q&S Techinque used as a button to invoke a script. The demo below contains three buttons named Demoize, Childize and Parentize place on the view record page. The Demoize button resets the demo and initializes the [Items] and [Quantities] to randomly generates values. The Childize button takes the [Items] and [Quantities] in the Parent record and transfers the values to individual child records. The Parentize button reverses this process.

Here is a screenshot after pressing the Demoize button and generating random [Items] and [Quantities] in the Parent record:



Here is a screenshot after next pressing the Childize button and transferring the [Items] and [Quantities] values in the Parent record to individual Child records:



Pressing the Parentize button will reverse the Childize process.

Here is the demo in which you can test and even create your own record:

Childize / Parentize ~ View Parent Record #1
https://haversineconsulting.quickbase.com/db/bpcesdwqm?a=dr&rid=1

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=726

Notes:

(1) This demo has features beyond what you asked for but are appropriate for a demo that hopefully will not require a lot of maintenance on my part.

(2) The same core approach can be used to perform bulk operations and a selection or records rather than an individual record.

(3) You probably need some additional logic and features to deal with edge case in your data or other feature that were not articulated.

This is really impressive, thank you.  Ill most certainly take a look at this as well.  Is there any way to automate this process without pressing the button?

Hi Dan.  I'm busy implementing this but I have hit a small snag:

In your example, you work with 2 tables [Parent] and [Child].  I have 3 tables:

[Parent]-<[Line items]>-[Child] - > with the line items table being the connector of the 2.  I've done this because I have a set list of items that can be chosen from the child record.

How would one work this type of scenario?

I think you could do this w/ a single formula field and a webhook.

Building on Mark's original formula suggestions, you could do something like this:

List("\n",  If(Trim(Part([my comma separated field],1,","))<>"",  Trim(Part([my comma separated field],1,",")) & "," & tonumber(Trim(Part([my commas separated numerical field],1,",")))  ),    If(Trim(Part([my comma separated field],2,","))<>"",  Trim(Part([my comma separated field],2,",")) & "," & tonumber(Trim(Part([my commas separated numerical field],2,",")))  ),    If(Trim(Part([my comma separated field],3,","))<>"",  Trim(Part([my comma separated field],3,",")) & "," & tonumber(Trim(Part([my commas separated numerical field],3,",")))  )    ) // end list    ...etc., as high as you'd like to go...

This should give a list of all items and quantities, one per row. Then you would set up a webhook using API_ImportFromCSV to create all of the child records at once, based on that formula.

Yes, I forgot about web hooks. That would be a great solution.

The formula for the records to be updated with also need to include the two fields for the related parents.

But CSV is the way to go.