Formulas

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

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

There are 2 fields: the time of the last modification of the line and just a number. (last_modified_time and time) How do I create a formula for the “result” field to add the minutes from the last_modified_time field and the number from the “time” field? That is, the example should get: 38 + 100 = 138 (the field “result” should be 138) (101 is there for an example, do not pay attention. Instead it should be 138)

Hello, Quick question! I have a table with recurring tasks. Every week I have to redo these same tasks. I know the progress of my task thanks to “Multiple Select” (Ex: In progress, to be done…) but I would like this column to be reset at the beginning of each week. Is it possible to create a formula to set the status of these tasks to “To do” every Monday? Thanks for your help !

Hey Community, I’ve made this on Gsheet and I would like to do it on Airtable. Any idea? Thanks!!

Hello, please dont be too harsh with me I am starting with this and have a simple question I cannot solve by myself though trying different combinations of formulas. I have 3 fields : possibleloan1, possibleloan2, loanoutput. I want a formula that defines loanoutput. Loanoutput is possibleloan1 if possibleloan1 is greater or equal possibleloan1. if possibleloan1 is minor possibleloan2 , loanoutput should be possibleloan2. I tried for loanoutput: IF(possibleloan1 > possibleloan2, TRUE(possibleloan1), FALSE(possibleloan2)) Do you know what I did wrong? Airtable is not accepting the formula itself though I wrote it in accordance to the guidelines. Thanks in advance!