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:
- 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
- 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:
- Use API_EditRecord in REQUEST DECISIONS to update the status directly when an approver takes action.
- 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.
