Convert comma separated values to new child records

  • 0
  • 1
  • Question
  • Updated 5 days 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,980 Points 1k badge 2x thumb

Posted 6 months ago

  • 0
  • 1
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,980 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?


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,",")))

Photo of Geoffrey

Geoffrey

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

Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 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,980 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

  • 30,224 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.
Photo of Geoffrey

Geoffrey

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




Alas, we have not seen Dan on the forum in months.
Photo of Geoffrey

Geoffrey

  • 1,980 Points 1k badge 2x thumb
Hi Mark.  Is there any way i can get this to work without doing automation?  I dont want to go and add another 60 odd fields just to cater for the unknown limitation of items being added.


I like Dans idea, but it exceeds my current skill level to implement.  Dont mind working off line if you feel its too complicated to post here.

Will appreciate the help
It can be done with script or natively.

Natively, the only way I know is to set a maximum number of children to be created, perhaps a number under 50 as an Automation can only have 50 steps, unless you want to have one Automation trigger a second Automation to add more. Then use an Automation to add those children one by one.

Or else you will need to contact someone with scripting skills, perhaps Chase from Quandry who is quite active in the formula lately .

I see everything though a low code lens, so it it wuz me, I would just hunker down and would have it running in well under an hour. The fields to do the Parts would be very respective in format, so it’s just the brute force of copying the formula field and editing it slightly and then for the Automation, an Automation step can also be copied to make the slight adjustment for each step.

Not elegant, admittedly, but it’s no code and easy to maintain.

But it will not handle a data feed with unlimited parts to be added, so you would need to have a subscription or email to notify someone when data comes in with more Parts that the Automation can handle.
Photo of Dan

Dan

  • 1,580 Points 1k badge 2x thumb
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.
Photo of Dan

Dan

  • 1,580 Points 1k badge 2x thumb
Good call, wouldn't work too well without the reference field values!
Photo of Geoffrey

Geoffrey

  • 1,980 Points 1k badge 2x thumb
ok, im getting a better understanding of this.  I've never done a webhook such as this.  Is there any example you may have?

I’m just heading into a client site right now so I don’t have time to respond with an example.

But my advice includes the following.

Have a look at the Online documentation for the import from CSV API and it will give you an example of the syntax to use near the bottom of the documentation.

You will need to include a user token because everything in Quick Base including web hooks hast to run under some user IDs authority.

If your application requires application tokens, and that is set in the advance properties tab at the application level, then you will also need to provide to user token.
Photo of Geoffrey

Geoffrey

  • 1,980 Points 1k badge 2x thumb
Thanks Mark.  I'll give it a go
It is always a bear to get your first webhook working.

I had some notes to myself of a Webhooks I did for a client

bxv4tx_bjix_8csgf2bk9smkxxxxxxxxxx3r

%RepeatOn%

"[Last Modified By]","Client Name","[old.Client Name]", "[Client Name]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Site Name","[old.Site Name]", "[Site Name]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Contact Name","[old.Contact Name]", "[Contact Name]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Contract Name","[old.Contract Name]", "[Contract Name]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Contract Type","[old.Contract Type]", "[Contract Type]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Industry","[old.Industry]", "[Industry]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Primary Project Manager","[old.Primary Project Manager]", "[Primary Project Manager]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Secondary Project Manager","[old.Secondary Project Manager]", "[Secondary Project Manager]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Large Contract Approver","[old.Large Contract Approver]", "[Large Contract Approver]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Estimator","[old.Estimator]", "[Estimator]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Projected Start Date","[old.Projected Start Date]", "[Projected Start Date]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Projected End Date","[old.Projected End Date]", "[Projected End Date]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Contact Status","[old.Contract Status]", "[Contract Status]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Lock Editing","[old.Lock editing]", "[Lock editing]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Award Date","[old.Award Date]", "[Award Date]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Customer PO","[old.Customer PO]", "[Customer PO]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Required Estimate Submission Date","[old.Required Estimate Submission Date]", "[Required Estimate Submission Date]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Revised Estimate Submission Date","[old.Revised Estimate Submission Date]", "[Revised Estimate Submission Date]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Driving Requirements","[old.Driving Requirements]", "[Driving Requirements]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","One Way Miles to Site","[old.One Way Miles to Site]", "[One Way Miles to Site]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","General Contract Notes","[old.General Contract Notes]", "[General Contract Notes]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Specific Location(s) of Work at Client Site","[old.Specific Location(s) of Work at Client Site]", "[Specific Location(s) of Work at Client Site]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Work Being Performed","[old.Work Being Performed]", "[Work Being Performed]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Purpose of Work being Performed","[old.Purpose of Work being Performed]", "[Purpose of Work being Performed]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Explanation for change to Required Submission Date","[old.Explanation for change to Required Submission Date]", "[Explanation for change to Required Submission Date]", "CO","[Record ID#]","[Record ID#]"

