Solved

Hi all, I am formatting my database to show how long a volunteer has been with us (in order to determine when to give them their 1 year of service, 2 years of service etc), however, the cut-off date each year for our volunteers is 30th April. For example, if a volunteer starts with us on 1/5/2018, at 1/5/2019 the years of service is 0, but at 1/5/2020 it will be 1 year. If a volunteer started with us on 29/4/2018, at 1/5/2019 the years of service is 1, and at 1/5/2020 it will be 2 years. I hope this makes sense! I’d love to know how to formulate this so I can get a ‘1’ or ‘2’ etc. Thanks in advance for your assistance.

Doing some simple substring removal and getting different results from the block than in JS Bin. let renderName = "MX_Topo-Chico-Agua_Mineral-Water_600ml_PET_6x"; let secondaryQuantity = "6x"; // scripting block generates an UNEXPECTED output: "MXTopo-Chico-Agua_Mineral-Water_600ml_PET" output.text( removeSubString(renderName,secondaryQuantity) ); // JS Bin, however, outputs as EXPECTED: "MX_Topo-Chico-Agua_Mineral-Water_600ml_PET_" // console.log( removeSubString(renderName,secondaryQuantity) ); function removeSubString(str,substr) { return str.replace(substr,""); } Oddly, making use of a character (rather than “”) yields proper results. renderName.replace(secondaryQuantity,"1") => "MX_Topo-Chico-Agua_Mineral-Water_600ml_PET_1"

Hello, I’m trying to compare A, B columns and fill C column with the equal values. Simple example of how it has to look like: I know that Airtable is not a spreadsheet and rows cannot be cross connected but anyway what is the best way to solve this (Links/Rollups, Zapier…?) and how to implement? I would be grateful for the help.

Hi, I need some help to do something… 1/I have a column with a price 2/ I have a column with a Single Select option, and a list of discounts (10%,15%,20%,30%) I would like to create a 3rd column, with a formula which gives me the total discounted price, based on the discount which has been selected. So if : Column B has a total of $100 Column C has 10% selected The new column D should show me $90 I don’t know how to create a formula which will calculate the discounted amount based on the selected field. I would be great to have some help on that! :slightly_smiling_face: Thanks, Sébastien

Hi airtablers I’m trying to create a business schedule table. There are ‘start time’ and ‘duration’(a lookup value from a different base) fields, and I try to formulate the ‘end time’ field by aggregating the first 2. For some reason the below formula gives the start time unchanged. DATEADD({Start time},Duration,'seconds') I tried to change the seconds to minutes and hours with same result. {Duration} also didn’t change. The formatting option “Use the same time zone (GMT) for all collaborators”, is disabled in both date fields. What am I doing wrong? Thanks

What is wrong with my nested if? It says that that It’s invalid IF( AND( {5dayswithnochange}, {issueStatus🟢or🔴or🟠}=🟢 ), "Stable ⚖", IF( AND( {5dayswithnochange}, {issueStatus🟢or🔴or🟠}=🟠 ), "Stuck 🆘", IF( AND( {5dayswithnochange}, {issueStatus🟢or🔴or🟠}=🔴 ), "Broken 🔥", "Too soon to tell ❔" ) ) )

Hi. I’m new to airtable, and fairly new to formulas. I’m trying to build a formula that looks at the part of an email domain and based on if a specific word exists or not, enter the persona into another field. Example Fields are EMAIL and PERSONA If the EMAIL domain is @usa.company.com and I’m checking if the domain contains “company” I then want to have the PERSONA be “employee”. If it does not contain “company” I want the PERSONA to be “customer” So far this is what I have IF((FIND(“company”,{Email Domain}) >=1)“Employee”, “Customer”)

Hi there I am wondering if there is a way I can have a field to auto-populate based on a previous answer. E.g. if they choose to attend EVENT 1, it will auto-populate that it is 3 days ‘Number of Days’ field, or if they choose EVENT 2, it will auto-populate that it is 5 days etc. Is there a way to do this? Thanks in advance

I need a formula to calculate ETA (more like Estimated Day of Arrival) for animal breeding purposes. I have a column with number of days bred and a column for the date checked. The average gestation period is 278 days. Is there a formula in which I could use the start date(ex: 9/11/2019), days bred (ex:84), and gestation length (ex:278) to calculate delivery day? Please and Thanks!!!

Hi all, I have a block with the following setup. For week 2 and 3 on the graph, the bars are clearly not grouping together, and even still, the group of bars in week 1 is very much left aligned. Not sure if this is relevant, but this block references a view of 9 records (filtered by if record is checkboxed) 3 of which are labelled with the same name (yellow bar), the other 6 are unique names (hence the different colours). The three types are: Single Select (Week in session) Number (Number of Rego’s) Single line text (Event name for block) Any ideas on why this the spacing on the bars is messing up? Thanks, Tom.

Hi friends, I’m new to Airtable A friend recommended this tool to me because of its adaptability to create data collections without the need for a custom tool. I have the following doubts, after I checked the airtable help tool create formula in the primary field. I took the sales crm template and just want to simplify it, only the relationships it brings are not what I need and after reviewing the help I still have problems. the idea is the following from the contact table to relate it to accounts just for one id (i don’t know if the autonumber function will work) The idea that if you can’t use autonumber, you could use email as your unique identifier. Then, the connection to the account table (which I will convert into services) will link it to the users’ email (one-to-one relationship) (as unique identifier) (the idea is that for each user you can have a one-to-many relationship; so, for each user by x amount of services). and finally, the table of opportunities (I’m going to rena

Hi! I currently use an embeded Airtable form on two sites, same form on both sites. I would like to know from which site a person filled the form. I have tried change the embed url of the embed code to an url that use prefill. From <script src="https://static.airtable.com/js/embed/embed_snippet_v1.js"></script><iframe class="airtable-embed airtable-dynamic-height" src="https://airtable.com/embed/ID?backgroundColor=green" frameborder="1" onmousewheel="" width="100%" height="6066" style="background: transparent; border: 1px solid #ccc;"></iframe> To <script src="https://static.airtable.com/js/embed/embed_snippet_v1.js"></script><iframe class="airtable-embed airtable-dynamic-height" src="https://airtable.com/embed/ID?prefill_Tips%20-%20GU%20eller%20BiU=GU?backgroundColor=green" frameborder="1" onmousewheel="" width="100%" height="6066" style="background: transparent; border: 1px solid #ccc;"></iframe> This simply results in the form not load

Hi. I have a database with veg and flowers in. For the flowers I have fields for colours, scented and evergreen. I don’t want these to appear in the veg details. Same as I don’t want ‘hungry gap’ fields with flowers (for example)… Is this possible or should I split the data? If is is, please could you give me a forumula example I could use.

Hi, I’m wondering if there’s a way to do this. I have a table with the category of a used book and the original price. I want to create a formula that calculates the selling price based on the category of the book. So it would be IF category = A then take Original Price and deduct 30% to get the selling price. I’m not sure if I can do all of this in one go with an IF formula. But maybe I can do part of it? Thanks for the help!

I have been using this formula: IF({Completed}=1,DATETIME_FORMAT(NOW(),‘M/DD/YYYY h:mm’),0) to record a date when a “Completed” check box is ticked off in another column. This worked flawlessly up until recently. For some reason, all past dates are now changing to whatever the current days date is. I have jobs months old that now say they were only completed today. Any help is appreciated. I can’t figure out what happened other than it is a new year and maybe that messed something up.

Hi all! New to Airtable here. We’re considering using Airtable for a few clients to serve as a CDP (customer data platform). In this case, we’re merging 4 data sources: 1) website conversions, 2) CRM, 3) contracts/invoices, 4) NPS/surveys. For all 4 of these data sources, there is 1 shared field: Unique ID. I had planned on posting each data source via Airtable’s very well documented API to a table. So, we have 4 tables in 1 base. The website conversion data source will generate a few hundred rows every day. My ideal scenario would be for these rows to auto-link across the 4 tables as a matching Unique ID row is added to the other tables. Example: Today, the customer converts on the website, and they are added to table 1, website conversion data. Tomorrow, they enter the CRM, and are added to table 2. A link between this customer row on table 1 and table 2 is automatically created. A week later they sign the contract and are invoiced, and are added to table 3. A link between the custo

