Formulas

Hi, Wondered if anyone could help with this…I have the following fieldsTitle, Sub Title, Series Title and Series OrderThen I have a full title field where I want to bring those together depending on which of those is filled in. So, if there’s a Sub Title, I want the Full title to show {Title}: {Sub Title}. If there is a series title then I want it to show {Title}: {Series Title} {Series Order} (as an aside, if I could get the series order in brackets that would be amazing, but I suspect impossible because brackets are part of the formula). And if neither Sub Title nor Series Title have an entry then I just want the {Title}. I don’t think there’ll ever be a case where we have a series title and a sub title so I’m not worried about that for now. At the moment I have:IF(  {Series Title}, {Title} & ": " & {Series Title} & " " & {Series Order},     IF(      {Sub Title}, {Title} & ": " & {Sub Title},      IF({Series Title} = BLANK(), AND({Sub Title} = BLANK(), {Title}

Hi I would like to translate this formula to function in an airtable workspace, please help me.=IF(G2="Yes", SUMIFS(F:F, A:A, ">="&NOW(), A:A, "<"&A2, D:D, "Yes"), "-")

Scenario Field | Type | Value-------------------------------Amount | number | 100VAT rate | Single select | 13.5VAT amount | formula | {Amount (Net)} * VALUE(SUBSTITUTE({VAT Rate}, "%", "")) / 100 Interesting to see that the VAT Amount value is 14????It should be 13.5, well, mathematically speaking it has to be 13.5 With VAT rate as number, it works as it should be.However the idea is to use single select to reduce human error/workload. Is it possible to get VAT Rate as single select and VAT Amount working together correctly?Any ideas out there? I just ran out of them. Thanks

I am new-ish to Airtable. I am having an issue with creating a formula that give the correct output for contribution points acquired. I have a Total contribution points table, a points log table, And the master table with the contributors. I think I have the pieces in place but the category based formula is giving those with 1 point 100% and those with 2, 50% I am wondering if this is because along with a total count of all the points across all categories do I need to also collect the total points on another table of each individual category count? The current category count, counts only how many points a contributor has in that category. Secondarily, there is a weight to each category sor CAT 1 is 40%, CAT 2 is 40%, CAT 3 is 10%, CAT 4 is 10%. I hope this makes sense.

So far, I haven’t had any trouble entering text and currency into Airtable bases, but I’d like to calculate simple overall percentages from my data. I’ve looked everywhere and can’t figure out how to do it in Airtable.I know how to do it at the row level, but I don’t know how to calculate it for the total data. For example, I have values in Column 1 and Column 2, and I need to sum all the data from Column 1 and all the data from Column 2, then divide them to get the overall percentage. Im trying to do this in the interface view too, but its imposible.I’m starting to think that it’s not possible to calculate simple total percentages in Airtable. Or am I missing something?Thanks very much in advance for any help with this.

I’m having an issue with a formula that rounds up incorrectly, i’ve browsed the forums and can’t seem to find a solution.I have a simple math equation x * y = zI suspect that the issues is stemming from a formula that contains four decimal points:I’m not able to round up this number, however, it needs to remain as is.  I’m not sure how to get a correct calculation using airtable. 

Hi - I have two fields, both text:Form Title (same for every record) Form URL (long-#$@$, customized URLs for prefilled forms; different for every record)Is there a way to make the title of the form clickable with the URL - possibly by creating a third formula field? Chat GPT tells me to do: HYPERLINK({Form URL}, {Form Title}). Alas, HYPERLINK doesn’t seem to be an actual function. Does anyone know? Thank  you!​​​​

I could really use some help!I want my formula to return the correct Ship Deadline taking into consideration the time the order was submitted. If it came in before 4:00 pm on a weekday, it needs to ship the next day. If it came in after 4:00 pm on a weekday, it needs to ship in 2 weekdays.This screenshot shows that order 5014021 came in at 5:15 pm correctly shows it needs to ship 2 weekdays later. But for some reason, order 5014020 that came in at 7:15 pm on the same date is showing it needs to ship in 3 days instead.I can’t for the life of me figure out what I am doing wrong. Formulas are not my strong suit so I use AI to help write them. Any advice here would be very much appreciated. It will be so awesome if I can get this to work with anyone’s help!My formula:IF({Store Name} = "Retail",IF(OR(WEEKDAY({Created (DEV)}) = 6, WEEKDAY({Created (DEV)}) = 0),IF(WEEKDAY({Created (DEV)}) = 6,WORKDAY({Created (DEV)}, 1),WORKDAY({Created (DEV)}, 2)),IF(VALUE(DATETIME_FORMAT({Created (DEV)}, "H

Hi all,I am having trouble with a nested “IF” formula which I believe is correct because when I enter it into the formula field, it is accepted and saved. However, when I then click on “Edit field” to view the formula, it has “self-changed” the formula to something else. This is my original formula which the formula field is accepting:IF({Transaction Status} = "Order Filled", IF({Transaction Type} = "Sell", ({Total Cryptocurrency Value USDT}-({Value per Buy Cryptocurrency USDT (from Linked Buy Transaction (From field: Linked Sell Transactions))} * {Amount of Cryptocurrency})) * 0.35), IF({Transaction Type} = "Buy", IF({Total Sell Transaction Values USDT (from Linked Sell Transactions from Transactions)} = {Total Cryptocurrency Value USDT}, ({Total Sell Transaction Values USDT (from Linked Sell Transactions from Transactions)})-{Total Cryptocurrency Value USDT}) * 0.35, IF({Total Sell Transaction Values USDT (from Linked Sell Transactions from Transactions)} < {Total Cryptocurrency V

how can we get data from API in Airtable? below is the sample API curl -X GET "https://apiv2.shiprocket.in/v1/external/courier/track/shipment/1559017361980" -H "accept: application/json" is it possible ?

I sometimes wonder why I still pay airtable money. For an application that claims to be good for time planning it’s mad it still doesn’t seem to be able to do time and date maths properly. I am in UK (so GMT/BST) - I want to have a date field and a separate time field, and to be able to combine these two into a calculated dateTimenothing I have tried seems to work reliably for this without jumping the date back to UTC Has anyone found a solution for what should be a trivial task?Thanks Richard

I’m working with video timecodes using the DURATION field type, and am entering data in an HH.MM.SS.mmm format (H=hours, M=minutes, S=seconds, m=milliseconds). I need the data to reflect the values in the hours and minutes places even if those are zero. Right now, if I type in the data for one minute, three seconds, and 124 milliseconds, that data shows as 01:03:124 , but I need it to display as 00:01:03:124Similarly, if I type in the data as 15 seconds, 654 milliseconds, it shows as 15.654 , but I need it to display as 00:00:15.654 Anyone have a formula or workaround that will facilitate this?

hi all, i am sure this should be easy and i cannot get it to work i have a products table {product id} and {product name}a menu table which pulls the product name using the product idand an orders table which lookups the product name from the menu i am then trying to concatenate in orders the product_name and qty into one string {products}&" ("&qty&")"{products} here is a lookup field i’m expecting Bread(1)but instead get 2(1) 2 is the product id i’ve tried formula, arrays and rollup function thank you

I currently have a table where I compare student scores to the national mean score. For example, I have 40 students and need to calculate the difference between each student's score and the national mean.I would prefer to do this without repeating the national mean 40 times in a separate column. Is there a way to achieve this with a linked, locked reference?In Excel, using the $ symbol locks the reference cell in another table (allowing you to only input it once). I know Airtable doesn't offer that feature, but I'm sure there's a workaround or method. Could someone share it with me and help?Example of what I want to accomplish in Airtable using excel logic:[Table B1: Student Score]-[Table 2: $B$2]  1550 - 1555 1549 - 1555 1540 - 1555 1536 - 1555 Where 1555 is in Table 2: B2, listed only once

BonjourJe débute sur airtable.Je souhaite faire un formulaire personnalisé. C’est à dire avec le nom - prénom et téléphone en en-tête. J’ai une table formulaire avec un lien vers une table contactJ’ai plusieurs questions :Peut on faire un formulaire avec le lookup de la table contact qui concatenate le nom et prénom  Sinon peut on reprendre les fenêtres nom et prénom et téléphone ? Pouvez m’indiquer si le principe de ma formule est correcte, en sachant que tous les champs viennent d’une autre table, qu’il s’agit de lookup : “https...form?prefill_{champ ex: nom du contact}=”&ENCODE_URL_COMPONENT{champ ex: nom du contact} & {champ ex: prénom du contact}=”ENCODE_URL_COMPONENT{champ ex: prénom du contact} & / & {champ ex: téléphone du contact}=”ENCODE_URL_COMPONENT(champ du télépone} Merci de votre aideViggie

I've created a calendar table synced to GCAL and wish to populate a calendar view with the events based on the Event Start and when Event Start is not available based on Event Created. I've struggled to achieve this (I welcome any other possible solutions) so I attempted to create a formula field with the following formula: “IF({Event Start}, {Event Start}, {Event Created})”.  As generated by the ai agent. Somehow that formula changes both Event Start and Event Created data. I've also tried the following formula: DATETIME_PARSE(IF({Event Start} = BLANK(), {Event Created}, {Event Start}))To the same effect. Screenshot attached to quell my possibly insufficient description. Questions.Why is the data changing when the formula is only requesting to display the data? Are my assumptions about the formula only asking to display data correct? Is my formula broken/insufficient? if so, what's the fix? Is there a simpler way to achieve my goal of populating a calendar view with the events based o

Found some older posts that this is impossible, but hoping there might be a solution within the last two years:I am creating a “phone sheet” within Airtable, essentially a tracking list of all the outgoing and incoming calls. My end goal is to have a field that will show last time I update a record’s “status” column (from incoming to outgoing, and vice versa). But I would also like to be able to manually overwrite the time… up until the next time I change the status, therefore going back to the automatic last modified time.A case study: I receive a voicemail that some left an incoming call at 10:00am. It is now 3:00pm, so I would need to modify the record and then manually back-date the last call to have been at 10:00am.Is there a formula function that would allow for this? Or would this be better suited for an automation?Thank you - open to any suggestions!

Im trying to use airtable record search in Make . . .I have a field called PostID. I want to search it for a number. I Cant find anywhere how a simple airtable syntax works for this  .. is it {PostID}=”123456” or do i put search or someting in front of it? 

Hey Community,Just seeing if there’s still a need for this kind of array function in formula fields?It would compare 2 arrays and remove the values that appear in both.I submitted the suggestion through the idea form, but when I realized it’s an old community idea I thought it might need more feedback/attention.My use cases would be product gap reports in my CRM and non-openers in my email campaigns. What would your use cases be? This is how it works with javascript:const arrA = [1, 2, 3, 4]; const arrB = [3, 4, 5, 6];const difference = arrA.filter(x => !arrB.includes(x)); //Output: [1, 2] Here’s the OP:  

 Hi I am brand new to Airtables and have been looking around for a solution to this for a while but I have had no luck… I have two tables;One with Products which includes 2 different fields that display the royalty rates per product; “AU/NZ Rate”  and “ROW Rate”the Other table is a Sales Table and has a Region field that is either “AU/NZ” or ROW depending on where it was sold.How do I create a field that looks at Sales->Region and uses the value there to choose the value from  the corresponding field  in the Products table?  

Hi,I need to pad a number to a fixed amount of digits (8), i.e. prepend with leading zeros when the significant number is less than 8.This is easy enough to do for integers with a substitute formula:RIGHT("00000000" & {Test SUM},8)However, in my scenario I have decimals and that doesn’t work, since a number 10.00 will count as 2 digits while 10.11 counts as four digits. Hence the padding will be off.I can replace the decimal character for the decimals with something else than .00, but the .00 ones still counts as two digits shorter.SUBSTITUTE( RIGHT("00000000" & {Test SUM},8), ".", "")The above substitute/right formula gives this result. The .45 decimal number is otherwise right but has one leading zero too little. The .00 number is off by two positions to the right (should be 00012300). I tried to come up with a way to treat the integer part and the decimal part separately, but could not find a way with LEN/MID/LEFT/RIGHT to split up 123 and 00 to two separate parts. The de

Hello - I have a base with a parent table, Projects, and multiple child tables including a Milestones table. They are separated due to creating levels in a project update interface. When a teammate creates a new milestone, it does not pull into the Project table and when a new project is created, it is not pulling into the Milestone table. How do I correct this? 

A couple months back, I asked for help deleting lines of text beginning with the word ‘Highlight’ and was given this great formula: REGEX_REPLACE({Hook},".*Highlight.*\\n\n?","") It has been a huge time-saver but something I forgot to ask with my original question is if there is a way to add a blank line/line break where those lines were. I input data into the records that looks like:Highlight(blue) - Page 34 · Location 290“Look at that. That thing’s almost a book.”Highlight(yellow) - Page 34 · Location 292“You think… she’s going to print it? When it’s done?”Highlight(pink) - Page 34 · Location 294“I hope so,” I say as brightly as I can manage.  And when using the current formula, it comes out like:“Look at that. That thing’s almost a book.”“You think… she’s going to print it? When it’s done?”“I hope so,” I say as brightly as I can manage. But I need blank lines where the lines starting with ‘Highlight’ were so it looks like:“Look at that. That thing’s almost a book.” “You think… she’s

Fairly new to AirTable, so this is probably really simple, but I can’t find anything helpful.When I have a Rollup field, I cannot expand the Formula field DOWN using the bottom corner handle - only to the right. This means I can only see about 1.75 lines of the formula. I’m copy-pasting in and out of Notepad to try to create/edit formulas because I can’t see the formula in AT.I can expand the Formula field down as far as 10 lines if the field type is Formula instead of Rollup. What on earth is going on here? 

I’m making an analytics dashboard for a client. They want to be able to see the top X records by viewcount, which we track. When I make a graph for this, there’s simply too many records and the bar chart looks a mess.I want to create a formula field that has a rank and I can include the top 5 records in a bar chart. What’s the right approach for this? I am not a developer and hoping to avoid an automation w script.