Checkbox Formula to give me a 1 or a 0
Hey hey I was trying to figure out the formula so that when I check a checkbox (In another field) it will give me either a one or a zero in this formula field that I'm trying to build. I imagine it will look thing like... If([Checkbox Field]="Checked",1,0) This doesn't seem to work... error message is "the operator = can not be applied to types bool" please advise how I can set this formula field up - Thank you! ------------------------------ Jack Woods ------------------------------29Views0likes4CommentsFormula 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 ------------------------------10Views0likes1CommentRich Text Formula Field - Comma for Currency #?
Hey Community, I'm using a Rich Text Formula field called NPV(Color) to show the NPV value in red when it's negative. It's working well but now I just need to format it - I can't seem to add commas to the currency number in the formula field. Could you help? the formula is below. If([NPV]<=0,"<font color=\"red\">$"&[NPV]&"</font>","<font color=\"black\">$"&[NPV]&"</font>") ------------------------------ Jack Woods | Low Code 4 Lyfe ------------------------------10Views0likes6CommentsNested If/and statements
Hello! I recently started with San Francisco Opera who uses QB to maintain their artistic database. I'm hoping someone can help me with the following formula. BACKGROUND: I am trying to edit an existing formula (created by a former employee) that injects clauses into our contracts if certain parameters are met. Specifically, if an Artist is casted for more than one role assignment within a given contract, it should inject clauses reflecting their multiple assigned roles and respective fees. I believe the If statement is stopping after the first formula block because it is true, therefore not injecting the clause(s) for the remaining roles. FORMULA: If(([Fee Type]="Per Performance" and [Sing/Cover 1]="COVER"),(List("","Should PRINCIPAL SING a performance in role of ", Trim(Part(Upper([Character Name 1]), 1, "()")),", PRINCIPAL will receive a Per-Performance fee of $", ToText([If Sing Fee 1])," in lieu of the cover fee.")), // the above if statement is TRUE, therefore stopping after the first code block ([Fee Type]="Per Performance" and [Sing/Cover 2]="COVER"),(List("","Should PRINCIPAL SING a performance in role of ", Trim(Part(Upper([Character Name 2]), 1, "(")),", PRINCIPAL will receive a Per-Performance fee of $", ToText([If Sing Fee 2])," in lieu of the cover fee.")),... The formula continues for other use cases but for clarity, I've just included just a snippet. RESULT: (In this case, the contract/artist in question has two assigned roles, but only injects one clause) Should PRINCIPAL SING a performance in role of Role 1, PRINCIPAL will receive a Per-Performance fee of $xxx in lieu of the cover fee. Please see the attached memo detailing how compensation is broken down at San Francisco Opera. DESIRED RESULT: Should PRINCIPAL SING a performance in role of Role 1, PRINCIPAL will receive a Per-Performance fee of $xxx in lieu of the cover fee. Please see the attached memo detailing how compensation is broken down at San Francisco Opera. Should PRINCIPAL SING a performance in role of Role 2, PRINCIPAL will receive a Per-Performance fee of $xxx in lieu of the cover fee. Please see the attached memo detailing how compensation is broken down at San Francisco Opera. QUESTION: How would I alter this formula so that it includes all [Sing/Cover] fields if they exist for the given contract? Thank you for your help! ------------------------------ Devin Hinzo ------------------------------9Views0likes0CommentsView query record if single record otherwise view query list
I am creating some buttons in a Formula - Rich Text field. They will allow users to quickly jump from one table to the next using the table to table relationships we have set up. Mainly what they will be seeing is a list of related records based on a query. For example, if I am in the Customers Table and I click on the Invoices button it will take me to the invoices table and I can see all the invoices related to that customer. If there is only one invoice I would like the button to take me directly to that invoice record and not view it in list mode. Is there a way to use the query and set up an if statement that would check the length of the query response and if there is only one then view that record. Below is an example of the query code I am using for one of the buttons. URLRoot() & "db/" & [_DBID_INVOICES] & "?a=q&query=" & "{'6'.TV.'" & [Record ID#] & "'}"8Views0likes3CommentsFormula: Color Coding and Dates
Hello All! I am new to Quickbase and I am having trouble finding out how to code. I need to make a report that tracks all employees and how long they have worked at my company. I have the basic information, (I.e. Start date, employee, etc.) but I was wondering of there would be an easier way to automatically calculate how long the employee has worked from when they were hired until the present date. I also need to find out how to code those times in to different sections (green= 0-1 years, blue= 1-5, etc) so I can graph that info. If anyone has any suggestions or can point me in the right direction to go that would be amazing! Thanks7Views0likes3CommentsFormula Help for Weighted Average Field
Hello, I am trying to fix a formula for a numeric formula field that calculates the weighted average build cost of house plans we build. Here is the formula:(([3 Month Average Build Cost] * 10) + ([6 Month Average Build Cost] * 6) + ([1 Year Average Build Cost] * 3) + [2 Year Average Build Cost]) / 20 The Field References are summary fields. The numeric value is the "weight" we are giving to that summary field. The problem we are having is that occasionally the Summary Field used in the formula is blank/null and it causes the result of the formula to be blank. What I believe needs to happen is for the formula to simply disregard blank values and continue the formula calculation. For instance, see the attached screenshots. 3 Month Average Build Cost is blank and therefore the "Weighted Average" formula result is blank. Rather, it should still calculate the remainder of the 6mo, 1yr and 2yr averages. The other obvious issue is that if any part of the formula is blank then the number by which the whole formula is divided would change. In the above formula it is divided by 20 because it assumes there will always be a value in each field. If a field were blank, then the divisor would also need to be reduced by the "weight" of whichever field were blank. Hopefully I've explained this well and the result I'm looking for is clear. I'm guessing I'm going about the formula all wrong! ------------------------------ Josh Collins ------------------------------7Views0likes1CommentHow to use another table for Calculated/Formula Fields
Hi, I created Table A which contains the fields(please see attachment for more details): Stem(Text Field) Region(Text Field) Country(Text Field) Date 1(Number Field) Table A is the parent table of Table B. Table B Contains the fields: Date(Date Field) -> User input Stem(Text Field) -> Drop Down Region(Text Field) -> Drop Down Country(Text Field) -> Drop Down Calculated Date(Date Formula field) - Current Formula is "AdjustMonth([Date], )" Basically, the setup is when a user fills data for Date, Stem, Region and Country in Table B, the Calculated Date field should result in the Date based on the formula above. However, I cannot seem to select the correct record in Table A based on the inputs(Stem, Region and Country) of the user. I have used the reference field for Table B, but it just provides a dropdown of all the records in Table A. This should not be the case as the reference field should be filtered based on the fields (Stem, Region and Country in Table B) which would select the respective record in Table A As an example, a user selects the following in Table B: Date = April 1, 2021 Stem = 1 Region = Region A Country = Country A The Calculated Date should result to June 1, 2021 which is 2 months after April 1, 2021 without manually adjusting the reference field in Table B. Hope this makes sense. Thank you for the help! ------------------------------ Paul Tria ------------------------------7Views0likes8CommentsHow to get values from a fields on the form and display them?
I'll have a form with Main Contact(text), Invoicing Contact(text), Second Contact (text) and etc. Need to find a way to store all this information in one field and display it on a form. ------------------------------ Olena Bilinska ------------------------------6Views0likes6CommentsGet the Today() function into Date/Time Format
I am getting back whole numbers for days and I want 2 decimals instead. I created a Today field based off days but can't use with a Date/Time field without converting that field with ToDate, giving me only the whole numbers as mentioned. Is there any way to convert Today (either just using the Today function or the Today field) to Date/Time so I can get more accurate results? Even if Today() defaults to something like 6/7/2021 00:00? That way I at least can get the difference of the time/hours into decimals for my other field. Maybe I am going about this entirely the wrong way. Below is the basic premise for what I am trying to get, which is aging ticket time. So instead of a rounded number like 10 days, I want something like 9.53 days. ------------------------------ Russell Hanson ------------------------------Solved6Views0likes2Comments