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: 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.22Views0likes4CommentsIn-App Formulas - Removing Duplicates From A List or String
I'm working on a formula to concatenate values from multiple multi-select text fields while removing any duplicates in the output. My goal is to create a unique, semicolon-delimited list that’s easy to process further. Here’s a breakdown of the fields I’m working with: [Related Modification - Operator Access] [Related Project - Operator Access] [Related Phase - Operator Access] [Note Replying To - Operator Access] [Related User - Affiliation] Quickbase’s formula language doesn’t support loops, assignments, or a direct Unique() function to filter out duplicates. Here are some of the approaches I’ve tried: Using Conditional If Statements I set up If statements that check each field’s value with Contains to see if it’s already present in the accumulating list. While this approach works in theory, I have been having trouble with type and syntax errors. It also quickly becomes verbose and difficult to maintain as the list of fields grows. var text initialList = ToText([Related Modification - Operator Access]); var text withProject = If(not Contains(ToText(initialList), ToText([Related Project - Operator Access])), List("; ", ToText(initialList), ToText([Related Project - Operator Access])), ToText(initialList)); ... and so on for each field Using List and Split Functions with Unique() Equivalent I attempted to split the concatenated list and simulate unique functionality by adding fields conditionally. Unfortunately, Quickbase lacks a direct Unique() function for removing duplicates from lists, so this approach didn’t work as expected. Help Requested Has anyone found an efficient way to concatenate and de-duplicate multi-select text fields in Quickbase? Ideally, I’d like a solution that’s concise, scalable, and doesn’t require manually adding each field with repeated If and Contains checks. Are there workarounds, or perhaps Quickbase functionality I’m overlooking, that could streamline this task? I would much prefer to keep the processing in the table as it pulls from other tables where the data can change. So it needs to be quite responsive and I don't want to have to declare an excessive amount of triggers. Any insights, examples, or alternative approaches would be greatly appreciated!8Views0likes2CommentsFORMULA ASSISTANCE NEEDED
This is my goal...getting error message tho. Have tried several other ways, but can't get it to work...thanks for your help. If( [Task - USE DEFAULT UNIT COUNT]=true,[Task - Default Unit Count], [Task]="CAULK HVAC VENTS", [Task - Default Unit Count], [Task]="HANG",[FIELD BOARD COUNT.TO PAY], [Task]="Drywall.Item",[Take/Off Calc - NON-STRUCTURAL ITEM COUNT], [Task]="SCRAP",[Job - Maximum FIELD BOARD COUNT.TO PAY], [Task]="FINISH" and [Job - Project - GARAGE.FINISH-TAPE ONLY]=true,([Job - Maximum FIELD BOARD COUNT.TO PAY]-13), [Job - Maximum FIELD BOARD COUNT.TO PAY], ****GETTING ERROR MESSAGE-expecting boolean, but found number***** [Task]="SAND" and [Job - Project - GARAGE.NO SAND]=true, ([Job - Maximum FIELD BOARD COUNT.TO PAY]-24), [Job - Maximum FIELD BOARD COUNT.TO PAY], [Task]="PRIME P/U" and [Job - Project - GARAGE.NO PRIME PT/UP]=true,([Job - Maximum FIELD BOARD COUNT.TO PAY]-24), [Job - Maximum FIELD BOARD COUNT.TO PAY], [Task]="PRIME", [Job Specific Take/Off - Take/Off - Total CEILING CALC HSF], [Task]="2nd COAT",[Job - Maximum Take/Off - BASE HOUSE.CALCULATED HSF], [Task]="FINAL T/U",[Job - Total Take/Off Calc - CEILING CALC HSF], [Task - Task Phase]="Paint.Interior.Item", [Take/Off Calc - NON-STRUCTURAL ITEM COUNT], [Task - Task Phase]="Paint.Exterior.Item", [Take/Off Calc - NON-STRUCTURAL ITEM COUNT])10Views0likes1CommentMerging 2 Fields into a Formula-Text Field for Reporting Purposes
Hello: I have two fields that I want to merge for reporting purposes. Application Status - Primary Application Status - Secondary I created a formula-text field named,Merge Primary/Secondary Application Status. I tried the formulas below, but none are working. Could someone assist me with tweaking any of my formulas, please? First Try If([Nursing]="",[Application Status - Primary Program],[Application Status - Secondary Program]) Second Try If( [Nursing]="Application Status - Primary Program", [Nursing]="Application Status - Secondary Program" , true, false, ) Thank you, Roula19Views0likes4CommentsApproval Button redirect to View Form
I used the magic buttons app Approval Button, and it seems to work great! But I noticed that it redirects back to whichever version of the form you were on when you clicked the button - if you click it from the View Form, it takes you back to the View Form version, whereas if you click it from the Edit view, it keeps you in Edit view. I'd like it to redirect to the View view in either case so that the User understands that it has been saved. Anyone know how to do this? Here's the formula: URLRoot() & "db/" & Dbid() & "?a=API_EditRecord&apptoken=APPTOKEN&rid=" &[Record ID#] & "&_fid_139=Approved&_fid_141=" & Now() & "&_fid_140=" & UserToEmail(User()) & "&rdr=" & URLEncode(URLRoot() & "db/" & Dbid() &"?a=doredirect&z=" & Rurl())Solved74Views1like4CommentsFormatting Date(s) in a Text Field
I am wondering how to format a date or potentially two dates in a Text field. I have a text field that is populated with up to two dates from a pipeline automation. The dates I believe are coming in as UTC. See example below. How can I get them displaying at MM-DD-YY or something similar?23Views0likes3CommentsFormula Assistance
Newbie here! I need to get the word 'County' inserted into this List formula after the [County] field so that it shows up as, Chicago, Cook County, IL instead of Chicago, Cook, IL. Can anyone help? Thanks! List(", ",[Address: City], [County], [Address: State/Region])Solved13Views0likes2CommentsFormula Assistance
Can't get this to work: [Job - Project - GARAGE.NO SAND]=true and [Task]="SAND",([Job - Maximum FIELD BOARD COUNT.TO PAY]-24), [Job - Maximum FIELD BOARD COUNT.TO PAY], [Job - Project - GARAGE.NO PRIME PT/UP]=true and [Task]="PRIME P/U",([Job - Maximum FIELD BOARD COUNT.TO PAY]-24), [Job - Maximum FIELD BOARD COUNT.TO PAY], thank you15Views0likes1CommentA way to look up a previous Record ID's [Date Created] Field
Hi, thank you in advance for any help, I am currently trying to figure out a way to look up a previous Record ID's date created and calculate the time between the old record ID's [Date Created] and the new Record ID's [Date Created] to calculate the time between for an SLA report function. For a description: I have 1 record that has an imbedded record called "Change History", Inside this change history, It logs any status changes that happen throughout the order. For Example: 123 John Street (Order Number #27) had a job status of "On Hold", When this changed from on hold to "In Progress" a new change history is logged which would look something like the following: Embedded report showing all records relating to order number #27 Record ID: #71, Date Created: 28/10/2024 14:22PM Order Created - 123 John Street Record ID: #84, Date Created: 29/10/2024 08:32AM On Hold - 123 John Street Record ID: #106, Date Created: 29/10/2024 08:52AM In Progress - 123 John Street What I want to do is work out the duration between the new Record ID(106)'s Date Created and the old Record ID(84)'s date created (I want to use this formula to do the same for Record ID 71 to Record ID 84 but its off the same basis) So the end result would return a value of 30 Minutes. The reason being is I am trying create a countdown timer which is straight forward but once this record is no longer the newest record I need to find the time taken between the 2 fields.Solved38Views0likes4CommentsBest practice for using @mention in a log rich text field.
Anybody have a best practice on using the 'Mention' feature in a log rich text field? My use case is: A rich text field w/ log edits turned on is used as a collaboration tool for team members to provide updates and ask each other questions. They mention each other in the comments and receive an email when they have been mentioned, via the handy 'send to users @mentioned' feature in the new notifications (see image below). However, when the 'Log edits' setting is turned on, Quickbase will email ALL the users who have ever been mentioned, when really we want just the person/people who were mentioned in the most recent comment. My workaround for this is to create a formula field to parse out the most recent comment and then a webhook or form rule to enter this value intoanother rich text field, with the @mention feature turned on. This last rich text field is used in the 'To' setting of the notification. This is a lot of work to simply use the mention feature! Is there a better way to do it that I'm missing?18Views1like0Comments