"[Last Modified By]","Specific Location(s) of Work at Client Site","[old.Specific Location(s) of Work at Client Site]", "[Specific Location(s) of Work at Client Site]", "CO","[Record ID#]","[Record ID#]"

%RepeatOff%

6.7.8.9.10.11.14
Photo of Dan

Dan

  • 1,580 Points 1k badge 2x thumb
As Mark mentioned previously, you'll need to update the formula I provided with the record ID# of the parent record, so the created records will be linked back properly. That would look like this:
List("\n",
If(Trim(Part([my comma separated field],1,","))<>"",
[Record ID#] & "," & Trim(Part([my comma separated field],1,",")) & "," & tonumber(Trim(Part([my commas separated numerical field],1,",")))
),
If(Trim(Part([my comma separated field],2,","))<>"",
[Record ID#] & "," & Trim(Part([my comma separated field],2,",")) & "," & tonumber(Trim(Part([my commas separated numerical field],2,",")))
),
If(Trim(Part([my comma separated field],3,","))<>"",
[Record ID#] & "," & Trim(Part([my comma separated field],3,",")) & "," & tonumber(Trim(Part([my commas separated numerical field],3,",")))
)
) // end list
This should give output similar to this, where the Job Card in question has a RID# of 500:
500,Screws,2
500,Plugs,3
500,Nuts,1
The webhook would look something like this--your "clist" values will depend on the field IDs in your child table:
<qdbapi>
<usertoken>USERTOKENHERE</usertoken>
<clist>6.10.15</clist>
<records_csv>
<![CDATA[
%RepeatOn%
[THE BIG LIST FORMULA FIELD]
%RepeatOff%
]]>
</records_csv>
</qdbapi>
In this example above, these are the field mappings in the child table:
Related Job Card: 6
Item: 10
Quantity: 15

Hope this makes sense.

(Edited)
Photo of Geoffrey

Geoffrey

  • 1,980 Points 1k badge 2x thumb
Thanks this does.  I'll give it a crack...  You guys have been a great help thank you
Photo of Harrison

Harrison

  • 602 Points 500 badge 2x thumb
Quick FYI that Usertokens supersede Apptokens. You don't need an Apptoken if Usertoken is passed.
Photo of Geoffrey

Geoffrey

  • 1,980 Points 1k badge 2x thumb
Hi Dan.  So i go the formula and output to work perfectly, but the webhook doesnt seem to be working.  I have tried everything, but no luck.

Am i missing something?
You need to use the correct API.
API_ImportFromCSV

https://help.quickbase.com/api-guide/...
Photo of Geoffrey

Geoffrey

  • 1,980 Points 1k badge 2x thumb
So i tried it and nothing gets posted.  Not sure why
Photo of Dan

Dan

  • 1,580 Points 1k badge 2x thumb
"Quick Base-Action" should be "QuickBase-Action" (remove the space)
Photo of Hongkun liang

Hongkun liang

  • 182 Points 100 badge 2x thumb
Hi Dan,
For API_ImportFromCSV, If one of the values in the single formula field (CSV file) has comma, how to handle this.   I try to add double quote for each value, it does not work.
Any ideal on this?   Thanks!
Photo of Dan

Dan

  • 1,580 Points 1k badge 2x thumb
I think you need to add ".csv" to the end of the formula name:
[THE BIG LIST FORMULA FIELD.csv]