Solved

Hi everyone - I’m still very new to Airtable and ran into a formula error that I’m trying to solve. Ideally, we would like to group tasks by the week they are due. I was able to find the formula to output the week number but can’t seem to find one that works that will produce the following output: “Week of 06/21”. I did find the following two formulas from a previous post: Display the "week of" for a given date starting with Monday CONCATENATE(“Week of”," ",DATETIME_FORMAT(DATEADD({Date},-DATETIME_FORMAT({Date},‘e’),‘days’),‘M/D’)) DATETIME_FORMAT({Date},‘e’)+1,‘days’),‘M/D’)) However, when I go to add the formula to my calculated field it returns an error. Could it be because my {Due Date} field is calculated as well? This is the formula that I am using: CONCATENATE(“Week of”," ",DATETIME_FORMAT(DATEADD({Due Date},-DATETIME_FORMAT({Due Date},‘e’)+1,‘days’),‘M/D’)) The specific error is as follows: Sorry, there was a problem saving this field. Invalid formula. Please check your f

I feel this is super simple and I’m just not seeing something. If someone could put me out of my misery, it would be much appreciated. I want to send slack notifications when someone fills out a form. One of the fields is long text and asks for detailed description. I wanted to only show a preview of that field in the message. I see the “trim” option there but I don’t know how to actually use it! It’s not doing what I assume it should do. I am selecting + > the field (Description) > Continue > trim (insert) and it doesn’t do anything. When I do the exact same with any of the other properties (length, upper case, lower case, etc) it does react the way I would expect. Does anyone know how to do this? :thinking: See it not working here:

Hi Airtable Community! In my Airtable base, I’ve got two main tables: one for stories written by kids, and another for notes that volunteers write to the authors. These tables are related through a one-to-many linked record setup: there can be multiple notes for a story, but not multiple stories for a note. I’m trying to make two rollup fields: one that displays the text of the first two notes a story receives and one that displays the text of every note after the first two. I’d like to email notes back to authors after they have two notes and then again when they’ve accumulated several more, and would use each rollup field as part of the automation for the email body. The additional wrinkle is that the rollup should only count notes that haven’t been flagged (and therefore shouldn’t be returned), and there are handwritten notes too. My thought was to add an Autonumber field in the notes table–each note will have to have a higher number than the last. Then, the aggregation formula of t

Hi Community, I have a cruddy report that cannot give me the data i need which forces me to calculate the number of days a person has been helping our organization and I was hoping there might be a formula that can help automate the calculation a bit, but I cannot wrap my head around it (terrible with IF formulas). The report is run once per month and will produce 4 different combinations of dates for the in and out field. Combination 1, OUT date is prior to the start of the month, and IN date is after the last date of the reporting month OR blank . This calculation would then be the same as the number of days in the month. Example for May (OUT is 4/20/21 and IN is 6/5/21 or blank, would = 31 days) Combination 2, OUT date is prior to the start of the month, and IN date is within the month of reporting. This calculation would be the datediff between the IN date and the last day of the previous month. Example would be (OUT 4/20/21, IN 5/14/21= 14 days) Combination 3, OUT date begin

Hello community! This is my first Airtable script. I’ve been proud so far but there’s a detail that I cannot crack. The situation is: I’m generating a dynamic chart via quickcharts.io using Airtable fields and I want to create a .png file from it to send by email. So I created a formula field that generates the appropriate URL for the chart, this works perfectly. Then, I created a script that runs upon that URL being updated, here’s the kink. The script is based on a similar Airtable script which takes a URL and creates a file in another column on the same table. The issue is that the ´filename´ is not being updated. It creates the file perfectly, but the filename is taken as a part of the URL which is unreadable and doesn’t contain the file extension. Here’s my take: let table = base.getTable("Table 5"); let attachmentField = table.getField("Attachments") let inVar = input.config(); let url = inVar.Attachment_URL; let updates = []; let attachmentUrl = [{url: url, filename: 'Chart.pn

Hey, I’m facing a specific issue in Airtable Automations: Background: I’ve created a automation to do the following: Trigger: Receipt of Webhook Action: Update a Record in Airtable. The trigger is working fine, however the action is continuously failing with the following error: “Received invalid inputs.”. I’ve taken the usual steps of: Re-test the Trigger and then re-try the Action Re-configure the fields to be updated in the Action Check the field types that need to be updated in the action. Does anyone have experience with what could be the issue here? (Screenshot attached)

Hi everybody, I’d like to check if an attachement field is empty or not. Someone knows how to do this ? Tanks a lot. Fred

Hey guys and girls. Do anyone know of a way to change the status/state of linked records when a record enters a view?

Desired outcome: When someone enters our blood donation center (no appointment needed) the front desk looks them up in the donors table and changes their “last visiting date” to today. I want to add a record that shows their new visit and insert the changed date into a date field in a (growing) “visits” table. I created a simple automation: When record is updated in “donors” table, create new record in “visits” table. Date Field to watch: “visit date” (this is the last date a visitor arrived) Action: Create new record in different table and add “visit date” into date field. Weird/unexpected behavior: the date added is 1 day earlier than the chosen date.

We´re a learn platform with memberships. Each mempberships has its values in numbers. I need to replace/substitute those values into another values. This is what I want: 9=1 19=2 39=4 99=11 This is my formula: SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({FOX XP AMOUNT},“9”,“1”),“19”,“2”),“39”,“4”),“99”,“11”) This is what I got: I think I need a solution to get the Formula know or read two digit numbers together. But I don´t know how. Would love if somebody could help me please.

