Solved

Hello Airtablers! For my CRM I’m trying to automate the ranking of my prospects from “ :fire: Flaming hot” to " :skull: Dead". I tried using a SWITCH formula but got in difficulties, so I reverted to IF and DATETIME_DIFF. I want to switch between the 6 values below and though I managed an IF statement with just 2 values, when I try 6 (and DATETIME_DIFF) I get no-where (I even checked for curly brackets, straight quote marks and the number or parentheses)! Can you help? I tried the following: IF({Last Contact}=TODAY(),"🔥 Hot!", IF((DATETIME_DIFF{Last Contact},TODAY(),'days')>3,"🌡 Warm", IF((DATETIME_DIFF{Last Contact},TODAY(),'days')>7,"😎 Chilled", IF((DATETIME_DIFF{Last Contact},TODAY(),'days')>21,"🚰 Tepid", IF((DATETIME_DIFF{Last Contact},TODAY(),'days')<90,"🥶 Cold", "💀 Dead!") ) ) ) ) Thanks in advance!

Hi! I have a formula that works awesomly to calculate international age (see below). IF( {🆔 Birthday}=BLANK(), ' (N/A)', IF( {🆔 Death}=BLANK(), IF({🆔 Birthday},' ('&DATETIME_DIFF(TODAY(), {🆔 Birthday}, 'years')&')'), IF({🆔 Birthday},' (✟ '&DATETIME_DIFF({🆔 Death}, {🆔 Birthday}, 'years')&')') ) ) { :id: Birthday} is a date field. I’ve been trying to adapt this formula to calculate Korean age. I would need it to do something like IF({🆔 Birthday},IS_BEFORE(TODAY(),DATETIME_FORMAT({🆔 Birthday}, 'MMDD'),+2 'years',+1 'Years')) …if that makes sense? I’ve been banging my head for too long that even the above might have an error. So in plain English: if today is before a persons birthday (but after January 1) I want it to add 2 years to their age. If it’s after their birthday I want it to add 1 year. I just can’t get it to actually work and I wouldn’t be at all suprised if it’s all down to my nesting, which I’m horribly at. I would

I’m sure this is a simple solution but I am just learning the formula lingo. All in the same table, in field a, I multiplied field b by .14 and it automatically rounds the number up. As this is a dollar amount and field b is also a formula multiplying other fields, how do i get them to give me two decimal places instead of rounding all numbers up to the nearest integer. I’d love to take the extra cents but don’t think my customers would appreciate. Thanks.

Hello! I’m trying to write a formula to get a bell emoji if a record has been modified after the date we close our quarterly reporting by. I have the “last modified” field and the “report closure” dates to compare, but I am struggling to write the “IF” formula in the right way. Any help would be massively appreciated! :grinning_face_with_big_eyes: Thank you! Belén

Hi! I wanted to show a lookup field as <RecordCardList />, but I can’t access the original field. I’m only getting the recordId and fieldId, not the tableId (in field.options). Can someone help me with that? Thanks!

Hi, I’m using sleiman/airtable-php to integrate airtable with my website, the problem that I’m having right now is inserting a public url where my image is hosted to the attachment field of the table; this is the code: $url = "<url-of-the-image>"; $attachment = array('url' => $url); $update_details = array( "ID Verification" => $attachment, ); $update = $airtable->updateContent("Applications/$record_id", $update_details); And I get this error: [error] => stdClass Object ( [type] => INVALID_ATTACHMENT_OBJECT [message] => Invalid attachment object for field ID Verification: "<url-of-the-image>" ) I have also tried to make the $attachment variable an object like this: $attachment = (object) [ 'url' => $url ]; Or do I have to define $attachment differently or ‘sleiman/airtable-php’ doesn’t support attachment fields? Help!

I am new to relational databases in general My goal is comparing reports of what are supposed to be the same items, but will occasionally have small (and sometimes large) errors. Because the reports come from 3 different locations, each one is already slightly different, but they each have a Serial Number field and an Object ID field. Those are what are most important to me What I’m trying to be able to do is have a table where I can plug in my Serial Numbers and see what each report says their Objected ID is, so if they are mismatched, I can see that easily. I know this seems straight forward, but I’ve been trying to use linked records and Lookup…and it keeps messing up.

Hi…I have the table “Groups” and the table ‘Assets’. If a record in the field "Type’ in the groups table equals the word ‘bus tour’ I want to attach an images URL from the field ‘logo’ in the assets table to the field ‘logo’ in the groups table. My intention then is to use that URL to insert an image in a page designer block. I thought I had an idea but this one is a bit complicated for my beginners mind. Thank You!!!

