Formula Rich Text Button: Report Link
Hey! Mainly for aesthetic reasons, I am attempting to create a Formula Rich Text Button that just points to the Report Link (Built through a relationship). "<table style='width:100%;border: 0px Solid White'>" & "<tbody>" & "<tr>" & "<td style='vertical-align:top;text-align:Left;'>" & "<div " & $stylebtn & ">" & "<a href='" & [Report Link] & "' style='color: inherit; text-decoration: none;'>Tittle</a>" & "</div>" & "</td>" & "</tr>" & "</tbody>" & "</table>" The Formula returns no errors, but when pressed it just returns back to the Original record. I am trying to get it to open a report of the Child Records using the Embedded for Report. If I can't do it that way (which seems simplest), I think I would have to do a whole query within a URL. Writting a query isn't the problem so much as I am not sure how to direct a URL to use a Particular report with a Report ID. Any suggestions on either how to modify the formula to get it to actually use the Report Link, or on how to generate a Formula URL that goes to the Report but filters using the Query?35Views0likes1CommentPreventing Budget Overrun
My objective is to prevent users from exceeding the available budget when logging time. Users can attach multiple task records to the DSR (Daily Status Report), and those tasks can belong to different budgets. I want to use a Formula query to obtain the value in the [Remainder (Available)] Field 64 of _dbid_budget, and compare the total of hours logged in [Units Delivered] Field 103 of _DBID_TASKS. Once all tasks are attached to a DSR, the Total Units Delivered Field 108 of _DBID_TIME_CARD (where the DSRs reside), I would want to compare available budget to hours consumed on tasks, and if there is an overage i will stop the flow of the report until it is resolved. I used a summary [Budget Line - Record ID# (Text)] field 267 on the DSR form configured for combined text, and was able to validate that I can see two correct record ID's when attaching 3 tasks and two of them using the same budget. The [Total Available Budget] field 268 formula on _DBID_TIME_CARD has no errors, but fails to render anything other than zero: SumValues( GetRecords( "{3.IN.('" & [Budget Line - Record ID# (Text)] & "')}", [_DBID_BUDGET] ), 64 ) Where 3 is the [record ID#] and 64 is [Remainder (Available)], both on the budget. Any input will be appreciated.Solved68Views0likes7CommentsPreventing Scheduling Conflicts
I'm helping develop an app to track and reserve company vehicles. One of the requirements given to me was to prevent scheduling conflicts for the vehicles - essentially not allowing any overlaps for a given vehicle. I have searched this before and I found some answers on these forums that helped point me in the right direction but I'm still coming up short. What I've done is pair two formula queries into a field that would check for start and end time overlaps and add the queries together. The field is simply called Conflicts and if it is over 0, I know there is a conflict. My issue is two-fold. The first formula I used and works (sort of, we'll get to that) is Size(GetRecords("{7.OAF.'"&[Start]&"'} AND {7.OBF.'"&[End]&"'} AND {10.CT.'"&[Vehicle - Vin Last Four]&"'}",[_DBID_RESERVATIONS])) + Size(GetRecords("{6.OAF.'"&[Start]&"'} AND {6.OBF.'"&[End]&"'} AND {10.CT.'"&[Vehicle - Vin Last Four]&"'}",[_DBID_RESERVATIONS])) First things first is that this means each record will presumably 'find itself' so the result will always be at least 2. This is mitigated by simply adding '- 2' to the end to account for that. The trouble is that if you create a new record, it's the pre-existing records that count the conflict, not the new one, which is useless if I'm trying to use a form rule to prevent a new record from conflicting with others. Swapping the OAFs and OBFs seems to work logically but doesn't deliver the expected results. So that's my first sticking point is that I can't seem to get the logic in this formula correct. I just need to check to see if the starting or ending time falls between the start and end times of other records using the same vehicle. My second issue is that even testing this with NOT accounting for a default result of 2, I can't get the form rules to stop a save if the conflict is > 0. Which does make sense to me since it can't count the records before it exists, but I'm not sure how else to notify someone that the record they are trying to create has a conflict. The form rule does work if I reopen the record but that doesn't help during creation. Does anyone have any ideas on how to get this working? Many thanks in advance!Solved40Views0likes2CommentsMulti-Upload Button
Hi there, We had a form button (URL formula field) that allowed us to upload multiple documents at one time. Here is the formula: var text URL = URLRoot() & "db/"&Dbid() & "?a=dbpage&pageID=4&refFid=7&rid=" & [Project #] & "&fileattachment=36" & "&attachmenttableurl=" & URLRoot() & "db/" & [Project Name]; If(IsNull([Project Manager]), $URL) After the last QB update (last month?) it stopped working. Can anyone assist with this issue? Much Appreciated, Veronica23Views0likes1CommentTrigger RULE is there is a change in an attachment field
I have RULE that says when "Ready for Approvals" is checked and an attachment has been added, it triggers an email. Now I want that email to be triggered again if the attachment is modified, that is someone saves over the attachment again (they may or may not change the name). How do track that an attachment has been modified? Thanks! DeepaSolved29Views0likes2CommentsFind Multiple Dates older than 2 Weeks and not blank.
For future me. Leaving this here because I was unable to find any reference using the Khoros Search or Google Search. If((ToText([Email Date]) <> "" and [Email Date] <= (Today()- Days(14))) or (ToText([Instructions Send Date]) <> "" and [Instructions Send Date] <= (Today()- Days(14)) or (ToText([Verification Email Date]) <> "" and [Verification Email Date] <= (Today()- Days(14)))),true,false) This formula defines a checkbox formula field. The Checkbox formula field is used in a Pipeline to filter records meeting the criteria. The entire purpose of the Pipeline is to clear the dates from these records once they are older than 14 days in the past. I add this formula field to a report with the fields being evaluated and then look at the report. Only the ones that meet my criteria should be checked. I can use the report to verify it is working or make changes as needed. Once satisfied with the results, I add the formula checkbox to the Pipeline as a single filter. In case you're wondering, I also put in a Give Feedback asking to grant rights to use reports as a filter in a Pipeline.27Views0likes2CommentsIdentifying identical values between two table
Hi Everyone, It's been a while since using QB and my brain appears to have lost QB memory! I have two tables: Preferred Providers eligible for auto-assignment All Providers that have been auto-assigned (regardless of preferred designation) I am looking for a formula that will look for a TIN match between both tables and pull into table 2 under new field "Preferred Provider" Ultimately, I need to identify the auto assigned preferred providers without scrubbing a list of thousands of TINs. Any help is greatly appreciated!41Views0likes2CommentsExtract Date and Comment from prepend log entry field
I have created a formula field by piecing together answers from similar posts, but now need to take things a step further and can't seem to find the answer. I have a notes field (Status Update) that is set to log field entries which prepends the newest entry at the top. The field displays as: [DATE User] Comment Additionally, the formula results in showing either the maximum of the three most recent entries, or 2 if only 2, or 1 if only 1, or displays "No Update" if none. Original formula: var text LINEONE = Trim(Part([Status Update],2,"[")); var text LINETWO = Trim(Part([Status Update],3, "[")); var text LINETHREE = Trim(Part([Status Update],4,"[")); If(Length($LINEONE) < 1 and Length($LINETWO) < 1 and Length($LINETHREE) < 1, "No Update", If(Length($LINEONE) > 1 and Length($LINETWO) < 1 and Length($LINETHREE) < 1, "- " & $LINEONE, If(Length($LINEONE) > 1 and Length($LINETWO) > 1 and Length($LINETHREE) < 1, "- " & $LINEONE &"\n"& "- " & $LINETWO, "- " & $LINEONE &"\n"& "- " & $LINETWO &"\n"& "- " & $LINETHREE))) The request came back that they would only like to see the Date and the Comment (not the User name). I was able to modify the above formula to remove the User name, but only for the top most entry. I am trying to modify the original formula so that it will return just the date of the comment and the comment. The updated formula (below) does strip out the User name, but only for the most recent entry. Updated formula: var text LINEONE = NotLeft(Left([Status Update]," "),1) & ": " & Part([Status Update],2,"]"); var text LINETWO = NotLeft(Left(Part([Status Update],2,"["),9),9) & ": " & Part([Status Update],3,"]"); var text LINETHREE = NotLeft(Left(Part([Status Update],3,"["),9),9) & ": " & Part([Status Update],4,"]"); If(Length($LINEONE) < 3 and Length($LINETWO) < 3 and Length($LINETHREE) < 3, "No Update", If(Length($LINEONE) > 1 and Length($LINETWO) < 3 and Length($LINETHREE) < 3, "- " & $LINEONE, If(Length($LINEONE) > 1 and Length($LINETWO) > 1 and Length($LINETHREE) < 3, "- " & $LINEONE &"\n"& "- " & $LINETWO, "- " & $LINEONE &"\n"& "- " & $LINETWO &"\n"& "- " & $LINETHREE))) This successfully pulls out the User name, but only for $LINEONE. [Status Update] field actual entries: [AUG-28-24 QuickBase Admin] 7.30.24 New offer received from Diamond Rock Construction - low at $2.2 mil. [AUG-28-24 QuickBase Admin] 7.11.24 Price renegotiations during feasibility period have failed and Buyer has submitted a rescission agreement. We will go back to market. [AUG-28-24 QuickBase Admin] 7.3.24 Price reduction request received for $2,212,800. Pushing back to $2,790,000. [AUG-28-24 QuickBase Admin] 6.10.24 Title Review period has passed. 10 days from receipt of Title was 6/3. [AUG-28-24 QuickBase Admin] Blackout dates of closing early due to office outages. Output of [Status Update] after running updated formula: - AUG-28-24: 7.30.24 New offer received from Diamond Rock Construction - low at $2.2 mil. [AUG-28-24 QuickBase Admin - : 7.11.24 Price renegotiations during feasibility period have failed and Buyer has submitted a rescission agreement. We will go back to market. [AUG-28-24 QuickBase Admin - : 7.3.24 Price reduction request received for $2,212,800. Pushing back to $2,790,000. [AUG-28-24 QuickBase Admin Any suggestions to my formula that would result in the formatting of the 2nd and 3rd most recent entries to match that of the 1st most recent entry? ThanksSolved33Views0likes1CommentNeed help resizing pop-up window
I am trying to my Change Status field to a Formula Rich Text field from Formula URL field because the popup that opens when clicked is never the correct size for me. This is what I was able to cobble together with my limited qb knowledge but it gives me and error saying it doesn’t allow javascript. I can’t suss out why. Any ideas? var text RID = If([LEAVE REQUEST - LEAVE STATUS]="FINAL APPROVAL", "", "%%rid%%"); // only displays button if decision is ‘pending’ or ‘rejected’ var text URL = URLRoot() & "db/" & [_DBID_DECISION_STATUS_CHANGES] & "?a=API_GenAddRecordForm&_fid_26=" & URLEncode ([Record ID#])& "&dfid=11" & "z=" & Rurl(); // when clicked opens popup window with DECISION STATUS CHANGES form #11 – Add Form Temp "<a class='Vibrant Success' onclick=\"window.open('" & $URL & "','Edit','width=800,height=700')\">MAKE DECISION</a>" // link code for popup with dimensions for width and height46Views0likes3CommentsFormula Help
Hello, I need help combining text fields into a formula(s). Here are my formulas: [Finance Disposition]=""&[(Text) PipeLine Value Requested]="<$500,000"),"YELLOW") I'm getting the operator "=" can't be applied on types bool, text error message [Current Status]="Submitted",[Finance Disposition]="",[(Text) PipeLine Value Requested]="<$500,000","Red") I'm getting expecting text, but found bool error message70Views1like3Comments