Using 'not ifnull' formula in a 'formula text field'
I need some help with a formula. I am trying display text if a field is not null. This is my current formula: If(not IsNull([AP Supplier Type]), "Oracle Hold", If(not IsNull([CURRENTWORKNODE]), "Rapid Hold", If(not IsNull([Amt]), "Payment Hold"))) Currently, everything is being listed as an "Oracle Hold". I am not sure what I am doing wrong. Your help is much appreciated!!300Views2likes14CommentsFormula to Show "Day of Week, XX/XX/XXXX to Day of Week, XX/XX/XXXX"
Hello, Can someone provide any insight into how to create a formula that produces text that will show [event date start] to [event date end], when [event date end] is empty. I need a field that will combine two date fields to show, for example, "Thursday, May 6, 2021 to Friday, May 7, 2021". I have the following formula in a formula - text field set, but it's showing me what I need in a simple "5/6/2021-5/7/2021" format when I need to spell it out more like above. If(not IsNull([event date end]),ToText([event date start])&"-"&ToText([event date end]),ToText([event date start])) Thank you in advance for your help! ------------------------------ Gabriella Tremoglie ------------------------------299Views0likes1CommentFormula URL button - save and redirect
Hi there I?ve created a form accessible to ?everyone on the internet? for our clients to submit service requests. I added ?&ifv=20? to the url to hide the qb branding. However, doing this causes thesavebutton to become hidden. I created a formula url button that will save it using this javascript I found on the forum: var text URL = "javascript:void(DoSaveAdd())";var Text Image = "<a id='saveButton' class='Vibrant Success' onclick='DoSaveAdd()' href='#'>Submit</a>";"<b href =" & $URL &">" & $Image & "</b>" My problem now is how do I get thepage to redirect after theform is saved. Here?s a link to the form, you can try it and see what happens. https://sparkav.quickbase.com/db/bnzwm7ykp?a=nwr&ifv=20 I'd like it to redirect to an external webpage. If that is not natively possible than I'd like it to redirect to a rich text page I've created in quickbase. Any help with this would be greatly appreciated! Thanks, Elisha299Views2likes31CommentsUsing HTML in Formula - Rich Text Fields
At EMPOWER2019 in Miami, Senior UX Designer Lisa Sawyer shared some awesome tips in her session Quick DIY solutions for beautiful & user friendly apps. Do yourself a favor and carve out an hour to check out this highly informative session. If you only have a few minutes to spare, check out this quick video highlighting my favorite tip: using HTML in Rich Text Formula Fields This is a great technique to quickly add some color and style app by highlighting key data points or KPI’s for your end users. Here is the code snippet that is used in the video: "<div style=\"color:#74489D;font-size:350%;font-weight:bold;\"align=\"center\">"& ADD YOUR FIELD &"</div>"& "<div style=\"color:#gray;font-size:150%;font-weight:bold;\"align=\"center\">PLACEHOLDER TEXT</div>" To learn more about which HTML Tags are allowed by Quick Base, click here ------------------------------ Freddie Sabbs fsabbs@quickbase.com ------------------------------200Views4likes3CommentsCan I use concatenate in Quickbase?
Trying to combine two text fields in Quickbase into one text field. Field 1 is a title from another table and Field 2 is the version of that title. So Field 1 might be "Diabetes" and Field 2 might be "v2". I just want to combine them to be "Diabetes_v2" in the new field. Is that possible? Seems like if should be simple enough with CONCATENATE([FIELD1),"_",[FIELD2]) but that doesn't seem to work. Getting this error: Formula error -- Unknown function The function name Concatenate(text, text, text) is unknown. Really? You can't use CONCATENATE in Quickbase? Any advice? Thanks!199Views0likes26CommentsFormula to check for blank/null Multi-Select Text field
I am writing custom data rules and would like to have a rule that when a specific Multi-Select Text field [Items Awarded] is blank/null the record cannot be saved. I've tried using each of the following, and each returned an error [Items Awarded] = "" -- The operator '=' can't be applied on types textlist, text ToText([Items Awarded]) = "" -- Expecting text but found bool [Items Awarded] = Null -- Expecting text but found bool ISNULL([Items Awarded]) -- Expecting number/date/datetime/TimeOfDay/duration/workdate/user/userlist but found textlist How do I use a formula to check for blank/null Multi-Select Text fields? ------------------------------ Amanda Thomas Business Systems Analyst Morse Steel Reinforcing Bellingham WA 360-756-6208 ------------------------------199Views0likes2CommentsHelp with ""Contains"" formula
Hi, I am trying to create a formula that indicates the following: IF [field 1- text multiple choice] contains "GM" AND [field 2 - text] contains "GM", display text "GM Opp/GM Agency" IF [field 1- text multiple choice] contains "AA" AND [field 2 - text] contains "GM", display text "AA Opp/GM Agency" IF [field 1- text multiple choice] contains "AA" AND [field 2 - text] contains "AA", display text "AA Opp/AA Agency" I think that the AND portion if the formula is messing me up, but can't figure out how to fix. Below is the current formula I'm working with: If( (Contains([GM/AA Market - Opp],"GM" AND Contains([Agency/Client Market - Consolidated], "GM"), "GM Opp/Agency", (Contains([GM/AA Market - Opp], "AA") AND Contains([Agency/Client Market - Consolidated],"GM"), "GM Opp/AA Agency", (Contains([GM/AA Market - Opp], "AA"), AND Contains([Agency/Client Market - Consolidated],"AA"), "AA Opp/Agency", "NA")) Thank you!199Views1like6CommentsEliminating Duplicates with a Formula Query
I have been able to participate in the Beta for Formula Queries. Best I can figure not all of the features that were shown at EMPOWER are live in Beta, but these three functions are, which were provided by my Beta contact at Quickbase. Currently Available Formula Query Functions · GetRecord(record id) o Returns a single record based on the Record ID provided · GetRecords(query) o Returns a set of records which match the query parameters · GetCellValues(record(s), field id) o Returns a list of values from the Field ID of the given records During Beta there are a few safeguards in place protect your applications The following limits will be reduced/removed as Formula Queries reach GA · GetRecord(s) only work within the given table, no cross-table queries yet · When trying to save a Formula you may receive this error: 'Uh-Oh! The way this formula is built will take too long to process. Please try refining your formula.' o This often means that the table is too large. Try testing on a table with fewer records. The API_DoQuery guide provides details on how to construct a query. Try to follow these best practices when forming your query: · Since filters are processed sequentially, your query should eliminate the most records with the first filters. · Try to use scalar fields (manual input fields) rather than derived fields like summary or formula fields. Derived fields must do additional queries, permission checks, and calculation before they can be evaluated against your query. · Wherever you can, use exact matches when building your filter criteria. The goal I had was to eliminate duplicates from a table so as to be able to mindlessly copy the non duplicates to another table using a saved table to table import (click a URL formula button). As you are probably aware if there are duplicates in the import set for a unique target field then the import will fail. In my case I didn't really care which of the sibling duplicates I copied across so it was good enough for me to preserve (flag) the duplicate with the Minimum Record ID number and eliminate (not flag) the others. In my case the unique field was called NPI This formula here called [Record IDs for this NPI] returned a semi colon delimited list of all the [Record IDs] for the siblings of this NPI. GetCellValues( GetRecords("{73.EX." & [NPI] & "}"),3) From the inside out that formula says query for fid 73 (which is the field for NPI) and go off and get the records from the table I'm sitting on where the [NPI] for the record I'm sitting on matches with the same value in any other record. Then bring back the Cell values from that Query for Field ID number 3. Of course field ID number three is the Record ID. But then how to find the one with the Minimum Record ID# of all the duplicates? Most conveniently in my use case, the records are returned in Record ID sequence! As you can see from the screenshot, I am sitting on Record ID# 203432. That is the first one on the returned list, so its the Minimum Record ID# of its sibling NPIs. Then I created this formula to flag which ones I can import. ToText([Record ID#]) = [Record IDs for this NPI] // this is probably not necessary or ToNumber(Trim(Left([Record IDs for this NPI],";"))) = [Record ID#] //The Formula Query returns a list of all of the Record IDs for this NPI. The first one on the list (at the left) will be the minimum. I thought I would just post this because it is has been classical problem in QuickBase for the past 20 years of how to filter a table report or a saved table to table import to only have unique records without needing have a "helper" Parent table, typically maintained by Automations, of the unique Parents. I have posted a screen show of the results of the Formula Query. ------------------------------ Mark Shnier (YQC) mark.shnier@gmail.com ------------------------------100Views2likes9CommentsAdding number of days to Date - Formula Field
Hello: Your friendly yet persistent novice with another question: I am trying to add days to a date - formula field based on selection in a different field. Error Message: Formula syntax error Please check the syntax of your formula. Look for mismatched parentheses, missing quotes, or extra brackets. if( [Work Type]="RD-Reactive",([Date Complete WO Received]+Days(5)), [Work Type]="PJ-Planned Job",([Date Complete WO Received]+Days(12)), [Work Type]="RP-Reactive Project",([Date Complete WO Received]+Days(10)), ) Any help would be greatly provided Dirk100Views1like5Comments