Create new records using data from a multi-select field

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
When I save a record in a table, I would like to take items selected in a multi-select field in that table and create a record for each item selected into another table.  I tried an automation but it only grabs the first selected item.  I need it to iterate through each item selected and create a record for each.  Any help would be much appreciated.
Photo of John Reinagel

John Reinagel

  • 92 Points 75 badge 2x thumb

Posted 3 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,218 Points 50k badge 2x thumb
You can make 10 fields each with a formula like these here

var text value = totext([my multi select field]);

Trim(Part($value,1,";")),
Trim(Part($value,2,";")),
Trim(Part($value,3,";")),
Trim(Part($value,4,";")),
Trim(Part($value,5,";")),
Trim(Part($value,6,";")),
Trim(Part($value,7,";")),
Trim(Part($value,8,";")),
Trim(Part($value,9,";")),
Trim(Part($value,10,";")



Then fire an automation to create 10 records and then the 11th step will be to delete all the blank records as in many case you would have less than 10 valid records created so a bunch will be blank.
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,760 Points 3k badge 2x thumb
You can also use the formula that Mark has provided; and use it to create a formula that can be read by Quick Base as a csv import and leverage the Webhook automation feature to create child-table records with the values from your multi-select text field.  This is much cleaner than having 10 separate fields that run on separate automations.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,016 Points 50k badge 2x thumb
True, but this would be just 1 Automation with 11 steps, so not separate Automations.  Also there is a COPY feature to COPY a step :).  Also I'm not smart enough to remember the syntax for a Webhook - I have to go back to my cheat notes each time.  But you are correct, that your way can work too.
Photo of John Reinagel

John Reinagel

  • 92 Points 75 badge 2x thumb
I can see how the webhook would be clean, but have never used them.  Any good places to see examples of webhooks.  I can usually figure things out if I have a good example to work off of.
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,760 Points 3k badge 2x thumb
If you call me tomorrow I can walk you through a webhook setup and the import from csv formula setup I am referring to.
Photo of John Reinagel

John Reinagel

  • 92 Points 75 badge 2x thumb
Thanks!  What's the best number to reach you at?
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,760 Points 3k badge 2x thumb
Intelligentdbs.com has my contact info on it at the bottom of the page.
Photo of Jason

Jason

  • 1,406 Points 1k badge 2x thumb
There are many ways to do this and it always depends on the use case and how far down the rabbit hole you want to go. Both answers are great with one going a bit more down the rabbit hole technically. I am taking a different angle, what if I really didn't want this to occur every single time I hit save or I needed to review the record that other users are making before the other records are made (I am not a fan of going back to delete extra records). I went with making a button using a Formula Rich Text field. The button adds a record for each of the items selected (up to 10) and also fills in a field with the Record ID from the table of origin. (if using a table to table relationship you could populate the related field with the record ID to connect the records).
In the table of origin I made 2 fields, the one for the button and a checkbox field that is used to hide the button after being used.

Yes it a bunch of lines but it offers some good learning material if you have time to learn. I apologize if it is too much, I just wanted to do it and use my brain on something different today.

var text value = ToText([item choices]);

// splitting out the selections

var text valone = Trim(Part($value,1,";"));
var text valtwo = Trim(Part($value,2,";"));
var text valthree = Trim(Part($value,3,";"));
var text valfour = Trim(Part($value,4,";"));
var text valfive = Trim(Part($value,5,";"));
var text valsix = Trim(Part($value,6,";"));
var text valseven = Trim(Part($value,7,";"));
var text valeight = Trim(Part($value,8,";"));
var text valnine = Trim(Part($value,9,";"));
var text valten = Trim(Part($value,10,";"));

//how many records will I be creating

var number howmanyitems =
If($valten>"",10,
If($valnine>"",9,
If($valeight>"",8,
If($valseven>"",7,
If($valsix>"",6,
If($valfive>"",5,
If($valfour>"",4,
If($valthree>"",3,
If($valtwo>"",2,
If($valone>"",1,
0))))))))));

//creating the variables for adding records to the 'Send It Here' table, mostly boring repetition also it is going to edit the current record

var text URLONE = URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valone)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLEncode(URLRoot() & "db/"& Dbid() & "?a=API_EditRecord&rid="& [Record ID#]
& "&_fid_9=1"));

