formulas

222 Topics

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
5 days ago Place Quickbase Discussions
240Views
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
8 days ago Place Quickbase Discussions
47Views
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
12 days ago Place Quickbase Discussions
73Views
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
14 days ago Place Quickbase Discussions
61Views
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
15 days ago Place Quickbase Discussions
122Views
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
19 days ago Place Quickbase Discussions
55Views
0likes
2Comments
Quickbase Native File Attachment Question
I think this is a simple thing, but I've been struggling with it for a while, so I thought I'd ask the community. I have a direct quickbase file attachment on a form. I want to only show that in edit mode (easy), then I want a button that will download that file when in view mode. I've tried formula URL and Formula Rich Text, but I can't get the syntax right I guess, because I continually get a 400 error. Can someone tell me how to accomplish this please?
JoeHargrave
21 days ago Place Quickbase Discussions
90Views
0likes
4Comments
Any tips for ensuring system calculations display consistently?
I have a billing application that tracks Assets, their monthly rates, and the Rental Charges. I'm finding instances when the same field doesn't display the same value. Example: Sub-Total field when viewing that field on an embedded report shows one number, but if I view the record directly, it shows another. The difference is nearly always a $0.01 difference. But this isn't the only field that this happens to. So I'm seeking any tips or general good practices I should consider when creating calculation fields to ensure the data is consistent across the application. I currently have nearly 15 different calculations that a record may need to compute, store and display. I've played around with using ROUND in some places, but that seems to cause additional problems.
Solved
EmberKrumwied
21 days ago Place Quickbase Discussions
86Views
0likes
4Comments
Button formula to force a "reset" of snapshot field.
I have a parent table - Customers - there is a field for Sales tax rate. This rate can change periodically, so in my Charges table (that looks up the Customer) I am looking up the sales tax rate from the related Customer and then placing that value in a snapshot field. Charge calculations then use the snapshot field for the tax rate that way, if the Customer rate changes, historical charges will still be correct. From time-to-time, new Charges are created before the Customer tax rate was modified, so now the new Charges are out-of-sync with the correct value. The only way I know to correct this is to edit the charge, remove the customer link, save and keep working, then reselect the customer. Is there a way to build that logic into a button that would accomplish the same thing? Essentially turning 5 clicks into 1?
Solved
EmberKrumwied
22 days ago Place Quickbase Discussions
45Views
0likes
2Comments
Setting Default Report Row Colors based on Date Modified
I just spent several hours trying to come up with a creative solution to a problem. We want a record where the Date Modified is on or before a specific Date to have a different color, in this case grey. Easy we thought, this should work. - It doesn't. If(ToDate([Date Modified])=ToDate(6/22/21),"#666666","") Tried to add a formula check box field to get an idea what was happening. Check box field namd [Date Formula]: Part(ToText([Date Modified]),1, " ") this works when used in the color formula. Then in the Default report in the Color formula: - kinda seems to work but not really. If([Date Formula],"#666666","") Tried this one and no luck If(Part(ToText([Date Modified]),1, " ") < "06-22-23","#666666","") I asked ChatGPT and this came out: If([Date Modified] <= ToTimestamp(Date(2021, 6, 23)),true,false) So I was trying my best to convert Date Modified to a Date and failing. Instead by converting the static date I wanted to use as my filter to a Timestamp everything fell into place. Leaving this here because I couldn't find anything similar. Please let me know if there is a solution already in this forum and I will link it here.
JimHarrison
2 months ago Place Quickbase Discussions
62Views
0likes
2Comments