Formulas

Hi there, I’ve taken to Airtable recently to catalog all my acquired clients. I have a single select field called ‘$Flow’ that tells when the current status of the client in the sales funnel, for example: Made contact, Talking, Email, Won, and Lost. What I want is a formula that can tell me exactly when I win a client over. I.E. when I turn the single select field from any other option to ‘WON’ the date (and time, if possible) should be captured in the formula column. I used the formula: IF({$Flow}=“Won”, NOW()) But the date always resets every time I reopened the table, not when I originally turned the option to WON. Does anyone have any suggestions that could help? Thanks Yashwant.

Thank you for taking your time to look at this. The company I work for offers services that are modular, so not all clients are going to request the same services. The client will pick and choose the services that they need. To help automate the quotes we give for our pricing, I’m trying to use a formula field to calculate the final cost, however I’m running into problems. As of right now, I want to use multiple single select fields with the name of the service with the options of selecting YES or NO I’ve written up the following formula. IF({Service 1}=“Yes”,13,0) I can assign a dollar amount to the service.The problem I’m running into, is getting all the services to add together to give one total amount. What I would like to do is, 1. Give each service a dollar amount. 2. Then using that number, calculate all the services the client has requested into one final total that uses the currency format. Lets pretend that there are 3 services. Service 1 is $13. Service 2 is $67. Service 3

