Convert comma separated values to new child records

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
So i have a Table for Job cards and a second child table for parts used.  These 2 tables are connected with a Line items table:



Job Card --<Line Items >--Parts


I have a third party app that automatically pushes information into the Job card table when employees have completed a job card on site.

The problem I am having is that the line items get posted into quick base separated by commas and the number of units in a different field also separated by commas.


this is how it comes into quickbase:

Field 1[Item] :        Screws,Plugs,Nuts,Pipe
Field 2[Quantity] :  2,3,1,1


so I want to create 4 line item records onto the Job card that shows

a. Screws x 2
b. Plugs x 3
c. Nuts x 1
d. Pipe x 1


Is there any way I can take the 2 bits of information and create a child record to bring line items into the job card?


Photo of Geoffrey

Geoffrey

  • 1,820 Points 1k badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
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.
Photo of Geoffrey

Geoffrey

  • 1,820 Points 1k badge 2x thumb
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?


Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
The 11th step in the Automation will delete any child records with blank descriptions
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
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,",")))

Photo of Geoffrey

Geoffrey

  • 1,820 Points 1k badge 2x thumb
Thanks for this :)

Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,024 Points 20k badge 2x thumb
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.
(Edited)
Photo of Geoffrey

Geoffrey

  • 1,820 Points 1k badge 2x thumb
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?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,964 Points 20k badge 2x thumb
You can initiate bulk conversions (multiple records) from the press of a single button but there is no way to have an automatic trigger of the bulk action as QuickBase does not currently have the ability to run a user supplied script as the result of records being added or changed.