Color Code Date Field based on Formula
Hi there, New to Quickbase, but have a big MS Excel background. What I used to find easy in Excel, I can't seem to get my head around in QB. If anyone can help enlighten my rookie brain, it would be greatly appreciated. I'm trying to colour-code a date type field based on multiple conditions: Say for example I have field called 'Survey Required By', which is the date for when we want a survey to be completed, and then another field called 'Survey Completed', and in this field we enter the date when the survey is completed. I want to highlight the field text or background in red if the 'Survey Required By' date is today or older, and the 'Survey Completed' field is blank. I've googled and tired many different formulas, but the best I've managed is to color code a whole row on a report, which I don't want to do. I just want the 1 field color changed. The formula I have so far is: if( isnull([Survey Completed]) and [survey required by]=today(), "red","" )Solved100Views0likes11CommentsCount instances of value in table prior to date
Hi everyone, I'm trying to find a way to create a field that counts the # of times an email address exists within a table based on a date field within that record. Example: Record is created with an email address and a date of interaction. Goal - return the # of times that email address already exists in other records on or before the date of that interaction. My formula query currently is: Size(GetRecords("{145.OBF.'" & [Date of Interaction] & "'}")) Which DOES work, but it's counting huge numbers, and I don't understand why. The results are in the thousands, for an email address that if I search for it, only exists 21 times. I *think* it's because my field 145 is also a formula query which reads - Trim(If([NEW Contact Email Address]="",[Contacts Table Field - Email Address],[NEW Contact Email Address])) I'm wondering if it's not counting the results in the field, but query; however, when I switched the field to the field 'NEW Contact Email Address] I returned tens of thousands of records rather than the very few times a staff member manually entered it as a 'NEW' contact rather than looking up the email from our contacts table. Ideas? and thank you!83Views0likes6CommentsGet Record IDs from Report Link
I have three tables Templates, Template_Events & Template_SubEvents. Templates has a relationship with Template_Events, Template_Events has a relationship with Template_SubEvents, and Template_SubEvents has two self-identifying relationships (this allows for different Template_SubEvents to be selected in two places on the form). What I'm needing to do is prevent a circular dependency in the Template_SubEvents table. What I've come up with is using a Text field (Hierarchy List) to store the Child record(s) ID and the Child records of its Parent. For example: If I have Record IDs of 1, 2, 3 & 4. 2 is a Child of 1. 3 is a Child 1. 1 is a Child of 4. In the Hierarchy List for ID# 4 would be "1,2,3". In the Hierarchy List for ID# 1 would be "2,3". So ID# 4 wouldn't be able to be the Child of the records 1, 2 or 3. A formula field would compare the contents of Hierarchy List field to the selection made in the Parent_SubEvent ID field and then a Dynamic Form Rule would display a message if the formula equaled 'Y'. I'm attempting to get the Child Records from a Report Link but that is proving difficult. I have the current formula ToText(GetFieldValues(GetRecords("{228.GT.0}", "bt6eizk88"), 178)) but it's displaying the same values for ALL records. fID '228' is a summary field that counts the number of Child records. fID '178' is one of the Report Link fields. Am I on the right track for something like this or is there a better way to prevent a circular dependency? I thought about creating another table and storing Child, Parent and Grand-Parent ID but got feedback to try and make the Hierarchy List field work.76Views0likes2CommentsButton to Add Record via Pipeline, and refresh Record
Hi All, I am having troubles trying to via a button to add a new record then refresh the current record. As a button can't trigger a Pipeline, I've created a checkbox field to act as a trigger field. Currently the button uses an API_EditRecord to edit the checkbox and refresh the record. The API_EditRecord triggers my pipeline however the refresh happens before the Pipeline has a chance to complete. I saw this thread where we can create a HTML Code page to do a pause and refresh. https://community.quickbase.com/discussions/quickbase-discussions/save-new-record-pause-refresh/19873/replies/19876 I thought I might be able to tag that to the end of my current URL code to run afterwards, however it seems with the record refresh, it never gets to this part. My current code is as follows. URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&rid=" & [Record ID#] & "&_fid_371=1" & "&apptoken=xxxxxxx" & "&rdr=" & URLEncode( URLRoot() & "db/" & Dbid() & "?a=doredirect&z=" & Rurl()) & URLRoot() & "db/" & Dbid() & "?a=dbpage&pageID="&715 &"&rid=" & [Record ID#] My question is: Is there a way to get the above code to run all parts or A way via the delay/refresh html code page to edit the checkbox which should trigger the Pipeline then continue with the delay/refresh html script? I used a Pipeline rather than an API_AddRecord as there are 30+ fields that need to be copied from one table record into another, plus update the record with the button with the related new record ID. Thanks.Solved50Views0likes2CommentsDuration of 48 hours starting on a workday
Greetings. I am trying to create a Due Date/Time field that would show 48 hours from the Date Created for a record, but need to take into account work hours. Right now my formula is [Date Created]+Hours(48), which shows the date/time 48 hours later, but if the record is created Saturday morning at 10am, for example, I would need the countdown to start as of Monday morning 8am. I thought about using ToWorkDate, then WorkDateAdd, but I believe I would lose the time component, so am not sure that will work for the date/time field type. Any suggestions?50Views0likes2CommentsSelecting which fields to display from Formula URL searches
I have a number of Formula fields in my database which are searches of my database based on the contents of another field. The general form is this: URLRoot() & "db/" & Dbid() & "?a=QuickSearch2&srchtxt=" & ToText([<SOME FIELD>]) &"&exact=false" (This approach may be out of date now, but it was current when I built the original database in 2017). Here is the problem: The fields that get displayed are determined by which fields are designated as "This field may be used in reports." However, what I want is way to differentially control which fields are displayed, depending on which fields are being searched, whether this is done as part of the formula, or through some other mechanism. For example: Assume I have 4 content fields Category, Manufacturer, Salesperson, Location. And two "search fields" using the approach above: Search_Category: URLRoot() & "db/" & Dbid() & "?a=QuickSearch2&srchtxt=" & ToText([Category]) &"&exact=false" Search_Location: URLRoot() & "db/" & Dbid() & "?a=QuickSearch2&srchtxt=" & ToText([Location]) &"&exact=false" When I search on the contents of "Category" (I.e., looking for all records with the same product category as the current record), I want the Manufacturer field to show up as a column in the search results, but not Location. When I search by Location, I want Salesperson to show up as a column but not Manufacturer. Currently, I have to manually show/hide which fields I want to see after I do the search--very inefficient. For example, is it possible to define a table layout for search results as a form and then indicate the use of that form in the Formula?50Views0likes2CommentsIF AND Formula
I am trying to write a report formula that is triggered off a checkbox and two different dates that I would typically write an IF AND formula. Anyone know how to make this work? It is giving me an 'expecting ,' error. The Cybersecurity field is a checkbox. The Engagement Begins is a date field. The Cybersecurity Discovery Engagement Ends is a date field. The logic should read if the Cybersecurity checkbox is checked AND the Engagement Begins field is blank, then the field should read "Upcoming", but if the Cybersecurity checkbox is checked AND the Engagement Begins field is not blank, and the Cybersecurity Discovery Engagement Ends field is blank, then the field should read "Assigned", otherwise show nothing.48Views0likes2CommentsSum Overlapping Time/Remove Timeframe Gaps
Hi all, is there a way to do this formula queries? I have a table of people who have child residence & employment records, with start & end dates for each, as well as a numeric field that summarizes each timeframe in months. I need to query for all child residence & employment records related to each person, find any potential overlapping timeframes between the 2 tables based on start & end dates, and sum the total # of months from the Duration (months) field from applicable records. Essentially I need a final number in months of time each person provided to us across 2 tables, so any overlapping time would basically get deduped out. I also need it to be smart enough to recognize some of these timeframes are not contiguous and contain gaps, so we might not have any date for particular person from 2010-2015, but we do from 2005-10 and 15-18, so any gaps would need to be excluded from the final count.39Views0likes7CommentsGet a value from another entry in the same table
We've been using a QB Table to track snap shots of business health. Once a week we log some numbers across different tables. This isnt exactly what we do - but as an example it could be: how many orders placed how many orders delivered how many orders on hold how many customers outstanding on payment how much billed received how many customer feedback received etc etc and we do this for each "store" - let's assume we have 2 stores. Now here is my issue! I want to track PERCENTAGE CHANGE since last week. So it's the same table - but I want to compare, "orders placed" this week for this store, versus "orders placed" last week for the same store. I want to be a formula for a field which would show PERCENTAGE INCREASE OR DECREASE. ( and then we can get fancy and make charts, graphs, color code etc) Where I am having the issue is - how do I find the value of last weeks entry for this store?? let's assume each entry has a "week of" and a "store code" - so for example: 07-05-2024 and MAINSTORE. How do I get the # of orders placed for 07-28-2024 and MAINSTORE and have it auto populate? I would prefer to go this route where I can lookup last weeks #'s - so if I ever need to scale I can. Worst case is that I will choose 2-3 KPIs to log each week. Any ideas?39Views0likes1Comment