Formulas

Hi Airtable Community,I am having an issue with a formula that I was hoping to get some help on. Here is the issue:We do our invoicing on Airtable. We have a few different types / categories of invoices - they are as follows:Quotes: This is what we use when we have to submit a price 1st before any approvals to begin have been granted.Suppleental: This is like a change order - we use this when the job has changed and or scope was added.Estimate: This is used  when we have submitted a price but it has not been approved - what we call "settled"Invoice: This is what we use when we have submitted an estimate and it has been approved. We have the primary field be a concatenation field that takes the name  of the customer, the type of work it is, and then looks to the "(MIT) Settled Date" field and if it is empty, uses the word "ESTIMATE" as the last word of the primary name. The thinking here is that when there is no date in

I got a table with DOB in date format dd/mm/yyyBest way to filter those records whose anniversaries (mm/dd) are within 5 days ahead?

Can someone help me figure out why the ‘else’ part of my IF statement isn’t working? I'm trying to create a friendly date/time/timezone field IF a certain date field is not blank. That part is working. But if the date field is blank, I want it to put “N/A” in the new field. That part is returning an error. Here is my formula.IF( {REQUESTED GO LIVE DATE} !=BLANK(),  DATETIME_FORMAT({REQUESTED GO LIVE DATE}, 'M/D/YY')

Hi,I have a table which is sort of a Bill Of Materials containing the all the required parts to assemble a single product, including the required quantity of each part. It's basically looks like:|Product|Part|QuantityRequired|There is another Parts table containing the cost of each part.I'm trying to calculate the total cost for each part in a single field. So far I was able to do it through two fields, one is lookup for the cost of the part and another field with formula {qty}*{cost from lookup field}My question is can this be done directly in a single formula field, without needing the extra aux lookup field to fetch the price of a single part? Feels a bit redundant and was wondering if there's a way to fetch that lookup directly through the formula. (The final calculation of of the product's cost is done in the Products table through a sum rollup on said result)Thanks.

Hi all, I’m a social media manager. I have a base which links together editorial content with associated social posts. It makes amazing things like this possible: Now, I’m trying to create a fully automated UTM parameter system which collects data from various linked records — such as linked web content, associated campaign, etc — and spits out a single URL with appended UTM parameters for tracking in Google Analytics. I’ve got most of it figured out. One step remains: how to extract part of a string. In this case, everything found AFTER the final “/” in a given field. Here’s what I mean. I’d like a formula which scans this text, finds the final “/” in the string, then spits out everything AFTER that:

Hello everyone, I have a data base with activities wher date and time are fulfilled but I would like to show only the hour in one column.I'm unable to display what i want. I have for example one column with ISO 14/12/2023 1:52 and i would like two different columns with 14/12/2023 and another one 13:52 Thank you for your help !

Hi, Seeking help here.. I need a formula in the Client name cell where in it will only show the client name if either of the Client or Brand columns has a name on it. Meaning if the Client cell has a name the Client name formula will only show "Path Social" and if the Brand name has a name on it "ThingTesting" it will only show in the Client name cell 

