Formulas

I have records that are automatically created in my base. Some of them end in the phrase “(X of Y)” to indicate they are one of many. Others do not have this tag at all. I need help creating a formula that removes all instances of this parenthetical count. This would give me ability to group all records with identical names: Kelly’s Record (1 of 2) Kelly’s Recors (2 of 2) ----> all turn in to “Kelly’s Record” However, I need the formula to leave unique records unaffected: Kelly’s New Record —> Kelly’s New Record

I’m currently building two different pipelines, one for sales and one for recruiting. Both require a way to measure the pass-through rates from one status to the next status. Ideally, this formula would reflect the % of the pipeline that changes from one status to the next status. Any help or ideas on how to show this % change?

Hey guys, I’m struggling to get my if condition to return a value which is formatted as a currency. Formula as below IF(Client=‘Ebay’,{Qty/Hr}*{Qty/Hr Price}*0.029+0.30,’’) This is returning the value as a number with decimals, but refusing to allow me to format as currency Formatting error: Is anyone able to assist with this, thanks in advance.

Guys you are always so amazing, I have tried everything I can think of I have a table with rows that represent different branches and then there’s 15 columns representing a variety of different areas all with options red, amber, green - i would really like to have a colum that counts how many “red” responses there are, how many amber etc but I cannot work out how to do that - any ideas Thanks in advance

Hi everybody. My question is simple… (hopefully the solution also) I need a formula who automaticly tells me what date it is next Tuesday. On Wednesday it needs to update itself to next Tuesday. Thanks in advance!

Hi everyone, Im trying to put together a payroll base for my small start-up but Im finding it quite challenging dealing with the income tax calculation bit which requires the use of a progressive tax bracket system. The income tax bracket system has the following rules - Chargeable Income Rate (%) First 319.00 0.0% Next 100 5.0% Next 120 10.0% Next 3,000 17.5% Next 16,461 25% Exceeding 20,000 30% Since there isnt any vlookup in Airtable like in Excel making an if statement is impossible. I am pretty sure there is a simpler way to go about it but I just dont have the insight yet. Any help is totally appreciated.

Hello, I’ve created a table in my base called “Email Templates”. It’s a simple 2 column table. First column: Template Name, 2nd column: URL Formula. In the email template column, I’ve selected a long text field. And for each row, I’m creating test URL email formulas. Here’s an example of one of the formulas: "mailto: [lindsey@realtimesavers.com](mailto:lindsey@realtimesavers.com)"* & "?subject=" & ({Property Address}&" | Test 1")* & "&body=" & ("Good day Selling Team!* %21%0D%0A%0D%0ACongratulations on satisfying the home inspection contingency. We’re now one step closer to helping both of our clients meet their goals. Thank you for all that you’ve done to get us to this point!* %21%0D%0A%0D%0AQuestion for the lender: I understand many lenders wait to order the appraisal until after the home inspection has been satisfied. Now that we’ve checked that off, can you please reply-all with a timeframe for the order?* %21%0D%0A%0D%0AIf there is anything that I (or a

I have 2 fields that are linked to other fields. I want to combine the 2 fields in a 3rd field and keep the 3rd field still linked. If I do a simple {Field 1} & {Field 2} in a formula field, it makes the linked records into stringed text. How can I keep the resulting field still linked? Thanks!

I’m using a formula to populate the PrimaryKey field which looks like YEAR(Date_form_submitted) &"/"& MONTH(Date_form_submitted) &"/"& {department_code_lookup (from Departments_Lookup)}&"/"& rolling_id It works perfectly without any issues. What I want to do is add this generate value in a form. So for example, someone is submitting a new form and prior to submission I want to let them know what the PrimaryKey value (labeled as URN) is. Equally is there a way that I can send an email on submission that includes the Primarykey (URN) value to the submitter? Regards Simon

Hello. I suck at formulas and need some help. I have 4 single select fields in which I set the status of different documents I collect from clients (not sent, sent, received, not needed). I then have a “document status” formula field that queries the status of all 4 single selects. I want the document status formula to display “1” if all fields indicate “received” OR “not needed”. It should return a “0” if any other combination is shown. The purpose of this is to allow me to use a gallery view that filters for “document status = 0” and show me only the clients that owe me documents. Can anyone please help me with this?

I would like users to be able to select an individual name when filling a form. I created a calculated field with the Last and First Names. But it seems I cannot add a calculated field to a form. How else can I do this? Just to clarify. If I have calculated two entries “Smith, John” and “Smith, Peter” how can I list them in the form?

Looking for a suggestion on how to best achieve the following: I have an order date, say “4th July 2018” and i have 3 workdays in which to complete the order. I’d like the formula to say “3 days left” or “2 days left” depending on today’s date. WORKDAY({Order Received},3) will give me the due date. But how to calculate the number of days left until we hit the due date?

Basically would like to do Lookup, {Products ordered} Double Scoop (Chocolate + Vanilla), Single Scoop (Chocolate), Triple Scoop (Chocolate, Vanilla, Mint) Formula field {Flavour categorizing} Chocolate, Vanilla, Chocolate, Chocolate, Vanilla, Mint The issue is that, the Lookup field can be different products at different arrangements, but would love to have the formula field to be able to SEARCH a certain product and state its flavour of choice. like the example above! Any help will be much appreciated!

This seems like it shouldn’t be that hard… SWITCH({Content Type}, Long-read, “$100 USD”, Standard-length, “$25 USD”, Photo, “$25 USD”, Video, “$40 USD”, Cartoon, “$25 USD”, )

I have a rollup field that uses ARRAYJOIN() to produce string values separated by commas. Next, I want to be able to count all instances of a particular string.For Example, the field I want to count contains: BT,DT,BT, DT. I want to count only the “DT.” Any suggestions on what formulas are able to do this? I would use a conditional rollup, but it doesn’t feature any “IF” statements.

I’ve found it extremely useful to have records with autonumber and “Title” field, while setting Name to something like "tn"&ID&" "&Title (where tn is an abbreviation for table name). Very helpful to distinguish events with similar agendas, etc. However, this means I no longer can auto-fill the name when creating records as a linked record field, from calendar view etc. and each time have to expand the record and fill it second time. Can I somehow fill a different field when creating the record?

Hi community! I am building a job site with Webflow and using Airtable as the database. Note: Job site not job board, which means the candidates will apply on the same site, instead of linking them to the company’s “jobs” page, as Job boards do. With the tutorials I saw online, including the one by @Aron, the “Apply Now” button links to an external site. With my Job site, I’m linking it to an Application form, where I need to prefill 2 things: The Company and the Position. I currently have a “Job Posts” table, a “Candidates” table, and a “Apply now” form/table. Every Job Post has an associated “Apply Now” URL which can be customized to have either the company name or the position prefilled (whichever is the primary field). The “Apply Now” form links to the “Job Posts” table and picks the primary field. I need both: the company name and the position to be prefilled into the form. How do I do this?

As you see in my lookup field, certain names (Mickey, Annie, Codi) are listed twice, since they’re responsible for multiple steps within one objective. How can I either create another field or change that lookup field so it’s displaying unique step owners (associated with steps). Thank you!

Hey team! I want to be able to show my “Current Step in Progress”, based on the information in my “Steps” table. How it should look: If there is an actual completion date, the formula should look at the next upcoming step, based on the date found in the projected completion date. In this case, the “Current Step in Progress” for the Merchandising Acceleration Program should be S2, since S1 has an actual completion date. Thanks in advance!

I have a formula where this year’s anniversary is based on the date of Marriage but it returns an ERROR if the marriage date is blank and I would like it show as a blank instead. Here is my old formula. DATETIME_PARSE( DATETIME_FORMAT( {Marriage Date}, ‘MMMM D’ )& ’ '& YEAR( TODAY() ), ‘MMM D YYYY’ ) II have tried using an IF statement and it does work for the blank marriage dates but for some reason, the ones with Marriage dates are no longer formatted as MMM/D/YYYY and instead show 2020-03-31T00:00:00.000Z. . Here is my new Formula with the wrong formatting: IF({Marriage Date},DATETIME_PARSE( DATETIME_FORMAT( {Marriage Date}, ‘MMMM D’ )& ’ '& YEAR( TODAY() ), ‘MMM D YYYY’ ),"") I can’t figure out where to move the date formatting so it shows up as 3/31/2020 Any help would be appreciated.

