Formulas

Hi, Sorry if this seems a simple issue, but I’ve got a table with a list of venues… some start with “the” (eg: The Royal Albert Hall). I want to be able to display the list of venues in proper name order - ie: Royal Albert Hall, without “the”… I’m thinking having the full name as one field, and a second, formula, field which removes THE if it’s the first word, but not if it’s in the middle of the name - eg: Her Majesty’s THEatre, or Auditorium at THE Excel Centre… Could anyone help please? Thanks!

I have a table of activities with start and end date columns and a column for the person assigned to the activity. There will be hundreds of activities but only one person should be assigned to each activity. How can I see if a person is assigned to more than one activity during the same time period?

Hello, I’m trying to generate two separate outcomes in the same field where one of our clients receives a discount that the remainder of our clients do not. We have what is called a 1/2 Day Session that is normally $600.00 for a four-hour session. One of our clients is only charged $500.00 for that 1/2 Day Session and I want to be able to see both in the same field if possible. Would this fall under a nested IF statement, and if so, how would I go about writing that formula? Thank you.

Hi, A really long story made short; I’m trying to get Airtable to detect duplicate names in two rollup fields. I have the following tables in my base Equipment Projecttype A Projecttype B Location In my Equipment-table I’m keeping track of routers we own. Different fields shows how many we own in total, where and for what projects they are currently installed and (this is the problem) how many we have left in stock. The Equipment-table rolls up on which projects the routers are installed. I have 2 types of projects, each in their own table (A and B) - meaning, I have 2 rollup fields. Then, the projects run at different locations. More projects (even the two different projects) can run on the same location. The Locations-table links to both the project-tables but not the Equipment-Table. I’m using an ARRAYUNIQUE in the rollups in the Equipment-table and that way I get to see on what locations the ro

What it says on the tin. Different authors make different amounts, I’d love to add this kind of conditional logic based on what we’re entering in the “numbers” field, and have those prices automatically calculated in the sheet. Ideally, I would also be able to hide the formula and have it entered in the currency field so I can later filter by author later and have it automatically populate the total at the bottom of the table, but I won’t get picky.

Sorry I struggle like crazy with formulas and know I post on here so often but Airtable is amazing and has revolutionized our reported!!! Cue me getting over-excited and moving everything into Airtable which is great 99% of the time and leaves me with my head in my hands other days. I was just wondering if anyone could help me I have a base for my family support workers, I have added a date created so I know when the referral came in and we have a linked table for interactions which is essentially every time we contact them so there are a lot of linked files in here. What I am trying to do is add a column to work out the date of the first interaction and then a second to work out the number of days between the referral coming in and the volunteer contacting them. Please can someone help before I literally start pulling my hair out

Hello, I’m new here but it’s look like, it’s the tool I need. I have an e-commerce site with a lot of product with a lot of turnover and all this products are in bundles of products. Easy no ? So I want to create a table with all my product and create an other for each bundle and add all the products I need in it. The goal is to create a list of SKU to import them in my ecommerce site with an other tool. For exemple in the bundle A I need a chair so I had chair A. If the chair A is not in stock I would like to automaticaly add the chair B in the bundle and delete chair A, Later if the chair A is in stock I would like to add automaticaly the chair A and delete the chair B, If the chair A and the chair B is available, I want only the chair A First and important question, Is it possible to do it ? Second question, How ? I need some lead or tutorial with information. I hope to be clear, if I can explain more I can Thank

Hello there, I would like to add up number like this : 142.10,42,21.20 The result I want is 205.30 I don’t know how to transform the cell to an array and add up each line of the array Thx for your help

There are 2 fields of type “Data”. What formula should be written for field 3 so that the difference in minutes between the fields ‘Date1’ and ‘Date2’ is calculated there?

Hi, I have two fields containing image attachements. I want to join them in one, but when i try to do this it is printed as text and url… any ideas?

I’m trying to achieve the following: If Invoice Status is ‘Paid’, then ‘Paid’ If Invoice Status is empty and the date in Payment Due is on or after today, then ‘Due’ If Invoice Status is empty and the date in Payment Due is before today, then ‘Overdue’ I have created this formula, which is not working (it does show ‘Paid’ but not ‘Due’ where applicable): IF({Invoice Status} = ‘Paid’, ‘Paid’, IF(AND({Invoice Status)} = BLANK()),IS_AFTER({Payment Due}, TODAY()), ‘Due’, IF(AND({Invoice Status} = BLANK()),IS_BEFORE({Payment Due}, TODAY()), ‘Overdue’))) Any help would be much appreciated!

Use case: View order progress in Kanban view and display relevant button based on stage. For instance, if I received an order I would see a ‘send order email confirmation button’ and when the order moves to order shipped I’d see ‘send order shipped email button’. Can I use a formula to display a button? Is there a way to filter columns in Kanban view? Any thoughts on an approach I can take or direction to go? Any tips, insights, or code would be appreciated.

