Formulas

Hi everyone! I’m surprising myself by not being able to figure this out. I swear at one point or another I had been able to pre-fill an airtable form with a field that includes an emoji. For example, I want to pre-fill a single-select {Status} field in a form with a status of “ :calendar: Consult Scheduled” I’ve tried it with the actual emoji character as well as with UTF-8 (hex) encoding (which is what Airtable spits out if I wrap the emoji in the ENCODE_URL_COMPONENT() function - see below). https://airtable.com/shrIXXXXXXXXXXXXX?prefill_Status=%F0%9F%93%86%20Consult%20Scheduled https://airtable.com/shrIXXXXXXXXXXXXX?prefill_Status=📆%20Consult%20Scheduled Neither of these work. Am I barking up the wrong tree? Should I just forget about the emoji? Maybe try a different emoji? :slightly_smiling_face:

Hi all, I am formatting my database to show how long a volunteer has been with us (in order to determine when to give them their 1 year of service, 2 years of service etc), however, the cut-off date each year for our volunteers is 30th April. For example, if a volunteer starts with us on 1/5/2018, at 1/5/2019 the years of service is 0, but at 1/5/2020 it will be 1 year. If a volunteer started with us on 29/4/2018, at 1/5/2019 the years of service is 1, and at 1/5/2020 it will be 2 years. I hope this makes sense! I’d love to know how to formulate this so I can get a ‘1’ or ‘2’ etc. Thanks in advance for your assistance.

I have a table of Customers and a table of memos which are related to the Customer by Account number and in the memos table I put a note each time I speak to the customer with basic info of the date the person I spoke to and what was said. In the Customer table I would like to see the Max (Memos:Date) ie the latest date that I spoke to the customer. At the moment it shows me all the dates as I have used it as a lookup since I cannot find a way to get the Max from a related field as a date. Anybody have any ideas please. I usually use Filemaker and the relationships then are quite transparent so am struggling a bit

I have a table that uses a Date field as its primary key. Each date in use happens to be a Monday and there are records for every single Monday for the past 6+ years. This means that for a given row, the row below it is always 7 days before it. Each row also has a Number field that grows from zero (6 years ago) to 7500 (today), but the growth is nonlinear and varies from week to week. What I want to do is compute a weekly growth rate field. This field would be equal to: (Number today - Number last week) / Number last week In order to do this, I need to reference the number field from one week ago. I’m able to compute the date that I should be looking up, but the result is a formula field and I can’t figure out then how to look up the value I need from the appropriate row. Anyone know a simple way to do this?

Hi, I need some help to do something… 1/I have a column with a price 2/ I have a column with a Single Select option, and a list of discounts (10%,15%,20%,30%) I would like to create a 3rd column, with a formula which gives me the total discounted price, based on the discount which has been selected. So if : Column B has a total of $100 Column C has 10% selected The new column D should show me $90 I don’t know how to create a formula which will calculate the discounted amount based on the selected field. I would be great to have some help on that! :slightly_smiling_face: Thanks, Sébastien

Hi. I want to use barcode scan just like PDA device on werhouse managment system. This means scan barcode from a product and than enter the quantity in the field. For example, I scan a barcode on a stack of 12 pcs and enter 12 pcs (meaning 12 pcs loaded). Problem is, next time when I scan same barcode I cannot enter 12 pcs again in the same field and get automatic sum 24 pcs, 36 and so on but I only can manually insert total result. Is there a formula that can make this happen or another way to achieve this?

Hi Guys, So i have this formula above and i want the result in Currency, like “0€” not “0”. Can anyone help me? IF((Tipo2)=BLANK(),“0”,IF((Tipo2)=“Fatura EU”,“0”,Valor*0.23)) Thanks

Hi - I’m trying to link a chronological set of tasks based on the completion date of a proceeding task. I’ve figured out how to project the due date by taking the start date plus Duration (in days) to get a projected due date, however, i’m stuck how to link this to the next record. For example Task 1 is completed on 1/1/20 Task 2 should have a start date of 1/1/20 plus some duration (say 3 days) and have a projected due date of 1/4/20 Task 3 will then have start date of 1/4/20 and so on… any help would be appreciated!

