Forum Discussion

GeoffreyHarmuth's avatar
GeoffreyHarmuth
Qrew Captain
7 years ago

Convert comma separated values to new child records

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?


Forms
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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.
  • GeoffreyHarmuth's avatar
    GeoffreyHarmuth
    Qrew Captain
    7 years ago
    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?


  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    The 11th step in the Automation will delete any child records with blank descriptions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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,",")))

  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    7 years ago
    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.
  • GeoffreyHarmuth's avatar
    GeoffreyHarmuth
    Qrew Captain
    7 years ago
    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?
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      7 years ago
      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.
  • GeoffreyHarmuth's avatar
    GeoffreyHarmuth
    Qrew Captain
    6 years ago
    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?




    • GeoffreyHarmuth's avatar
      GeoffreyHarmuth
      Qrew Captain
      6 years ago
      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
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      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.
  • DanLadner1's avatar
    DanLadner1
    Qrew Trainee
    6 years ago
    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.