Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 11, 2020 06:02 PM
Sorry in advance - I am a very green user to Airtable and Databases in general.
I would like to make a formula that can scan a record for a word and then automatically make the value 0.
Example:
Line Item ------------Unit ----------- Rate---------Total Cost
Invoice - 0011-------1.00------------$100----------$100
Bid - 0100------------2.00------------$500----------$0
In this case I am using:
Rate*Unit to reach the total cost. This will mean that the “Line Item” Bid-0100 will pop up with a total cost of $500, but I need it to show 0 as it hasn’t actually cost me anything. Is this possible?
Solved! Go to Solution.
Dec 12, 2020 02:18 PM
Hi Evan,
Welcome to the forum. You should be able to achieve this using Find() or Search():
IF(FIND("Bid",{Line Item}),"value if true","value if false")
Keep in mind that the function is case sensitive. If you need to find upper and lower-case versions of “Bid”, use this
IF(FIND("bid",LOWER({Line Item})),"value if true","value if false")
Dec 12, 2020 02:18 PM
Hi Evan,
Welcome to the forum. You should be able to achieve this using Find() or Search():
IF(FIND("Bid",{Line Item}),"value if true","value if false")
Keep in mind that the function is case sensitive. If you need to find upper and lower-case versions of “Bid”, use this
IF(FIND("bid",LOWER({Line Item})),"value if true","value if false")
Dec 12, 2020 06:29 PM
This is great. The only issue I am running into is that the solution isn’t popping up with a dollar amount, just a numeric value. Anyway to fix that?
Dec 12, 2020 07:19 PM
Right click on the field and choose “Customize field type”, then choose the formatting tab. It should give you the option to display as currency
Dec 12, 2020 08:10 PM
Thanks Julian,
Unfortunately Formatting isn’t an option for some reason.
“Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.”
This is the formula I am using:
IF(FIND(“Bid”,{Line Item}),“0”, Unit*Rate)
Dec 12, 2020 08:33 PM
Remove the quotation marks from the “0”. Since one result (the “0”) is a string, Airtable treats the whole field that way. Try this:
IF(FIND(“Bid”,{Line Item}),0, Unit*Rate)
Dec 13, 2020 11:01 AM
This worked beautifully! You’re a life saver!!
Dec 13, 2020 12:25 PM
Glad to hear! If you don’t mind, mark the correct answer as “Solution” :slightly_smiling_face: