Formulas

Goal: take Rollup results, and summarize the unique values with a count of each unique value. For example, instead of showing [10, 10, 10, 2, 2, 1, 1, 1, 1, 0.5], I want to show [3 x 10, 2 x 2, 4 x 1, 0.5]. Or, if needed, I can show [3 x 10, 2 x 2, 4 x 1, 1 x 0.5]. Or, similarly, instead of showing [carrot, carrot, carrot, potato, potato, onion], I want to show [3 x carrot, 2 x potato, onion]. The ARRAYJOIN seems to be working as designed so far, but I’m hoping to summarize the results in a way that is a bit more user-friendly. I suspect there is some way of using the ARRAYUNIQUE function to help with this, but I haven’t found a solution. PS: this is my first post here, so feel free to let me know if I’m doing anything wrong and I’ll work to correct it. [edits: spelling correction; new example and explanation]

How do I find the record with the maximum entry in a certain field. Note, I do not want the maximum NUMBER in this field, rather the record it corresponds to. For more info: I have a list of names, with how many tests they conducted. I want to find who has done the most tests. However, doing this once isn’t enough. I want to be able to replicate this given certain conditions. For example, of all the people with a specific bank (I have a field for this too), who has completed the most tests? I’m more than happy to provide more info if needed,

Hi, I need to insert data in a column of table 2 in table 1, automatically, if there is the same value (text type) as the NAME of table 1 in table 2. I’ve tried some formulas and I couldn’t. Please help me. Attached sample images! Thanks!

I extract only year from date field with DATETIME_FORMAT(Date, 'YYYY') and assign it to new field. If I order by that field it isn’t recognized as number, but as a string (the option is A-Z or Z-A). How can I change it to a number?

Hi AirTable Community, I am trying to create an IF formula to format a number field to an abbreviated format depending on if it is 100,000 or 1,000,000… eg. “780000” becomes “780k” “1000000” becomes “1M” Purpose is because I am trying to Concat the abbreviated number together with other fields. Does anyone know how to write such a nested IF statement? Thankyou in advance!!

