Formulas

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

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:

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:

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

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

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 whe

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

I am using the CRM template as a base for a more robust CRM solution. I’m trying to create a field for the most recent conversation note from the most recent interaction. There is a field in the main “CONTACTS” table titled “Interactions” that links to an “INTERACTIONS” table which includes “Date” “Conversation Notes”, and “Contact”. So the Interactions Field in the Contacts table lists dates for each of the calls/interactions with the corresponding contact. I also have a field with the date of the latest interaction. But I’m trying to pull the Conversation Notes from the most recent interaction into a field for each contact in the CONTACTS table. I’m fairly new to Airtable and at my wit’s end. Any and all help is appreciated.

Hello. I need to add and subtract items that are not numbers nor dates. How can I do it? To illustrate it, I have changing groups of people working on projects, so I have a first column for people originally assigned, then a second for people who left in the middle of the project and a third one for people assigned also in the middle: [A] Original composition: “John” “Jack” [B] Removed: “John” [C] Added: “Jill” (All above are linked to the ‘People’ table.) I would like to have a [D] Current column where I can get “Jack” “Jill” as result of a formula, which should essentially work as D = A - B + C, i.e., D should contain items in either A or C but not B. How can that be done, please?

Hello, I have an attachment column with mp3 files. I need to know the length of each mp3 file. Is there a way to do this by using a formula? The API does not provide anything I can use to this. Best, Kim

As you can tell by my question, I am new to Airtable :slightly_smiling_face: How do I rewrite this formula so it does not result in …#ERROR when I don’t have dates in some the cells used in the calculation? I would rather have that cell remain blank until I have all my dates entered into my table. Thank you! DATEADD({Speaking Date},-1,‘days’)