Solved

Hi everyone! I was wondering if someone may be able to assist. I am quite new to Airtable and just getting my head around formulas. I am looking to create a formula to auto-update the STATUS of a client depending on how many jobs we have done for them. I have created a STATUS column which contains the three single-line field options of HOT WARM and COLD. I have another column which records the AMOUNT OF JOBS we have done as a number. COLD would = 0 visits, WARM = 1-4 visits, HOT = 5+ visits. Is there is a formula I could use for this? Thank you so much in advance :slightly_smiling_face:

Hi there I have created a table for a client who needs to record the dates visits are made to a site. We note the dates in one cell, separated by commas. We would like to use this data to view the total number of visits. Is there are way to count comma separated values in Airtable? Thank you!

I’m looking for a way to have a calendar view of upcoming birthdays. I can use the formula below to show the CURRENT year’s birthdays but I also want the calendar to show NEXT year’s birthdays so that in December 2020, I could look at the Calendar and see the January 2021 birthdays. I know I need a separate field and have the calendar us both fields but I can’t figure out how to add 1 year to the formula below for the second field., DATETIME_PARSE( DATETIME_FORMAT( BirthDate, ‘MMMM D’ )& ’ '& YEAR( TODAY() ), ‘MMMM D YYYY’ )

Hi, I exported a view to csv file so I could work on it and ID duplicates for some of my data. However, when I compare the csv export with the data in the table, the order of the rows in the csv appears to be random and does not follow the same order in my table. How can I export to csv and maintain the same order I have in my table? Thanks in advance

Hello Airtable community! due to corona pandemic in Germany the sales tax was decreased from 19% to 16 % during the period from 01.07.2020 to 31.12.2020 (DD/MM/YYYY). I have a column which shows the date of performance which is decisive for the tax rate. My formula is accepted by AT but it just seem not to calculate because the result is always 19%. My formula is: IF(AND({Date of Perfomance}>06/30/2020,{Date of Perfomance}<01/01/2021),16,19) Can you find the mistake I made? Thank you for your help in advance! Avi

I’ve set up a few automated scripting actions using the beta automations features but keep receiving the same error message when I try to run the automation. The same script runs without issue in the scripting block, so I’m not sure how to resolve this. Any pointers? I’m also a JavaScript novice.

With my business, I have WAY too many deals that come across that are unique and individual from others. I am in DESPERATE need to find a way to create a formula, (or simply type in a number for that matter) and NOT have it affect ANY OTHER record. Just change the formula on that one particular record instead of every single one. For example. Right now my formula to figure out commissions looks like this: IF({PPW}=“4.35”,500, IF({PPW}=“4.25”,450,IF({PPW}=“4.15”,400, IF({PPW}=“4.05”, 350, IF({PPW}=“3.95”,300, IF({PPW}=“3.85”, 250,0)) ))))*kW This morning we sold a deal at $4.79. So none of the formula triggers did anything and they didn’t calculate. However, I want to pay him $600 at that price. So if I typed in $600, it would change that cell for every single record to $600 and completely ruin all our accounting. This happens multiple times a week, and I’m fear I may have to go back to an excel spreadsheet where I can do individual cells like that. I REALLY don’t want to go back to exc

Hello everyone! I’m having significant challenges with getting an automation to run on a schedule. Airtable says: " Can I set up a script to run on a schedule using Automations? At the moment, there isn’t a built-in way to set up a script that runs on a regular time-based schedule (e.g. every day at 9am). A workaround is to use a time-based filter on a view." However, the time filter doesn’t seem to work very well for more frequent than daily. I was able to get ~ every minutes, but I was hoping to get something more real-time. To test the concept, I made a table where the first column is the “age” of the record using the following formula: DATETIME_DIFF(NOW(),CREATED_TIME(),"seconds") I then set up the automation to trigger when a record enters a view which filters all records newer than 10 seconds. The automation adds a new record to the same table. In theory, the automation should be running every 10 seconds continuously. But, that formula only updates every 5 minutes or so. I’m

I am building a database and keep a history of the membership for every year. For example look up a member to see if he was a member for the year 2015. so, how do I set this up please. Membership Yearly History

Hi all I’m really struggling with this - where is @Bill.French when you need him :-), Bill I need you! (or anyone else that can help please). So I have a view based on my orders table that filters unfulfilled orders I have written some code (with the help of others) that loops through the unfulfilled orders and creates new records in the Prod Req (Producction Required table). This table contains the product sku as the primary field and has a Linked Orders field (linked record field) that contains all unfulfilled orders with their order numbers along with some rollup fields. This code works well but it will create duplicate records in the Prod Req table each time it runs. Ideally, I’d like the code to first compare the Unfulfilled Orders against the Prod Req table. If it finds the same sku in there, then add the associated order number/order record.id to the Linked Orders field. If the sku does not exist in the Prod Req table, then add it and also the linked orders. Here’s the code… le

When using a form to gather input to a linked record field, is there any way to show the end user a field other than the primary field/column? Case use: I have a CHECK IN form that my workers can use to check in when they begin their job. The ‘Name’ field is linked record to my WORKERS Table, where Worker Name is the primary field/column. SO when they visit the form from their phone and fill out the ‘Name’ field on the form, they are essentially choosing their names off the list populated from the WORKERS table. That part is perfect. But the second field on the form asks for ‘location’ - I want that list populated with entries from the ‘location’ field/column in the JOBS table. But I cannot link to a specific column it seems, only the whole record, which means on mobile form, they only see the primary field/column info, which is job number. Is there a way to get it to show the ‘Location’ data instead? Besidies either… a) making ‘location’ the primary field/column in the JOBS table b) c