Hi. I’m new to airtable, and fairly new to formulas. I’m trying to build a formula that looks at the part of an email domain and based on if a specific word exists or not, enter the persona into another field. Example Fields are EMAIL and PERSONA If the EMAIL domain is @usa.company.com and I’m checking if the domain contains “company” I then want to have the PERSONA be “employee”. If it does not contain “company” I want the PERSONA to be “customer” So far this is what I have IF((FIND(“company”,{Email Domain}) >=1)“Employee”, “Customer”)

Hi, I have a base that I copied from an example inventory sheet. I deleted all the records but the first one and then started entering my own. For the first record (from the original copied base) through the 18th record the date shows the wrong month and year (the day may have been correct). The rest of the rows look like they are displaying the right date. How can I fix the first 18 rows to get the correct create date (or since this is not editable is that possible)? Thank you!

I need a formula to calculate ETA (more like Estimated Day of Arrival) for animal breeding purposes. I have a column with number of days bred and a column for the date checked. The average gestation period is 278 days. Is there a formula in which I could use the start date(ex: 9/11/2019), days bred (ex:84), and gestation length (ex:278) to calculate delivery day? Please and Thanks!!!

I’m Looking for Excel Vlookup option, Where I need to fetch data using the other table based on the value keyed in. I can do it now using lookup and linking the sheets but the problem is every time, there is a pop up opened which is inconvenient.

Hey, Airtable Community! I’m building a Running gamification system in airtable and right now I’m Stuck in counting the Number of records (Activities) per week Nº. In my base, I have a table with the participants - which is a Link to another record: another table with a database of people - and one with the recorded activities - which has lots of fields, The important ones for this solution being the “Activity Day” - Which I use a formula field to extract the week number. How Can I create a formula/rollup that counts the number of activities in a week?

Hi I have been looking for ages but just can’t work out what I need to put. I have a url field and want to create a formula to be able to group records from where they came from say ‘airtable.com’ or ‘youtube’ as a ‘live’ way to group. So easy with filters - wish you could use ‘contain’ in formulas i would be laughing! Thanks, Maria

I’m constantly making tables to organize information into hierarchies. I also want them all to have the same type of information, and to be groupable/etc, so they have to be on the same table. I’ve tried working with cross-tables, but even then I run into formula circular error prevention. All I want is a sub-sub-item to inherit the ID numbers of both parents in its name, in order, and I can’t for the life of me figure out how to do it. So if My hierarchy is like this: Item 1 Sub-Item 1.1. Sub-Item 1.2. * Sub-Sub-Item 1.2.1. Sub-Item 1.3 Item 2 Item 1.2.1 to inherit that number in front of the name, so I can see it on linked records. Thoughts? (I’m sure it’s been done before, but. No dice on the search yet.) I know that blocks can handle hierarchies now, but I’m too broke to pay for a subscription (yet!!). And I’m not 100% satisfied on how it works anyway, from what I can see. And I guess there’s zapier, but anything useful requires too much edit volume or too complicated to again

Hello All, I am looking for a formula that will calculate historical orders placed with the option to query 14, 30, 60, 90 and all time order history. Is there a way to do this? I am very new, go easy on me :slightly_smiling_face:

I’m trying to write a formula which returns either a value from field A or a string within the formula, dependant on the value in fields B & C. Where the values in fields B & C are met then a value within the formula should be returned, when the value in field B is not met then a value from field A should be returned. My table has 3 fields: Client Name Currency Customer Code Where ‘Client Name’ is ABC and and ‘Currency’ is USD then return a string from the formula. Where ‘Client Name’ is ABC and and ‘Currency’ is GBP then return a different string from the formula. Where ‘Client Name’ is not ABC then return the ‘Customer Code’. I’ve tried using nested formulas for the first 2 conditions but I do not know how to include the last condition. Can someone please help with this?