Hello all, I have a table with filled with products each with their respective item numbers that are 24 characters long. I am looking for a way to put in the 24 digit number and have it automatically fill a separate cell in the line with the last four digits of that number. I need two columns one with the full 24 digit number and one with just the last four. I’m currently just manually entering them separately. TIA EDIT: Fount the solution https:// community.airtable .com /t/formula-for-extracting-last-text-digits-from-a-code/23215

Hi! I am clearly missing something easy on lookups and linked fields, but I have been googling endlessly so would love some help. I have a base with a table that has customer info like so: Customer A, Attribute123, Date Added Customer B, Attribute123, Date Added I am trying to use Zapier to trigger an action for all of those records added the prior day, en masse at once (e.g., if Customers A-C all were added on Monday, Tuesday at 9am they would get a message). My hunch is that if I could create Table 2 that just automatically updates, then I run a zap once a day for all new records in that table, I’m set. Any ideas or tips? I guess my asks are (1) can I save a view as a dynamically updating new table, or (2) any other ways you’ve seen this work? Thank you!

Hello everyone, I am wondering if there is a way to use the checkbox field to move lines to a new table once checked. I have a table of current inventory, I have a form linked to this inventory list so colleagues can see the inventory and request accordingly. Once the inventory is depleted I would like to just use the checkbox to trigger the line to move to a different table of depleted inventory. I have used the VIEWS function to filter the depleted inventory out of my view, but in this case the depleted inventory still will show up on the linked form that others use. I am currently just manually cutting and pasting the lines in the new table. TIA

Hi Everyone, I just can’t figure the formula thing out and I’ve spent hours on this, so am hoping that someone can help. Please if you respond - know that I’m not a programmer or formula savvy. I’m struggling with this. I have a column called Follow-Up Date. I create a column called Task Status. I want to do the following, so the Task Status column populates with Planning, Due Soon, Wrap it up, Due Today and Overdue based on date criteria of the Follow-Up date. If Follow-up Date is 2 weeks from the follow-up date based on today’s date, then I want the field to show “Planning” If Follow-Up date is 1 week from the follow-up date based on today’s date, then I want the field to show “Due Soon” If Follow-up date is 3 days from the follow-up date based on today’s date, then I want the field to show “Wrap it up” If Follow-Up date is today, then I want the field to show “Due Today” If Follow-Up date is past the date, then I want the field to show “Overdue” I"ve played with many m

Hey, would love to be able to not only get the cursor.selectedRecordIds, but also the selected fields, if any fields are selected in a grid view. Seems like there will be many use cases that would require such functionality. I personally have a few. Thanks, Ronen

Let’s say I have an item A. A was ordered at $10/pc with qty of 100 A was then ordered at $11/pcs with qty of 50 I have a table “Order” which keep track of qty and unit price at which the item is ordered. I made first shipment of 70 which is recorded in table “Shipment” I would like to have a summary table grouped by Item code. In this table, it will show that I still have to deliver 30pcs at $10/pc and another 50pcs at $11/pc. How can I do that?

I see from the feature requests that the ability to manually reorder views was introduced last year but I don’t seem to have it. When I try to drag a view up or down the list nothing happens. Using Firefox 54.0.1 on Windows 7

I am trying to make a formula that will create a status after a certain # of days has passed Scenario Client Makes Appointment Appointment date is added to the record The field called “Client Status” is changed to “Evaluating Proposal” What I need: IF: 7 days later the “Client Status” is still “Evaluating Proposal” then change the formula field to say “Follow Up Email OverDue”

What is wrong with this IF Statement?