Associate existing child record to existing parent record from parents table.

  • 1
  • 2
  • Question
  • Updated 6 months ago
  • Answered
I have two related tables, a Parent and a Child. The way users typically use my app, they often find themselves wanting to associate new Children to a Specific Parent record, without actually knowing what specific Child records they might want. 

Right now if I want to associate an existing child record with a specific Parent, I have to remember that Parent name, then go into the Child table and search for a relevant Child.

Since in my case searching for a relevant Child might be a long and involved process, it is easy to lose track of which Parent you are intending to associate the Children with. 

What would be more useful is if there was a way to, from from a specific Parent record, generate a report of all un-linked Child records, and easily associate them with that specific Parent.

I have been looking into generating such a report using the Quickbase API and a formula URL field on the Parent table, but so far no luck.

This is an odd and specific case, so I would not be surprised if it wasn't possible, but it would be surprisingly helpful for our workflow. 

Thanks. 
Photo of Alex Gale

Alex Gale

  • 1,430 Points 1k badge 2x thumb

Posted 1 year ago

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

QuickBaseCoach App Dev./Training, Champion

  • 69,572 Points 50k badge 2x thumb
I do this all the time for my clients, but the process is a bit too complicated to explain here.  basically my process is that you launch off a Parent record  into a report of children with no parents.  You can then click click click on as many children as you like and have full access to the dynamic filters and search box on a regular table report.  Then click a button to get back to your parent and you will see your Parent with all its children now attached.

Contact me via the information on my website if you would will like me to set this up for you and teach you the technique to use in future.  It involves no "javascript" , all native Quick Base.

My contact info is at QuickBaseCoach.com

It usually takes about 1.5 hours to set this up and do the training so you understand what I have done and could replicate it if you need the technique for another similar situation. 
Photo of Alex Gale

Alex Gale

  • 1,430 Points 1k badge 2x thumb
I appreciate your willingness to help. That does seem like exactly what I am looking for. I may end up contacting you, but for now we need to consider how involved the process seems to be on our end. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,572 Points 50k badge 2x thumb
I can do a quick demo if needed.

There is an app which does quotes to customers.  The similar need is to create the parent "Quote" and then quickly add product line items to the quote where there are thousands of possible products and there may be a need to add say 5 lines to a Quote..
Photo of Alex Gale

Alex Gale

  • 1,430 Points 1k badge 2x thumb
Hi, 

I've been working on this problem on and off for a while now and I still haven't come up with a solution. Do you by any chance have a link to this quotes app that I could view for reference, and to see the feature in action? 

Thanks
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,572 Points 50k badge 2x thumb
If you contact me via my website, I can show you the technique in action on a live production app.

Basically the technique is to have a table of users with key field User.  The user clicks and launches off the Parent record and the URL formula button creates or updates their User record with the Focus Parent Record ID#.  Then the user will redirect to a report of children.  That User Focus Record ID# is looked up down to the child records via a relationship with a reference formula field if User().

So now it knows the parent record to hook it up to and a URL formula field can make that API_Edit.

I'm at QuickBaseCoach.com
Photo of Alex Gale

Alex Gale

  • 1,430 Points 1k badge 2x thumb
Oh that makes some sense. I was thinking that maybe I could use a relationship like that to store the information about the parent record ID#, but the problem was it would break down with multiple users. 

I'll try this out, thanks. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,572 Points 50k badge 2x thumb
Thx crux of the code is this

//Note to self: remember to set permissions on the User Focus table

