formula query

39 Topics

Creating a formula field to capture time intervals
I have a field that captures the time an event occurred, it is a Time of Day field type. These are examples of the output 8:13 am, 12:28 pm. I would like to create a field that lumps times into 1/2 hour intervals. For example, If the Time of Day is =>8:00 am and =<8:29 am then the interval would be captured as 8:00 - 8:30. If Time of Day is =>8:30 am and =<8:59am then the interval would be 8:30 - 9:00, etc. Nothing I've tried seems to work as I repeatedly run into sytax errors. Does anyone have suggestions? Thanks Sheila
SheilaAllas
2 days ago Place Quickbase Discussions
18Views
0likes
2Comments
Need help with a query formula for ranking dates.
I have a table of Training cycle start dates. Each record is a Training Start Date (FID 6 - Date Field) for a Training Team (FID 25 - Text lookup field from related Table) with a Start Date Status (FID 7 - Text). I have created a Formula Numeric field titled Rank. I need to rank the upcoming Training Start Dates for the Traditional team only, and exclude any dates that are past or do not have a Status of "Available." The one coming up soonest on/after today ranked 1, next ranked 2, and so on. We've tried several formulas without success. var text QUERY = "{6.AF.'" & Today() &"';}AND{25.EX.'Traditional'}AND{7.EX.'Available'}"; If([Start Date Status]<>"Available", null, Size(GetRecords($QUERY))+1) Attempt 1 above failed. Returned rank 11 for all available start dates, past and present. var number position = Size(GetRecords("{6.AF.'"& Today() &"'}AND{25.EX.'Traditional'}AND{7.EX.'Available'}")), $position+1 Attempt 2 above failed. Returned rank 10 for all available start dates, past and present. var text baseQ = "{25.EX.'Traditional'}AND{7.EX.'Available'}"; var text QAfter = $baseQ; var number position = Size(GetRecords("{6.AF.[Training Start Date]}AND{25.EX.'Available'}")); $position+1 Attempt 3 above failed. Returned rank 1 for all dates.
AVulliet
17 days ago Place Quickbase Discussions
37Views
0likes
1Comment
Formula Query with custom key
I have a parent table with a custom key, and I have several formula fields with formula queries that pull data into the parent. The ones that are summing into numeric fields work, but I also have one that is a checkbox formula. I want the parent to have a checkbox if any of the children do. var Textlist vals = GetFieldValues(GetRecords("{48.EX.'"&[Custom Key]&"'}", [_DBID_TABLE_NAME]),43); If(Contains($vals, "High"),true,false) There are no syntax errors but it's not working - there are child records with a checkmark in field 43 but on the parent records there isn't a checkmark. I know the GetRecords function is returning the correct records because it works for my other functions that sum correctly. I tried to run the formula checker, to check each step, but it doesn't work with my custom alphanumeric key. How can I figure out what's going wrong here?
Solved
CharlotteM
24 days ago Place Quickbase Discussions
29Views
0likes
3Comments
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?
EnderTaylor
3 months ago Place Quickbase Discussions
59Views
0likes
1Comment
Preventing 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.
Solved
WadeMyers1
3 months ago Place Quickbase Discussions
86Views
0likes
7Comments
Preventing 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!
Solved
IanGrant
4 months ago Place Quickbase Discussions
50Views
0likes
2Comments
Multi-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, Veronica
30418
5 months ago Place Quickbase Discussions
30Views
0likes
1Comment
Trigger 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! Deepa
Solved
DeepaPrashant1
5 months ago Place Quickbase Discussions
34Views
0likes
2Comments
Find 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.
JimHarrison
5 months ago Place Quickbase Discussions
35Views
0likes
2Comments
Identifying 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!
JamieStrauss
5 months ago Place Quickbase Discussions
49Views
0likes
2Comments