Forum Discussion

kheatley's avatar
kheatley
Qrew Member
2 months ago

How should I set up this Formula URL button

I’ve been trying to set up a notification email for approvers, where when a leave request is submitted by a faculty member, approvers receive the email notification that there is a leave request pending that they then need to approve or deny. The email text will be pulled from a form I’ll create including approve/deny buttons. My problem is the two tables that hold the approve/reject statuses: REQUEST DECISIONS and DECISION STATUS CHANGES, which you helped set up by having REQUEST DECISIONS be the table containing the single approval record for each approver for each request and the DECISION STATUS CHANGES table contain all status records over time (ie: pending/approved or rejected). I’m not sure how the process for the buttons would work correctly.

Should I:

  1. Create Formula-Rich Text button fields that use API_EditRecord from related REQUEST DECISIONS RID and then add copy of this info to new record in DECISION STATUS CHANGES or
  2. Create Formula-Rich Text button fields that uses API_AddRecord to add a new record to the DECISION STATUS CHANGES (akin to the Add Form Temp form you created in this table)

I asked chatgpt what it thought and this is what it came up with.  Do you agree?

For your notification and status update setup, both approaches could work, but each has its advantages. Here’s a breakdown of the two options:

1. Using API_EditRecord in REQUEST DECISIONS

  • How it works: A Formula-Rich Text field with API_EditRecord could be used to directly update the status in the REQUEST DECISIONS table, making it easy to capture each approver's decision (approve/reject) for a specific request. After updating REQUEST DECISIONS, you could use an additional API_AddRecord action to copy the status change details into the DECISION STATUS CHANGES table for historical tracking.
  • Pros:
    • Directly updates the status in REQUEST DECISIONS, so you can see the current status for each approver in one place.
    • Easier to manage a single source of truth for each approver's decision.
  • Cons:
    • Requires an additional step to create a historical log in DECISION STATUS CHANGES.

2. Using API_AddRecord in DECISION STATUS CHANGES

  • How it works: Here, you would create Formula-Rich Text button fields that trigger API_AddRecord actions to add a new status change record to the DECISION STATUS CHANGES table directly. Each time an approver makes a decision, a new record in DECISION STATUS CHANGES will capture the action, with fields indicating the approver, request ID, and decision.
  • Pros:
    • Automatically generates a history of all status changes without needing extra steps.
    • Each decision action creates a new entry, making it simple to see all status changes over time.
  • Cons:
    • Would require additional queries or formulas to retrieve the current status for each approver since DECISION STATUS CHANGES would hold multiple records per request.

Recommendation

To streamline the workflow while maintaining both current and historical statuses:

  1. Use API_EditRecord in REQUEST DECISIONS to update the status directly when an approver takes action.
  2. Immediately after, use API_AddRecord to insert a new record into DECISION STATUS CHANGES to log the decision.

