Formulas

My data record structure{    "id": "rec6CtSsmlVJe48Uj",    "createdTime": "2022-12-07T11:55:53.000Z",    "fields": {        "VOTES": 5,        "ID": 2,        "CONTENT": "....",        "AUTHOR": {            "id": "...",            "email": "test@",            "name": "test"        }    }}How can I use AUTHOR.id I wan filter by ID and AUTHOR.id in one queryID=1, ID=2 and AUTHOR.id=5How can i write it?

Hi there!I'm building a booking system for a Go Kart hire company. I need to be able to detect if a new booking I am making will clash with other bookings.I have a date field "Date" and multi-select "Race Time" field (with races or selections every 10 mins starting at 9:00am up until 9:00pm). It needs to be multi-select to avoid mis-spellings.I would need an automation or formula that lets me know if any bookings are clashing based on both the Date and Race Times. I have also considered Make.com (Integromat) but I can't quite get the Airtable functions right it seems.It would also need to be able to indicate which record the booking is clashing with.Is there any way to do this?

Hello everyone! I've made this Table: The idea with the table is to show different kind of tasks, sorted by Week and Day. Right now when a task is completed, I will click on the 'Næste gang?' field and choose whether the task should be listed in the daily or weekly view. If it's a daily task, I will choose 'Daglig' and for weekly tasks I will choose 'Ugentlig'. However when I do that, this happens: Instead of the task being re-created in the right view, it's being added in an Empty view. I therefore manually have to add it to the right view by clicking in the 'Hyppighed' field and select either Weekly or Daily. What I want here is the task to automatically be added to the right view. Also I really want to just be able to click in a check box when the task has been made and then Airtable knows which view the task belongs to instead of have to choose the right one in 'Næste gang?'Are the 2 above improvements possible to do? 

Hi, I'm trying to do an inner join (find elements in common between 2 columns).How can I do that here?I saw the functions Concatenate and Arrayjoin, but I didn't find anything about Inner join, maybe I'm looking in the wrong place. Is it possible to do it? Thank you😊 

My end goal is to calculate the Flesch–Kincaid readability of a given text field ("Copy (Required)"). In order to do that, I need to determine how many syllables are in that text.Syllabification is somewhat of a subjective thing, but the approach that I've chosen is to use a series of nested REGEX_REPLACE functions inside a formula field based on this approach from Guilherme D. Garcia. The resulting formula looks like this: REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( {Copy (Required)}, "([aeiou])", "\\1-" ), "-$", "" ), "-([bcdfghjklmnpqrstvxz]$)", "\\1" ), "-(n|r|st)(t|n|d|f)", "\\1-\\2" ), "([aeiou])-s([tpnml])", "\\1s-\\2" )  I know that Airtable’s REGEX functions are implemented using the RE2 regular expression library but I can't seem to get the syntax correct for the "replacement" string. For refe

I have a zap that pushes 3 cells with comma-separated values, the first one being the part or service names, for example [trip, labour] the second cell is the quantities [1, 3.5] and the third one the prices [60, 120] to our invoices table.How can I extract that information and create a record in another table (line items) every time  a new record is added to the Invoices table.

I have this relational feature of 2 tables:Table 1 (material) and Table 2 (material attributes)Now, Material Table has one or many material attributes (Table 2).I've been stuck with this feature where I can filter all the records belongs to Material ID (1234567)I checked the datatype of the reference ID and its an array. Now how can I filter all the material attributes that belongs to the specific Material?I tested all the helper functions of FilterByFormula and the response is not correct. It returns a mixture of IDs that does not belong to the given material ID.Thanks for the help!!

when does the date actually change when using today() function? i would think midnight est for my acct but it seems to be around 7pm of the previous day as evidence by the email I received from my automation/workflow...thoughts here?

Scenario:CONCATENATE(Town, "-", Address, "-", {No / unit})displays for example Town-Address-100Question:: I would like the display to appear as Towm Address 100.  How do I enter a blank space between the field items rather than a -  ?tia  

I run a digital marketing company and I'm trying to have an element in an interface that reports the Average Cost Per Lead. For this I need to divide my total amount spend on paid ads with the amount of leads generated. Is there a way to do this?

