Formulas

tel:// works as a click to phone in shared view but sms:// formula doesn’t make a clickable link what am I missing

Hi there, This is my first time on this forum and I was wondering if anyone could help me with a slightly niche issue that I’m hoping is possible. At first I thought this might need the scripting block, but I’m wondering if this can be done with an extremely complex formula. I’m doing a database of musical pieces for a symphony orchestra. They have a lot of instruments so we use a shorthand. This shorthand always has the same number of characters where we simply list the number of instruments in score order. Here’s an example. 2222-4321-13-11-1 Translates to: 2 flutes, 2 oboes, 2 clarinets, 2 bassoons, 4 horns, 3 trumpets, 2 trombones, tuba, timpani, 3 percussion, piano, harp, and strings Other instruments we add on in the end in parenthesis i.e. (+contrabassoon, +eng horn). I developed it this way so that it might be easy for what I’d like to do, which is to translate the shorthand into longhand since the longhand form is what’s getting copy/pasted into our website. We can add extra i

I have created a field for the total number of employees in a company (e.g. 227) and company revenue (e.g. $897,000). When the specific number for value is entered for each field, I want it to correlate to another field for a range of employees (e.g. Under 100, 100-249, 250-499, etc.) or revenue range (e.g. Under $1m, $1m-$5m, $5m-$10m, etc.). For example, if I entered 227 for the number of employees it would automatically classify that company in the 100-249 range and if I entered $897,000 it would automatically classify the company in the Under $1m category. Any suggestions for the best way to do this?

Hi all! How to set up a Formula to calculate correctly even if some of the fields are empty. Thx! :m:

MAX(values) doesn’t work properly in the scenario where you rollup a date field which uses formatting. It’s no longer recognised as a date and can cause problems. It also looks like old field level formatting is stored when a datatype changes. I used an existing field that was formatted for currency and changed the formula to simply show a date field. My rollup in the parent record caused a major problem later on although the child field appeared as it should: I was locked out the parent table as it got stuck on the ‘Loading’ screen. To re-enable access to that table I had to change the formula in the child table. FYI. :slightly_smiling_face:

