Formulas

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

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’)

Here is my current formula: IF(Store=“A”,(({Price}/100)*10)) This formula gives me the calculation of 10% of the figure in the Price column if the store in the Store column is A. So far so good. But how do I add store B to that formula? Say I have store A, B, C, D etc but I only want the formula applied if the store is A or B in the Store column? Thanks in advance.

Hi, I am creating a base for a bar which has registered members coming in to order drinks every evening. I have created a table for recording the various members’ tabs. This tab has to record the details of each member’s drinks consumed/ordered on that particular day. My challenge now is effectively recording the quantity of the drinks. Especially when a member orders the same drink more than once. The drinks are listed on a separate table which is connected to the ‘Tab’ table through a link field. Now when a particular drink is selected once, it cannot be selected again. Also, in the event that the buyer orders more than one type of drink, recording the quantity bought for each type is proving hard to do. Any thoughts as to how to go about it?

I’m looking to generate a formula that lists the date a record is moved to a specific selection from a single select field. Our company does video production and I want to track the actual dates projects are moved to a particular stage like “delivered” or “rough cut”. Currently, I have to go inside each record and look at the activity tracker. There must be a better way without creating another field. Thanks!

Hello, I have a number with decimal (for exemple 12.5), I want 12.90. The field is a number field. Exemple : 25.5 > 25.90 18 > 18.90 Thx for your help

I set up a database to help me organize battery cells and figure out which are good and bad. I have a table for each make & model’s rated specs, a table with the results for the each battery cell’s capacity test (each test is a record and a battery cell can have multiple tests/records) and a table for each battery cells’ details & summary. In the battery cells detail table I want a field for each battery cell that is populated with the average result of all it’s capacity tests from the capacity tests table. How do I achieve this? P.S. - The value in question from the capacity test table is a number/integer (i.e., 2400).

Hello ! I made a formula to get all the holidays for the current year automatically and I can’t use that value directly with WORKDAY_DIFF in another formula. I have to copy and paste. If anyone has the answer I would love to have it, thanks a lot !

Hi :wave: If anyone can help with button field… In a single click I want to achieve to trigger a webhook and open different url. I manage to have button trigger Integromat webhook, it successfully opens url saying “Accepted”. Now, is it possible to open different url(google docks) in a same click after the webhook is triggered. Some sort of redirect or something… Is there a way to do this? Thank you for your help, Pjero