Hi, while this Airtable Scripting is a good start. Is there any way to get the last logged in date or update the sharing permissions the collaborator might have on the base? Thanks, Nigel

Hi! I would like to use the WORKDAY function to count the number of working days from an input date, but I would also like to include the input date as well. For example, if I count 6 working days from 19-Aug, the WORKDAY formula would count until 27-Aug. However, I want 19-Aug to be included in those 6-days – so by right, 6-working days from 19-Aug, including 19-Aug itself, should be 26-Aug. How do I include that in a formula? Thanks in advance for any help! :grinning_face_with_smiling_eyes:

Hello, I’m trying to calculate an hourly rate using a duration field and a currency field. I have a formula field that calculates $/hr: ({Earnings}*3600)/{Hours Worked} However, I have a few records where I don’t have the number of hours worked, just the earnings amount. The formula calculates this as “infinity,” which messes with my sum of the entire column. Is there a way to tell this formula to leave the cell blank if there’s no hours worked?

When using Sanity it’s really important all of our entires in our base have unique id’s not just on tables but in the base itself. Are the id’s for the entry unique to the table or the base?

what is the simplest way to convert a text field which is the three letter month '-'two letter year (Dec-02, May-18, etc) to a usable date field of the same format. The day of the month isn’t important, could be set to 01.

I need to be able to calculate the number of working days between two dates determined in “Start Date” and “End Date” columns. I know the DATETIME_DIFF formula exists, but it includes weekends and public holidays – I need only working days to be calculated. Is this something that can be achieved with a Script block?

I would like to capture the record number that appears on the LHS of each record in a particular view in a field (when you hover over the record this number turns into a check box). I want to print labels for each record, and I want to have a field which shows what number the label is out of the total records in that view (i.e. “3 of 10”). Or is there another way to calculate the order of records using formulas? Any help would be much appreciated!

I would like to know how to Count records from different table with specific conditions? For example I have a table called “Stats” where I would Like to put some spesific information (automatically) for example number of boys and girls (based on another table called Students with a field called sexe) ?

Hi! I have a rollup field (using ARRAYUNIQUE) that should always produce a single array value. I want to pretty it up and remove the encompassing quotation marks, but I can’t seem to figure it out. I’ve tried all of the following formulas, but none of them seem to work. SUBSTITUTE({Vendor Rollup}, '"', '') SUBSTITUTE({Vendor Rollup}, '/"', '') SUBSTITUTE({Vendor Rollup}, """, "") SUBSTITUTE({Vendor Rollup}, "/"", "") Can anyone help me shed some light on my issue?

Is it currently possible to limit the selection options based on the single select conditional field? Example: Single select is ABC or DEF, which makes the next multi-select field visible based on the ABC condition being met. Can I limit the multi-select options based on the ABC selection?

Hi, We have a base that, amongst other things, generates requests and records when the request is completed. To provide feedback on performance, we measure the number of working days it took to complete the task using: WORKDAY_DIFF({Request Date},{Time Processed}) We can then use a Summary Block to give an average “headline" of the number of working days taken to complete. We then have then used the following to report the average during the previous month: IF(DATETIME_FORMAT({Request Date}, “YYYY-MM”) = DATETIME_FORMAT(DATEADD(NOW(), -1, “month”), “YYYY-MM”),WORKDAY_DIFF({Request Date},{Time Processed}),BLANK()) So here, during Aug, it will give the average for July. How can we have this a little more “live” by reporting the number of days taken to complete over the past 30 days?

Months Active is a formula that returns what appears to be a number. IF({First Gift Date}, DATETIME_DIFF({Last Gift Date},{First Gift Date},‘month’),"") Number of Gifts is a count field I’m trying to create a field that returns an frequency {Months Active}/{Number of Gifts} But it is throwing an #Error What am I over looking? I’m pretty sure it has something to do with the DATETIME_DIFF returning a date and not a number

Hi I simply need to add some records to a table into a "Linked Record’ field. I struggle to understand why it’s so difficult. I have this VIEW: and I want to add single and concatenated ‘Linked Orders’ via a script. This my array of records I’d like to add… Here is my code and results… full code let table = base.getTable("Daily Orders"); let ws = base.getTable("Production Required"); let view = table.getView("Unfulfilled Daily Orders"); let sku = table.getField("Line Items: Sku"); let ordernum = table.getField("Order: Name"); let currsku = ""; let ordstr = ""; let result = await view.selectRecordsAsync({ sorts: [{field: sku, direction: "asc"}] }); output.inspect(result); let ordersArray = []; for (let record of result.records) { ordernum = record.getCellValue("Order: Name"); currsku = record.getCellValue(sku)[0]; ordersArray.push({ fields: { "Order_Num": ordernum, "SKU": currsku }

I have built a dashboard pulling in data from 2 different views, both of them filtered by a formula field that prints out the month. For instance, if it’s August the formula returns 8 and I manually filter both views by number. Once that’s done my blocks get updated and I can see the monthly dashboard. I would like to achieve this dynamically from the dashboard by having an input field/dropdown, in which I would just type in the number to filter the view, rather than having to do this manually in the view itself. I’ve looked at the Views API but I can’t find anything that would the job. Is this achievable?