Hello, I would really appreciate some help here. I have a big list of client birthdays, the majority of them are partial dates (DD/MM), but some are complete dates (DD/MM/YYYY). Now, I have 3 fields: Date of birth This is a single line text field because I can’t enter partial dates in a date field Age This is a formula field I use to get their age when I do have their full date of birth IF({Date of birth}, DATETIME_DIFF(TODAY(), {Date of birth}, ‘years’)) Birthday This is a formula field I’m using to strip away the year of complete dates and make both, partial and complete dates, recurring dates that I can see in the calendar view IF({Date of birth}, DATETIME_PARSE(DATETIME_FORMAT({Date of birth},‘MM/DD’)&"/"&YEAR(NOW()))) The problems I’m having are the following: We use the European date format (DD/MM/YYYY) here, but when I enter a partial date in the date of birth field (single line text field), say, 1/6 (June 1st), the birthday field (date field, European date for

Hi, I found a script enabling a percentage count: https://airtable.com/shr1uUxBtpin68DsN/tbl83MENluhVCm20y/viwuf0HQjtKfYVvmb and I would like to adapt it by counting the number of cells completed, in place of the direct input in the “Current” cell. What type of formula can count the number of cells completed for a specific row? Thanks for your help.

I’ve been trying but it seems like it’s not possible.

Hello! I am having trouble with what I think would be an IF formula, but maybe a switch? These are my fields: Product Unit Price Shipping Cost We have our shipping costs listed as a line item and it will show in the product column. I need to extract the shipping cost for our P/L down the road. What I am looking to do is: IF the product is “shipping” , I want to show the unit cost in the Shipping Cost field… Can’t quite figure it out…Thank you for your help

Hi, maybe I its to obvious and I don’t get it - but how can I work with the outcome of a formula field? Every time I build formulas with which I want to “work further” with the outcome of another formula field there is no output (blank). Any idea how to make that possible? Thanks in advance Onur

Hi, how to use a XOR formula with 3 and 5 arguments (with real world use cases)? I’m trying to understand how i can apply this in real life

Hi all, I may be missing something in which case apols! I have two fields: an integer field and checkbox field. I wish to set the checkbox field if the integer field is >x. I can see how to simulate this using a formula field formatted as text and a ‘tick emoji’, but this doesn’t work for me as I need to link my base to an external system that uses airtable checkbox fields to construct filters. Alternatively, would be nice to flag a feature request for the formatting option ‘Checkbox’ for formula fields where the output is boolean.

Hi guys Thank you for your help! and sorry for the noob question, but i’m struggling with below scenario: i have a service provider that services multiple branches The link on AT allows for multiple selections and all is good. Using the fantastic On2AIr widget (subselect) i was able to easily pull all the providers affiliated with a specific branch by using : Child Search Field with “Branch Affiliation::Branch ID” logic When a provider has only one branch affiliation results come up nicely, but once there are more than that no records are found. So the questions is: What do i need to change in “Branch Affiliation::Branch ID” logic to allow for the multiple affiliation ? thank you!

I have a holiday calendar that I want to plan for future years. Instead of typing in future dates, I would like a formula to calculate it automatically. I would like a formula that if the date type is fixed, add one year to 2020 date. I also am different and use the mmmm dd, yyyy format. Is there anyone that can help?

Hi, can airtable link a record to another one in the same table ? making recursive fields , or tree like fields or hierarchical structure ? like category sub category sub sub category ?

I’m looking to use Airtable as a full project management solution for my company. But one of the requirements is that projects must adhere to a certain project numbering convention: XXXX-0000-X XXXX is a 4-letter code, specific to each of our clients (1000+) 0000 is a 4-digit number, which auto-increments starting at 0001 for each client (not globally, each client counts up) X is one letter, based on the project type Our current system generates this number automatically upon entering a new project. Let’s say that my client’s name is ABC Tool Company. They already have 96 projects in our system. I’m going to enter a graphic design job for them. The client’s unique client code is ABCT. The next project number would be 0097. The suffix for a graphic design project is G. All I have to enter on my project request form is the client’s name and the project type, and the system would automatically generate this code: ABCT-0097-G I’ve searched everywhere to figure out how to get Airtabl

I’m importing a google spreadsheet of workers and their trades. The sheet has over 11,000 rows (each worker = 1 row). There are 40 trades. Each trade has its own column, and there is an “X” if the worker is skilled in that trade. I converted each “X” into the name of the trade that it represents. I now want to combine all of the columns for each worker into 1 multiple select field so that each worker has one field where it lists all of his/her trades. Any ideas?

Looking for a formula where if a field is left blank it will automatically be filled in with the same info from the previous field. For example: I have a field for dates. Fill in date on line #1. Move to next line (line #2) and leave blank. Move to next line (line #3) and line #2 automatically gets filled in with the date from line #1. Any suggestions?

I swear this must be really easy, in fact in excel it’s really easy what am I doing wrong :frowning: I have one sheet on there I have a variety of columns I want to create a column at the end that says how many out of columns xyz (which are all single line text columns) contain text - please can anyone help as 4 hours later a rabbit hole of videos and posts on here I still cannot get it to do anything - thank you in advance

I have a column for “Completed?” with a checkmark option. When this is checked, I’d like for that column (“When complete?”) to populate the time it was checked. I think I need to combine something like this: IF({Completed?} = 1, LAST_MODIFIED_TIME({Completed?})) with DATETIME_FORMAT(SET_TIMEZONE({Completed?}, ‘America/Chicago’)‘M/DD/YYYY h:mm’) but I’m not getting it to work the way I was hoping. Thanks in advance! Rachael

I have 2 fields that both sum up strings of currency. One is the sum of services and the other s a sum of the money paid in. If they total to the same, then my drawer should be balanced. The sums are the same in both fields. Originally, I had an if formula that contained the same sum action that is in {Paid In} because I would rather use an IF formula to tell me if they are the same, rather than comparing the two myself. I thought they must not be the same, so I created the {Paid In} field to check. They are indeed balanced, but the formula does not recognize this as so. My formula: IF({Paid In}={Gross Income},“√”,“CHECK BALANCE”)

HI! I’m concatenating a few name fields and have been running into what seems like a bug. An errant space shows up before the first field insert in some, but not all instances. Code: CONCATENATE("Set (",{Score Name}," + ",{Part Set Name},")") Output: Thoughts? Thanks –

I’m trying to list all records using the Airtable API that have been updated in the last 24 hours. This job will run each day and check for the last modified column and if the date `IS_AFTER(TODAY()-1 DAY, TODAY()). I’m struggling with the syntax. Does anyone have any guidance on this?

I need to round off a list of values ​​to 0.5 With the formula “Ceiling” they all go with the highest value, instead I need that the values ​​under 0.25 or upper 0.75 etc must go to 0. With the “Round” formula I can’t understand where I’m wrong. I’m using Round ({column of values}, 0.5) Tnx in advance

Hello, I have a column that is called Contacts that lives in table Publications. In this column, each cell has one or multiple linked records that can be found in another table called Press & Media Contacts. A cell inside Contacts looks like this What I would like to do is to filter and select the rows that have a certain contact in, using the record id. The only way I could do this was to use filterByFormula and Name, not record id: "filterByFormula" => "FIND('Keith Shearin',Contacts)" What I would like to do is something like this: "filterByFormula" => "FIND('reclhWRUlh9q47Zfd',Contacts)" Where reclhWRUlh9q47Zfd is Keith Shearin record id. FIND is obviously not the proper way to do it since it finds a string inside a string. FIND(stringToFind, whereToSearch,[startFromPosition]))` Do you guys have any tips on how can I achieve this?

Can someone help me combine multiple roll-up fields into one? These fields are notes from another table. Instead of having multiple roll-up fields spreading across the projects table I’d like to combine them into 1 field. I’d like to add a “heading” of text in between each rollup to differentiate each note field. My current formula gives me an error. CONCATENATE("Incoming Request: " & {Incoming Request}& "\n" & "Contractor Notes: "{Contractor Notes}& "\n" &"Client Notes: " & {Client Notes}& "\n" &"Project Notes: " & {Project Notes}&"\n" &)

Hi I am a complete Novice with Airtable. I was once OK with Microsoft access but a long time time ago. I have a website and I sell subscriptions which go up in the following increments 48hrs 1 month 3 month 6 month 12 months In my orders table I have the date ordered field and other field called Item’s Name. This contains the following text 12 months Subscription, UK TV, Box Sets, This has been imported via a CSV and whilst it is showing as a single line text field the contacts are part of a url. I am looking for a formula to create a renew date 1 month, 3 month hence etc please. I hope all this makes sense. Any help appreciated please. Andy

The funny part is that when I change the data type in field in question from a formula to a plain decimal number - it all works! Does Airtable not support this kind of calculation when the primary field is a formula deriving data from another formula, or I am doing something wrong?