Update Parent record from child records - multiple child records, multiple child tables

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have a parent table called "jobs". I have several different child tables that are different types of requests for people to perform related to a job. On the job parent table are five different multiple choice fields that represent the "status" of five different aspects of a job.

What I want is for people that create a child record to see what those five status fields are at the time they create the record. I can do this easily via the relationship and lookup fields. However, I also want them to be able to change those status from the child record they are on without entering the parent record itself. These various status may or may not be directly related to the creation of the child record, but are critical fields that I need viewed and updated regularly. I need them to be able to be changed from EITHER the child records, or from the parent record itself where they are located.

Currently, I do this with a report link to a grid edit report of those 5 status fields that is included on the child table forms. However, that can't be used on the mobile version, and there are other aspects I don't like about it.

One aspect of the grid edit report that is a positive for me is that the results shown are always current. As my child tables are essentially tasks created by one employee and sent to another, each one may or may not have a need to update one or more of those 5 parent table fields. So if the first employee creates the child task and makes a change to the parent status record, the next employee that opens the child record will see the most current status. They can then make any changes they need.

Any suggestions?



Photo of Drew

Drew

  • 522 Points 500 badge 2x thumb

Posted 6 years ago

  • 0
  • 1
Photo of Jack

Jack, Champion

  • 50 Points
Hi Drew,

Are these statuses the result of something automated? or particularly something your adding/entering/creating into your quick base app? For example, if your parent has no child records, then your status is pending, if your parent has two child records it status could be "Job raised and allocated" etc etc. Sorry the reason I ask is that you could actually automate your statuses so when certain criteria are met the status changes, removing the need for the user to update this manually.

Jack
Photo of Drew

Drew

  • 522 Points 500 badge 2x thumb
That's the issue, and I probably didn't explain it well enough, the fields on the parent record are not necessarily explicitly related to what is happening on the child record.  There really isn't any logic that can be implemented from that respect.  Essentially the 5 fields on the parent record are very broad overall status fields for the job.  Our business process is that these are reviewed, and in many cases updated, at the time one of these child records is created.  From a business process perspective, any time an employee is creating one of these child records, they are in a position to know the current status of those parent record fields, and we want them to update them accordingly.
Photo of Jack

Jack, Champion

  • 50 Points
Do you have a uniform set of statuses in the parent record, i.e. a drop down or is this free text?
Photo of Drew

Drew

  • 522 Points 500 badge 2x thumb
They are a dropdown.  All five fields in the parent record all share the same dropdown options.  

You may be headed the same direction i've been working on:

 I created two more fields in the child record for each reference field from the parent:.

Field 1 - Update Parent Record:  This is a multiple choice field that shares the same source as the parent.
Field 2 - "New " Status field:  This is a formula-text field.  If the user changes field 1 to be a choice different that what is currently in the parent reference field, this field will duplicate that value.  If the user does not pick a choice from field 1 (meaning they do not want to change the current status of the parent field), field 2 simply mirrors the current parent field value.  In other words, this field by formula will determine what the parent record field should be after saving.

Update Status button: This is a formula URL field that when clicked will change the parent field to whatever is in field 2 for each of the five fields i'm wanting to update.   

I then created a form rule which states that after saving, field 1 should be set to blank again so the process can be performed by the next person that opens the record.  

Not sure if that all makes sense.  The fact is, this method does function.  However, it is massively bulky and complicated.  As I currently have 5 parent fields I need the ability to change, i'm essentially adding 10 new fields to each child record.  I also have to do this for 4 different types of child records.

My guess is that this can be accomplished by some other means with only the reference field, and 1 additional drop down box per field.  I'm assuming there is a way to accomplish the logic via javascript and create one "update and save" button.  The logic essentially would be to look at each dropdown box, if the value in it is different than the reference field and is not null, update the appropriate parent field accordingly.  I'm hoping someone is able to provide an example of this or something similar.
Photo of Jack

Jack, Champion

  • 50 Points
I've created something in a simple database which updates the parent from the child. I've attached a screenshot series for you to look at what its doing.

This code is inserted in a forumula URL field which in the field options you can set it to show as a button on forms and reports.

