Formulas

I have sample db like this here i want to retrieve only Card colum for last 4 digit alone. I have used the below formula but it returns the whole data But i was success with other filterByFomula like searching InActive members from Status col like this Please help me to resolve

I’m trying to link multiple fields together and cap the number depending on another field’s selection. For example: Currently I have this formula working as intended in the a field: {X field} / WORKDAY_DIFF({Y Date}, {Z DATE}) I want to add a quantifier on top of that will determine the number it puts out. So, if field A=B, cap the number at 2 If field A=C, cap the number at 1.

I’m trying to format markdown in a long text field to something I can use in Zapier. The input looks something like this: # This is a heading 1 This is a paragraph - bulleted item 1 - bulleted item 2 - bulleted item 3 And here's a paragraph - with an embedded minus, and some more normal text The output should look like this: # This is a heading 1 This is a paragraph - bulleted item 1 - bulleted item 2 - bulleted item 3 And here's a paragraph - with an embedded minus, and some more normal text In essence, I need to replace the single newline after each paragraph with two newlines, except for the bulleted lists. My first strategy is to use REGEX_REPLACE(source,“\n”,“\n\n”) which adds the 2 newlines everywhere. So far so good. But for the life of me I can’t find a way to remove the (now) extra newline between the bulleted items (lines starting with a “-”). It seems that the “^” (match at the beginning of a line) in Airtable’s REGEX doesn’t seem to work? Any suggestions highly apprec

Hello community, I’m using an Airtable form and Make’s (formerly Integromat) Airtable module “Watch responses”. Every submission trigger the hook twice within 5-6s. Has anyone experienced this or an idea of how to avoid the duplication? Thanks!

I want to make formula that tells you in what Season we are. I have the seasons divided as follows: Summer = July-Oct Spring = Mar-June Winter = Nov-Feb I have a column which shows the number of the month we’re in with help of the formula MONTH(TODAY()) Then in the column next to it I made a nested IF statement which should determine which the season we’re in based on the division I set above, but it’s only showing the season ‘Summer’ when the month column has a number between 7 and 9 (July-September). With the other month numbers it show no result (i.e. a blank cell). Here’s the IF statement I’m using: IF( {Current Month} >= 7, IF({Current Month} <= 10, 'Summer', IF({Current Month} >= 3, IF({Current Month} <= 6, 'Spring', IF({Current Month} >= 10, IF({Current Month} <= 12, IF({Current Month} >= 1, IF({Current Month} <= 2, 'Winter') ))))))) It would be great if you could help me out. Thanks in a

I have tried to edit a formula in a shared base, my own existing bases and an completely new base and all I get is a spinning circle. I have a deliverable that is due tomorrow and I am dead in the water. The response from airtable is that they’ll get back to me tomorrow. HELP

Greetings Airtable Community, Several weeks ago I received great assistance with a formula quandary involving the combination of two multi-select fields: {College(s)} & IF({Department(s)}, " and " & {Department(s)}) This formula assumed that the {College(s)} field would always be populated. Unfortunately, as we’ve progressed, I’ve discovered this will not always be the case—there will be rare instances were the {College(s)} field will be empty. In these cases, the formula returns: “and Athletics” instead of just “Athletics.” Is it possible to adjust the formula so that it will return just the name of the department (i.e. the " and " will not appear)? Thank you very much!

I want to write a formula that calculates percentages of a total sale price based on data within a multiple select field. Basically if an item is marked “commission” in the multiple select field the formula would calculate 30% of sale price. If marked “asset” in multiple select field the sale price would not change. Do I need to use an if statment to write this?

Looking to convert this excel formula into an Airtable formula. =TODAY()+7-WEEKDAY(TODAY(),16) Details We run payroll every 2 weeks, so we’d like to send a reminder on the Friday that timecards are due. So, if today is 7/21, the date in the cell should read 7/29 - until it passes. Then the next date should read 8/12. The above formula does that in Excel, but I’m at a loss of how to convert it.

Hello- I need help, I have 2 separate date fields and only one will be filled out for each record, I need a new field to show all dates MERGED. Thank you! SG

