Formulas

Hello, Currently, we have payment data that comes in from Wordpress via a .csv through Zapier. The problem is it comes into one column like this: s:212:“a:6:{s:17:“bankaccountname”;s:8:“Checking”;s:19:“bankaccountnumber”;s:9:“111111111”;s:9:“bankname”;s:5:“BigBank”;s:19:“bankroutingnumber”;s:9:“000000000”;s:9:“bankiban”;s:0:”";s:14:“bankbicswift”;s:0:"";}"; We need to pull that data into 4 different columns - Account Name, Bank Name, Routing, Account Number. Givens: This data is presented as a popup inside wordpress. The end goal is to automate payment requests. Currently, we would have to manually enter in these details inside our payment gateway to issue payment. With a bit of automation it would streamline the process. Any help would be greatly appreciated!

Can I write a formula to do multiple things? For example I want to write a formula that when an items sales it calculates how much the fee would be based on the price. So if I sold something and it was $15 or less the fee would be $2.95 but if something sold and it was more than $15 it would be 20%.

Hello there, I have been using Airtable for a few years now and I absolutely love it. I am creating a basic HR system for a startup and I want to create a formula for the employees’ paid leaves. Basically, each employee should receive 2 days off for every month they work for. it should be added by the end of each month. How do I automatically make it add up in Airtable, using the joining date only? Is there a formula for that? Thanks in advance.

Some help with lookups appreciated. We deliver courses and have a table (A) where participants info is collected, like the name of the course they will follow. (f.e. ‘Photoshop Basis’) We also have a table (B) where the info of the course is seated, like the price of the particular course. Now we want to show in table (A) the price of the particular course so the participant can get an invoice with the right price. How to do that? We need some sort of lookup function for the course name and then return the price-field. Thanks for helping.

Hi There, Brand new to Airtable, so apologies for this potentially stupid question. Am I able to sort a series of dates by month and date only (year isn’t important for the sort)? My current workaround is pulling the month and day into their own columns, then having a third column using this formula (MONTH&" "&DAY) and then sorting the results of that formula. I’d rather not have 3 columns to do a basic sort if I can help it. Thank you!

I would like to sum the number. Here is an example. (value) (accumulate) 10 10 20 30 8 38 11 49 3 52 If Excel, it would be calculated as below (B line) sum($A$1:A*) * means same row number sum(B1,A2) The summary bar will not be fine in my case. I want to understand the accumulated total in each row. Thanks for your support!

I am trying to create a formula to name records by concatenating specific fields but using different groups of fields depending on the item type. In other words: IF {Item Type} is Watch, THEN CONCANTENATE {Field 1} {Field 2} {Field 3} ELSE IF {Item Type} is Necklace CONCANTENATE {Field 4} {Field 5} {Field 6} … and so on for 8 or so item types. This is how the formula looks currently: Is it possible to do this? Help appreciated! I love Airtable but I have no formula skillz.

Hi there, I’ve got a CRM base that has a series of stages that an opportunity (aka the main record) goes through. I’m trying to track the conversion rate for each stage, basically if my stages are: [Prospecting Qualifying Proposal Won / Lost] Then I’m trying to pull a number for: What percentage of prospecting moves to Qualifying? What percentage of qualifying moves to Proposal? What percentage of proposal moves to Won? Assumptions: A key assumption here is that every opportunity goes through a linear process from one stage to another - so nothing just drops into the Proposal stage without having been prospected first. Any ideas or introductions to people who could help would be greatly appreciated :pray:

I’m trying to set up a customer having multiple addresses. So Table 1 is customer that contains the profile, then table 2 for addresses and I link the customer to it. I want the primary record of Addresses to have a count based on how many same customers are linked to it. Any leads?

Hi there, I have a question that I can’t seem to wrap my head around, so I would love to have your input. Let’s take a look at the following screenshot: As you can see, I group records based on a name. In this case, we have “K” and we have “T”. I would love to create a sum of the points for each person. So K would have 1 point, where T would have 5 points. There is indeed already a SUM, but I’m not able to sort on the SUM of each group (if I can, please let me know how, meaning that T would be on top of K). So, I would like to create a field that is able to calculate the SUM from all the records for each name separately. I would not mind if I would see that same result in each record, but the idea is to create a scoreboard, where the highest sum of points for each person is on top (currently, when I sort, it just sorts on the individual point). Is this possible, and if so, how would I go about this? (Basically, I want to do some kind of count if on each name separately and on each mon

Hi community! I have a very strange issue here… I have a lot of duplicates within the same field, a lookup field (I’m not so sure why, but that’s for a different question) In order to try to create a field with no duplicates, I created another field with the formula: (ARRAYJOIN({Most recent First Name :zap: },", ")) It works fine for most of the records, but in some it creates a a comma in front of the name, which I couldn’t get rid of. It happens only in some of the records, and only those that are not duplicated to begin with For the full picture, the next step is a field that takes the string that I created and find the name to the left of the comma which gives me just the name. Any idea what is it that I’m doing wrong? Thanks!

In order to track the workload we’ve had in the past, we want to create a formula that displays which specific weeks we worked on different projects. That way, we’ll be able to see how many projects we had active during specific weeks. We know which week we started a project, and which week we finished it, but I can’t figure out how to display the weeks that passed in between those dates. The number of weeks in between isn’t relevant, we want to see “We worked on 45 different projects during week 14”. Any ideas?

I’m running a datediff formula but it’s not displaying the result I’m expecting. I would like the output to include the day itself. In other words if I’ve got a date range from 28 April to 30 April, for our purposes this is a 3 day event. DateDiff gives 2 days though. DATETIME_DIFF ({Date ended},{Date started}, ‘days’)&" days" Any thoughts?

I understand this is a known issue, but I’ve read several threads on the topic and tried a few solutions but still can’t figure out how to fix this. I have two fields of scores, one from a 2020 publication and one from an upcoming 2022 publication. I am trying to be able to show if the score is an improvement, worsening, or the same. The issue being that the score “0” is an acceptable input and the formula is recognizing 0 as blank instead. The formula is currently as follows: IF( {KI-1 2022} = BLANK(), "", IF( {KI-1 2020} = BLANK(0), "⬛️", IF({KI-1 2020} - {KI-1 2022} = 0, "⬜️", IF({KI-1 2020} - {KI-1 2022} < 0, "🟥", IF({KI-1 2020} - {KI-1 2022} > 0, "🟩", "") ) ) ) ) I have tried COUNTA as well, but nothing changed: *I tried both COUNTA()=COUNTA() and COUNTA()-COUNTA()=0 but neither scenario worked… IF( {KI-2 2022} = BLANK(), "", IF( {KI-2 202

Dear community, first of all I really appreciate this forum, even if just as a silent participant so far. I’m pulling my hair out trying to replace strings in variables to make them ready for webmerge/formstack documents. Input string: my webmerge variable Wanted result: {$my_webmerge_variable} Formula I am using: REGEX_REPLACE(REGEX_REPLACE({regex test},"\A","{$"), “\s”, “_”) Result I am getting: {$my_webmerge_variable How do I get the closing brace = } at the end of the variable? I tried $ and \z but they don’t seem to be working. Thank you all in advance, any help is much appreciated :slightly_smiling_face:

Hello, I am trying to add a duration field in my base and I want the end date to be calculated based on the duration I am inputing. How can I do that? Also, I would like to be able to change the duration whenever I need to and it so reflects the end date accordingly. Thank you for your help! Celine

I have an Airtable form that has a number of yes/no questions on it. I want to convert the Yes’s to 1s and no to 0s. Is there an easy way to declare Yes=1 without having to write as many IF statements as I have questions? Appreciate your help.

I’m trying to write a simple IF statement in the CM Maturity Date column comparing the Policy Type field and outputting either a DATEADD function, if “Claims Made”, or a text string (“N/A”) if “Occurrence”: This is the formula that I currently have in CM Maturity Date that results in the above screen cap: IF({Policy Type} = ‘Claims Made’, DATEADD({Retro Date}, 5, ‘year’), “N/A”) For the Claims Made policy type, it should have added 5 years to the retro date. I can’t figure out what I’m doing wrong here, so I’d appreciate any help. Thanks!

Hello everyone, I was just wondering if there’s a way I can make my airtable cells clickable. I know there’s a button field but with that, I can’t have different labels for each row. Basically, I want the cells in each row to be redirected to the respected detail page for that cell. Any help would be appreciated, thanks!

Does anyone know if it’s possible to run an if() formula for a keyword in a lookup field. (see screenshots below) Im looking to return a ‘true’ or ‘false’ if it contains the word “Floorplan” however i believe that the lookup fields are not considered searchable by the formula as it is returning ‘false’ even when the parameters are meet? Can anyone confirm if my suspicions are true and if so how can i go about making the lookup field compatible so it returns the correct answer in my if() formula Thanks in advance

please help me. I have 2 tables, one for sales and the other for stock. The inventory table receives data (customer id and product) that is informed via a bot every time the customer goes through a certain flow. however, I need this product to be counted only if the sale is made. In the sales table you will find the customer’s id. and this table is only fed if the customer makes the purchase. So basically I need a column in the inventory table that asks the following question: if the sales table has an ID equal to the inventory table then 1 if not 0.

Hello! What do I have to do if I want to add an image (logo company updated as an attachment) to my main record with a formula? I tried {Company name} & " - " & {Logo}, but it gives me the name of the file (logo.png), not the image as it self. Is that possible? Sorry, I am a begginer, as you can see! Thanks!

Already seen: /t/if-contains-formula/10126 How can I count the number of linked records in a linked record field (column)? – Airtable Support /t/counting-linked-records-formulaically/20537 /t/formula-to-count-records-in-linked-tables-based-on-values/32018 Hello Airtable Community :slightly_smiling_face: I’m currently consuming Airtable via the API in Javascript and I want to fetch some records accordingly to some linked field based criteria. I have a linked field (called “trainings”) and I need to return records containing only a specific linked value. The way is obviously with filterByFormula options in my base.select() function BUT I cannot use SEARCH() or equivalent in my formula: e.g. base.select({ filterByFormula: "SEARCH('Some Training Name', trainings)" }); The reason is that “training”'s names may have a comma or any other special char and a formula is computing a linked field by default by concatenating corresponding primary field to a single comma separated string… I wan

Hi! I am building some QA into my Base, and have x4 date columns that I want to check all equal each other. To ensure the first x3 dates equal each other I have used: IF(AND({Date 1}={Date 2},{Date 2}={Date 3}),“Match”,“Not match”) I think that is right and seems to work well. This all works fine, but the issue I have is that the {Date 4} column doesn’t always have a value (intentionally), and hence I want to ignore it in the date comparison if not populated. i.e. if {date 1}={date 2}={date 3} but {date 4} is empty, the formula should still return “Match” to signify that all dates match. I’m sure this is very simple, but I’ve tried multiple different IF statements and quite simply can’t get it to work! Any help would be greatly appreciated :slightly_smiling_face: Thanks.