URLRoot() & "db/PARENT TABLE ID?a=API_EditRecord&rid=" & [Related Record] & "&_fid_6="&URLEncode([Update Parent Status to])& "&rdr=" &URLEncode(URLRoot() &"db/CHILD TABLE ID?a=er&rid=" & [Record ID#])

Field 6 is the field to be updated (so the parent status field in the parent table)
Related Record is the related record ID in the child table (the parent record ID)
Update Parent Status to is the drop down in table 2

The above is for apps without app tokens turned on.

If you use app tokens see below:

URLRoot() & "db/PARENT TABLE ID?a=API_EditRecord&apptoken=ENTER APP TOKEN&rid=" & [Related Record] & "&_fid_6="&URLEncode([Update Parent Status to]) & "&rdr=" &URLEncode(URLRoot() &"db/CHILD TABLE ID?a=er&rid=" & [Record ID#])

The bold sections in the formula URL code above will need to be changed to the relevant fields or DB ID's.

Hope this helps.
Photo of Jack

Jack, Champion

  • 50 Points
In response to your above post you could nest your code so you can update multiple field with only one button.

URLRoot() & "db/PARENT TABLE ID?a=API_EditRecord&rid=" & [Related Record]
& "&_fid_6="&URLEncode([Update Parent Status to])
& "&_fid_7="&URLEncode([Update Parent Status to1])
& "&_fid_8="&URLEncode([Update Parent Status to2])
& "&_fid_9="&URLEncode([Update Parent Status to3])
& "&_fid_10="&URLEncode([Update Parent Status to4])
& "&rdr=" &URLEncode(URLRoot() &"db/CHILD TABLE ID?a=er&rid=" & [Record ID#])
Photo of Jack

Jack, Champion

  • 50 Points
The only other way you could possibly do it is to use the MAX child record status to show in the parent, the status in the parent would always be the status of the most recently added current child record
Photo of greg

greg

  • 0 Points
Jack,

I don't know if you are still watching this, but I have been trying to implement your solution as it seems to be the best solution out there. I pasted my formula below as well as the error I am receiving from Quickbase.

Formula:
URLRoot() & "db/PARENT TABLE ID a=API_EditRecord&apptoken=APP TOKEN&rid=" & [RELATED PARENT ID]
& "&_fid_PARENT LOCATION="&URLEncode([CHILD FIELD])
& "&rdr=" &URLEncode(URLRoot() &"db/CHILD TABLE ID?a=er&rid=" & [CHILD Record ID#])

Error:
This XML file does not appear to have any style information associated with it. The document tree is shown below.
<qdbapi>
<action>API_EditRecord</action>
<errcode>102</errcode>
<errtext>
Invalid request - we cannot understand the URL you specified.
</errtext>
</qdbapi>

Any Ideas what I am Missing?
Photo of Jack

Jack, Champion

  • 50 Points
Hi Greg,

Apologies I havent come back to you sooner, I've been really busy.

Just to explain the above code a little more may help you diagnose the issue:

URLRoot() & "db/PARENT TABLE ID a=API_EditRecord&apptoken=APP TOKEN&rid=" & [RELATED PARENT ID]

The above section is creating the url string to determine which record you want editing. I note that you have a missing question mark before the a=API this should be ?a=API

& "&_fid_PARENT LOCATION="&URLEncode([CHILD FIELD])

I am not sure what you have done here.

& "&_fid_PARENT LOCATION <<<<<this section should contain a field ID that you want to write information to, if for example you have a field called "My Text" that you want to write the data that you have to in "CHILD FIELD" you first need to find the field ID of "My Text", you can do this in the parent table settings, then click on fields and advanced options and tick the field ID box and click save, all the fields listed will then have numbers next to them, find the field number that belongs to "My Text" and add this to code. If for example the field "My Text" has a field ID of 22 it would look like this:

& "&_fid_22="&URLEncode([CHILD FIELD])

The section below looks ok.

& "&rdr=" &URLEncode(URLRoot() &"db/CHILD TABLE ID?a=er&rid=" & [CHILD Record ID#])

Just to recap, this is how the above should look together.

URLRoot() & "db/PARENT TABLE ID ?a=API_EditRecord&apptoken=APP TOKEN&rid=" & [RELATED PARENT ID]
& "&_fid_22="&URLEncode([CHILD FIELD])
& "&rdr=" &URLEncode(URLRoot() &"db/CHILD TABLE ID?a=er&rid=" & [CHILD Record ID#])

I hope this helps and once again I apologise for the delay in responding.
Photo of greg

greg

  • 0 Points
Jack,

Thanks for the help. This is what I came up with and it it working. Thanks again!!

URLRoot() & "db/" & [PARENT TABLE] & "?a=API_EditRecord&apptoken=APPTOKEN&rid=" & [Related Customer (ref)]
& "&_fid_FIELDTOUPDATE="&URLEncode([FIELD TO UPDATE WITH])
& "&rdr=" &URLEncode(URLRoot() &"db/" & [CHILD TABLE] & "?a=er&rid=" & [Record ID#])
Photo of Jack

Jack, Champion

  • 50 Points
That's great Greg!
Hi Greg,

I have same scenario. when i am updating this formula with ?a=dr for display the record.
It gives me error that it can't find that child record.

My formula is -

URLRoot() & "db/" & [_DBID_ASSOCIATES] & "?act=API_EditRecord&rid=" & [Related Requestor or Traveller]

& "&_fid_693=" & URLEncode([Passport Number (Manual)])
& "&_fid_694=" & URLEncode([Passport Issue Date (Manual)])
& "&_fid_695=" & URLEncode([Passport Expiry Date (Manual)])
& "&_fid_696=" & URLEncode([Passport Issue Place (Manual)])

& "&rdr=" &URLEncode(URLRoot() &"db/" & [_DBID_TRAVEL_REQUISITIONS]  & "?a=dr&rid=" & [Record ID#])


Can you please check ?


Thanks,
Gaurav Sharma
Photo of Jack

Jack, Champion

  • 50 Points
Hi Gaurav,

In the child table is the primary key field called [Record ID#]?

Regards

Jack
yes.
Photo of Jack

Jack, Champion

  • 50 Points
Are you in the child record when you click the button which runs the api call?
Yes,
Photo of Jack

Jack, Champion

  • 50 Points
does it work if you use er instead of dr?

and do you have app tokens on or off in the app?
Yes, it works for er but it don't show that record in edit mode. It shows a blank form in edit form/ New form.
No, I don't have any tokens.
Photo of Jack

Jack, Champion

  • 50 Points
are you triggering it when adding a new record in the child table (from an unsaved record) or editing a child record (an already saved record)?

just to confirm you have turned off the need to use app tokens in the app? if you haven't then you will need to create an app token in the app settings, and then reference the app token in your call so your code would look something like the below (if 45hgt54t4htrhtr54 was your app token):

URLRoot() & "db/" & [_DBID_ASSOCIATES] & "?act=API_EditRecord&apptoken=45hgt54t4htrhtr54&rid=" & [Related Requestor or Traveller]

& "&_fid_693=" & URLEncode([Passport Number (Manual)])
& "&_fid_694=" & URLEncode([Passport Issue Date (Manual)])
& "&_fid_695=" & URLEncode([Passport Expiry Date (Manual)])
& "&_fid_696=" & URLEncode([Passport Issue Place (Manual)])

& "&rdr=" &URLEncode(URLRoot() &"db/" & [_DBID_TRAVEL_REQUISITIONS]  & "?a=dr&rid=" & [Record ID#])
Yes, Jack.

I am triggering it when adding a new record in the child table.
Hey Jake,

After creating app token and apply same but again giving this error.

Travel Requisition Not Found
It has probably been deleted. If you followed a link to get here, you may want to inform the author of that link that this travel requisition no longer exists
Photo of Jack

Jack, Champion

  • 50 Points
now try triggering it from a saved child record and see if you get the same error?

I've got a feeling its not redirecting due to the child record not being saved first, as no content on a form is saved until you click the save button, when saved the record is assigned its record ID, therefore if adding a record you would need to save it first to make the button work.
Hey Jack,

I have tried from a saved child record and not getting any error it is working fine.
you are right.

but i have checked this option "Save parent record automatically when a child record is created." from table setting.
Photo of Jack

Jack, Champion

  • 50 Points
yes but that option only works if you start at the parent click on the add child button and then it auto saves the parent. Since your starting with the child and updating the parent, then coming back to the child it wont work without saving it first.

I'll be honest rather than copying the fields wouldn't it be better to use max related record id (and reverse relationship) to show the value in your parent record, from the child record?
Photo of Jack

Jack, Champion

  • 50 Points
each time you add a new child record the fields would update.