Forum Discussion

AleishaAldrich1's avatar
AleishaAldrich1
Qrew Member
3 years ago

Formula-URL button with API_AddRecord, then API_EditRecord (need to edit the record just added)

I am working with two tables:
  • Parent Table: Work Orders
  • Child Table: Status History
I have a formula-URL button that is supposed to do multiple things:
  1. Add a record in the Status History table:  [Type]=Work Order Status, [Status]=Closed, [Status Changed By]=Current User
  2. If [QC/QA Status] Contains "QC/QA Fail", then Add a record in the Status History table: [Type]=QC/QA Status, [Status]=Revision Complete - QC/QA Needs Review, [Status Changed By]=Current User
  3. Open Code Page 9, Prompt User for Comments, then Edit the record added in step 2 by populating [QC/QA: Work Order Review/Revision Comments] with User Prompted Input.
Everything works perfectly, except the User Prompted Input doesn't populate the [QC/QA: Work Order Review/Revision Comments] field on my Status History record (created in Step 2). I've tried including a formula query (as a variable) and referencing it in my formula to use as the Record ID from the record added in Step 2 above but it didn't work. Then I tried using another formula field [Max QC/QA RID for Comments], which I created on the Work Order form to run the query and I referenced it in my formula to use as the RID but it didn't work. I guess the API-EditRecord isn't working which I can only speculate it's related to the Record ID? Does anyone know if there is a better way to do this?

Here is a link to the code page I used: Code Pages - PromptAndRefresh.html (quickbase.com)

Here is my formula below:
var bool show = If(Contains([W/O Status],"Received") or Contains([W/O Status],"Created") or Contains([W/O Status],"Dispatched")or Contains([W/O Status],"Pending Review") or Contains([W/O Status],"Pending Revision"),true,false);

var bool QCQAFail= If(Contains([QC/QA Status],"QC/QA Fail"),true,false);

var text WOStatus=
URLRoot() & "db/" & [_DBID_STATUS_HISTORY]
& "?a=API_AddRecord&_fid_6="
& URLEncode ([Record ID#])
& "&_fid_10=Closed"
& "&_fid_11=Work Order Status"
& "&_fid_9=" & URLEncode(User()) & "&apptoken=xxxxxxxxx";

var text QCQAStatus=
URLRoot() & "db/" & [_DBID_STATUS_HISTORY]
& "?a=API_AddRecord&_fid_6="
& URLEncode ([Record ID#])
& "&_fid_10=Revision Complete - QC/QA Needs Review"
& "&_fid_11=QC/QA Status"
& "&_fid_9=" & URLEncode(User()) & "&apptoken=xxxxxxxxx";

//var Text qcqaID = ToText(GetFieldValues(GetRecords("{10.EX.'Revision Complete - QC/QA //Needs Review'} AND {6.EX."&[Record ID#]&"}",[_DBID_STATUS_HISTORY]),3));

//var text MAXID=
//ToText(Max(ToNumber(Part($qcqaID,1,";")),
//ToNumber(Part($qcqaID,2,";")),
//ToNumber(Part($qcqaID,3,";")),
//ToNumber(Part($qcqaID,4,";")),
//ToNumber(Part($qcqaID,5,";")),
//ToNumber(Part($qcqaID,6,";")),
//ToNumber(Part($qcqaID,7,";"))));

//var Number MAXID=
//Max(ToNumber(Part($qcqaID,1,";")),
//ToNumber(Part($qcqaID,2,";")),
//ToNumber(Part($qcqaID,3,";")),
//ToNumber(Part($qcqaID,4,";")),
//ToNumber(Part($qcqaID,5,";")),
//ToNumber(Part($qcqaID,6,";")),
//ToNumber(Part($qcqaID,7,";")));

var text urlToExecute =
URLRoot() & "db/" & [_DBID_STATUS_HISTORY]
& "?a=API_EditRecord&apptoken=" & "&apptoken=xxxxxxxxx"
& "&rid=" & [Max QC/QA RID for Comments]
& "&_fid_28="; // Field to Populate with the user prompted input

var text Page=
URLRoot() & "db/" & AppID() & "?a=dbpage&pageid=9" // Open code page 9
& "&url=" & URLEncode($urlToExecute); // Pass in the URL to execute

var text RefreshPage = URLRoot() & "db/" & Dbid() & "?a=doredirect&z=" & Rurl();

var text DoBoth=
$WOStatus
& "&rdr=" & URLEncode($QCQAStatus)
& URLEncode("&rdr=" & URLEncode($Page))
& URLEncode("&rdr=" & URLEncode("&rdr=" & URLEncode($RefreshPage)));

var text DoOne=
$WOStatus
& "&rdr=" & URLEncode($RefreshPage);

If($show=true and $QCQAFail=true, $DoBoth, $show=true, $DoOne,"")

Any help or advise would be much appreciated!

Thanks,


------------------------------
Aleisha Aldrich
------------------------------
No RepliesBe the first to reply