Formulas

Hello Community! I have been trying to figure out how to perform a double conditioned rollup (if that makes any sense). The example I think will explain better the situation: In this example there are three tables that are concerned Project, Provider and Concept Catalogue. On the Concept Catalogue table there are records which contain scope for each of the providers by project. Each record has linked fields to Project and Providers. Now, I can nest-group the Concept Catalogue table as shown in the image, into projects and then into Providers, which will allow me to see the totals by-proyect-by-provider. I would like to obtain those values somehow in another table so I can use them. If I perform a rollup (for any of the fields on Concept Catalogue) on the

I am creating a base with two tables to determine the likelihood of which medical condition is most causing symptoms. In table 1 I have essentially a dashboard. List of possible conditions, percent likely, my symptom number, number of symptoms possible, list of all symptoms I selected, etc. In table 2 I have a long list of symptoms possible in all the conditions. I have a column linked to table 1 pulling the condition type information, a column linked to table 1 called “link to possible diagnosis”, and a column linked to table 1 called “Condition (from possible diagnosis)”. “Link to possible diagnosis” has all of the conditions listed that share that symptom. “Condition (from possible diagnosis)” is currently empty. What I want to do is have a checkbox column in table 2 labeled “Experienced?” where, when checked, would copy the conditions from “Link to possible diagnosis” field for that specific symptom in table 2, to the “Condition (from possible diagnosis)” f

I got two table in a base, one is main table which show big categories The other table is record the small task of the big categories (each small task data have link to the big categories) Can the main table rollup the earliest due date of the small task of that big categories and show up the due date and title of that small task in a same block?

I want to get minimum value in a cell however I want to exclude 0.

Hi all I want a field to only display if this condition is met: {Session Status} is “ :sparkles: Public. Live on web.” (Session Status} links to the [Session Status] table. One of the records in there is named “ :sparkles: Public. Live on web.” When I write a formula for: FIND({Session Status}, “ :sparkles: Public. Live on web.”) I get 0 as a result. I am guessing FIND() isn’t reading that field because it is a linked record. Any workarounds come to mind? Thanks!

I want the start date to be the initialized date of the task plus 1 day. The end date should be the start date plus whatever number is entered in the duration field. I was trying to use IF plus DATEADD to first check if there was data in the initialized and duration fields, then proceed to auto fill the start and end field. Any help is appreciated!

I’ve got a staff list that also has columns for who their supervisor is, who reports to them, and who their teammates are. I need to count the number of times the staff member’s name appears anywhere on the table. Can you help me craft that formula?

Hi! This is probably super simple but I just can’t find how. I have a multiple select column with some 15 options. I want to get the summary of how many times each option has been selected in the whole columns, so something like: Option 1 = 3 times, Option 2 = 0 times, Option 3 = 6 times … Not sure if it requires a formula or something different. Thanks in advance! :slightly_smiling_face:

I’m trying to write a formula that will take the total sum from ‘Participant Growth’ column and divide it by the sum from the ‘Output 1’ column. The sum in Column A is 3 and the sum in column B is 4. I’m trying to write a formula for this…The formula I currently have is: SUM({Participant Growth})/SUM({Output 1}). But it is not returning 0.75. Am I thinking about this correctly or might I need to create a new view?

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