I want to find a way to automatically mark a record as “Used” if it is linked to another table. Is there a formula for an IF statement for “IF a field has information in it (if it’s NOT empty), add “Used”” Right now I have the following, but I’m receiving an invalid formula error message: IF({field}, “USED” Thanks in advance!

Hi! How do I format total cost with if the checkbox is checked it multiplies the cost by 50% and if the checkbox is unchecked, it keeps the full total amount? Example: cost= $100, if I check the checkbox it totals the cost to 100*.50= $50; if the box is unchecked it totals the cost to $100

Hello, I can’t create a progress bar based on the empty or not-empty columns of my table. ex: if I have 6 columns and only 3 are filled then the expected result will be: 50% :black_circle: :black_circle: :black_circle: :black_circle: :black_circle: :white_circle: :white_circle: :white_circle: :white_circle: :white_circle: On Notion I succeeded, but I can’t do it on Airtable. Can you help me ? Thanks !!

Hi! I’ ve encountered a possible Airtable quirk and was wondering if anyone has a hint how to work around it. I have a Long Text Field with RTF enabled. In this field I have RTF formated text / Markdown text. In a formula field I’m trying to search this field for Blood Type: AB and if found output AB. Nothing strange so far. Only issue is that no such instance is found and the cell remains empty. I have tried IF( FIND('Blood Type: AB',{Profile}), 'AB' ) and IF( REGEX_MATCH({Profile}, 'Blood Type: AB'), 'AB' ) As I figure that it’s the markdown messing with it I have also tested IF( FIND('**Blood Type:** AB',{Profile}), 'AB' ) and IF( REGEX_MATCH({Profile}, '**Blood Type:** AB'), 'AB' ) and IF( FIND('__Blood Type:__ AB',{Profile}), 'AB' ) and IF( REGEX_MATCH({Profile}, '__Blood Type:__ AB'), 'AB' ) I even tested adding a &'' at the end of the field name, just in wild fishing/testing effort. No luck. Does anyone know how to do this? If I change the text to ‘n

Hi! I have a base with quite a bit of Korean names in it. As the names often have a few different ways of romanization I have a field for alternative romanized spellings; {Alternative Spelling(-s)}. What complicates it further is that each romaization can be formated in three ways. Format 1: Name with only spaces, e.g. Park Bo Young Format 2: Name with a hyphen in the second ‘space position’, e.g. Park Bo-Young Format 3: Name with space only between first and last name, e.g. Park Boyoung I need to be able to search by any of these format versions and thereby need them all listed in a field. Today I do this manually, but I figure there’s a way of doing this with a formula with possibly some regex in there. It’s above my current knowledge to 1) figure out the best path to do this and 2) get all the wrappings and or syntaxes in the right position, so I seek help figuring this one out. Each alternative spelling is always separated with ; I need a formula that takes each name separated by ;

Hi there! I’m losing my mind trying to figure out a formula for something I thought would be quite simple. Basically I have one field into which we input a URL (which comes from our CRM). I want to extract a particular portion of this and then add it to text in another field. To explain: The original field might have something like the below: https://author-p4650.adobeaemcloud.com/editor.html/content/dd/au/en/the-project/**about-us/our-favourite-restaurants-with-a-view**.html I’d like to extract just the section in bold and add it to the following: https://www.danmurphys.com.au/the-project/ to create the below in the formula field: https://www.danmurphys.com.au/the-project/about-us/our-favourite-restaurants-with-a-view I’m not super well versed in formula writing but have tried quite a few things. While the first portion of the original string (ending with “the-project/”) is always that exact number of characters (80 in this example), the issue I keep having is that the portion I want

Hi team, I’m hoping for some help. I am trying to work out the number of weeks between 2 dates. It keeps returning the results Nan and I have no idea what I am doing wrong. Here is the formula I am using. I want to know how many weeks between “Cancel Date” and “Sale Date”. If anyone could give some input or advise it would be massively appreciated. Thanks DATETIME_DIFF( {Cancel Date}, {Sale Date (from Sales)}, ‘weeks’ )

Hi all! So I’m trying to make things a little easier for our Training Team and automating their Class Agenda creation process. Currently I have 3 tables in the base: Classes, Agendas, and Class Agenda Templates. Using automations, I am able to select a template in the Classes table (through a linked record field) that generates all agenda items for the given class in the Agenda table. Each agenda item created is also linked to the specific class through the linked record field. I’ve gotten as far as you can see in the screenshot of the Agenda table below. I’m trying to figure out a solution that will allow me to use the Start Time (given) of the first Agenda Item along with the Duration field to generate the start and end time for each topic throughout the day. I’m aware that I will most likely need to create and use formula fields as well. Any help is appreciated!

