Discussions

Expand all | Collapse all

Create new records using data from a multi-select field

  • 1.  Create new records using data from a multi-select field

    Posted 01-29-2019 21:55
    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.


  • 2.  RE: Create new records using data from a multi-select field

    Posted 01-29-2019 22:23
    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.


  • 3.  RE: Create new records using data from a multi-select field

    Silver
    Contributor
    Posted 01-29-2019 22:26
    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.


  • 4.  RE: Create new records using data from a multi-select field

    Posted 01-29-2019 22:32
    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.


  • 5.  RE: Create new records using data from a multi-select field

    Posted 01-29-2019 23:13
    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.


  • 6.  RE: Create new records using data from a multi-select field

    Silver
    Contributor
    Posted 01-29-2019 23:41
    If you call me tomorrow I can walk you through a webhook setup and the import from csv formula setup I am referring to.


  • 7.  RE: Create new records using data from a multi-select field

    Posted 01-29-2019 23:54
    Thanks!  What's the best number to reach you at?


  • 8.  RE: Create new records using data from a multi-select field

    Silver
    Contributor
    Posted 01-30-2019 00:03
    Intelligentdbs.com has my contact info on it at the bottom of the page.


  • 9.  RE: Create new records using data from a multi-select field

    Posted 01-30-2019 21:06
    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&r


  • 10.  RE: Create new records using data from a multi-select field

    Silver
    Contributor
    Posted 01-30-2019 22:26
    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!