I have a script that has been working for over a year, but now will not update records.  I sync a table from another base to a second tab in main base.  I then use the following script I was given over a year ago (can't remember where I got it).  It has worked great to look at the synced tab to see a unique ID (emp #) and then pull that onto the main table and pull multiple records from the syned tab.  Now when I run it the script runs super fast and doesn't update anything even though I have matching Unique IDs.Here is the script I am using://Substitute "Orders" for table name which contains values//on which you want to run the vlookuplet mainTable = base.getTable("Covid LOA");

Hi, I'm pretty new to Airtable and currently just exploring if it will be viable for our company to use. The set up I have is two tables, one for learners and one for workshop sessions.  The sessions table has names etc and a field linked to the learners table that shows the attendees booked on the session (multiple select allowed). The learners table has a similar field linked to the sessions table showing the sessions that they are booked on to (again multiple selections allowed). What I would like to be able to do is have a new field on the sessions table that for example shows the attendees that did not show up (nothing fancy I would just use a linked field to the learners table and just input the learners manually, again multiple select allowed). But then I want a corresponding field on the learners table showing the names of sessions they have not attended, is there a way to populate this with the

Hello guys,I'm trying to create a formula for the main Name field on my table.I just can't do it, I'll be more than glad to write here the details and if someone can write the formula for this it will be great, and i will also learn that way how to create one in the future.My table is data from the computer inventory list in my office.I want the Name field to first start with the "User" field (the field type is "Link to another record" ) and after that it will have one space and then a Comma and then another space before the next field.Next I want the "Model" field (Single line text) and after that it will have one space and then a Comma and then another space before the next field.The last one is the field "Product ID" (field type

I think I may have gone too complex with a formula as my base won't open now. It's just stuck on "Loading" on every table. How can I reverse what I did?Note: I created a duplicate of the table and it did the same thing

Hi…I have a field with ‘arrival date’, a field with ‘terms’ (which is days prior to arrival payment is due. This is a number and varies from record to record)(Both are lookup configurations) and I want the third field to calculate the date it would be if the terms number (such as -3) is subtracted from the ‘arrival date’. I tried DATEADD({arrival date},terms,‘days’) but it didn’t work. I experimented with DATEADD({arrival date},-3,‘days’) and with the ‘terms’ look up field just being an integer instead of the look up and those did work so I am not sure how to do this. Thank you!!

Hello, In my base, one table has 2 records each. Currnetly, i do a lookup and it shows me both records. I only want the first to show up because it just doubles the name twice. How can I make it so that when i lookup from that table, only the first (or last) record shows vs having both.

Hi can you please help me return the value "qc" from this "inventory tags column"this formula doesn't seem to work. Thank you!

Is there any way to search for emoticons (any of them) in a text field, and replace with something like a dash or space? Is there a code for emoticon that allows a formula to search for this? Emojis and emoticons are often put into titles of blogs, but I am curating titles and creating url’s out of them, but want to remove any trace of these.

Hello guys! I’m new here and I’m still learning about Airtable, but I’m really enjoying it! I have some doubts about formulas, I would be very happy if they work for what I need, here are my doubts: 1 - Is there any way to add numbers in a column automatically every time you turn the month? For example: Column “parcels” on 12/22/2021 = 1 on 01/22/2022 = 2 and so on? This would be very useful because here in Brazil we make purchases in installments, so the number of installments, changing month by month alone, would make it much easier. 2 - Is there any way for the formulas to understand the “unique selection fields”? I would like to create a formula like: If the “unique selection field” has the option “green” the answer is 1, if it has the option “purple” the answer is also 1, but if it has the option "black " the answer is “1/2” Thanks in advance!

hey there,how would i go about creating a formula column where if a timestamp from a column (let's call that column "timestamp") is older than 24 hrs, then the resulting value would be "follow up", otherwise the resulting value would be "do not follow up" Thanks!!

We run a charity vege farm. I'm building a crop planning tool. For planning, a couple of pieces of key information about a crop are:-Number of weeks from Sowing in the nursery to Transplant (Weeks to transplant - WtT)-Number of weeks from Transplant to Harvest (Weeks to Harvest - WtH)I use this info to predict how long a crop will be in a part of the farm for, and planning to ensure we have the right amount to harvest at different times of the year.In summer, a crop like lettuce may have a WtT of 3 weeks and WtH of 6 weeks. In the middle of winter, that may be more like WtT of 5 and WtH of 10.There is a whole spectrum in between those dates!How would one write a formula for WtT and WtH to be adjusted according to the date of the year? There are input fields for "sowing" and "transplant" which would act as the date to adjust WtT and WtH for.

My table has four fields: Total Price, Total Paid, Amount Owed, and Payment Status. When the value of Total Price and Total Paid are the same, the value in the Amount Owed field, predictably, shows "$0" – however when I use the value of {Amount Owned} in a custom formula for the Payment Status field, it shows up as "7.275957614183426e-12" i.e. a near-zero value, but not zero. This is causing my formula to break, although I can work around it by checking if the value is "< 0.01" instead of "== 0". This appears to be a bug in AirTable, Total price is a manually entered currency value.Total Paid is a rollup field, summed from a different table using SUM(values)Amount Owned is a simple formula subtracting Total Paid from Total PriceAnd Payment Status is a formula that looks like this:IF({Total Price} > 0,  IF

Hello,I am very new to Airtable, and would love some advice or guidance bringing a few ideas to life. I am using Airtable for an applicant/candidate tracking system, interview scheduling, and job tracking for a recruitment firm.The current set-up is that we have a master database table that houses any and all candidates we've ever spoken to for any role, past or present, like a CRM. In the master database, I have a "Considered for" column, which shows which role(s) this individual has or is being considered for. Then we have a table for each active role.1. I want to create a formula/linked workflow so that if an applicant's "considered for" column is the same as the name of the open role of another table, they are automatically added to that table. Example: We are opening an Account Executive role and create a new table for it. If an applicant's "Considered for" column contains "sales" OR "account executive," or their skillset keywords include "sales," then they

Hi all -I have a table that acts as a timesheet for the same users to submit hours each week.I use the 'Hours Logged' from Fig. 1 (which is an entry for that week) and add it as a lookup value to my table with my people names (Fig. 2). There's multiple entries in the Hours Logged in Fig. 2 as it should, but when I go to my main table, it doesn't show the 'Hours Logged Sum' for that week. Instead, it shows it based on all entries. Is there a way so that I can get the 'Hours Logged Sum' to show what the sum would be for that week like a running sum for each individual? As an example, how would I show that the hours logged sum would be: Hours logged = 3.0; Hours Logged Sum = 3.0; Max Hours = 40; Hours available = 37Hours logged = 1.0; Hours Logged Sum = 4.0; Max Hours = 40; Hours available = 36Hours logged = 5.0; Hours Logged Sum = 9.0; Max Hours = 40; Hours available = 31Fig. 1

Hello!Currently I have a button column linked to a column with a webhook. When I press this button, the webhook is triggered and the row informations are exported to Make.I want to get in a column, the number of times the button has been triggered (+1 each time).Do you have an idea ?Cheers

I have a Date field (Date Asset Added) whose value is entered manually formatted like this:12/8/2023Another field (Date RM) contains this formula: DATEADD({Date Asset Added}, 0, 'day')  which outputs this: 12/8/2023.  12:00am I want to execute an automtion when these two values match, which they don't. So I tried using thiis: DATETIME_FORMAT(DATEADD({Date Asset Added}, 0, ‘days’), ‘M/D/YYYY’)) which outputs this: 2023-12-08T00:00:00+00:00  I think that DATETIME_FORMAT turns the date value into text. In any case, what form

Hello,Thanks in advance for any assistance with this.We have a ticket system whereby people submit by form an action and it's completed with a time stamp; with a goal of completing every request within 8 hours.Currently we determine if the time completed was within 8 hours of it being submitted and that generates a "1" in a formula column.  That column provides a percentage of the amount of 1's filled and gives us a service level.Recently I was asked to refine this solution to only consider submissions during business hours (Mon-Fri between 9am-6pm).This way, anything submitted outside of business hours doesn't count against the 8 hour service level until it's within spec.If something is submitted at 6:01pm, the timer freezes until 9am the next day given it's not a weekend.Alternately as long as something is submitted within business hours the timer does not stop even if it exceeds business hours or goes into a weekend.I'm

I have searched Airtable Help and Google and haven’t located a solution for the following issue: I have created simple formulas in Currency and Number fields. The results are always rounded up. I’ve tried different options (Integer, decimal places) but they made no difference. How do I get Airtable to show the correct result without rounding up? Thank you!