Hello all! I’m trying to create a formula that will extract all text from a string that comes after the last comma in that string. For example: From the string “Fashion sketch from A. Beller & Co. of a Lelong Cape, Blouse, and Skirt, circa 1926” I need to extract “circa 1926” I’m struggling to find a way to point a formula to that last comma and space in the string. Thanks!

Hey there! I have a Status Report table built to allow for all updates about each step to be created. In the Steps table, I need to create a field / formula that says "if the most recent update for each step is more than 2 weeks from today’s date (in the past), change status to “NEEDS UPDATE”. The “Formula” field in Status reports shows “true” if it’s the most recent update for the step, which is the row I’d want to reference as my most recent update. Thanks in advance for your help!

Hello! In my Objectives tab, I want to display the “Upcoming Step Due Date”, which should be calculated by using the most recent date between the Revised Completion date and Projected completion date, found in the Steps Table. If the Revised Completion date is empty (row 4), it should default to the Projected completion date. I’ll then want to determine how many weeks until that object is due. Can you help me determine 1) how to get the most recent date and 2) how to create a formula for the weeks until that date? THANK YOU!

I want to create an IF formula so that if a a calculation > 0, then the calculated positive % + “ :white_check_mark: ” should populate, but if the calculated formula is < 0, then the calculated negative % + “ :stop_sign: ” should populate. When I do write this formula, the correct number and emoji shows up, but the calculated value has many decimal places. How can I change the formula so that no decimal places will appear (e.g. 35%)? Formula: IF( (1-SUM({Apr 2020 Staffing 1},{Apr 2020 Staffing 2},{Apr 2020 Staffing 3}))>0, “ :white_check_mark: ” & " " & (1-SUM({Apr 2020 Staffing 1},{Apr 2020 Staffing 2},{Apr 2020 Staffing 3}))*100 & “%”, “ :stop_sign: ” & " " & (1-SUM({Apr 2020 Staffing 1},{Apr 2020 Staffing 2},{Apr 2020 Staffing 3}))*100 & “%”)

Hi There, I’m having a hard time figuring out how to put a value when a subscription occurs between to date : I’ve tried this :arrow_down: IF( {Date} < ‘30/09/2020’, “TPP Test phase”, IF( {Date} < ‘31/12/2020’, “TPP OND”, IF({Date} < ‘31/03/2021’, “TPP JFM”, IF({Date} < ‘30/06/2021’, “TPP AMJ” ) ) ) ) But it won’t work at all. Someone could help please ?