Formulas

I have a field linked to another table, and when I use roolup with ARRAYJOIN, it didn’t keep original sequences. For example, the sequence of the linked field is [a, b, c], but the output of the roolup is “c, b, a”.

Hi I made two columns (1 for current and 1 for complete) to count how many projects are linked to each contact I have. When I made the formula with an & it added 2+0=20? When I changed the & to a , it is now calculating the correct amount 2+0=2 Why is this?

How to change the map marker color based on a column value for eg. company type

Ah! ALL of my fields that incorporate the SWITCH() function are suddenly returning “#ERROR!”. Is anyone else having this problem? It happened when I just created a new field using that formula as well…

I wanted to enter emails into an airtable. Each email would be its own record and their would be three fields - open rate (percentage), click rate (percentage), and ‘tags’ (multi-choice). I want to be able to average the click rate/open rates based on the different tags I give the emails. So say I have a tag called ‘topic 1’. I want airtable to create an average of all the open rates or click rates for all the emails with the tag ‘topic 1’ in the multi-choice tag field.

Hello, First post and couldn’t find the answer. I’m trying to create a nested IF. Field A, single select as choice 1, 2, or 3. Field B, is a Date Field C, is a Date Field D, is a Date I’d like to the formula to check the value of Field A. If it’s 1, return date from B, If it’s 2, return date from C If it’s 3, return date from D Thanks!

Hi All, I am loving the new Last Modified Time as I have been waiting for this ever since we started using Airtable but now I have run into an issue. I would like the LMT to see when I add a new project that the lookup field finds in another table but Airtable isn’t allowing me to do this. I read that you can create a formula to get around this but I can’t seem to figure out how to make that work. Thanks for the help.

Hi, I have migrated my data from Google sheet and each record has a unique code (i.e. TO-1234). I want to autogenerate a new number in increments of 1 for each record that is added which shold not a duplicate of any previous code listed in the field. ; in regex the formula is: =MAX( ARRAYFORMULA( value( if(REGEXREPLACE({N3:N9290},“TO-”,"")=“XXXX”, “”, REGEXREPLACE({N3:N9290},“TO-”,"")) ) ) )+1 I can’t figure out how to replicate this in Airtable. Help! Thanks

I have embedded my base in our SharePoint site. On my screen I see the fully grouped view. My colleagues see the mobile view. Any ideas of what could cause this?

I have a credits of 260 and i transfer it to a base and i lost my credits and Dont Get UPgraded Why?