I have a Service Years column that I would like to round in a new formula filed This is what the formula looks like in the Service Years column I would like to have a new field/column that take the year and month and round it up to the year. Example: (Service Years) 10 years 10 months – (New Field/Column) 11 years

Is there a way for me to auto tabulate from “Inventory” description to the respective field under “description” column for all the rows? Title: enter title Year: enter year Type: enter type Dimension: enter dimension https://airtable.com/apphT5d1TuRAoA4Tw/tblgN5TZV5AoF3Uo0/viwietBKsP2FguBSx?blocks=hide Original post was here. Auto Tabulate Between Tables Formulas Is there a way for me to auto tabulate from “Inventory” description to the respective field under “description” column for all the rows? Title: enter title Year: enter year Type: enter type Dimension: enter dimension I am getting this error. ‘ Brand:’ & {Brand Name} & ‘Title:’ & {Title} & ‘Year:’ & {Year} & ‘Type:’ & {Product Type [Non Editable]} & ‘Dimension:’ & {Length} & ‘x’ {Width} & ‘x’ & {Height} & '

I have a very predictable set of data across multiple tables. There is one table where a person can request a room for a meeting. If the room for the meeting is more than 30 minutes I run automation to make sure the next 30 minutes time period is assigned and link it back to the original request. This results in a LookUp field called Timeslots that has two results 30 minute period 1 w/ Date & 30 minute period 2 with date. For the next step I need to take 30 minute period 1 w/ date and use that info to Schedule the meeting in my cal. the result is always hh:mm mm/dd/yyyy I am stuck trying to figure out how to extract just that first 30 minute entry from the look up datetime format it.

Hey, I’m looking for a solution to choose the random row from another table. Here’s my scenario: 2 tables. One has text data, the other has just two columns: ID and Attachment (in this case it’s a png file). I want to create a lookup filed to pull the attachment, but I want this lookup to pull the RANDOM ID out of finite number of rows (like 10 in my example). I could do that in Excel by combining VLOOKUP with RANDBETWEEN functions, but how to I do that in Airtable? Thanks!

I’m having trouble figuring out how to do a formula for dependent single select fields. This formula will be used in a form for field technicians for a construction company. The first single select field will have the choices: Product A, Product B, Product C The second field will have the choices: Variation 1, Variation 2 Any help with the formula below is greatly appreciated!

Am unsure how to best evaluate an impact range. Three attempt below. Would appreciate help to divine practical solution. Thanks in advance. Impact Assessment No Impact, 0 - .9 Very low Impact, 1 - 1.9 Low Impact, 2 - 2.9 Medium Impact, 3 - 3.9 High Impact, 4 - 4.9 Very High Impact, 5 I thought to evaluate and have tried these: Attempt1, cannot get beyond first set of IF(OR IF( OR({xf.Impact Score Average}=0,“No Impact”, {xf.Impact Score Average}<=1),“No Impact”, IF( OR( {xf.Impact Score Average}=1,“Very Low Impact”, {xf.Impact Score Average}<=2),“Very Low Impact”,“X”)) Attempt 2, does not appear to work IF({xf.Impact Score Average}>0 <1,“No Impact”,“XX”) Alternative 3, Appears to work but bit cumbersome IF({xf.Impact Score Average}=0,“No Impact”, IF({xf.Impact Score Average}=0.1,“No Impact”, IF({xf.Impact Score Average}=0.2,“No Impact”, IF({xf.Impact Score Average}=0.3,“No Impact”, IF({xf.Impact Score Average}=0.4,“No Impact”, IF({xf.Impact Score Average}=0.5,“No Impact”, I

Hello everyone, I am trying to use workday_diff function to calculate whether my team is able to process orders within specified TAT or not. and hence needs to calculate workdays. We have a column that tracks date created ({Date Created})for any order and another column that tracks last modified time to track how much time we took to finish the order ({Date Completed}). Hence the formula is : WORKDAY_DIFF({Date Created},{Date Completed}) But the output counts weekends too and the output is same as using DATETIME_DIFF. Please help me figure it out.