I am monitoring a project. It has a field for due date (DUE) and a dropdown select field for status (STATUS). I would like to create a formula that would alert me if the Due Date has passed but the task STATUS is anything except “complete”. I am trying this formula but its not working: IF(AND(IS_BEFORE({Due},TODAY(),STATUS!=complete),‘ :x: ’,‘ :white_check_mark: ’) What am I doing wrong? Please help a newbie. Thank you in advance!

Hi again! One more question from me for this base. I want to combine multiple IF statements from data in two columns. I’m working with the ‘points length’ field and the ‘total rating’ field. I’m attaching screenshots of what I’m talking about and the formulas I’ve tried, since it’s probably easier than me trying to explain. (I’m new to Airtable formulas and still not entirely sure what I’m doing!) In this screen shot you’ll see that the documentation status column doesn’t differentiate between ‘well-documented’ videos with perfect point length (40) and those with less. This is the formula I used to get the screenshot above: IF({Total rating} <=3, “Unsatisfactory”, IF({Total rating} <=7, “Passing”, IF({Total rating} <=9, “Well documented” ) ) ) Here’s what I would like to incorporate: ‘Well-documented’ status should only go to videos with points length of 40 and total rating of 8 (if the total rating is 8 and points length is less, like others you will see in the example, it

I need to populate a field with a value based on three conditions: Checkbox Field “Current Edits Complete” = 1 or Checkbox Field “No Changes Suggested” = 1 AND Checkbox Field “Content Implemented” = 0 So, if Edits are complete or No Changes Suggested are checked - BUT - Contentent Implemented must be empty, I want to bring back the {Editor copy}, otherwise I want the cell blank. Here is what I have built, but it doesn’t grab the records where No Changes Suggested is selected. IF({Current Edits Complete}=1,IF({Content Implemented}=1,"",IF(OR({No Changes Suggested}=1,{Current Edits Complete}=1),{Editor copy}))) Thoughts???

How can we make a judgment between each date in column {Directive status 1} cell and the date in Column {Calculated ETA}, and then return the “Overdue” if one judgment result is false? Or how do I get the smallest date in column {Directive status 1} cell.

I have developed a database which tracks members and instruments of a music club. Now I need to build a synced base for the treasurer to use which records membership subscriptions. I have synced the list of members into the first column and their contact details in other columns. To the right, I have one column per month to record subscriptions paid. The treasurer will insert a floor or amnesty date in a column, before which subs will not be charged. In a column after the Months, I want calculate the number of months owing after the floor. Struggling to get my head round it. Any ideas? I would be grateful for suggestions.I suspect I ned a spreadsheet for this really, but would love it all to work alongside the membership database in Airtable.

Had a good formula going for the status of the books in our shop… IF({Sales}, “ :green_circle: Sold”, “ :white_circle: For Sale”) But then I realized, we also “ :orange_heart: Donate” and “ :beer: Trash” books (we only trash them if they get damaged, say, in a flood, and the Microsoft garbage emoji is garbage, so beer it is!). Like the {Sales} field, the {Donations} field is a linked one, only, unlike {Sales}, in {Donations} it’s not enough to say EMPTY=For Sale and NOT EMPTY=Sold because there are three options in the Donations table {Beneficiary} single select field: Charity, Thrift, and Trash. What’s the formula for this? Do I need to create lookup fields first?

How would I go about adding a field to an existing table that already has 25,000 records and filling all 25,0000 cells in that new field with the same value, say, “Cookbooks?” If you’re curious, it’s because my dear old dad put both general subject location data in the same cell with specific location information (i.e., Cookbooks - garage wire rack, top shelf). And before you go suggesting that I use a formula which divvies up the data before and after the dash, I should say that he’s totally inconsistent in the way he orders this information and the way he separates it.

Hello community, I’m having a very very hard time finding out the perfect formula to activate my automation… :pleading_face: Here the thing : I offer a 14 days trial with my services and wanted to track this with airtable. Each time the status change, an email is sent to the client via automation. For exemple : New record :arrow_right: Confirmation email Service online :arrow_right: Validation email One week left :arrow_right: Reminder 14 days :arrow_right: Service is over - Email sent Service off :arrow_right: Upsell - Email sent The point of entrance is airtable form, but the service is live once the field “update URL” which is a last modified field is updated. Here’s the formula : IF({Interface supprimée}=1,“Interface supprimée :pensive: ”, IF(DATETIME_DIFF(TODAY(), {Update URL},‘days’) >=14, “C’est fini”, IF(DATETIME_DIFF(TODAY(),{Update URL},‘days’) >=7, “Plus qu’une semaine”, IF(DATETIME_DIFF(TODAY(),{Update URL},‘days’) >=1, “Phase test”, IF(DATETIME_DIFF(

Dear All, I have an initial inventory of 2500 books. My table is tracking for me when I made small shipments of these books, and because the field type is a # field, the sum is being tidily tallied. Is there a way to ask Airtable to subtract this sum from my original 2500 as I go? Thanks!

I have a table with multiple single select columns. Each single select column generates a numerical value, through a switch formula. I want to average the numerical values for each switch formula column. When I average the columns out now, I get back NaN. Here are examples of my switch formula columns: Is there a way to do this?

Hello everyone! I need your ideas :slightly_smiling_face: I am trying to invert a string (of numbers). I had this nice easy formula in excel, but I am not sure how to transform it to Airtable as it contain an array. exemple: A1: 123456 A2: =TEXTJOIN("",1,MID(I49,{10,9,8,7,6,5,4,3,2,1},1)) result in A2: 654321 Is it possible to convert or do I need a new method? Those functions seems to be available in Airtable, but nothing worked from all I tried. As I could not find similar case in the forum I am asking it here today so others can also get an a solution! Thank you very much for your kind clairvoyance :grinning_face_with_big_eyes:

I can sort of see my mistake but not sure how to come at it a different way to correct it. What i am trying to ask is “Is the job booked in date, within the previous 7 days of today ? Is the Job booked in Today? Is the Job booked in within the next 7 days from today?” What i have thus far: IF({Job Booked In} >= 7, "PREVIOUSLY", IF({Job Booked In} <= 7, "UPCOMING", IF({Job Booked In} = TODAY(), "TODAY" ))) “Upcoming” and “Today” both work but “Previously” does not. I’m guessing it has to do with it being a negative number i am not sure what i am looking for.

Hey everyone, I am trying to set up my upcoming family tasks and expenditures over the next couple month. One feature that would be nice, is a “remainung budget for month x” formular. I set the amount I am willing to spend on my tasks every month of the year e.g. 1000 USD max. (don’t know where and how) I prioritise my tasks ( single select - High, meduim, low) set an expected expenditure / how much is it going to cost me (Single select Currency) set the month I want to make that expence (single select - January to December) ----> How much do I spent each month??? What is my available money in month X? Airtable has to combine my max amount i am willing to spend, the month the money is planned to be spend and the expected amount, to give me my available budget for this particular month. This would help me to know if I can afford to spent some more money on tasks this month or if I have to rescedule this task to a diffentent month. I hope this was not too confusing… Thanks for your

I got an issue with these two characters… The issue is with: ’ and ’ When I copy a formula from AirTable into textpad on MacOS and start editing the formula there, the ’ character gets eventually somehow replaced with ’ and when I copy the formula back into AirTable, the formula is broken because some of the ’ got replaced with '. It’s strange. I don’t have any text-expander or text correction software so not sure why it happens. Wondering if anybody else has the problem. The issue doesn’t happen when editing the formula in Airtable box, it only happens when I copy the formula outside to edit it and then copy it back to Airtable. The editing box for formulas is way too tiny to be useful when editing long formulas. Interesting thing is also when I am editing the forum post here, the difference in characters shows when I am editing the title of the post here, but once I save the post, the characters look same in the title.

How can I implement such a formula in Airtable? // all fields in an Airtable table const allFields = [ { key: 'type', value: 'TypeOne', } { key: 'name', value: 'Some name', }, { key: 'description', value: '', }, ] // set in a field of the table const requiredFields = [ { type: 'TypeOne', requiredFields: ['name', 'description'], }, { type: 'TypeTwo', requiredFields: ['name'], }, ] let invalid = 0 for (let i in requiredFields) { if (!allFields[requiredFields[i]]) invalid += 1 } if (invalid) return '⚠️ Some fields are not filled out!' For now, I have to do something like: IF(type = 'TypeOne', IF(OR(name = 0, description = 0), '⚠️ Some fields are not filled out!')) & IF(type = 'TypeTwo', IF(OR(name = 0), '⚠️ Some fields are not filled out!')) But I would like to set just an array of required fields but I don’t know how to dynamically set a variable like at this point allFields[requiredFields[i]] (not a value of some pr

I am trying to convert between WEEKDAY(TODAY() and the word version for the day of the week. I would imagine it is a common need. This works just fine. IF(WEEKDAY(TODAY())=1,“Monday”, “”) Why might this not work? IF(WEEKDAY(TODAY()) = 1,”Monday”, IF(WEEKDAY(TODAY()) = 2,”Tuesday”, ”Wednesday”)) My error: Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.

Hi there, I just need a little help to finish my workflow ! I want to put a status from the automatic field “Last modified time”. Formula is : IF(DATETIME_DIFF({Update URL}, TODAY(),‘days’) >= 13, “ :exclamation: ”, IF(DATETIME_DIFF({Update URL}, TODAY(),‘days’) >= 7, " :alarm_clock: ", IF(DATETIME_DIFF({Update URL}, TODAY(),‘days’) >= 1, “ :repeat: ”, IF(IS_SAME(TODAY(), {Update URL}), “New :rocket: ”))))) It works pretty fine when the field is “date” but won’t work when field is modified in “Last modified time”. Anyone can help please ?

In Excel I would have Column A (Date), B (Time in Seconds), and C (Percentage Increase/Decrease). I created a formula that when I’d enter in Column B (Time in Seconds) starting at the row 3 (because row 1 is the header and row 2 is the first entry), C3 would then be populated with the percentage increase or decrease. Is it posible to do something like this in Airtable?

I have a link field called Sale and a formula field called Status. What I want is for Status to say “For Sale” when the Sale field is empty, and for Status to say “Sold” when the Sale field is not empty. I swear I do try to search for answers in docs and previous forum discussions before asking here, and I’m so grateful for those of you who come through. From a previous discussion, I used this formula which helped part of the way… IF({Sale},{Sale}, “For Sale”) I just don’t know how to add the “Sold”

When I enter this formula… IF({Book Type Entry},{Book Type Entry}, “Book”, IF({Book Type Entry},“Advance Reading Copy”,“Advance Reading Copy”)) …the formula field accepts it and all seems well until I go to add more to the formula and see it’s been trimmed down to just this… IF({Book Type Entry},{Book Type Entry}, “Book”) …and when I close the customize cell popup I see that the information it was supposed to render for the second part of the formula is also missing. How can I solve this problem?