Forum Discussion

MelissaFreel1's avatar
MelissaFreel1
Qrew Cadet
5 years ago

Adding Multiple Child Records with CB in Parent and Lookups in Child

Hi all: (edit for more clarity) I would like automatically add between 1 and 9 new records to a child table when a modification has been made to the parent record using 1-9 check box fields. I tri...
Relationships
MelissaFreel1's avatar
MelissaFreel1
Qrew Cadet
5 years ago
Hi Mark:
I edited my original post and yes a formula url could do it too, just unfamiliar with nesting all action steps to do it particularly with the child field being "lookup" type fields

------------------------------
Melissa Freel
------------------------------
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
5 years ago
Ok, here goes.
We will need to combine a few Techniques and then also hope that the resulting URL is not too long.  There is a length limit or # of redirect limits that's a bit fuzzy. 

Firstly you need 10 steps.  Nine to do the up to 9 children and then a tenth to redisplay the record.
​
$URLONE
& "&rdr=" & URLEncode($URLTwo)
& URLEncode("&rdr=" & URLEncode($URLThree))
& URLEncode(URLEncode("&rdr=" & URLEncode($URLFour)))
& URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLFive))))
& URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSix)))))
& URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSeven))))))
& URLENcode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLEight)))))))
& URLENcode(URLENcode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLNine))))))))
& URLENcode(URLENcode(URLENcode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLTen)))))))))


OK so now all we have to do is to define what the formula variables URLONE through URLTEN do. 

I will do the easy one first.

var text URLTEN = URLRoot() & "db/" & dbid() & "?a=dr&rid=[ & [Record ID#];  // redisplay the Parent record

There are nine more but they are all sort of the same. Your complicating issue is that we do need to do those first 9 URL calls even if they are not all needed.   So what we can do  for the un-needed ones is to edit a dummy field to be true.  So make a field in the parent table called [Dummy Checkbox used in URL] and let's say it is field ID 100.

var bool Dummy = URLRoot & "db/" & dbid() & "?act=API_EditRecord&rid=" & [Record ID#]
& "&apptoken=xxxxxxxxxxxx" // needed if you have the need to app tokens enabled
& "&_fid_100=1"; // make the Dummy checkbox true

var text URLONE = IF(not [Checkbox  1], $Dummy, URLRoot() & "db/" & [_DBID_MyChildTable_Alias] & "?act=API_AddRecord"
& "&_fid_10="  & [Record ID#]
& "&_fid_11=1); // 5 is the Record ID# of the required Master task corresponding to [Checkbox 1]

In the above I assume that your first checkbox field is called [Checkbox 1]. If it's unchecked (false), then we just do the Dummy checkbox, else we do the real URL.

So in the above I have assumed that fid 10 in the child record is the field for [Related Parent]  ... We needed to populate that or else the created child will be an orphan, just floating in space. 

Next I'm assuming the fid 11 is the field for [Related Master Task]  .  ie you are saying that these children being created are really join table record between the parent (say Project) and a Master Table list of possible Tasks. In the example above I'm assuming that [Checkbox 1] means you need to have Record ID# = 5 in the Master Tasks table.

So in the table of Master Tasks, you need to expose the Record ID# on a report so you know which Master Tasks Record ID needs to be populated to match up with each of your nine checkboxes on the parent record.

Give it a try and post back if you get stuck with your formula and the syntax error message if there are any.  Obviously I'm just typing while watching the super bowl and I don't have a formula editor in this box. 




 







------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    Correction to the above

    var text Dummy = URLRoot & "db/" & dbid() & "?act=API_EditRecord&rid=" & [Record ID#]
    & "&apptoken=xxxxxxxxxxxx" // needed if you have the need to app tokens enabled
    & "&_fid_100=1"; // make the Dummy checkbox true

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • MelissaFreel1's avatar
      MelissaFreel1
      Qrew Cadet
      5 years ago
      Mark:
      Thanks for the help.  I got it to "slightly" work. I think the place where I am struggling is with your fid10 and fid 11. I think I am misunderstanding what you mean by "5".  Here is what I have so far:

      //Add Utility Allowances

      var text LABEL= "Add Allowance";

      var text Dummy = URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&rid=" & [HH Record ID#]
      & "&_fid_3037=1"; // make the Dummy checkbox true

      var text URLONE = If([Heating Natural Gas]=false, $Dummy, URLRoot() & "db/" & [_DBID_TEST] & "?act=API_AddRecord"
      & "&_fid_3=" & [HH Record ID#]// Record Id of Parent
      & "&_fid_33=" & 5 // Related Record in Child
      & "&_fid_8=" & [Structure]// change the structure field in child to the selected information in parent [Structure]
      & "&_fid_11=" & "Heating Natural Gas"//change the utility type field in child to utility type identified with check box 1
      & "&rdr=");
      //Put 9 more in

      var text URLTEN = URLRoot() & "db/" & Dbid() & "?a=dr&rid=[ & [Record ID#]"; // redisplay the Parent record


      var text URLALL=$URLONE & URLEncode($URLTEN);


      "<a class='Vibrant Alert' href='" & $URLALL & "'>" & $LABEL & "</a>"

      ------------------------------
      Melissa Freel
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago
        Melissa,


        This line here
        & "&_fid_3=" & [HH Record ID#]// Record Id of Parent

        You cannot populate the [Record ID#] field of a Table. Fid 3 on the child table is the field for its Record ID#.  The field to populate is the field in your relationship for [Related parent].

        There are also likely other problems which will require working with you one on one to get this working.

        The other approach that is possible on a no code basis would be to set up a table of nine records and have an Automation copy those nine records in, and then have the next step of he Automation delete the ones which are not valid based  on the checkboxes selected in he parent.

        I think we need to either work one one one to get the URL method working or else switch gears and try to get an Automation working.  




        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------