Hi, I have a rollup field in a table, and I want to limit which linked records are included in the rollup by a condition. Specifically, I am looking to get “this week’s data”. In my table where the rollup resides, I created a field to use the Weeknum() function and get the number of this week. Then, in my linked records I also have a Weeknum field. So, in the rollup, I want to only sum those linked records where the Weeknum field value in the linked record = the Weeknum field value in the table where the rollup field resides. But it doesn’t seem I can do that. I would have to update the condition each time I need to pull or review the weekly data and manually set it to the number of the week I’m looking for. Any ideas on how to accomplish what I’m trying to do? Thanks in advance!

Resurfacing the discussion from Reload app on useRecordActionData hook since it was never really solved. I have the same issue. I’ve tried useEffect() and useState() to store and update the state, but Airtable’s button action appears to generate recordActionData one time. So, in my case, I can get the record data, create a new record on a separate table and attach it to the original record via a linked record field, and expand the new record for editing, and I report back to the user with a message indicating the name of the newly created record. That all works, but the app itself just stays on that final display state, saying the name of the new record. When the user hits the button again, nothing happens. No new recordActionData is sent, so the app’s useEffect function is not triggered again. Here’s the bulk of my working code: function SourceTrackerApp() { const recordActionData = useRecordActionData(); if (recordActionData === null) { return <Box padding={2}

Disclaimer: I’m moderately new to Airtables. I am working on a base that tracks commission earned for our sales department. Essentially there are two separate commissions rates that are earned: a pre-threshold rate and a post-threshold rate, where the threshold is an amount of commission earned (for example, the threshold could be $100,000, and the salesperson earns at a commission rate of 5% before they reach the threshold and at a rate of 3% after the threshold). I’m trying to write a formula that determines which commission rate to use based on the amount of total commission earned, but I keep getting a circular reference error, which I believe is caused by the fact that the total commission depends on which interest rate is used, causing the loop since both of these fields are dependent on each other. Does anyone have any advice on how to fix this? Sorry if the description is too vague, I can provide more information if need be. Thanks!

I created a formula to add 3 weeks to a date that I capture elsewhere in my table, and added an IF blank statement, so if there is no date it won’t show anything: IF({Date Intake Completed}=BLANK(),0,DATEADD({Date Intake Completed},21,‘days’)) The formula is working, but the output format is not what I’d like: 2021-05-19T00:00:00.000Z, I would prefer 05-19-2021, or something like that. When I try to change the format though, I get this message: Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date. The output is a date, so I am not sure why I am not given the option to update the format. Appreciate any help! I am new to Airtable and this is my 1st formula, so hoping I can get it working.

I’m tracking properties that are on the market and haven’t sold. I can pull a data source which gives me “Days on Market” as a number. I can then use DATEADD() to subtract the num of days from the date entered 6/13/21 in this case, to get the start date of the listing. But there are cases where I know a property is being sold, but I don’t have a date it first hit the market, so it results in an error. Not sure if this will be a problem, but I tried to use an ISERROR formula but wasn’t able to get it working.

How do I test for an empty Message field when it’s a “Long text” type? This code does not work… let strReplyStatus = ''; if (rsReply.getCellValue('Message') === '') { strReplyStatus = 'Empty'; }; if (rsReply.getCellValue('Message') == '') { strReplyStatus = 'Empty'; };

Trying to update additional fields of an updated record accordingly. Getting “undefined” value for recordId. What would be the problem? let recordId = input.config().recordId; console.log(recordId); let rootTable = base.getTable('uretim-kaydi'); let rootQuery = await rootTable.selectRecordsAsync(); let record = rootQuery.getRecord(recordId);

Hi there Airtable community! I’m having trouble with an IF statement. I have a lookup column with two values, Turned or H/L. If the value is Turned, I want my formula to add the values in each of the shoe sizes listed. I’m getting an ERROR every time I try it though. Is it because the value (H/L) is a lookup value? The formula saves correctly, but the resulting value is #ERROR! Screenshot attached too… thanks for your help :slightly_smiling_face: IF( SEARCH(“H/L”, {Toe Puff}), SUM({2},{2.5},{3},{3.5},{4},{4.5},{5},{5.5},{6},{6.5},{7},{7.5},{8},{8.5},{9},{9.5},{10},{10.5},{11},{11.5},{12},{12.5},{13},{14}) )

I know that these types of questions have been asked several times here, but I haven’t been able to replicate suggestions from other threads to get the desired empty cell for my formula. Thanks in advance for any assistance! My formula is: DATEADD({Associated Event Date}, {Days before/after event date} * 1, ‘days’) some of my tasks do not have a value for the {Days before/after event date} field. In these cases, I would like for the cell to show up empty rather than show #ERROR. Please help :grinning_face_with_big_eyes:

That is probably a very confusing title, I will do my best to explain what I want but I am pretty new to coding and Airtable. I already have a Grid View with multiple fields, and I am using the Forms View to add to my Grid View. In my Form I have a multiple select option where I can add multiple pieces of equipment that will be assigned to one project. However, for my Gantt Views I need the Primary Field to only contain one value. Right now I am using a formula to define my Primary Field. I think what I need to do is create a script that will extract the individual values of the array and assign them their own records. You can see from the images that not every record contains an array. Just for the sake of clarity, my Forms View updates the “Equipment # and Description” field, which has the lookup field “New # (Don’t Use)” which is what is used to define the Primary field. Not really an elegant solution but it is what I have so far. My Forms View: My Table, with primary field formula

Hi I managed to get a button to run a script to populate a linked field with a unique value, thanks to the exellent advice by @JonathanBowen The link to the topic is here As the populated field only ever has the value pasted by the script or is empty, I wonder if I can adapt the script to only run if my target field is empty and display a warning and abort if it isn’t? For ease, here is the script… let repoT = base.getTable('Repo'); let sourceT = base.getTable('Source'); let record = await input.recordAsync('Pick a record', sourceT); // let name = record.getCellValue('Part 1') + '-' + record.getCellValue('Part 2'); let name = record.getCellValue('Data'); // create the record in the repo table let newRepoRecord = await repoT.createRecordAsync({ 'Name': name }) // set variable for today's date let now = new Date().toLocaleDateString("en-GB"); //note that newRepoRecord is the record ID of the created record // see this by logging the result: console.log(newRepoRecord); // n

Hello :slightly_smiling_face: I am a beginner in the creation of Airtable formulas. Is it possible to create a formula IF(AND(value1,value2,value3) with at least 3 conditions ? I only succeeded it with 2 conditions. If it’s not possible, do you know how i can make it with others formulas ? Tx u very much for your help/time

Hello Everyone! Would greatly appreciate any help on this matter. I have a base with two rows: people can enter their name and a date. Is there a formula I can use in the third row which would check how many entries are recorded for the same date, and could show a message along the line of: “Too late, there are already 8 entries with this date”. Thank You!

Hi all, I am building a sort of ‘attendance list’ which is displayed over a calendar, all I want from it is so that people are able to add their name, and a date in which they will be attending. I do however, want to limit the number of records per day. For Example: If there are already 10 people with the date 09/06/2021, I would like to either not allow another person to add an entry with their name and this date. A warning message would also suffice, in the way of having a last column, and once a person tries do add himself to the a date that has reached its max amount, the term “unavailable” would appear in the last column Any idea on how to approach this situation? Thank You!

Ive looked at numerous other community posts but don’t seem to be understanding this. I want to update a linked field with a room location. The location is being provided by users selecting it from a list via recordasync, and I’m storing it as a string with .name. This unlinked field updates without issue: sourceTable.updateRecordAsync(recordID,{'nonlinked location': roomName}); but i can’t get the linked table to update no matter how I format the portion after the :. I’ve tried sourceTable.updateRecordAsync(recordID,{'linked location': [{id: roomName}]}) and other variations of that to no avail. I believe it has to do with the location being passed as a string to the linked array, but I’m not sure how to format either the input or the updaterecord to accept the change. The script is meant to accept a room choice, then the user enters an Item Number and the script updates the location of that item with the room chosen in step 1. let location = base.getTable("Rooms"); let sourceTable