I cant seem to get this formula to work. its prefilling an airtable form but i cant get the second feild to fill. it says its a good link it just doesnt fill the second box. heres what i have CONCATENATE(‘https://airtable.com/shr4cGVHhm4dRLllz?prefill_UNIT=’,{UNIT} & ‘&PLANT=’,{ENCODE PLANT}) here is an example of what that outputs https://airtable.com/shr4cGVHhm4dRLllz?prefill_UNIT=261A&PLANT=12-CONV the unit number prefills fine but the plant does not. 261A is the data from the UNIT table. the title of the field on the form is also UNIT. 12-CONV is the data in the feild PLANT. The title of the feild on the form is also PLANT note everything in my base is in all caps and “encode plant” is all the plant names run through the url encode formula. i

Hi everyone, I’m new to Airtable and am really struggling migrating a formula. I’ve spent a couple of hours and made little progress… I am using Integromat to run a query against an Airtable date column. Basically the logic should be this “return all rows that match a specific date and time”. Anything returned (usually just one row per hour) will be then parsed by Integromat. E.g. If I set 31.08.2020 15:30 in a date field in the column, I want Integromat when it runs (every 15 minutes) to return all the contents from that row to then be processed by Integromat. I’m generally fine working out the logic in Integromat… Thanks!! :slightly_smiling_face:

hello there, i.ve got a big issue, I have one base with multiple tables, in one table i have proyect # (a number to identify each proyect) requirement # (number to identify each purchase made for the proyect) concept (single selection to identify the type of service or good i.e construction, services, labor etc) value (amount $ of the requirement) Now in another table in the same base, i want to sumarize the total amount paid for the proyect in each concept. ie. proyect # …concept (labor)… total amount $ (1.000) when this total amount is the sum of all the concepts named labor in the other table. i dont knwo how to do it, please help

Dear community, I am working on a table for an art collection. The first field is supposed to sum up my most important information about each piece with a formula: CONCATENATE({artist},", “,{title},”, “,{year},”, ",{height (cm)}, " x “, {width (cm)}, " cm” , ", ",{technique}) This works perfectly fine so far, but I am struggeling to include an IF-Formula within the existing formula: Since a few of the works have a depth worth mentioning, I also want to include the depth of the pieces in the first field, but only IF the field {depth (cm)} is NOT EMPTY. As far as i researched this would be IF (depth != BLANK (), " x ", {depth}) , but I didn’t manage to make this work, let alone yet including it into the existing one. Just to sum it up, I want the formula to be displayed like this, with the bold part only being shown if the field is not empty: artist, title, year, height x width x depth cm, technique I’m sorry if

Hi All, I’m trying to add an individual button for reach row which would redirect to different URL’s, For ex : Row 1 : Button 1 : Google.com Row 2 : Button 2 : Airtable.com so on Currently when I proceed to add the button table with a simple URL formula (image below) It proceeds to apply the same URL for all rows on the Airtable sheet, from which every button points to the same url (google.com in this case) How do I edit individual button’s URL destination? Am I missing something? Let me know! Thanks in advance.

There is a last_modified_time field. I display in it the time of the last change of the “Статус_Заказа” field. The “Статус_Заказа” field has several meanings: How can I create a formula to get the time when the “Статус_Заказа” field was last modified for a specific value? For example, “Частично_Готов”? So that when the “Статус_Заказа” field takes on a different value that is different from “Частично_Готов” - nothing happens

I have a table that has multiple lookup fields to another table. I need to be able to sum the number of unique values in each lookup but keep failing to get it to work. Example is I have a lookup called “Promotions” and then I want the next column to says # of Unique Promotions. Please help!

Hello! I am attempting to make a furniture reuse request system for my company. I have made a furniture removal form/table that generates a gallery view with the items available for reuse, and then a furniture request form that allows users to request furniture from the gallery by unique ID number. I would like to link corresponding entries between the two tables by matching the ID numbers, and bring the destination from the furniture request into the furniture removal table, to facilitate the move from point A to point B. I have made a linked column that allows the mover to manually link them, but I would like to do this automatically. I see several other people have looked for this functionality but it wasn’t supported in 2019… has any progress been made in automatically linking two tables via a function or other logic? thank you!

I just wanna say TNX! to @Justin_Barrett for superb explanation !! He teaches you how to do stuff and shows how to solve problems and not just bumps the solution! Thank you very much! Url Formula to Emcode Mailto:

Hi, I want to do a select on my records according two conditions : Find in “Resource title” field a text Find in “Format” field an exact text For both conditions, I want them to be case nonsensitive because some text are written in caps, other not. So, at the moment, I’ve this : filterByFormula=AND(FIND("some text",{Resource title}),(FIND("Video",{Format}"))) And I’m getting the INVALID_FILTER_BY_FORMULA error message . So, what I’m doing wrong ? Is it possible to have the nonsensitive case research ? Thanks.

I’m new to AirTable bases. I’ve been set up a base (a table), say base_1. I would like to write some Node js code to query the data from the base_1. The query like this: SELECT col_1 from base_1 where col_2 = ‘value_col_2’ What I need to write for “filterByFormula:” Thanks so much for any help.

Hi everyone :grimacing: ! I have been working on different ways to represents performance information. We usually have to register how many points an employee got per week, and then we have to see the average of points by a pair of weeks :nerd_face: . For example, if “Employee 1” got 100 points on week 10 and 100 points on week 11, and the corresponding pair of weeks is week 10 and week 11, I would like to see the average of points of week 10 and week 11 in the same column Biweekly Points (even if by row is repeated). Here is the base I made: Right now, what I tried was to use Find and IF this way :face_with_monocle: : IF(FIND(Week,Biweek),AVERAGE({Points}),“NA”) But the problem is that this formula doesn´t show the average unless: **Biweek and Week have exactly the same information (for example both have W10-W11)* :fearful:

Could someone please explain to me what the purpose of the T() function is? I’ve tried using it on a variety of different fields, and it always returns the field value if the referenced field was a text field, and it always returns nothing if the referenced field was a number field. But — we already knew in advance that one field was a text field, and one field was a number field. So why do we need the T() function? It seems redundant and unnecessary. Perhaps the one cool thing it does is that it converts Date fields into this format: 2020-03-01T10:30:00.000Z Which I think might be useful for sending dates to Google Calendar?