I want to formulate a date if: under one column the answer is 3-day add 3 days to date under one column the answer is 2- day add 2 days to date under one column the answer is 1- day add 1 day to date I think I begin like this, DATEADD({Proposed Date of W/C},3, ‘days’) but how do I formulate the if this then that?

Hello, I am migrating my company booking system into Airtable. Long story short, I provide freelancers to clients on a day basis. I have > 200 clients, and over 100 freelancers. My challenge is that some freelancers are charged a different price depending of the client. Right now, I have a pricing matrix on a Sheet, as for exemple : I can"t find an easy solution to get the same result in Airtable. Let’s say I have 3 tables : Clients , Freelancers , Prestations. In the table Prestations, if I create a new record with Client B and Freelancer 2, i’d like the price to be automatically inserted as $200. If a Prestation record is Client C and Freelancer 5, that’d be $250. Would you have some suggestions? Thank you very much!

Hi, :wave: I would like to shift a base from one workspace to another. I am working through API of the base. I would like to know, will shifting change the base’s Base ID? I would not love to take any chances. Thanks! :blush:

I’ve currently got a bar graph for amount of plays a track has in a recording, but there’s 1000s of tracks in the grid view data and I want to limit the graph to only show a certain number (i.e. the top 25/50 tracks) or to filter the graph’s dataset (i.e. tracks that have had 3 or more plays), is there a way to do either of these?

I am trying to determine if an appointment date (stored as UTC) is on the same day or a different day depending on the timezone. I think I know how to do this with an IF statement in general: IF(“Date in CTZ1”=“Date in CTZ2",“same”,“different”) but I don’t know if they work with dates and times. In the image Call 1 is a date in UTC. I want to compare if that date is the same in Sydney (CTZ1) as it is in Nicosia (CTZ2) - which it isn’t - Sydney is on the next day. I need another field that says ‘same’ or ‘not same’, but I can’t figure out how to do it. Help or ideas are appreciated.

I need some Airtable Formula help. I am trying to merge the below. I’ve been looking at it so long I can’t work out what is going on any more! This works: IF({Rejected}, IF({Rejection Reason}=“Unresponsive”,“Unresponsive”, IF({Rejected}, IF({Rejection Reason}!=“Unresponsive”,“Rejected”)))) And this does too: IF(Rejected}=BLANK(),IF({Status 1}+{Status 2}=2,“Done”, IF(Rejected}=BLANK(),IF({Status 1}+{Status 2}=1,“One of two”, IF(Rejected}=BLANK(),IF({Status 1}+{Status 2}=0,“Pending” )))))) However, if I put them together, then it only displays the results of the first 2 lines. If I switch it around so the 3 lines goes first, then it only displays the results of the 3 lines. Not sure why but any help gratefully received. :pray:

Hello! I have a Base with several complex formulas that I’m trying to pair down to be a bit more streamlined. If been trying to get the following formula to work for days with no luck. If anyone can help, it would be super appreciated… Here’s the setup: Title Category (Detail) / Single Select Field: Feature, Series (Comedy), Series (Documentary)… etc. Title Category (Main) / Nested IF Formula Field: This formula looks and the Genre Single select field and determines whether the record falls under four more macro-categories: Series, Feature, Sizzle, or Other. Title Release Date / Date Field: Date field that every record has formatted as YYYY-MM-DD Season Number / Single Select Field: Only records for series’ have this selected. If the record is for a movie it is left blank The formula field I need help with is titled Year/Season For Campaign Title. What I want this formula to do is the following: IF Title Category (Main) is Series, then use Concentrate to put parenthesis around the Seas

Hi Team, I wants to insert the dynamic value through variable in formula. eg: filterByFormula: “IS_AFTER({Time-stamp}, dateValue)” Here dateValue is a variable that gives error. but if i use the string instead variable it works . Also variable is type of string . Please suggest the best way to insert the variable value in formula.

