Discussions

 View Only

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

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

    Posted 08-25-2022 17:39
    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
    ------------------------------