var text AddUser = URLRoot() & "db/" & [_DBID_USER_FOCUS] & "?act=API_AddRecord"
 & "&_fid_6=" & ToText(User())
 & "&_fid_7=" & ToText([Record ID#]);

var text EditUser = URLRoot() & "db/" & [_DBID_USER_FOCUS] & "?act=API_EditRecord"
& "&key="  & ToText(User())
& "&_fid_7=" & ToText([Record ID#]);

var text DisplayReport = URLRoot() & "db/" & [_DBID_PRODUCT_CATEGORIES] & "?a=q&qid=7";

If([User exists in User Focus Table?],
$EditUser& "&rdr=" & URLEncode($DisplayReport),
$AddUser& "&rdr=" & URLEncode($DisplayReport))
Photo of Alex Gale

Alex Gale

  • 1,430 Points 1k badge 2x thumb
Oh that's very helpful, thanks. I hadn't been thinking about it, but of course the workflow would otherwise break down if the user didn't exist. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,572 Points 50k badge 2x thumb
Exactly, and I didn’t want to have to train my universe to remember to add themselves

Exactly, and I didn’t want to have to train my users to remember to add themselves to that users table.

So the first time in their life that they push the button It will create a record in the users table and then for the rest of their life it will just edit that record. Make sure you remember to give everybody the ability to add and edit those records.
Photo of Alex Gale

Alex Gale

  • 1,430 Points 1k badge 2x thumb
Ha! I can't pretend that my users would be any better about remembering little technical things like that. 
Photo of Alex Gale

Alex Gale

  • 1,430 Points 1k badge 2x thumb
This is one of the more complicated things I've had to do in Quickbase but I finally got it to work. Thanks. The trick for me was figuring out that reference fields can be changed to formulas. 
Photo of Alex Gale

Alex Gale

  • 1,430 Points 1k badge 2x thumb
This is one of the more complicated things I've had to do in Quickbase but I finally got it to work. Thanks. The trick for me was figuring out that reference fields can be changed to formulas. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,572 Points 50k badge 2x thumb
It’s a very powerful technique to be able to record info in a table of users and then look that up into any detaiks table with a formula reference field of

User()
(Edited)
Photo of Tate Forgey

Tate Forgey

  • 702 Points 500 badge 2x thumb
This is a brilliant technique!  A truly impressive idea I'm still absorbing.  Thanks to the very clear descriptions here I also got it to (basically) work.  This is REALLY awesome and just what I was hoping to accomplish.  Thank you so much for this contribution.

To simplify, I just did this for existing users in the User Focus table.  Can you describe the best way to create the [User exists in User Focus Table?] field you reference in your if statement?  

Also, when you mention a second button to redirect back to the parent record, do you just put that on each record next to (I am assuming) the API_EditRecord button you create to associate the parent without redirecting or is there a better way to present this "return to parent" functionality?


Photo of Tate Forgey

Tate Forgey

  • 702 Points 500 badge 2x thumb
This is a brilliant technique!  A truly impressive idea I'm still absorbing.  Thanks to the very clear descriptions here I also got it to (basically) work.  This is REALLY awesome and just what I was hoping to accomplish.  Thank you so much for this contribution.

To simplify, I just did this for existing users in the User Focus table.  Can you describe the best way to create the [User exists in User Focus Table?] field you reference in your if statement?  

Also, when you mention a second button to redirect back to the parent record, do you just put that on each record next to (I am assuming) the API_EditRecord button you create to associate the parent without redirecting or is there a better way to present this "return to parent" functionality?


Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,572 Points 50k badge 2x thumb
re: Can you describe the best way to create the [User exists in User Focus Table?] field you reference in your if statement?  

I just make a formula checkbox field in the user focus table called [User Exists?] and set the formula to be 

true

I then look that up down to the detail record.  If the User does not exists, then that will evaluate to false.  so them the formula to set the focus knows that it needs to create the User in that table, else it just will edit the existing record with

"&key=" & ToText(User())



Typically my URL to set the User Focus will look like

var text AddUser = URLRoot() & "db/" & [_DBID_USER_FOCUS] & "?act=API_AddRecord"
 & "&_fid_6=" & ToText(User())
 & "&_fid_7=" & ToText([Record ID#]);

var text EditUser = URLRoot() & "db/" & [_DBID_USER_FOCUS] & "?act=API_EditRecord"
& "&key="  & ToText(User())
& "&_fid_7=" & ToText([Record ID#]);

var text DisplayReport = URLRoot() & "db/" & [_DBID_PRODUCT_CATEGORIES] & "?a=q&qid=7";

If([User exists?],
$EditUser& "&rdr=" & URLEncode($DisplayReport),
$AddUser& "&rdr=" & URLEncode($DisplayReport))


as for this

Also, when you mention a second button to redirect back to the parent record, do you just put that on each record next to (I am assuming) the API_EditRecord button you create to associate the parent without redirecting or is there a better way to present this "return to parent" functionality?

Yes, that is just a URL formula to display the parent record and yes it will be on each line of the report, so repetitive yes, because the report will have say 100 rows and there are 100 identical buttons, but that does give the user an easy way to get that to the parent record to satisfy themselves that the child records did get added.

Usually I make the button to add the child record to look like a button and with color, and then the link back to the parent record might be called [View Order] and show as just a link as opposed to a colored button.

Every year i apply to present this technique at EMPOWER and every year i get turned down.  But maybe I will apply to be a speaker again.  :).  it's my very favorite trick and once you "get it" (which I see you do) you will see so many uses of the same technique and it's all really low code/no code, ie just really native Quick Base formula URLs and no real coding , so no javascript or code pages.  So still KISS.


Photo of Tate Forgey

Tate Forgey

  • 702 Points 500 badge 2x thumb
Got it!  I think I'll be able to work through this advance myself even more with your help.  

It seems to me that this is the exact thing QB should be sharing; all native capability, meets a clear need, extensible to a many uses cases, clever but not "too clever" if you know what I mean.

Along those lines, this may be a little crazy, but I'm thinking about taking fields from the parent record and passing them to the child URL report link as dynamic filter values.  It looks like clicking on those filters is reflected int the URL.  Seems possible, right?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,572 Points 50k badge 2x thumb
The syntax for that would likely be brutal given how ugly the url becomes when you start using those Dynamic filters.  But you can link off to an <ask the user> report and there is easy syntax to answer the <ask the user> question(s) if you know the answer from either where the user launches from.
Photo of Tate Forgey

Tate Forgey

  • 702 Points 500 badge 2x thumb
Glad I asked.  I'll pursue that method instead and see what I can come up with.

Champion seems somehow not enough given your contributions to this forum.  Thanks again.
(Edited)
Photo of Tate Forgey

Tate Forgey

  • 702 Points 500 badge 2x thumb
I think I am missing something here regarding the check for the existing user (admittedly not the biggest part of this.)  I am right with you in creating the checkbox in the user table and looking it up to the detail where it will evaluate to false, but how can you use that field in the formula URL that is on the parent table where you are setting the User Focus?  I can't reference that field in a formula, I don't think.
Photo of Tate Forgey

Tate Forgey

  • 702 Points 500 badge 2x thumb
I think I am missing something here regarding the check for the existing user (admittedly not the biggest part of this.)  I am right with you in creating the checkbox in the user table and looking it up to the detail where it will evaluate to false, but how can you use that field in the formula URL that is on the parent table where you are setting the User Focus?  I can't reference that field in a formula, I don't think.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,572 Points 50k badge 2x thumb
I think that your latest post here must belong to a different thread.
Photo of Tate Forgey

Tate Forgey

  • 702 Points 500 badge 2x thumb
Do you say that just because I am being daft?!?!  :) 

No - I am having issues with using the filed you reference as [User Exists?] in the formula to set the user focus.  I feel like I am being dumb here and I can just omit this part of the functionality, but I'm trying to understand it.  Too much egg nog, maybe.
Photo of Tate Forgey

Tate Forgey

  • 702 Points 500 badge 2x thumb
Do you say that just because I am being daft?!?!  :) 

No - I am having issues with using the filed you reference as [User Exists?] in the formula to set the user focus.  I feel like I am being dumb here and I can just omit this part of the functionality, but I'm trying to understand it.  Too much egg nog, maybe.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,572 Points 50k badge 2x thumb
Here is some syntax and clear notes for the URL formula button to set the User Focus from whatever record you are launching off of.

Edit or Create a User Record for the Current User (remember to set permissions in the User focus table)
Set Key Field
User Exists - true!

Remember to set permissions

var text AddUser = URLRoot() & "db/" & [_DBID_USER_FOCUS] & "?act=API_AddRecord"
& "&_fid_6=" & ToText(User())
& "&_fid_7=" & ToText([Record ID#]);

var text EditUser = URLRoot() & "db/" & [_DBID_USER_FOCUS] & "?act=API_EditRecord"
& "&key=" & ToText(User())
& "&_fid_7=" & ToText([Record ID#]);

var text DisplayReport = URLRoot() & "db/" & [_DBID_PRODUCT_CATEGORIES] & "?a=q&qid=7";

If([User exists in Users for Library Update table?],
$EditUser& "&rdr=" & URLEncode($DisplayReport),
$AddUser& "&rdr=" & URLEncode($DisplayReport))
Photo of Tate Forgey

Tate Forgey

  • 702 Points 500 badge 2x thumb
Okay - daftness it is.  For posterity I'll note the embarrassing source of my confusion. 

I was so focused on the relationship with the User Focus table and the table that held the records that would be assigned that when you said you pushed the [User Exists?] field to the details table I thought you meant to that "assignee" table. I neglected to realize you were also talking about re-setting the reference field in the relationship between the User Focus table and the table where the parent records are contained.  I had had created a "normal" relationship there with the User Focus table as a child.  I deleted that relationship, reversed it, and used the user reference trick there.  That allowed me to push down the [User Exists?] field to the formula in the parent table exactly as you specify.

All works now and no user maintenance!  Brilliant!
Photo of Tate Forgey

Tate Forgey

  • 702 Points 500 badge 2x thumb
Okay - daftness it is.  For posterity I'll note the embarrassing source of my confusion. 

I was so focused on the relationship with the User Focus table and the table that held the records that would be assigned that when you said you pushed the [User Exists?] field to the details table I thought you meant to that "assignee" table. I neglected to realize you were also talking about re-setting the reference field in the relationship between the User Focus table and the table where the parent records are contained.  I had had created a "normal" relationship there with the User Focus table as a child.  I deleted that relationship, reversed it, and used the user reference trick there.  That allowed me to push down the [User Exists?] field to the formula in the parent table exactly as you specify.

All works now and no user maintenance!  Brilliant!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,572 Points 50k badge 2x thumb
OK, so good job getting that to work with limited instructions!

Yes, the self maintenance part is what makes it elegant.  You do not have to worry about a new user touching the app for the first time and having it fail for them right out of the gate.
Photo of Tate Forgey

Tate Forgey

  • 702 Points 500 badge 2x thumb
In retrospect this seems like an obvious issue, but I realized when a record is first created if you hit this formula button it does not function properly even if when you indicate you want to save the record in the stock Quick Base message you get about saving the record before leaving the page.  It looks like the ID has not yet been created before this formula is executed, which totally makes sense since you already clicked the button.

I looked through the forums and saw some complicated techniques for saving a record before moving on, but ultimately I just created a rich text button that duplicates the save and keep working functionality that I'm going to call "Save to Assign" and use the form properties to only display that when the form is used for add.  Then, I also use form properties to indicate the URL formula button should only be displayed when the form is used for edit or view.

This seems pretty low-tech and does require an extra click to get to the assign button.  So, I'm open to suggestions here if there is a way to combine these, but because they are both in the same spot and seem like they are replacing each other it seems okay in use, I think.  It also has the advantage of getting out of the way when the record has already been save once since you will never see that "Save to Assign" button again.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,572 Points 50k badge 2x thumb
Yes, the parent record doers need to exist for you to be able to record the Focus Record ID#.