formulas

228 Topics

URL formula to Add Current User to Userlist
I have a table with a userlist, I want to have a button that the current user viewing records can click to add themselves to a userlist field. Having trouble remembering how I have done it before.
StevenNelson
9 hours ago Place Quickbase Discussions
43Views
0likes
2Comments
Help me write a formula that is dynamic
Me again. This is the perfectionist in me. I'd love suggestions on if it's possible (and how) to write a formula that calculates a person's age based upon DOB field, converts it into a category ("0-12", "13-17", etc.), AND ALSO where that field can apply the reference date based upon a report filter. So, if in the future I need to see a report and the age needs to convert to the date where it would be in the beginning of that date range (or even more ideally, if it can reference the first date of service within the filtered time range on a connected table and apply the rule as it would have been in that time frame), that way I can always see what we would have received as a report-out at that point in time if I needed to go back and spot-check something. I know that sounds terribly convoluted. It's for grants reporting, and honestly these are some of the items that get at some of the most obnoxious calculations we're asked to report out on...... If I can't make it perfect, no big deal, but I thought I'd see what you all can conjure up. Example: Person is 17 at the time they come to receive services for the first time. The dynamic field should be able to reference my "Services" table, see when the first Service record within the filtered quarter is, and apply the age range. The following quarter, we'd actually report them out again, but applied to the new quarter -- and let's say they turned 18, so for THAT filter, I want it to now produce the updated age group. Ultimately, accuracy within my present reporting period is of chief importance, but ideally, the ability to pull up historic records and check would really be fantastic.
ChristineK
8 days ago Place Quickbase Discussions
56Views
0likes
4Comments
Replacement for "Copy Parent & Child Records" Button Creator?
In App Settings -> App Management, there's an option to create a button to add to forms to "Copy Parent & Child Records." However, the buttons created by this process are powered by Javascript, which I was under the impression was no longer really supported. Is there another option for recursively copying a parent record and any related child records (and child of child records)? Or can this button generator still be trusted despite the JS?
ALinic
15 days ago Place Quickbase Discussions
189Views
1like
12Comments
List If Formula
Why is my list if formula only returning the first if statement List(", ", If([Engr check]=true and [Engr Approval Status]<>"Approved",UserToName([Engineering Manager Assigned]), If([Production Check]=true and [Production Approval Status]<>"Approved",UserToName([Production Manager Assigned]), If([Quality Check]=true and [Quality Approval Status]<>"Approved",UserToName([Quality Manager Assigned]), If([Additional Check]=true and [Additonal Approval Status]<>"Approved",UserToName([Additional Approval Assigned]))))),"")
sburres
30 days ago Place Quickbase Discussions
39Views
0likes
1Comment
Code Page Formula Button
I have a formula button whose intended purpose is to check a box on the record (to start a pipeline) then redirect to a code page that is coded to pause, then check if a status field has changed, if not, pause again. If the field is changed it returns the user to the form originally launched from. I'm trying to cobble together different posts/search results as I haven't been able to find any direct examples of my need. This formula works to open the code page and passes the correct parameters for checking the status, but it doesn't check the box and thus the pipeline never runs and the page never closes. var text codePageID = "6"; // Replace with your actual code page ID var text recordID = ToText([Record ID#]); var text currentStatus = URLEncode([Charges Pipeline Status]); // Replace with your field's name var text url = URLRoot() & "db/" & AppID() & "?a=dbpage&pageid=" & $codePageID & "&rid=" & $recordID & "&initialStatus=" & $currentStatus; "<a style= \" text-decoration:none; background: #b4b55d; border-radius: 15px; color: #fff; display: inline-block; padding: 10px 10px 10px 10px; width: 100%; font-size: 14px; text-align: center;\" href='" & $url & "'>Create New Charges</a>" This formula correctly edits the checkbox but doesn't pass the status parameters (I do have the correct app token inserted in the field - replaced with [apptoken] here for reference: var text urlToExecute = URLRoot() & "db/" & Dbid() & "?a=API_EditRecord&apptoken=[apptoken]" & "&rid=" & [Record ID#] & "&_fid_12=true"; "<a style= \" text-decoration:none; background: #b4b55d; border-radius: 15px; color: #fff; display: inline-block; padding: 10px 10px 10px 10px; width: 100%; font-size: 14px; text-align: center;\" href='" & AppID() & "?a=dbpage&pageid=3" & "&url=" & URLEncode($urlToExecute) & "'>Create New Charges</a>" This is my attempt at trying to combine the 2 formulas above: var text codePageID = "6"; // Replace with your actual code page ID var text recordID = ToText([Record ID#]); var text currentStatus = URLEncode([Charges Pipeline Status]); // Replace with your field's name var text url = URLRoot() & "db/" & Dbid() & "?a=API_EditRecord&apptoken=pamm2bcsqv939bufwq8hwzvwx4" & "&rid=" & [Record ID#] & "&_fid_12=1" & AppID() & "?a=dbpage&pageid=" & $codePageID & "&rid=" & $recordID & "&initialStatus=" & $currentStatus; "<a style= \" text-decoration:none; background: #b4b55d; border-radius: 15px; color: #fff; display: inline-block; padding: 10px 10px 10px 10px; width: 100%; font-size: 14px; text-align: center;\" href='" & $url & "'>Create New Charges</a>" When I use the third formula, I get an error that says the XML file does not appear to have any style information associated with it and shows the following tree: <qdbapi> <action>API_EditRecord</action> <errcode>0</errcode> <errtext>No error</errtext> <rid>7</rid> <num_fields_changed>0</num_fields_changed> <update_id>1757601111721</update_id> </qdbapi> Here is the script for the code page: <script> // Configuration const recordId = new URLSearchParams(window.location.search).get('rid'); const initialStatus = new URLSearchParams(window.location.search).get('initialStatus'); const dbid = 'bvcwn2ues'; // <<< REPLACE WITH YOUR TABLE'S DBID const appToken = 'pamm2bcsqv939bufwq8hwzvwx4'; // <<< REPLACE WITH YOUR APP TOKEN IF NEEDED const statusFieldId = '40'; // <<< REPLACE WITH YOUR STATUS FIELD'S FID const checkInterval = 120000; // 120 seconds in milliseconds function checkStatus() { $.ajax({ url: window.location.origin + '/db/' + dbid + '?a=API_DoQuery&qid=1&options=csv&apptoken=' + appToken + '&query={' + recordId + '.EX.' + recordId + '}', type: 'GET', success: function(response) { const lines = response.trim().split('\n'); if (lines.length > 1) { const data = lines[1].split(','); const currentStatus = data[statusFieldId]; if (currentStatus !== initialStatus) { // Status has changed! Redirect to the updated record. window.location.href = window.location.origin + '/db/' + dbid + '?a=dr&rid=' + recordId; } else { // Status is the same. Pause and check again. console.log('Status has not changed. Checking again in ' + checkInterval / 120000 + ' seconds.'); setTimeout(checkStatus, checkInterval); } } else { console.log('Record not found or API error. Retrying...'); setTimeout(checkStatus, checkInterval); } }, error: function(xhr, status, error) { console.error('API Error: ' + status, error); setTimeout(checkStatus, checkInterval); } }); } // Start the check after a brief initial pause setTimeout(checkStatus, checkInterval); </script> I'm thinking I might need to add another const under the Configuration, but I just don't know enough to determine that. Or, I'm thinking that the structure of the button formula itself is where adjustments need to be made.
Solved
EmberKrumwied
2 months ago Place Quickbase Discussions
449Views
0likes
16Comments
Create a url for a third party to create a new record
I wanted to know is it possible to create a url that can be shared with another application that uses http as a connector, that will allow it to post to QuickBase creating a new record. If so, what would the format be?
CarolMcconnell
2 months ago Place Quickbase Discussions
56Views
0likes
2Comments
Need to display hours and minutes within a TAT formula
I am using the formula below for a duration TAT. I have checked off the HH:MM:SS for the type and it is still displaying 0:00:00 for things that are one hour from rec'd to acknowledged time entered Days(WeekdaySub(ToDate([Acknowledgment Date/Time]),ToDate([Communication Received Date/Time]))) Do you have any suggestions on how to get the TAT to display accurately? Thank you Jill
JillKauffman
2 months ago Place Quickbase Discussions
93Views
0likes
4Comments
Prefilled Value with a Button
Hi everyone, I’ve created a “Copy Line Item” button that is supposed to create a new record and prefill it with the same values selected by the user in the original record. The button works, but I’m running into an issue: the following fields are not being prefilled when the new record is created: [Related Trademark] [Related Beverage Type] [Related Package] All three of these fields are text field types, populated through relationships from their parent tables. The values that show up in them are alphanumeric (e.g., A1, B1, C700, etc.), and they are key fields in their respective tables. For some reason, when I click the button, these related fields don’t carry over into the new record. Has anyone run into this issue before, or know how to get these relationship-based fields to prefill correctly? For reference the code is in the attachment section. var text nonNational = URLRoot() & "db/" & [_DBID_PR_LINE_ITEMS] & "?a=API_GenAddRecordForm&dfid=22&_fid_13=" & URLEncode([Related Price Request]) & "&_fid_10=" & URLEncode([Start Date]) & If(IsNull([End Date]), "&_fid_11=" & URLEncode("2050-12-31"), "&_fid_11=" & URLEncode([End Date])) & "&_fid_12=" & URLEncode([Rate]) & "&_fid_6=" & URLEncode([Customer Option]) & If([Customer Option] = "Customer", "&_fid_376=" & URLEncode([Related Customer4]) , [Customer Option] = "Local Chain","&_fid_360=" & URLEncode([Regional Account - LocalChainId]) , [Customer Option] = "Promo Plan","&_fid_365=" & URLEncode([Related RegionalPromo]) , [Customer Option Channel] = "Channel","&_fid_34=" & URLEncode([Related Market]) ) & "&_fid_85=" & URLEncode([Geography Option]) & If([Geography Option] = "Included All Price Zone", "" , [Geography Option] = "Specific Price Zone","&_fid_73=" & URLEncode([Related Price Zone]) , [Geography Option] = "Specific Sales Office","&_fid_73=" & URLEncode([Related Price Zone]) & "&_fid_74=" & URLEncode([Related Price Zone - Sales Office Relationship]) ) & "&_fid_55=" & URLEncode([Product Option]) & If([Product Option] = "Material", "&_fid_70=" & URLEncode([Related Material]) , [Product Option] = "Beverage Type & Package Size","&_fid_42=" & URLEncode([Related Beverage Type]) & "&_fid_46=" & URLEncode([Related Beverage Type - Package Relationship]) , [Product Option] = "Trademark & Package Size","&_fid_50=" & URLEncode([Related Trademark]) & "&_fid_52=" & URLEncode([Related Trademark - Package Relationship]) , [Product Option] = "Package Size Only","&_fid_72=" & URLEncode([Related Package]) ); $nonNational Thanks in advance for your help!
SMK
2 months ago Place Quickbase Discussions
104Views
0likes
4Comments
Formula to calculate hours
Please help write a formula to take estimated hours time different frequencies (daily, weekly, monthly) and calculate monthly hours. I currently have box for estimated hours, and radio buttons for different frequencies (daily, weekly, monthly, quarterly, yearly).
Solved
PennyPalmer
2 months ago Place Quickbase Discussions
224Views
0likes
8Comments
Cancel and Go Back Button
Hi Everyone, I'm new to Quickbase and could use some help. I'm building an application with two related tables: Price Request (Header Table) Attachments Table (Each Price Request can have multiple attachments) I've created a form in the Price Request table with an "Add Attachment" button that routes users to the Attachment form, allowing them to upload documents. Here's the formula I'm using for that: URLRoot() & "db/" & [_DBID_ATTACHEMENTTABLE] & "?a=API_GenAddRecordForm&_fid_7=" & URLEncode([Record ID#]) & "&z=" & Rurl() Now, I want to add a "Cancel and Go Back" button on the Attachment form that: Deletes the current attachment record. Redirects the user back to the related Price Request record. Here's the formula I tried: URLRoot() & "db/" & Dbid() & "?a=API_DeleteRecord" & "&rid=" & URLEncode([Record ID#]) & "&apptoken=APP_TOKEN" & "&rdr=" & URLEncode( URLRoot() & "db/" & [_DBID_PRICEREQUESTTable] & "/form?a=dr&rid=" & [Related Price Request]) But I’m getting this error: <qdbapi> <action>API_DeleteRecord</action> <errcode>30</errcode> <errtext>No such record</errtext> <errdetail>Missing "rid" parameter.</errdetail> </qdbapi> In the picture below(Attachment Form) Field Pricing Request ID is a lookup field from Price Request Table(Related Price Request) Field Price Request - Status is a look up field from Price Request Table
SMK
2 months ago Place Quickbase Discussions
83Views
0likes
2Comments