How can I auto change the value of a field in a Parent record when the value of a field in a child record is modified to a specified value?

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered

The Parent record is "Request for IT Services".The detail record is "Financial detail; opex, capex etc). When the financial detail: Request Status is set to Approved. how would I auto change the status of the "Request for IT Services": Status field to approved?

Photo of Krishna

Krishna

  • 10 Points

Posted 5 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 67,610 Points 50k badge 2x thumb
No problem.
Do a summary total count if he number if detail record which are Approved.

Then on your parent record make a new field which will be used for all reports ( or perhaps simply change the existing field to a formula, text field.  The formula will be

If (# of approved detail records]>0, "Approved")

So the suggestion is to not actually change the value on a data entry field on ghe parent reps but rather to take it into account is a formula field.
Photo of Krishna

Krishna

  • 10 Points
Thanks! I used this formula in a formula text field: If([# of Approved Funding Requests]>0,[Status]="Approved"). It errors out saying this returns a boolean and I cant use a boolean in a text field. I  had the same problem when I used a fomula numeric field.How would I write this formula to check my summary field  and set the value of Status = Approved. Status is a field in my parent table. I do not want to change the Status field type since there are manual edit situations.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,610 Points 50k badge 2x thumb
no problem.
Rename your current status field to be called [Status manual update]

Then make a new field called [Status] and the formula would be

if(
[Status manual update]<>"",[Status manual update],
[# of approved detail records]>0,"Approved")

That will update the text formula field for [status].  I'm not sure what you mean by updating a checkbox field to checked, but if you want a mirror field to be a checkbox if [Status]=Approved", then the formual for a formula checkbox field would be

if([status]="Approved",true)
Photo of Krishna

Krishna

  • 10 Points
Thanks for your help! For my understanding: Could you please translate the formula (for the new Status field) in simple english?
Photo of Krishna

Krishna

  • 10 Points
Just tried out your suggestion, it does not change the status of the "Status manual update" field.What could I be doing wrong?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,610 Points 50k badge 2x thumb
Your original question asked how a manual data entry field  on a parent record can be updated when a child's field's record value is changed.

The answer to that question is "No, that cannot be done". End of story.

So I am offering up an alternate approach.

I'm suggesting that you retain your original [Status field] on the parent but rename it to be a manual entry field.  The original data is all preserved when you rename a field.

Then have a formula field which will use the manual update value if there was one entered, else it will automatically updated based on the children record value.

The new field which I suggest be called [Status] would be used in all reports and forms, except when the Parent record is in edit mode.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,610 Points 50k badge 2x thumb
To answer what the formula does I will copy it below with comments.

If(
[Status manual update]<>"",[Status manual update],  //"if the manual update is not blank, then use it
[# of approved detail records]>0,"Approved") // else if there are children which are approved, set the value to "approved"
Photo of Krishna

Krishna

  • 10 Points
Ok, different scenario: how would I change the formula field to automatically update my status field based on the child record value by overwriting the previous manual update value?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,610 Points 50k badge 2x thumb
Its really not something I would recommend.  If you wanted to do that, you would need to build a formula URL button on the child record, which when pushed would update both the child record's value in a field as well as Update the Parent record.  You would remove the user's ability to update the child's record in any other way.  But while this is certainly possible to do, I don't see why you would want to go to that complexity.
Photo of Krishna

Krishna

  • 10 Points
I am sufficiently frustrated here after all this back and forth. To be clear: you are telling me that I cannot change the value of a parent record value based on a child record value?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,610 Points 50k badge 2x thumb
YES I am saying exactly that.

Think of excel.  Say you have a blank value in cell A1.  Its just an empty cell.  You then key a value into cell B1.  Then you go to an excel user help forum and say "when I key a value into cell B1, I want the value in cell A1 to change.".  The "experts" will come back and say that you need to make cell A1 be a formula field  and there is not a way to otherwise change the value of A1  unless you are willing to get into programming and visual basic type code.

But you are saying that you are unwilling to use a formula, so around in circles we go because you do not know visual basis and don't want to get into that level of complexity.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,610 Points 50k badge 2x thumb
How would you do this in excel?
Photo of Krishna

Krishna

  • 10 Points
Thanks for your guidance! Your "declaration" that I cannot modify a record value (without knowledge of VB) doesn't reconcile with the fact that one can use act=API_EditRecord in formula url's to modify record values after specifying fields id and Db id's and without making the field of interest a formula. Probably tie this edit action to the summary field that is an indicator of child record status.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,610 Points 50k badge 2x thumb
The issue is your last sentence.  The API can definitely do that update if you are willing to have the user push a button on the child record.  But it will not fire on its own based on saving a record.
Photo of Krishna

Krishna

  • 10 Points
Thats right! Back to square one and figuring out a scripting solution.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,610 Points 50k badge 2x thumb
Do you want help with that formula URL button on the child record.  If you do, then I would need to know the field ID's that need to be updated and the name of the table of the Parent record.  That can be found in the advanced properties of the parent table and will be something like _DBID_PARENT
Photo of Krishna

Krishna

  • 10 Points
Sure, I can use your help. _DBID_PIFS and field ID = 78 for the field to be modified. I appreciate it.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,610 Points 50k badge 2x thumb
// the first formula variable is the code to update the child record to "Approved". Change _fid_24 to the field ID# of the field to be updated on the child record.
var text URLONE = URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&rid=" & ToText([Record ID#]) & "&_fid_24=Approved";

// this next formula variable updates the parent record. Change the fid to be updated from 6 to to its value.
var text URLTWO = URLRoot() & "db/" & [_DBID_PIFS] & "?act=API_EditRecord&rid=" & [Related PFIS] & "&_fid_6=Approved";

// then you need to decide what to do after the update. There are many choices. This below is the simplest way to redisplay the Child record.
var text URLTHREE = URLRoot() & "db/" & Dbid() &"?a=dr&rid=" & ToText([Record ID#]);




// now we string together the three different API calls. On each successive nesting you need to layer in a URLEncode ( ).

$URLONE
& "&rdr=" & URLEncode($URLTWO)
& URLEncode("&rdr=" & URLEncode($URLTHREE))

// note that you will need to go into the Application properties to disable the requirement for "Application Tokens" or alternatively add in the text

&apptoken=put your application token here into each of the first two API calls.