> As detailed on this page, the ARRAYJOIN function is supposed to let you choose a separator. However, no matter what I type for the separator, it remains as a comma without a space after it. The relevant part of my formula code: (ARRAYJOIN(Author, ", ") {Author} contains two items in the array: “Surname, Name” and “Surname2, Name2”. What I get after ARRAYJOIN: “Surname , Name,Surname 2, Name2”. Just in case, my full formula code: SUBSTITUTE(CONCATENATE(SUBSTITUTE(ARRAYJOIN(Author, “, “), '”’, ‘’), " (”, Year, "). ", Title, ". ", Publication, ", ", Volume, “(”, Issue, "), ", Pages, “.”), “()”, “”)

Is there a formula that chooses an item from a list /arrayand displays what number in the list it was? For example, I imported data from Zapier that says “No,No,Yes,No,No” Is there a function that tells me that Yes is the third option? Or something in Zapier? Thanks!

How can I call an external API from Airtable to CRUD Airtable field ? Is there any tutorial available?

I am having trouble, where when I update one of the variables that should change the results of an IF statement, the formula as a whole doesn’t seem to be “rerunning” and I get stuck with the last result. Example, I start with a {fabric release date/dates} of 05/16/2019 and I get a result from farther down this IF statement. I then change the release date to 05/16/2050 and the result remains the same. If I start fresh, it will recognize that the date is later than today and return “Fabric Not Released”, but it doesn’t seem to consistently refresh/rerun from the beginning of the formula. What can I do? Is the a “hard reload” type action? Do I need to simplify the IFs? IF({Retired?}=1,“Retired”, IF({Model Completed Date}=0,“Unmade”, IF(DATETIME_FORMAT({Fabric Release Date/Dates},‘MM/DD/YYYY’)>DATETIME_FORMAT(TODAY(),‘MM/DD/YYYY’),“Fabric Not Released”, IF(DATETIME_FORMAT({Pattern Planned Release Date},‘MM/DD/YY’)>DATETIME_FORMAT(TODAY(),‘MM/DD/YY

Hi, I have a database set up with multiple tables. Set up like this Person1 (Information in one table) gets 5 Linked Entries in another table How can I get these linked records to sum in the table with the first table

Hi, I need formula where Due Date=Date of release + 6 workdays. Please help! Thanks!

I have a table with shipment tracking ID and Carrier, so i want following solutions. if Carrier = Fedex then “track1” if Carrier = DHL then “track2” if Carrier = Blue Dart then “track3” if Carrier = DTDC then “track4” if Carrier = FedexLite then “track5” I can do it with basic if function but got stuck with multiple conditions.

I’m trying to count specific records that appear in a “Lookup” field. The problem with applying a “Count” field to the “Lookup” field is it counts ALL the linked records that appear in that lookup field. Lets say the Lookup field is pulling the names of multiple people from Table A into Table B. In one of the records of Table B, lets say the following values appear in the Lookup field. Brooke H Brooke H Brooke H John M Ronny W Ronny W I want to apply a formula to the Lookup field that counts the amount of times “Brooke H” appears. With the above data, the formula should return the value of “3”, since Brooke H is listed 3 times. If I were to apply a “Count” field to this, it would give me a value of “6”, counting all the “records” that appear in the Lookup field. I want to figure out how to count specific values, not total records. Any tips or suggestions in much appreciated!

Hi I wondered if anyone might know what this formula does, it was created for my scenario in Integromat: {{replace(toString(14.data); “/^([^\n\r]*[\r\n]){12}/”; emptystring)}} I am just curious to know. Thank you, Mary

Hi, I have these functions in two fields: Field “Dani”: DATETIME_DIFF({Od},{Do},'days')-1 “Od” field is a date without time, and “Do” is also a date. Sometimes they could be the same dates. Field “Test date function”: IF( Smena=1,Dani-1, IF( Smena=2,Dani-1, Dani) ) “Smena” field can be only one of these three numbers: 1, 2 or 3. Would it be possible to run the IF function of “Test date function” field only if the date range between “Od” and “Do” include Friday(s) and/or Saturday(s)?

I have table with pin code, city and state, Have second table with contacts In contact record, if I type pin code then how can I get city and state value filled in related field from linked table, IF function not works for me as pin codes are 26k records.

I’d like to be able to have a team member track and log a specific time in/time out for a specific airtable. Kind of like logging hours. Can this be done? I already have a seperate “Date” column setup. Thanks!

I would like some type of variable drop down in a form where users can add references. E.g. a prompt, “List up to 3 references.” And then up to 3 pairs of fields asking for (Name), (Phone number) What’s the best way to structure the base and form for this

Hello, I need with some a formula I’m trying to calculate, I need the percent (%) difference between two values fields and would like to format it so that if the percentage comes back as infinity, that data would be removed from the record so I can view the summary percentage of that field in my dashboard block. This is the formula Airtable told me to use to get the percentage difference: {PURCHASE PRICE}/{LIST PRICE}*100 …which shows up like: ie: Sale Price/List Price100 = List to Sell Data % $575,000/$500,000100 = 115% $575,000/$0*100 = Infinity …but then my dashboard block can’t show the avg % of the sale to list price. Any help would be extremely appreciated!

Hi, I am working on a check in/ check out system for a local studio. I’ve hit a problem that I can’t find a solution for elsewhere. The image below shows the problem. The second row should show a “ :white_check_mark: ” as the Last Check In is 1 minute after the Last Check Out, however the difference between the two times (using DATETIME_DIFF) is showing as 58 minutes. I think the problem is caused by DATETIME_PARSE on another table not correctly setting the timezone (see below). If I have “GMT for all collaborators toggled on” it shows the correct time (as above) but is still being treated as the correct time + 1 hour Any ideas what is going on here? Or how to get around the parsing issue? Any help would be greatly appreciated! Ross