Formulas

I’m trying to achieve the following: If Invoice Status is ‘Paid’, then ‘Paid’ If Invoice Status is empty and the date in Payment Due is on or after today, then ‘Due’ If Invoice Status is empty and the date in Payment Due is before today, then ‘Overdue’ I have created this formula, which is not working (it does show ‘Paid’ but not ‘Due’ where applicable): IF({Invoice Status} = ‘Paid’, ‘Paid’, IF(AND({Invoice Status)} = BLANK()),IS_AFTER({Payment Due}, TODAY()), ‘Due’, IF(AND({Invoice Status} = BLANK()),IS_BEFORE({Payment Due}, TODAY()), ‘Overdue’))) Any help would be much appreciated!

Use case: View order progress in Kanban view and display relevant button based on stage. For instance, if I received an order I would see a ‘send order email confirmation button’ and when the order moves to order shipped I’d see ‘send order shipped email button’. Can I use a formula to display a button? Is there a way to filter columns in Kanban view? Any thoughts on an approach I can take or direction to go? Any tips, insights, or code would be appreciated.

I cant seem to get this formula to work. its prefilling an airtable form but i cant get the second feild to fill. it says its a good link it just doesnt fill the second box. heres what i have CONCATENATE(‘https://airtable.com/shr4cGVHhm4dRLllz?prefill_UNIT=’,{UNIT} & ‘&PLANT=’,{ENCODE PLANT}) here is an example of what that outputs https://airtable.com/shr4cGVHhm4dRLllz?prefill_UNIT=261A&PLANT=12-CONV the unit number prefills fine but the plant does not. 261A is the data from the UNIT table. the title of the field on the form is also UNIT. 12-CONV is the data in the feild PLANT. The title of the feild on the form is also PLANT note everything in my base is in all caps and “encode plant” is all the plant names run through the url encode formula. i also have a single select with three options id like to prefill also if someone can help me with adding that as well?

Hi everyone, I’m new to Airtable and am really struggling migrating a formula. I’ve spent a couple of hours and made little progress… I am using Integromat to run a query against an Airtable date column. Basically the logic should be this “return all rows that match a specific date and time”. Anything returned (usually just one row per hour) will be then parsed by Integromat. E.g. If I set 31.08.2020 15:30 in a date field in the column, I want Integromat when it runs (every 15 minutes) to return all the contents from that row to then be processed by Integromat. I’m generally fine working out the logic in Integromat… Thanks!! :slightly_smiling_face:

hello there, i.ve got a big issue, I have one base with multiple tables, in one table i have proyect # (a number to identify each proyect) requirement # (number to identify each purchase made for the proyect) concept (single selection to identify the type of service or good i.e construction, services, labor etc) value (amount $ of the requirement) Now in another table in the same base, i want to sumarize the total amount paid for the proyect in each concept. ie. proyect # …concept (labor)… total amount $ (1.000) when this total amount is the sum of all the concepts named labor in the other table. i dont knwo how to do it, please help

Dear community, I am working on a table for an art collection. The first field is supposed to sum up my most important information about each piece with a formula: CONCATENATE({artist},", “,{title},”, “,{year},”, ",{height (cm)}, " x “, {width (cm)}, " cm” , ", ",{technique}) This works perfectly fine so far, but I am struggeling to include an IF-Formula within the existing formula: Since a few of the works have a depth worth mentioning, I also want to include the depth of the pieces in the first field, but only IF the field {depth (cm)} is NOT EMPTY. As far as i researched this would be IF (depth != BLANK (), " x ", {depth}) , but I didn’t manage to make this work, let alone yet including it into the existing one. Just to sum it up, I want the formula to be displayed like this, with the bold part only being shown if the field is not empty: artist, title, year, height x width x depth cm, technique I’m sorry if this is obvious but I’m very new to airtable.

Hi All, I’m trying to add an individual button for reach row which would redirect to different URL’s, For ex : Row 1 : Button 1 : Google.com Row 2 : Button 2 : Airtable.com so on Currently when I proceed to add the button table with a simple URL formula (image below) It proceeds to apply the same URL for all rows on the Airtable sheet, from which every button points to the same url (google.com in this case) How do I edit individual button’s URL destination? Am I missing something? Let me know! Thanks in advance.

There is a last_modified_time field. I display in it the time of the last change of the “Статус_Заказа” field. The “Статус_Заказа” field has several meanings: How can I create a formula to get the time when the “Статус_Заказа” field was last modified for a specific value? For example, “Частично_Готов”? So that when the “Статус_Заказа” field takes on a different value that is different from “Частично_Готов” - nothing happens

I have a table that has multiple lookup fields to another table. I need to be able to sum the number of unique values in each lookup but keep failing to get it to work. Example is I have a lookup called “Promotions” and then I want the next column to says # of Unique Promotions. Please help!

Hello! I am attempting to make a furniture reuse request system for my company. I have made a furniture removal form/table that generates a gallery view with the items available for reuse, and then a furniture request form that allows users to request furniture from the gallery by unique ID number. I would like to link corresponding entries between the two tables by matching the ID numbers, and bring the destination from the furniture request into the furniture removal table, to facilitate the move from point A to point B. I have made a linked column that allows the mover to manually link them, but I would like to do this automatically. I see several other people have looked for this functionality but it wasn’t supported in 2019… has any progress been made in automatically linking two tables via a function or other logic? thank you!

I just wanna say TNX! to @Justin_Barrett for superb explanation !! He teaches you how to do stuff and shows how to solve problems and not just bumps the solution! Thank you very much! Url Formula to Emcode Mailto: Formulas Hi All, I am trying to create a Email Link formula where “EmailAllStaff” is a rollup and “NamesAllStaff” is a lookup. this should be a strait forward thing to do but i am somhow stuck :slight_smile: i must be missing something obvious thx! :m: [image]

Hi, I want to do a select on my records according two conditions : Find in “Resource title” field a text Find in “Format” field an exact text For both conditions, I want them to be case nonsensitive because some text are written in caps, other not. So, at the moment, I’ve this : filterByFormula=AND(FIND("some text",{Resource title}),(FIND("Video",{Format}"))) And I’m getting the INVALID_FILTER_BY_FORMULA error message . So, what I’m doing wrong ? Is it possible to have the nonsensitive case research ? Thanks.

I’m new to AirTable bases. I’ve been set up a base (a table), say base_1. I would like to write some Node js code to query the data from the base_1. The query like this: SELECT col_1 from base_1 where col_2 = ‘value_col_2’ What I need to write for “filterByFormula:” Thanks so much for any help.

Hi everyone :grimacing: ! I have been working on different ways to represents performance information. We usually have to register how many points an employee got per week, and then we have to see the average of points by a pair of weeks :nerd_face: . For example, if “Employee 1” got 100 points on week 10 and 100 points on week 11, and the corresponding pair of weeks is week 10 and week 11, I would like to see the average of points of week 10 and week 11 in the same column Biweekly Points (even if by row is repeated). Here is the base I made: Right now, what I tried was to use Find and IF this way :face_with_monocle: : IF(FIND(Week,Biweek),AVERAGE({Points}),“NA”) But the problem is that this formula doesn´t show the average unless: **Biweek and Week have exactly the same information (for example both have W10-W11)* :fearful: *If Biweek and Week are both text columns :fearful: If I let Week column as a link and Biweek as a lookup of Week, the result of the formula is “NA” :we

Could someone please explain to me what the purpose of the T() function is? I’ve tried using it on a variety of different fields, and it always returns the field value if the referenced field was a text field, and it always returns nothing if the referenced field was a number field. But — we already knew in advance that one field was a text field, and one field was a number field. So why do we need the T() function? It seems redundant and unnecessary. Perhaps the one cool thing it does is that it converts Date fields into this format: 2020-03-01T10:30:00.000Z Which I think might be useful for sending dates to Google Calendar?

Hey, I am creating a base to understand staff allocation over the various projects - 1 person goes to 1 project only until they are transferred. I had to assign people (names) to specific projects (as in their current projects). I have created a sheet called transfers where there is a FROM project and TO project. Over time, each person will have a number of transfers. So in the staff sheet we see the various TO sites where the person has been transferred to over time. Now to find the most recent transfer and hence their current project I used the solution from this answer: Find last entry So it does some work in the background and basically finds the most recent entry for the person and shows that in the formula as text The problem is the grouping. As you see the name name has gone into 3 groups Cummins Pithampur Since they are showing text from the same source, they are required to be in the same group. The logic beats me. Some help please. Thanks Vatsal.

I’ve just discovered that the Open URL option in the new buttons feature is much more powerful than I realised. E.g open up your local email client with a prepopulated email. Even prepopulated forms. But it assumes you know how to code URLs. Which I don’t. Can anyone suggest some simple references please. I see from the Airtable Help samples you can send stuff to Google Mail / Calendar or Twitter or anything I guess. So would all these products publish somewhere what their own URL formats need to be? What should I search for? For example, one of the Help samples is: “mailto:” & ENCODE_URL_COMPONENT(Email) & “?subject=” & ENCODE_URL_COMPONENT(Subject) & “&body=” & ENCODE_URL_COMPONENT(Body) But what language is that? Where can I learn more?

Hello, I have a base with 2 sheets. In Sheet 1, I have a list of tasks, their due dates, client name, and other info from my job’s project management software. In Sheet 2, I have another list of tasks with similar info (tracking my tasks outside the project management software). I’d like to somehow incorporate both of these, and generate a list (maybe on a third sheet) that displays tasks from both sheets in order of due date. Is this possible? Thanks.

Hello - I’m brand-spankin’-new to Airtable. All I need is a formula to show the entered date as “Quarter / Year” e.g. 08/05/2020 should show up as: Q3/2020 I’ve been working w/ the instructions here - but I don’t need the shifting of a fiscal year that’s different than the calendar year and since I’m a noob - I don’t know which section of the formula is actually doing that. I’m also liking the formula that simpy uses the SWITCH(Month({FieldName}) I found here on the community forum, but I don’t know how to format it to include the Year and " / " between the quarter and year. SWITCH(MONTH({Field Name}), 1, "Q1", 2, "Q1", 3, "Q1", 4, "Q2", 5, "Q2", 6, "Q2", 7, "Q3", 8, "Q3", 9, "Q3", 10, "Q4", 11, "Q4", 12, "Q4" ) So 2 possible answers, 1) Show me where the year shift is happening in the linked article formula, or 2) show me how to add the slash / and Year in the above SWITCH formula. Thank you!

Hi I have one column called genres 1 and another column called genres 2 I want to add another column that returns me the value of genre 1 when it isn’t blank and returns me the column genre 2 when the column genre 1 is blank I know I have to use the If function, but I dont know how to apply to this case Can you provide me some help? Thanks in advance!

Has anyone seen duplicated content resulting from a Lookup? I’ve noticed this in a couple different tables and it is causing formula errors for me. For example, I have a lookup on a single select field with only these options: http://photoville.org/uploads/2020-08-05_073451_3zvyyjo.png Yet, in the resulting field, there are sporadic entries that appear as ‘Clinical Trial, Clinical Trial’ When I subsequently attempt to use this field in a Switch(), I get ‘#ERROR!’ in those case where the entry is duplicated.

Hey, I have a table with records that are tagged with a specific day of the month. I want to pull the 5 records associated with the previous 5 days (not including today) with a filterByFormula parameter. I am using the Node.js Airtable library. How would you do this?

I would like to create an if statement and could use some assistance … If cell A is not checked, outcome “planned” If cell A is checked, outcome "in progress If cell B is checked, outcome “complete” I hope this makes sense. Thank you for any help you can offer to this newbie :slightly_smiling_face:

Is there a way to pass a calculated value into a Field linked to another table? Here’s an example. Table A Field 1 Field 2 Field 3 [linked to Table B] Table B Field 4 In Table A, I want to auto-set the value of Field 3 = Concatenate ({Field 1}, “, “, {Field 2}). This will match the values in Table B.

Hello, I would like to had a record depending of a cell from a link. I Explain myself The result in “field 12” “Product 1” is a link to an other table “Unit cost” and “in stock” are colomn from the other table depending of “product 1” I would like to display in field 12 only the first product in stock. Thx