Formulas

I am looking to score my leads in my Airtable which I use as a CRM. I have a multi select field called 'Journey' which has a number of events in the lead life cycle - for example an IOI (indicator of interest) of the prospect asking about the price, or the timelines - what I would like to do is rollup those Journey steps which are in the 'Touchpoints' table and have a lookup field for the Contact into a Contact rollup field and calculate the lead score which would involve assigning unique values to the Journey steps. For example an IOI - Pricing might be 5 points, an IOI - Timelines - might be 7 points (as an example) - adding all those points together and having a lead score. Hope that makes sense. Any input of how to make a formula like this would be much appreciated. Video below for reference also:https://share.getcloudapp.com/yAuAwJdW

Hello community!I have a column with 3 simple choices "number of trucks" and I wants to convert these choices into figures in a "quantity" column. IF({number of trucks} = "3 trucks of more", "0", )IF({number of trucks} = "1 truck (24 tons)", "24",)IF({number of trucks} = "2 trucks (48 tons)", "48",)I can't close my formula, do you have an idea?

Currently, the yyyy-mm that were paid are stored in a lookup field named Month Paid For.For instance, we only paid the vendor until 2022-11 even though we were supposed to pay them as of 2023-2. How do I obtain the months that have not yet been paid? ( 2022-12 , 2023-1 , 2023-1). Thanks !

Hello!Here is what I am trying to create:A formula that results in telling me if the same collaborator's scheduled leave is going to conjunct with another collaborator in the same team.This is what I am working with:leave start dateleave end dateTeam Slack ID which is a lookup field directly connected to the collaborator selection eg. if Mary JONES selects her name, the Slack ID for her teams HR and Marketing will show.I am trying to build a formula that will:be able to identify conjunction between leave periods.search if this conjunction is happening between collaborators who work in the same team.Has anyone some ideas to share?

Hello all, I have linked a SignUp Genius signup to populate a student table in my Airtable database automatically.  I have 3 custom questions, and Signup Genius exports those ALL in one custom questions field separated by commas.  I need to separate those responses into 3 separate fields in my base and cannot figure out how to set that up. One of the fields is a linked field to another table in my base. I'm assuming that I have to import that into one field in my table, then set up a formula in a different field that separates the comma-separated values.    I have no clue how to do this. 

Hope you guys can help! I have a list of projects ranked 1-28. If a new project is entered and the rank is set at #8, I would like all the following numbers to be adjusted. (i.e. original #8 becomes #9, original #9 becomes #10...).  Is there a clean way to accomplish this? Field type is "number" currently. Hoping to find a formula but that might not work.  

Hi all, Semi-new user to the tool here, and still learning the formulas. I have the following issue: I am using the switch function to categorize certain single select options into different categories (in this case, which statuses in the HR systems relate to an overall designation of accepted, dispositioned, interviewing, etc)My full formula is below. The issue is the very last line item: 'Doesn't Meet Min Qual', 'Dispositioned'This line has the apostrophe in the word "Doesn't", and that seems to be invalidating the formula. Everything worked great until I added that line, and then it breaks. I unfortunately can't change the wording of the option, since it comes directly from our system. Is there any way to get the formula to work, and ignore that specific apostrophe? SWITCH({Current HR Status}, 'Prepare Offer - Pro', 'Accept', 'Send Offer for Approval - Pro', 'Accept','Hold&n

I have three training sessions, and participants need to have completed all three in order to proceed. I'd like to have a formula that shows 'completed' when all three = completed and 'incomplete' when none/not all are complete.Thank you!

I want to link my URL column with my title column so that the Title is linked to the URL. Instead of having the URL, I have the Title but it is link to the URL, creating an hypertext link. 

I'm looking to auto-populate some date fields. Right now, you add the date and the day of the week auto populates but I'd like to be able to enter 2/14/23 in the "EST Release Date" field and have the rest of the dates in the column auto-populate. Is this possible.I'd also be copying and pasting multiple sections into one grid so each chunk would have to be independent of another chunk...if that makes any sense. I know how to figure this out in excel but I'm hoping this is possible in airtable, too! Thanks!   

I'm looking for a workaround to populate the distance between two zip codes. I need to identify which office is closer for each employee. I have input all the zipcode coordinates with a linking structure in the base. https://airtable.com/shrwDRIKVyoBphDBk/tblNsf1iP5agssIZS/viw5teuUhoedEPQA6?blocks=hide The excel formulas I would use don't work in Airtable. See the formulas in the base under the "Miles to office ONE" and "Miles to office TWO"I'm a beginner when it comes to formulas in Airtable so I'm hoping someone can help. I don't have any experience with JavaScript or coding. 

I'm puzzled by an error I am coming up against ... We pay our service providers' invoices (received by Monday) ten working days later (so, typically two weeks later). Using this formula, I get the result I want for a date of Monday 20/2/2023 being Monday 6/3/2023:  WORKDAY({Send your invoice by}, 10, '01-01-2023, 02-01-2023, 07-04-2023, 10-04-2023, 01-05-2023, 08-05-2023')  When I add the next public holiday date into the formula, I get an ERROR: WORKDAY({Send your invoice by}, 10, '01-01-2023, 02-01-2023, 07-04-2023, 10-04-2023, 01-05-2023, 08-05-2023, 29-05-2023')​ I don't understand why adding that date breaks the formula, can anyone advise please?