I have a marriage date for some people and no date for others. I created a field called THIS YEARS ANNIVERSARY as shown below. IF({Marriage Date},DATETIME_PARSE( DATETIME_FORMAT( {Marriage Date}, ‘MMMM D’ )& ’ '& YEAR( TODAY() ), ‘MMM D YYYY’ )) I need a formula for NEXT year’s anniversary. If THIS YEARS ANNIVERSARY was a simple date field, I know how to use the DATE_Add formula but I am struggling with how to use the DATE Add formula to this formula field so it is also seen as a date so I can use it as a date field in a calendar. Any help would be appreciated.

I have a measurement form. Users can add numbers into the appropriate fields (labeled number only fields). I think have a formula field that formats the numbers into something that can be readable. Each field is added on its own line in a formula field with a * by it, so the end result looks like this (sample formula below): Height: Bust: Waist: Thigh: CONCATENATE( IF({Height}, "* Height: " & {Height} & "cm\n"), IF({Head Circumference}, "* Head Circumference: " & {Head Circumference} & "cm\n"), IF({Neck Circumference}, "* Neck Circumference: " & {Neck Circumference} & "cm\n"), IF({Chest Circumference}, "* Chest Circumference: " & {Chest Circumference} & "cm\n"), This works great. However I have an “Additional Measurements” field that is a long text field, where a user can add any missing measurements. For example: Calf Circumference: Hip Circumference: I am wanting to know if I can add a * to each new line like in the other formula? The above user in

Hello Everyone, Maybe somebody form the community will help with something I’m struggling with. I’d like to make an API call with a Filter formula on a lookup field, which includes comme separated dates, see below : As you can see, there is a simple calculation field that is : IS_BEFORE(TODAY(),Booked_Dates) that doesn’t seem to be very happy with the multiple item field on Line 1. However, when I’m filtering lookup fields with conditions in Airtable it seems to work perfectly fine (don’t notice both conditions, only the second one): And the result : So my question is : Is there a formula I can use to have the same behavior than Airtable’s lookup filter so I can make the appropriate API call in the FilterByformula query ? Or is there any workaround that one of you might suggest ? The idea here is to look for an available timeframe in a booking system… Thank you for your help. Best, D

I am making an automatically repopulating task list. I can’t use the Workday function because we work on Saturdays and have a non-standard holiday schedule. The formula I am using checks a field which adds days based on a daily, weekly, monthly assignment to the old due date. I have a formula that successfully checks that new date against the holiday string rollup, but it can only add 1 date at a time- so if there are multiple holidays in a row, it doesn’t recognize that. Is there a way to make this formula keep checking the Date Calculation 1 sequentially against my rollup of days to skip until it reaches a workday again? IF(FIND({Date Calculation 1 String}, {Skip String Rollup}&""),(DATEADD({Date Calculation 1}, 1, ‘day’)), {Date Calculation 1}) I’m guessing I need to change the Dateadd value of 1 day, but I’m not sure how. Any help is appreciated.

Hola, tengo un campo que indica el numero de vencimientos. ¿Como crear en otra tabla tantos registros como es el numero de venciimiento? O sea si tengo 3 vencimientos que cree en otra tabla tres registro para gestionar el pago. Gracias

Hello. I have a table that it is filled using a forms. there are two fields coming from two other tables that are selected by the customer. I need a formula column where it analyzes the two filled fields and returns the result (remuneration) based on a third table (remuneration_table). below I am attaching a photo to illustrate. basically I need the formula to return the candidate’s remuneration based on the sector and position he will be hired. Could someone help me?

Hi! I would like to set-up a formula where if Column A is not blank, Column B says “8.0”, and if Column A is blank, Columb B will say “7.0” Is there a formula to do this? Thank you!

I have a date formula to work out the number of days between 2 dates, if either or both of the dates are blank, it returns NaN, how can I amend the formula to only perform the calculation if both fields are populated? (Long overdue a lay-in and my brain is not functioning by itself, need help… :slightly_smiling_face: ) DATETIME_DIFF({Finished},{Started},“days”)

Hi I have a lookup field that is displaying values from a multiple select field, which I have converted into a comma-separated format. Let’s say the values in that field (field A) are: Cat, Dog, Antelope, Lion I have another field (field B) that has the possibility of having at least some of the same values as the first field. It might look like: Lizard, Elephant, Cat, Eagle The order of the variables is, well, variable because the user may enter them in whatever order they wish. What I need to do is evaluate whether any of the terms in field A match any of the terms in field B. In this case, it would return true because ‘Cat’ appears in both. Thanks!

Is it possible to sort records numerically in a roll up field?

Hi, I am trying to generate a “Date” based on the month input available in my airtable. I tried using the If function, however, the result is not being recognised as a date. A little more information on my use case. I’m creating a database from a form that asks people to enter their preferred month, these months are provided as dropdowns. So, for example, if my table has October as a Month - I want to be able to create an additional column that tells me the first date of the months, i.e October 1, 2021, should be the value. Any ways to do this?

I’m working on a table where I have different currencies (in separate columns) and need to convert them all to one currency. I have a column marking the currency used and so the conditional if statement uses this to have a different calculation for each statement. However, for some reason, the formula doesn’t work but it does not return an error, it simply returns a blank Cell, so I have no idea what I’m doing wrong. Please HELP. IF({Currency} = “MXN”, {Amount in MXN}/{FX}, IF({Currency} = “EUR”, {Amount in EUR}*{FX}, IF({Currency} = “USD”,{Amount in USD}))) I know about the Currency converter app, but due to the Terms and conditions/privacy settings, I can’t use it.

Its probably a simple formula…but here goes… I have a record in airtable that looks like this: The A, B, C represent the responses to survey What I want to do is COUNT the number of each letter - so for example A = 3 B = 1 C = 2 E = 1 I have got as far as: IF({Col1}=‘A’,1,0) and that returns the 1 (in this example) What does the formula look like to check in Col2 - Col7 contains ‘A’ and then give me the total of 3? I hope that explains what I am trying to do clearly :grimacing: :nerd_face: Thanks in advance.

Consulta: Si tengo un campo donde ingreso la fecha de nacimiento de una persona, cómo debo formular otro campo para que me devuelva la edad de esa persona y que se actualice automáticamente cada vez que consulte la tabla. Gracias a todos!

Hi, Is there in Airtable a (cumulative) normal distribution formula or work arround besides writing the full formula ? ie : Excel norm.dist function equivalant with cumulative option set as true. Thanks

I have 21 lookup fields that are used by different team members, which may or may not be filled in for a given Airtable record (i.e., one record might have lookup items tagged in 3 of the fields, another record 5 of the fields, yet another just 1, and so on). I’d like to summarize, with a formula field, all of the lookup items tagged for each record across these 21 fields. When I just use an & operator, I’ll end up with something like this, if the record has items tagged in more than one of these 21 fields: Tag 1A, Tag 1BTag2A, Tag 2B, Tag 2CTag 3A …so I want to add a comma and space separator between the tags from each field, but only when values are actually present in any of the fields so as to not have redundant commas. I tried doing something like this: {Field 1} & IF(AND({Field 1}, OR({Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}, {Field 7}, {Field 8}, {Field 9}, {Field 10}, {Field 11}, {Field 12}, {Field 13}, {Field 14}, {Field 15}, {Field 16}, {Field 17}, {Field

Hi community, I need your help. There is something i don’t understand with the formula bellow : IF( SEARCH("Jungle", {Order Line Items}), "Jungle", IF( SEARCH("Baby Plantes", {Order Line Items}), "Baby plante", IF( SEARCH("Pets Friendly", {Order Line Items}), "Pet's Friendly" ) ) ) I want to write a 4th condition but it never work. What is the importance of the space between the condition and the “)” ? How can i built easly this type of formula ? Thank’s a lot, Matthieu

I have 3 columns that have data, and for any given record, only 1 of those columns will have data. I’d like a 4th column to populate the data that’s in whichever of the 3 columns has data. Examples: Column A = Blank, Column B = Blank, Column C = ABC, then Column D = ABC Column A = XYZ, Column B = Blank, Column C = Blank, then Column D = XYZ Column A = Blank, Column B = MNO, Column C = Blank, then Column D = MNO I cannot for the life of me figure out an IF/AND or a nested IF statement(s) that will make this work. Is this at all possible? Here’s a snipit of my table where I’d like Interaction ID to handle what Column D in my previous explanation would have. I appreciate any insight. Thank you!

Hello. I am trying to create a set of tables where our employees can fill a form to update the status of an order, and the latest status gets updated to a table which contains all orders. I have a table which contains details about orders, of which one of the fields is ‘status’. I have created another table with a form view, which has a single selection dropdown menu with different statuses of the order such as ‘Department 1’, ‘Department 2’, and ‘Department 3’. When an employee fills this form, they select the Order ID from a linked field (which is linked to the orders table), type in their name, and select the current status from the drop down. I would like only the latest entry from this Form Entries table to be visible in the status field of the ‘Orders’ table for that respective order. Any suggestions on how. to make this work? I would greatly appreciate any help you could provide. Thank you!

The setup: I have one table which is used to schedule marketing emails (each record contains a single date). I have another table which is used to schedule multiday promotions (each record contains a start date and and end date). What I’m looking for: For each record in my email-scheduling table, I would like a field that links to the records of all the promotions from the promotion-scheduling table that are ongoing during the day the marketing email is scheduled. Any help would be appreciated. Thank you.