Solved

I’ve tinkered with a few ways of doing this, but wanted to get some ideas about the most efficient way. I have this full address string: Street address: 1600 Pennsylvania Ave NW # 15 City: Washington State: DC ZIP Code: 20500-0003 I want to extract the State (in this case, DC). I’ve tried LEFT, RIGHT, MID, etc but nothing seems to zero in on it. Appreciate the help :slightly_smiling_face:

There are more fields being returned than in my View through the API. Any suggestions on what could be the issue? Regards, Ruchika

Hi everyone! I have done several searches and cannot find exactly what I am trying to do. I am relatively new to all of this and my ADHD brain struggles with all of the technical stuff at first, so I apologize if this is very obvious. I want to simply have a “total hours” field that takes the start and end date/time fields and puts it in hours rounded to the nearest quarter. 2/27/2020 8:00AM to 2/27/2020 9:45 would automatically compute to 1.75. This is for a timesheet :slightly_smiling_face: Thanks!

Hi There! I have a series of tasks with deadlines. The field next to “Deadline” is a countdown titled "Days Left’ with the amount of days left until the task is due. However, I don’t yet have/need deadlines for some of the tasks, so the Deadline field is sometimes empty which results in NaN. The formula I used for “Days Left” is DATETIME_DIFF(Deadline, TODAY(), ‘days’) I’ve tried a few different IF formulas to remove NaN but I keep getting errors. Can anyone help me out here?

Hello, I’ve a Linked Table with many entries which are populated by this linking feature. I’d like to add a field that counts all linked entries belonging to that entry. All I could find was the build in Count feature, but it’s only applicable to 1 entry. I’ve been trying different formula combinations, sum, lookup, count, counta. no success yet… How to calculate this?

Hi all, First-time post here! so a big hello! This is potentially a newbie question… I’m trying to stack my based column in the Kanban view but the option isn’t appearing? Based is a multiple select list of local authorities I did screenshot but it’s not letting me post them… Any help be much appreciated Thanks

I’ve seen a couple of different places where other users encountered the same issue when trying to use a simple multiplication formula (it simply does not work gives #ERROR! I am using the asterisk. I’ve read every piece of information available and still am not finding any resolution. I’m going to be pretty pissed if after all the work I’ve done I have to abandon this because of this error in a very basic formula. I used ask a question already and no response yet. I kind of need to get it fixed or move on to something that actually works. I thought I had finally found a gem :frowning:

Hi, I’m using Filter By Formula to find records based on Last Modified Date; As per Codepen Airtable URL encoder I get the following syntax; (IS_AFTER(%7BLast+Modified+Date%7D%2C+%222%2F23%2F2020%22)+%3E%3D+0) While syntactically this works fine, the results I get are not correct. I get dates before the given date too. Any suggestions? Regards, Ruchika

Hi, When I access a table-view through an API, fields that don’t have any values are not returned for that record in the API. Is this expected behavior? Regards, Ruchika

I am asking for some advice. I am working on a problem that I have with my Base. That problem is that I am attempting to automate data updates to one table from 4 other tables on a weekly basis. The puzzle is that I am tracking events that occur on each of these 4 tables on a weekly timeline. These events are by employee, in the case that they incur a trackable event on that week. We are tracking these events on a week (or month) per the time stamp on each occurrence. Each event results in either a positive or a negative score. This score is then subtracted/added to/from a credit on a different table. I have run into two problems. First, I tried to roll up each occurrence on the Employee directory. Rolling up there created an accumulation of points, not tracking by week. I then did a look up on the table with that person’s weekly stats. That repeated all the scores from each week to every week thereafter, making that occurrence manipulate their score every week instead of just the week

Hi, I am looking for a way to input Timecode in a Field, (ie: 12:24:32:00) (HH:MM:SS:FRAMES) The Duration type comes close but no option for double digit hours, and regular numbers don’t seem to allow colons. Anyone have any ides. Scott

Hi there, I’m trying to create a “ToDo” base with sub-tasks. I have one table with different “project” in every row, and second table with a few tasks linked to each project. Now, I want to create a rollup record to pull out the percent of the task that is already done. (like if I have 2 tasks for one project, and one is checked, so it’s 50% done…) How can I do it? Thanks!

Hello, Can you please help me : someone did something wrong in the account and delete a lot of customized fields. I see that I can restore an old version but that creates another version I think (and I am the one who can access in). But I would like to restore the latest version for everybody, is it possible ? Or do I have to open the access to the version restored to all the base ? Thanks a lot for your precious help!!! Justine

Hi everyone! I’m surprising myself by not being able to figure this out. I swear at one point or another I had been able to pre-fill an airtable form with a field that includes an emoji. For example, I want to pre-fill a single-select {Status} field in a form with a status of “ :calendar: Consult Scheduled” I’ve tried it with the actual emoji character as well as with UTF-8 (hex) encoding (which is what Airtable spits out if I wrap the emoji in the ENCODE_URL_COMPONENT() function - see below). https://airtable.com/shrIXXXXXXXXXXXXX?prefill_Status=%F0%9F%93%86%20Consult%20Scheduled https://airtable.com/shrIXXXXXXXXXXXXX?prefill_Status=📆%20Consult%20Scheduled Neither of these work. Am I barking up the wrong tree? Should I just forget about the emoji? Maybe try a different emoji? :slightly_smiling_face:

I have 10 checkboxes in my table. Each checkbox corresponds with a task that needs to be completed. I’d like to build a formal that shows an emoji each time another checkbox is checked. For example, if you’ve checked 4 boxes, there would be :white_check_mark: :white_check_mark: :white_check_mark: :white_check_mark: . Or, if you’ve checked 10 boxes, there would be :white_check_mark: :white_check_mark: :white_check_mark: :white_check_mark: :white_check_mark: :white_check_mark: :white_check_mark: :white_check_mark: :white_check_mark: :white_check_mark: . Thanks in advance.

I have a column of linked records. I would like to make a second column that filters those linked records based on a condition. Example: Table 1 Column A is a checkbox Table 2 Column A links to records of People that are associated with the Business Table 2 Column B should be a conditional subset of Table 2 Column A; linked records in Table 2 Column A should only appear in Table 2 Column B if the linked record has Table 1 Column A checked. Any time a checked record is added to Table 2 Column A, it should automatically appear in Table 2 Column B. Similarly, any time a record is checked or unchecked in Table 1 Column A, it should be added or removed from Table 2 Column B. From what I can tell, this is not possible in AirTable. Are there any creative ways to make it happen?

Hi there I am using airtable as OPS CRM Tool. Basically the Customer makes a Request (REQ-001) with description of the products he is looking for. Airtable then matches the description and link all PRODUCT MODELS with that Request. So on the tab REQUESTS it links all the models from the tab PRODUCT MODELS that fit that request. I would like to Batch download the Images of those products per Request (To make it easier to send to the customer, currently we are doing it manually). but there isn’t yet a built in solution for that unless you use Zapier. That said, I need create a column called “Pictures” on tab REQUESTS and link that column with the column “PICTURES” of the tab PRODUCT MODELS and get all those pictures there. So then I can use Zapier and batch download it per request. Is this possible? Thanks a lot!

Hello, I am working off the social media calendar template from Airtable and for the life of me I can’t figure out how/where to change the “content type”. I’ve tried doing it from the different views and it is just not obvious to me. Thanks heaps for your help!

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