In jira we have an Acceptance Criteria field with LOTS of text and we sync this to airtable because airtable offers more fields necessary for the work we do.I want to be able to create a formula that finds and returns the free form text after a specific string of text in the long text field.For example from the long text example further below I want the formula to SEARCH the {Acceptance Criteria} field for  “Creative_Type: " and return the text after it (maybe use “RIGHT”?) but before a hard return and the result in this case would be SIG I’ve created rules where the options are a static list (for example “Email Type: “can only have a certain selection so my formulas account for that but when the text is free from I just want that field to populate with the free form text that shows after “Creative_Type: “  I assume it would have to be a RIGHT and calculating the length or isolating anything after the "Creative_Type: and before the hard return or do i have to create a combo f

I have a list of unique ID+count information in a rollup field. See sample data below.16389-0*4, 70895-0*2, 53429-0*2, .....Everything before the * is an ID, and the integer following the * is a count for that ID. I would like to strip everything prior to the star, leaving:4,2,2,....Then I'd like to sum those values.Is that possible?

How to create formula that filter the rows by last month?The filter function doesn't allow a date range (which is strange). e.g. If I want to filter rows with dates fall within February 2023, how do I do that? Thanks a lot!

I have column A with the breakdown option and have columns B and C, what is the formula if someone chooses A1,A2 it will link to column B and if choose A3,A4 it will link with column C?

hello, I need help, I have my airtable automate with make for my bnb reservations. my lock generates a temporary code then add it in airtable. msis if the code starts with 0 , the 0 disappears this is very problematic for customers. how to do ? the code always has 10 numbers 

Good day team,  I would like to ask let's say for example I have these email addresses and I want to use a formula to get "ABC" to "ABC Company Philippines." What would be the correct formula?For your reference, this is the formula that I am using right now. IF(RIGHT({Email},LEN({Email})-FIND(".",{Email},FIND("@",{Email})))="abc", "abc company Philippines",IF(RIGHT({Email},LEN({Email})-FIND(".",{Email},FIND("@",{Email})))="bcd","bcd",IF(RIGHT({Email},LEN({Email})-FIND(".",{Email},FIND("@",{Email})))="cde","cde", RIGHT({Email},LEN({Email})-FIND(".",{Email},FIND("@",{Email}))))))

I am trying to use the new list view to create a system for putting together quotes for events. I have a list of event enquiries in table 1, and in table 2, have created a list view, where I am adding line items to events using levels. I would like to have an auto calculated field in the event enquiry item that gives me the total cost for all the line items I've allocated to that event. I've attached a screenshot of an example. I assume the "Total event cost" field needs to be a formula - can anyone help with a formula that will do this calculation?

Hello!  I have text of the form:10 x 8 inches (image)25.4 x 20.3 cm16 1/8 x 13 5/8 inches (frame)41 x 34.6 cmin a field called Image Size.  This is how the text came in from an Excel spreadsheet.  There did not seem to be a Delimiter I could successfully use to separate the text previous to my importing it to Airtable.  I want to take the string on the line that precedes "(frame)" and that follows "(frame)" (basically the inch and cm dimensions of the frame as opposed to the image size) and extract it to a new field (the formula field), while, hopefully also deleting it and the cm dimensions following "(frame)" on the next line from the original Image Size field.  Sometimes "(frame)" shows as  "(frame size)".  Thank you very much in advance for any assistance!

Hello Everyone,Is there a formula for multiple values I set to equal "Success"? ( put several values like "1234" which would be equal to "Success") please thank you in advance,Best regards,

Hello, I would like create a table with summary results of all the base. For exemple, for a base with different tables like : 1 - Suppliers2 - Products3 - Customers 4 - SalesI would like have in a table (to use in a app built with softr) global datas like : - the number of records in the supplier's table or of customers- the average turnover - the total average ...How can i do that ? Thanks 😃 (I know it is possible with blocks "extensions" but it is not possible to have datas blocks in softr ... ) 

I'm pretty sure I'm over thinking this process so I apologize in advance if this is obvious to most.I have a field in Table 1 which when data is entered, I'd like that data to automatically create a new record in the primary column of Table 2.Table 1 is my ideas bank and when I have created a  "Working Title" (field name in Table 1) for that idea, I'd like the title to be automatically added to Primary Column in Table 2, thus creating a new record.Really hope this makes sense.Any help appreciated.

Hello, maybe this is not even possible, but if it is, what would be the proper syntax for this ?IF(VALUE({Total Rate 6})) >0, VALUE({Total Rate 6}), Blank() This field is showing the data of another column called "Total Rate 6", but i want it to be blank if the amount is 0(zero)

Hi everyone, I'm trying to get this formular to work, and although it parses there's no output.   ARRAYJOIN(ARRAYCOMPACT(Field1,Field2,Field3,Field4), ", ")  The fields are a mix of and link-to-another-record fields lookups (of link-to-another-record fields) where the outputs all are fine. The reason why I'm using an ARRAYCOMPACT as well is because half of the fields are usually empty.Thank you very much ❤️