Formula to change value in another table

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
We have a button that starts a project task.  We would like to add logic so the button also changes the status of the parent project to 'In Process' of the project has a status of 'Not Started'  Can the single button update fields in the two related tables?  The present code is below.  The PROJECT table name is _DBID_PROJECTS and the project STATUS field id is 28

var text URL = URLRoot() & "db/" & Dbid()

& "?act=API_EditRecord&rid=" & [Task ID]

& "&_fid_77=" & ToWorkDate(Today())

& "&_fid_12=" & URLEncode("In-Progress");

"javascript:" &

"$.get('" & 

$URL & 

"',function(){" &

"location.reload(true);" &

"});" 

& "void(0);"



Thanks, Ron
Photo of Ron

Ron

  • 50 Points

Posted 2 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
After the button is pushed, where do you want the screen to refresh to?
Photo of Ron

Ron

  • 50 Points
Same screen (the button is on the Task form)
Photo of Ron

Ron

  • 50 Points
Does the script need to actually change screens?   We would prefer that the update to the project header status be invisible, this is simply a 'system' update to enforce report integrity...
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
The issue is that the type of button you are using will refresh what ever report or record you are on.   

The syntax is more difficult if you need to do two actions and then need to use that JavaScript compared to a button which would say always refresh to the Project record.
Photo of Ron

Ron

  • 50 Points
I would appreciate whatever recommendation you think will work best..
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
Try this.  It's not tested so if there is a syntax error please post your formula and the error message.

var text NewProjectStatus= IF([Project Status]="Not Started", "In Process", [Project Status]);

var text URLONE= URLRoot() & "db/" & Dbid()

& "?act=API_EditRecord&rid=" & [Task ID]

& "&_fid_77=" & ToWorkDate(Today())

& "&_fid_12=" & URLEncode("In-Progress");



var text URLTWO= URLRoot() & "db/" & [_DBID_PROJECTS]

& "?act=API_EditRecord&rid=" & ToText(Related Project])

& "&_fid_28=" & URLEncode($NewProjectStatus);


var text URLTHREE = URlroot() & "db/" & dbid() & "?a=dr&rid=" & ToText([Record ID#])

$URLONE
& "&rdr=" & URLEncode($URLTWO)
&URLEncode("&rdr=" & URLEncode($URLTHREE))
Photo of Ron

Ron

  • 50 Points
I made a couple of what I think were corrections (a missing bracket and semicolon).  Also, it turns out that the PROJECTS table alias is actually CLIENT (this started from a public app).  The script now looks like this.

var text NewProjectStatus= If([Project Status]="Not Started", "In Process", [Project Status]);

var text URLONE= URLRoot() & "db/" & Dbid()
& "?act=API_EditRecord&rid=" & [Task ID]
& "&_fid_77=" & ToWorkDate(Today())
& "&_fid_12=" & URLEncode("In-Progress");

var text URLTWO= URLRoot() & "db/" & [_DBID_CLIENT]
& "?act=API_EditRecord&rid=" & ToText([Related Project])
& "&_fid_28=" & URLEncode($NewProjectStatus);

var text URLTHREE = URLRoot() & "db/" & Dbid() & "?a=dr&rid=" & ToText([Project ID#]);

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

The current error message is "Formula error -- Unknown field
This table does not contain a field called Project ID#. To see available fields, click the Fields & Function dropdown to the right of the formula box.."  

The index of the PROJECTS table is definitely Project ID#, and the relationship uses the RELATED PROJECT field.  Is the validation of the fields still in the initial TASKS table, the Fields and Function dropdown shows fields from the initial TASKS table.

I think that we are close.  Thanks, Ron
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
That last formula variable for URLTHREE is trying to redisplay the task record. So you need to say that the record ID (rid=)   Is equal to the record ID you are sitting on.

So presumably that field is still called [Record ID#].  For some reason I typed [Project ID#].

That should read [Record ID#]
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
Also, you will need  if there is a field called [related project]. It is possible that field is called [related client]
Photo of Ron

Ron

  • 50 Points
That was it, the PROJECT ID# field was TASK ID.  Wow it worked!  The only minor issue is that if the user drills to the child task from the project screen and then starts the task with the button, the 'breadcrumb' path (using the up arrow on the screen) back to the parent project is lost (the user must re-find the project to look at it's other tasks.  It is possible to use the browser back-arrow to return to the project header and that works fine.

Thanks for the assist, Ron
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
Typically, in every app, you want all child records of any table to have a link on them to get back to their parent.

The easy way to do that is to look at the relationship between projects and tasks. I assume that there is a lookup field for Project name.

Edit the field properties for the field [Related Project] and set its proxy field to be the [Project Name] field.  And sure what your name field is on the task force. That way the project name field should be a hyperlink back to the project.
Photo of Ron

Ron

  • 50 Points
Unfortunately that did not work.  It is interesting that the link back to the project is there before the scripted button is pressed, but the link is gone after the script is executed.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
If you like you can contact me directly via the information in my profile and I will have a quick look at your application.