Hi! I’m one of those that like organizing and solving things, but is horrid at learning things strictly through textbooks and other textbased material. I learn best through application and therfore have a base where I try all kinds of things for the purpose of learning. I needed a new challenge and I usually try ideas that can be translated for other uses down the line. As I already have a column for { :id: Birthday} in my base I thought I could have it calculate Zodiac sign and even Chinese Zodiac based on the date entered there. Now to the questions. As I have more than one question I’ve marked them as A1, A2, A3…; B1, B2, B3… for ease. A - Zodiac sign I’m aweful at nesting formulas, so I came up with this that serves the purpose of finding the zodiac sign. IF( AND(DATETIME_FORMAT({🆔 Birthday}, 'MM/DD') >= "01-01", DATETIME_FORMAT({🆔 Birthday}, 'MM/DD') <= "01-19"), "« ♑ Capricorn »\n",'') &''&IF( AND(DATETIME_FORMAT({🆔 Birthday}, 'MM/DD') >= "01-20", DATET

Hi All, I’m new to Airtable and need some help please. I have 2 tabs one for Projects and another is Tasks which breaks down each project where you can add multiple tasks related to that project. I wanted to know is there a way to automatically update the Project - “start date” and “due date”, to reflect the first start date of the list of Tasks and the last due date in that list? Thanks T

This is odd. The display field for one table in the base I’m working on shows a concatenated string that represents each record’s basic uniqueness. At the end of the string, there is a reference to a charge. Should look like “$121” etc. For most of the 1000 or so recs in the table right now, that’s how it’s working. But I noticed a few records that were displaying something like “$121.000000001”. Maybe 5% of my records are showing that, maybe fewer. The display field’s calc includes a reference to a field ‘ChargeX’, a formula field. ChargeX = HowMany * Rate. The values in HowMany and Rate are not always integers but they are usually integers and they are integers in the cases where this display problem occurs. So 1.1 * $110 returns ‘$121.00’. The formatting of the ChargeX field is set to Currency with a precision of 2 (ex: ‘$5.00’). So I’ve got a nice clean $121.00 sitting in the ChargeX field. But when the display field for the record grabs that value and sticks it at the end of the s

Hey folks - can’t seem to find this in the forum. We have an Overview table that has records that correspond to dates - every month for each year (say 2019 and 2020). In another table, we have a Linked Record column, where one can select which Overview record to link (ie the month it’s associated with). When the user clicks to select a Linked Record, we see the choices ordered in two different ways on two different bases: First: Second: It is not obvious at all how to affect the order of these choices, but we’d very much like to. We’d like them ordered the way the Grid view on our Overview table is ordered - by year and month - or at LEAST grouped by year. This is client-facing, so it’s pretty important for this to be sensible. How do we re-order these choices?

Hi Team, Is there any way to get the relational data in single query. I am getting the record id of another table. But i wants to get the full data of relational table along with id. Please suggest.

I need to concatenate a bunch of values from different fields to create a string result like this: 1.5 hours @ $125 The formula I’m trying to use is HowMany & " " & What & " @ $" & Rate~ I’m able to get everything except the “125” part of that result. The “1.5” part is pulled from a data-entry number field called “HowMany”. It works okay. The “125” comes from a formula field called Rate~ that uses a formula like this: VALUE( someNumeral ) The Rate~ field displays the correct value and moreover is used successfully in a simple math calculation formula. I thought that the VALUE() function converted a text numeral into a number. But when I add the reference to that field (the one that contains “125”), the string concatenation fails. It doesn’t matter whether I use CONCATENATE() or ampersands. Any suggestions? At the moment the only thing I can think of is that, for some reason, an entered number value in a number field is somehow flexible enough that it can be used in a co

I’m trying to figure out a formula to give me the following info in our age of Corona… I want to put a column on our main table that returns either a ‘yes’ or a ‘no’ based on data from 2 other tables. I’ve got the tables set up to give me a ‘1’ for a count so all I want is for the column on the main table to read data from those other two tables and give me a yes or no… possible?

I’m pulling a numeral out of a string, for example, extracting “27” from “WP27”. Is there a way to coerce that extracted numeral to a number value? I’ve discovered that Airtable throws an #ERROR if I use that extracted value in a math formula like MyExtractedNumber * ItemPrice. I presume that’s because it thinks the value in MyExtractedNumber is “27” and not simply 27, ie a text value and not a number. There’s a way in Airtable to create a date value from parts but I’ve searched for something like a NUMBER() function and can’t find it. Am I missing something? William

Here’s what I want to do (simplified for clarity). I have 5 blogs, and each one has 2 hyperlinks in it, that link out to the others. I want to be able to open one record, and see list of links to Outgoing Blogs, and then click on them, to see the record for those. The info I want to capture for each record in my ‘Blog’ base and are my columns from left to right are: Post URL (locked column), Post Title, Outgoing Links (set to link to another record in the same ‘Blog’ base, aka the Post URL column). The URLs in Outgoing Links are identical to the URLs in Post URL column, but the problem is, when I import my data from a CSV, with multiple Outgoing Links separated by commas, Airtable dosen’t map them to the existing Post URLs that contain all the other meta-data. Instead, it creates new records with just the Outgoing Link url. Does anyone know why this is happening? What am I doing wrong? And is what I want to do even possible in Airtable? Happy to share a link to the base in question, bu