I need to create a formula that does this: Attendee Count < 100 put in 1 Attendee Count > 201 < 1000 put in 2 Attendee Count > 1001 < 5000 put in 3 Attendee Count > 5001 put in 4

I want to know what is the proper formula to use this… IF(Checkincount= 99, TRUE(), FALSE()) I’d like to have an or option meaning IF(Checkincount= 99, TRUE(), FALSE()), OR IF(Checkincount= 199, TRUE(), FALSE()), OR IF(Checkincount= 299, TRUE(), FALSE()) I can’t seem to find the right formula to use the OR Meaning, I want it to show if Checkincount is 99 or 199 or 299 etc… Hope that makes sense. Thanks

I’ve got an opportunity tracker base that’s logging potential project opportunities from five different countries. I’m trying to set up a column that takes those individual budgets and converts them all to USD so that I can see our global pipeline in one currency. To do this, I’m assuming I need to set up a formula that cross references each project budget against the city of origin for that project and only does currency conversion if necessary. Basically I want to do something along the lines of If {City of Origin} = “New York” or “Los Angeles”, then just reprint {Budget} If {City of Origin} = “London”, then just multiply {Budget} * 1.3 If {City of Origin} = “Paris”, then just multiply {Budget} * 1.4 etc etc But I can’t figure out the syntax. Would anyone be willing to help? Also, can anyone recommend best practice for referring to currency conversion multipliers in Airtable that might be simpler than hard-coding the numbers right into the formula? Thanks so much in advance for any h

Hi. I have a database with veg and flowers in. For the flowers I have fields for colours, scented and evergreen. I don’t want these to appear in the veg details. Same as I don’t want ‘hungry gap’ fields with flowers (for example)… Is this possible or should I split the data? If is is, please could you give me a forumula example I could use.

Hello All, I’m implementing a base that will be used to manage a process. As any process it has multiple steps and I want to identify which of them are bottlenecks. In order to do that, I created a Single Select field (Process_Step) that represents the current step of the process and other n fields that will hold the time that the process reached each step. For instance, S1 will hold the date and time when the process moved to step 1, S2 to step 2, and so on. I implemented it as follows: Field Last Updated Time = LAST_MODIFIED_TIME({Funnel}) Field S1 = IF({Process_Step} = "Step_1", IF({Origem} = "Maping", {Last Updated Time}, BLANK())) The field Last Updated Time contains information about when the process moved one step and S1 will hold that value if it has entered the first step. The problem is, every time the process moves to further steps the formula is recalculated and S1 becomes BLANK(). Any suggestions about how to make it work? I was thinking about using the Airtable Python Wr

I have been using this formula: IF({Completed}=1,DATETIME_FORMAT(NOW(),‘M/DD/YYYY h:mm’),0) to record a date when a “Completed” check box is ticked off in another column. This worked flawlessly up until recently. For some reason, all past dates are now changing to whatever the current days date is. I have jobs months old that now say they were only completed today. Any help is appreciated. I can’t figure out what happened other than it is a new year and maybe that messed something up.

I am a formula newbie and need some help. I am hoping this is even possible. Any help is welcome!! If the Expiration Date field is TODAY then I need to have Executed Agreement On File? field changed to No and the Status field changed to Expired. These values are from picklists. Can this be done and if so, how do I write it out? Thanks much!

Hi everyone, I am trying to auto publish content to my site according to formula in my table. My curl filter is - filterByFormula={diff}=1. The filter is working great but it seems my logic is lacking when it comes to determining what formula to use to get {diff} =1. I have a table that includes a column {publish-date}. {publish-date} is set to US 24hr format. In my {diff} column I tried IS_BEFORE({publish-date},TODAY(HOUR(8))) and IS_AFTER({publish-date},TODAY(HOUR(8))). It worked for one day then went screwy. In the end I want content from my table row to appear on my site once the current time is past it’s {publish-date}, specifically after 8 AM of it’s {publish-date} .