I am building a savings calculator, and I'm using Airtable to store a Rate Table, and also to do the formula based calculations needed for the project. Here's what I want to do: Have a formula that takes value x  and checks if it is in a range of a and b in a specific row. Once it identifies what row, and therefore what range it is in, it needs to show/equal value y. Here is what I have so far: (But it is giving me an error)SWITCH( {Inputed debit swipe}, >= 10000, <= 20000, 2.75/100 >= 20001, <= 30000, 2.75/100 >= 30001, <= 40000, 2.75/100 >= 40001, <= 50000, 2.75/100 >= 50001, <= 60000, 2.75/100 >= 60001, <= 70000, 2.75/100 >= 70001, <= 80000, 2.75/100 >= 80001, <= 90000, 2.75/100 >= 90001, <= 150000, 2.75/100 >= 150001, <= 250000, 2.75/100 >= 250001, <= 500000, 2.75/100 >= 500001, <= 750000, 2.75/100 >= 750001, <= 1000000, 2.75/100

I have a lookup field called “Jobs” that returns fields from a linked record. In my case these are job descriptions. It shows them as a comma separated list, e.g. “CEO, director, manager…”. I would like to convert the commas into line-breaks with a formula. But SUBSTITUTE(Jobs,", “,”\n") returns an #ERROR! What am I doing wrong?

Hi! I'm trying to create a field of active/inactive based on a date range. I already have a field each for start date and end date, but can't figure out how to create the formula for the active field based on those dates. Any ideas?Thanks! Sondra.

Hi, I'd like to sum up the values from the "Weitghted results" column, when "Technology"=Social Media, or when "Technology"=BlockchainI have made some researches but can't find to get it correctly. I've used this formula but it doesn't work: INT(IF({Technology (from 🇺🇸 Questions)}="Social Media", SUM({🇺🇸 Questions copy},"")))Would you please be able to help me?Many thanks

Hello everyone! I have this table:The issue right now is that when I in 'Næste gang?' selects an option, fx. 'Daily' or 'Weekly', the Record will update and get a new deadline based on my choise (daily or weekly). However the Record also looses all it's data, fx. Assignee and the 'How often' all the way to the right (let me show you):  How do I change this, so that the Record will appear in the right Group when updated? 

Hi guys, im trying to do an automation that performs the following: “If Salesperson A’s monthly revenue is below the target, then do X”  However, I can’t find a way to automatically reflect the revenue of the month based on the month I’m in. I can do Rollup to generate the total revenue,  but not revenue for that specific month. How can I solve this? 

For folks that write their formulas in VSC (or Sublime Text or any other code editor), what syntax auto-coloring do y'all use? There's nothing specific to Airtable's syntax, so I'm wondering what folks have been to be most pleasing and/or function the best? 

Hello,Is it possible to enable the URL from a long text field with rich text enabled (in other words, this field type) to be clicked on in another formula field? In other words, the long text field I have has text within the field linking out to URLs. When that long text field is included in another formula field (for instance, as a concatenation linking the long text field with other fields), the URLs disappear. Is there any way to enable the URLs to be included and clickable in that separate formula field?Thanks so much!

I have a sub-task field (long text with rich text format- check box format) I wanna Insert a field called “Processing” to calculate the % of sub task I have done (checked), which formula should i use for that ?

Hi there. I have a table that has all of our customers and their start date. When they register to an event, it goes into a different table and these are linked to the customers table. I can count how many events they have registered to without a problem, however i'm finding an issue as:I want to calculate how many events they have registered to after their start date (note they can register to events before they start), and I can't seem to find a workaround.  

Hi,Who's gonna help to :- concatenate Row 1+2+3 with a limitation of 3 character per row and a total character of 16 ?I need to create unique reference (in e-commerce) called Variant SKU looking likeModele, Size, Color but as we are limited in number of characters (16) it would look like#MOD-SI-COLIn this view : Variant SKU will be a formula including#SAM (from marque (brand in french)-modele-vendortaking only the 3 first characters thanks guys 🍭

Hi! I am trying to solve the following:I have a lookup field that lists multiple dates multiples times (the same day in each respective record). Like so: 10/10/2022, 10/10/2022, 10/10/2022, 10/10/2022. This is the "Move In Date ~ Expansion" Field. I only want one date listed and clean like so "MM-DD-YYYY". So I created a Formula Field ("Move In Date ~ Expansion (Calculation)" Field and did an ARRAYUNIQUE formula, however, it shows up with additional 0's and is not clean. How can I adjust this formula so that it lists the date of the lookup field only once and clean like so"MM-DD-YYYY"? Or does anyone have any other solve here? Thank you!

I am new to Airtable, but I cannot find a simple formula to calculate years of service for employees from the start date. I can get it to calculate years, but would like to see it in decimal format. For 8 1/2 years I would like to see 8.50. I am currently using the below...Adding the "months", did not do anything different. DATETIME_DIFF(TODAY(),{Start date},'years', "months") Any help is appreciated... 

Hi,I need to supply to my team of buyers the "best deal" for any given category and sub-category of products in our database. At first, it was easy to group by category / sub-category into a view sorted by price ASC. But over time, the database grew big and we now have 7000+ products splitted into 25 categories and 50 sub-categories ...that makes it long and tedious to scroll through this view to locate manually the cheapest product for each category/sub-category combo (about 150 unique combinations in total).My question: is there a way to get a view that'd output the best product per category / sub-category combo in a flat table format. In other words, this view would contain ONLY the 150 best deals.Many thanks!

Hello, I am looking for a solution to having dynamic conditions for fields. Example: I have a table with attendees and another table with events. I want a count of how many events an attendee has gone to (which is created via a linked field in the “events” table, to the “Attendees” table). However, I only want a count of how many events they’ve been to AFTER a certain date. That date is dynamic and unique to each participant and lives in the “Attendee” table. How can I filter the “count” field so that I can only see events that they have attended, but only after a certain unique date? The current filters in fields are static, meaning any value applied in the field applies to ALL records equally. Thanks so much for your help!