var text URLTWO = URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valone)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valtwo)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& Dbid() & "?a=API_EditRecord&rid="& [Record ID#]
& "&_fid_9=1")
));

var text URLTHREE = URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valone)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valtwo)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valthree)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& Dbid() & "?a=API_EditRecord&rid="& [Record ID#]
& "&_fid_9=1")
)));

var text URLFOUR = URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valone)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valtwo)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valthree)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valfour)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& Dbid() & "?a=API_EditRecord&rid="& [Record ID#]
& "&_fid_9=1")
))));

var text URLFIVE = URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valone)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valtwo)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valthree)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valfour)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valfive)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& Dbid() & "?a=API_EditRecord&rid="& [Record ID#]
& "&_fid_9=1")
)))));

var text URLSIX = URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valone)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valtwo)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valthree)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valfour)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valfive)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valsix)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& Dbid() & "?a=API_EditRecord&rid="& [Record ID#]
& "&_fid_9=1")
))))));

var text URLSEVEN = URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valone)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valtwo)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valthree)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valfour)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valfive)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valsix)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valseven)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& Dbid() & "?a=API_EditRecord&rid="& [Record ID#]
& "&_fid_9=1")
)))))));

var text URLEIGHT = URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valone)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valtwo)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valthree)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valfour)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valfive)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valsix)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valseven)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valeight)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& Dbid() & "?a=API_EditRecord&rid="& [Record ID#]
& "&_fid_9=1")
))))))));

var text URLNINE = URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valone)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valtwo)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valthree)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valfour)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valfive)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valsix)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valseven)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valeight)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valnine)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& Dbid() & "?a=API_EditRecord&rid="& [Record ID#]
& "&_fid_9=1")
)))))))));

var text URLTEN = URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valone)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valtwo)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valthree)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valfour)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valfive)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valsix)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valseven)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valeight)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valnine)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& [_DBID_SEND_IT_HERE]  & "?a=API_AddRecord"
& "&_fid_6="& URLEncode($valten)
& "&_fid_7="&[Record ID#]
& "&rdr="
& URLEncode(URLRoot() & "db/"& Dbid() & "?a=API_EditRecord&rid="& [Record ID#]
& "&_fid_9=1")
))))))))));

//which variable should I run

var text vartorun =
Case($howmanyitems,
1,$URLONE,
2,$URLTWO,
3,$URLTHREE,
4,$URLFOUR,
5,$URLFIVE,
6,$URLSIX,
7,$URLSEVEN,
8,$URLEIGHT,
9,$URLNINE,
10,$URLTEN
);

// Begin button style
var text bgcolor = "#808080";
var text bgcolorm = "#a1a1a1";
var text txtcolor = "white";

var text style = "style=\"text-decoration: none; box-shadow: 3px 3px 1px #888888;background:" & $bgcolor & "; color: " & $txtcolor & "; border-radius: 3px; padding: 5px 8px; display: inline-block;  font-weight: normal;font: 700 24px/1 \"Calibri\", sans-serif; \" onMouseOver=\"this.style.backgroundColor='" & $bgcolorm & "'\";\" onMouseOut=\"this.style.backgroundColor='" & $bgcolor & "'\"; text-align: center; text-shadow:none;";
// End button style

// make me hide the button if I have done this before

var number hidethebutton = If([stop me from making more records]=false,1,0);

// execute the record creation and return to where the button was pressed
If($hidethebutton=1,
"<a " & $style & " href=\"javascript:" & "$.get('" & $vartorun & "', function(){" & "location.reload();" &  "});" & "void(0);\">Create Records</a>"
,"")

Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,668 Points 3k badge 2x thumb
Jason & John; this is an illustration of the fact that there are many different ways to achieve things in Quick Base.  I spoke with John this afternoon; and his use-case is enter the text-options once on record-creation and to create the child records only once.  The list-formula & webhook solution I proposed to import those records in a single step works really well for that use case; but you have illustrated that there are always different techniques to do things and sometimes the complexity of what you might be doing will be a deciding factor in directing you to the implementation technique.  Also, choices become dependent sometimes on solutions you become familiar with implementing.  Well illustrated!