This approach leverages the strengths of each table while ensuring that both the current status and historical log are maintained. The Formula-Rich Text buttons can trigger both API calls sequentially to achieve this without user intervention.

  • Funny to be competing against CHAT GPT now.  It's a new world, huh?

    If it wuz me I would have the single source of truth being the child log table and use a summary field to roll up the current status.  Then I know for sure that there can never be a mismatch between the most recent child record and the status on the Parent as it's driven by summary fields and formula as opposed to an Automated Pipeline which can theoretically fail.

    I would probably do a summary maximum of the [Record ID#] of the most recent Child DECISION STATUS CHANGE, and then a reverse relationship where One  DECISION STATUS CHANGE has many REQUEST DECISIONS based on that Max Record ID# field. Then look up the value of the most recent decision,  and who entered it and when up to the decision record.

    If a reverse relationship seems too weird or too cluttered to you then you are free to use Combined Text Summary fields and summary maximum fields to summarize up certain fields from the the most recent record up to the Parent based on a filter that the  child record [Record ID#] is equal to that Maximum Record ID# field which you summarized up to the parent  and then would lookup back down to the child.

     

    • kheatley's avatar
      kheatley
      Qrew Member

      Mark

      Thanks for quick reply.  I actually have had some help from Don in setting this app up.  He helped me set up what you mention above (Max RID#) and I have a button in my REQUEST DECISIONS report that allows an approver to make their decision via the button, which saves the record in DECISION STATUS CHANGES table that is pulled into REQUEST DECISIONS via Max RID.  

      Button formula: If([LEAVE REQUEST - LEAVE STATUS]="FINAL APPROVAL", "", URLRoot() & "db/" & [_DBID_DECISION_STATUS_CHANGES] & "?a=API_GenAddRecordForm&_fid_26=" & URLEncode ([Record ID#])& "&dfid=11" & "z=" & Rurl())

      So you're saying that you would go with the opposite recommendation from chatgpt?

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend

        I guess what I'm saying is that independently Don and myself had the same recommended solution. Your source of truth is the Child table and you flow the most recent status update up to the parent record using formulas and summary fields which can never fail. So yes,  imho, our two brains are smarter than Chat GPT. 

        Under the Chat GPT recommended solution you can have a theoretical mismatch between the children and the parent record status, then you might need to have a safety net report to detect those differences and manually correct them. 

  • Ok so I am now trying to create formula in child...

    Formula:
    //Adds new DECISION STATUS CHANGES record with status = "Approved"

    var text SIGNIN = URLRoot() & "db/main?a=SignIn";
    var text apptoken = "b6w3x2vddzcyk3c8upwsnbu5vqbw";

    var text APPROVED =
    URLRoot() & "db/" & Dbid() &
    "?a=API_AddRecord" &
    "&rid=" & URLEncode ([Record ID#]) &
    "&apptoken="& $apptoken &
    "&_fid_25=" & "Approved" &
    //"&_fid_42=" & Now() &
    //"&_fid_32=" & ToText(User()) & 
    "&rdr=" & URLEncode(URLRoot() & "db/" & Dbid() &
    "?a=dbpage&pageID=2");
    $SIGNIN &
    "&NextURL=" & URLEncode($APPROVED)

    Ex:
    https://zzz.quickbase.com/db/main?a=SignIn&NextURL=https%3A%2F%2Fzzz.quickbase.com%2Fdb%2Fbuk4s63ek%3Fa%3DAPI_AddRecord%26rid%3D456%26apptoken%3Db6w3x2vddzcyk3c8upwsnbu5vqbw%26_fid_25%3DApproved%26rdr%3Dhttps%253A%252F%252Fzzz.quickbase.com%252Fdb%252Fbuk4s63ek%253Fa%253Ddbpage%2526pageID%253D2

    For some reason this is opening in the wrong table altogether, my LEAVE REQUEST table (the parent of REQUEST DECISIONS which is parent of DECISION STATUS CHANGES).  

    Table #1FACULTYbuk4s62z5
    Table #2LEAVE REQUESTSbuk4s623u
    Table #3LEAVE TYPES LISTbuk4s625z
    Table #4APPROVERS LISTbuk4s627y
    Table #5REQUEST DECISIONSbuk4s629y
    Table #6DECISION STATUS LISTbuk4s63bx
    Table #7DECISION STATUS CHANGESbuk4s63ek



    Can anyone see why it's not accessing DECISION STATUS CHANGES table? 

    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend

      I think I would do it this way.

       

      var text APPROVED =
      URLRoot() & "db/" & [DECISION STATUS CHANGES] &
      "?a=API_AddRecord" &
      "&rid=" & URLEncode ([Record ID#]) &
      "&apptoken="& $apptoken &
      "&_fid_25=" & "Approved" &
      //"&_fid_42=" & Now() &
      //"&_fid_32=" & ToText(User());

      var text LandingPage = URLRoot() & "db/" & AppID() &
      "?a=dbpage&pageID=2");

      $Approved

      & "&rdr=" & URLEncode($LandingPage)

      I'm not sure that you need that Sign On Step.

       

      If you find that you do, it would look like

      But if you do 

      $SIGNIN
      & "&NextURL=" & URLEncode($Approve)
      & URLEncode("&rdr=" & URLEncode($LandingPage))

  • This formula would be called from within an email so wouldn't it be best practice to ask for user to sign into quickbase, as only approved users can access app?

    Also, I got "LEAVE REQUEST Record Not Found" error when trying the url: https://mit.quickbase.com/db/buk4s63ek?a=API_AddRecord&rid=465&apptoken=b6w3x2vddzcyk3c8upwsnbu5vqbw&_fid_25=Approved&rdr=https%3A%2F%2Fmit.quickbase.com%2Fdb%2Fbuk4s62x2%3Fa%3Ddbpage%26pageID%3D2
    though the code is supposed to be just adding a new record to DECISION STATUS CHANGES, connecting this record with it's related APPROVER, REQUEST DECISION, and LEAVE REQUEST.

    Code taken from your reply and edited to fit my app:

    var text APPTOKEN = "b6w3x2vddzcyk3c8upwsnbu5vqbw";
    // Define the Approval URL to add a new record to the DECISION STATUS CHANGES table
    var text APPROVED =
    URLRoot() & "db/" & [_DBID_DECISION_STATUS_CHANGES] &
    "?a=API_AddRecord" &
    "&rid=" & URLEncode ([Record ID#]) &
    "&apptoken="& $APPTOKEN &
    "&_fid_25=" & "Approved";
    var text LandingPage = URLRoot() & "db/" & AppID() &
    "?a=dbpage&pageID=2";
    $Approved
    & "&rdr=" & URLEncode($LandingPage)

    Any ideas why it is trying to access a LEAVE REQUEST record instead of adding a record to DECISION STATUS CHANGES?

  • Let try this

    var text APPTOKEN = "b6w3x2vddzcyk3c8upwsnbu5vqbw";


    // Define the Approval URL to add a new record to the DECISION STATUS CHANGES table
    var text APPROVED =
    URLRoot() & "db/" & [_DBID_DECISION_STATUS_CHANGES] &
    "?a=API_AddRecord" &
    "&_fid_99=" & URLEncode ([Record ID#]) & // set 99 to be the field for related parent.
    "&apptoken="& $APPTOKEN &
    "&_fid_25=" & "Approved";


    var text LandingPage = URLRoot() & "db/" & AppID() &
    "?a=dbpage&pageID=2";


    $Approved
    & "&rdr=" & URLEncode($LandingPage)

    • kheatley's avatar
      kheatley
      Qrew Member

      Sadly it gives same error

      LEAVE REQUEST Record 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 LEAVE REQUEST record no longer exists.

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend

        Can you contact me directly and we will sort this out  on a quick Zoom call. 
        mark.shnier@gmail.com