Solved

Hi AT Community, I'm new to Airtable and ’m trying to create a formula that would display the Monday of the week / "week of" date for a given date. Monday is the start date of the week. I've tried formulas from other posts but when I add the formula to my field, it gives an error.The formulas I've tried are:DATETIME_FORMAT({Date},‘e’)+1,‘days’),‘M/D’))--   CONCATENATE("Week of ", DATETIME_FORMAT(DATEADD({Date},-DATETIME_FORMAT({Date},"e"),"days"),"M/D"))    --DATETIME_FORMAT(IF(WEEKDAY({Date Due})>1,DATEADD({Date Due},-WEEKDAY({Date Due})+1,'days'),IF(WEEKDAY({Date Due})<1,DATEADD({Date Due},-WEEKDAY({Date Due})-6,'days'),{Date Due})),'l')I really just want to get the Monday of the week when the record is created. So if the record was created on, say, January 19, 2023 - the Monday of that week would be January 16, 2023. And the field would display exactly "January 16, 2023."Thank you in advance! 🙏

Hi there.In a field, I have used the formula below: DATETIME_FORMAT({Datum gesprek}, 'DD-MM-YYYY HH:MM')&", "&{Traject begeleider} Weirdly, the outcome makes the time change by a few minutes. I can't figure out why. Setting timezone, or changing other aspect doesn't help.  Could someone give a solution for this? Thank you!

I am working with a subscription table and every row is an entry every time a customer subscribed to an account (It is possible for a customer to have more than 1 account). I have a field for Start Date and End Date, and I want to know the maximum account the customer had at some point, so I am thinking that if I would know which dates overlapped, I can then record it to a different table but is there any formula in airtable  where I can do that? 

I have the followign problem.In a cell I have a list of tags, separated by a comma, i.e.Apple, Banana, GrapeThen I have a control list of allowed tags, that isApple, Banana, StrawberrySo, the formula should eliminate the tags Grape is it is a now allowed tags, and return this resulApple, BananaAny ideas on how to obtain it?Thank you very much in advanceRegards  

I am trying to replicate something that I know I did previously, but cannot find the right options to do it now.I've created an Interface with a page layout that is designed exactly as it needs to be.  (Record Review, but I don't think that matters.)  It is drawing from Table X, with a filter for only records that match Y.I want to duplicate that page exactly, and then change the underlying filter.  So, this page should be identical except that it's drawing from Table X, and filtering for only records that match Z.  (I do not want to add a filtering element that can be toggled by the user of the Interface.)I did this easily about 9 months ago, but cannot figure out how to do it now.  I can duplicate a page, but cannot find where I change anything about the underlying data or filtering.  Anyone know how?  Thanks! 

Hi,I receive information from a webhook trigger at the beginning of my automation. I want to apply conditional logic based on the values of certain variables in the webhook data (in this case, I'm using Stripe to trigger this Airtable webhook). If you examine the structure of the data contained in the webhook, you'll find that most of the useful variables are located in Body > Data > Object > ...However, when i try to set the condition in the Conditional logic block, I can't have access to the variables in Object (there is no little arrow ' > ' on the right)... Is this bug ? Is there a workaround ?Thanks for the help,LF

I have two tables: Table 1 with companies and their data and table 2 which is synced from box that has the details on company logos as png images for each company. I have managed to use airtable sync to pull in the logo images from box to airtable - table 2. However now I want to copy those images to table 1 that has company data.  Basically the idea is to match the company names between table 1 and table 2 and then copy images from table 2 to table 1 if there is a match.Is there a way to automate this?If I had to make use of lookup fields, how should I go about it?Thanks!  

Hello - Situation: Volunteers on the team have a 2-year tenure and when those 2 years are up, they can opt-in for 1 more year. My table shows their "start date" and then I have a column showing their "end date" using this formula DATEADD({Current WON Role Start Date}, 2, 'years') and have an automation that alerts me 60 days before that end date.  Need: What I'd like to happen then is after the Opt-In option is discussed and if the volunteer decides to add 1 more year to their volunteer work, a formula runs that gives a new end date.  Based on: What that said, I'd want the formula to run only if YES is selected from a drop down. Then I would assume we can take the tenure date (original end date) and add 1 year to spit out a new end date.  I'm not great at formulas and tried - IF({1+ Year Opt In}='yes', DATEADD(Tenure, 1, 'years'), " ") It doesn't give me an error but also doesn't populate a new date. Any insight?

Is there a way to mirror information from a lookup field into an adjacent long text field? I'm using Diagram Automator and the "insert image" function can't read/populate a URL from lookup field only a text field.Maybe a formula or script that could be used as an automation?

I have a base used for tracking waiting lists for multiple events. A simplified version of the base for purposes of this question is here. Each record is one person’s entry on a waiting list for a specific event. Each record has a Status that starts out as “Waitlisted”, and then we change the Status to other values as we clear people from the waiting list for an event. We normally use a view that groups the records first by Event Name and then by Status, with records sorted by the Created date/time. This is the first view shown in the demo base. We take people off the waiting list for an event in the order that they signed up for the waiting list. We often have people ask us, “what number am I on the waiting list for X event?” Rather than having to manually count rows (which could be 100 or more) to answer this question, I want to have a field that shows each person’s sequential position on each event’s waiting list, based on the Created date/time. The mocked-up field called “Position

HiI have two tables: Table A that has a period_start_date and a period_end_date and Table B has a bunch of rows with a date columnIn an automation that's triggered by a particular row on table A (which means I know the period_start_date and period_end_date to use) I want to find all the records from table B where the value in the date column lies between the period start and end.The "Find records" automation seems to allow only static date values, but I need to use the period start and end dates from table A.Any suggestions on how to do this without having to fetch all the rows from table B and then loop through in code, which feels very wasteful and slow.Thanks in advance

I want to create a "Kudo board" where employees can select from a drop-down list (single select) of company employees they want to give kudos to.My thought process is I would like to create a form that includes a list of employees' names from a drop-down (single select field) and a long text field, to add the kudos. When they "submit," it will populate a table with the employee name and the kudos received.If I want to add a  single select field under forms, is there a way to copy/paste in a list of employee names? I don't want to type them out one by one.Note when you are working directly in a table, you caneasily copy/paste a list of names directly into a single select field. Here's a video that demonstrates how you can do this in a table: https://www.youtube.com/watch?v=uzCh020xracHowever, there does not seem to be an easily way to copy/paste a list of items into a single select field if you are working under "forms."If anyone knows, it would be much appreciated!

Hello. I've been working on a database template to share with other organizations doing similar work. I've recently published that template to the Airtable Universe, but two views are not transferring correctly. These two views are filtered views, and they are filtered based on the values in a lookup field. They are working in my base, but in the published version, the filter remains, but the values are listed as "unnamed position." Those look-up fields are themselves looking up data from a linked field that meets certain criteria. I'm assuming there is something that is breaking in all the steps necessary to carry this over. Any ideas?

  console.log(`Starting...`); let table = base.getTable("Imported table"); let inputConfig = input.config(); let last_name_array = inputConfig.last_name.split(' '); let formatted_first_name = last_name_array.shift(); let formatted_last_name = last_name_array.join(' '); console.log(`Customer ID: ${inputConfig.customer_id}`) let response = fetch(`https://api.shop-ware.com/api/v1/tenants/xxxx/customers/${inputConfig.customer_id}`, { method: "GET", headers: { 'X-Api-Partner-Id':'xxxxxx', 'X-Api-Secret':'xxxxxx', 'Accept':'application/json', 'User-Agent':'PostmanRuntime/7.34.0', }, credentials: 'include', }); let body_response = await response; let json_response = body_response.json(); console.log(json_response); await table.updateRecordAsync(inputConfig.record_id,{ "First Name Formatted": formatted_first_name, "Last Name Formatted": formatted_last_name, "Business Name": inputConfig.first_name, })  The last console.log returns an empt

Hello,   I am a commercial insurance agent and have create a pipeline report. I have a working formula that tells me how many days I have left before a policy needs to be renewed - DATETIME_DIFF((WORKDAY({Due Date},0)),TODAY(),'days') is there a way to show positive and negative numbers in different colors?ExampleThe Smith liability policy is up in 30 days when I enter it. So every passing day the countdown decrements. That works fine.But I use the same date field for new policy so if Mr. Jones was a policy it was show a negative numbers on the second day since I use the current day when I first get the file.The idea is to glance at the table and see what is most important.Any feedback would be welcome.  

It seems this may not be a robust as I’d hoped. I have set up project and task tables. I would like many tasks to one project. I went to tasks table, created link to projects with “allow linking to multiple records” disabled. This works great for assigning a task to a project. If I now go to the projects table I can still assign an already assigned task to a different project and this then carries back through to ‘pollute’ the task can only be assigned to one project constraint I set up. Seems the many to one is not truly in the database but just in the selection phase of the app. Worst case I could hide the link field in the Projects table and replace it with a lookup showing the same thing - this will force users to initate the link in the tasks table where the front end entry is enforcing the one-to-many while still allowing view of tasks. Is this just a limitation or is there a way I can work around this? Thanks, Sam

Hi,I'm getting this error when trying to append to a linked field. The code in question is: ModelRange.updateRecordAsync(record, {'Models List': [...record.getCellValue('Models List'),{id : BMRecord[0]}]}); I'm pretty sure that 'record' is indeed a record object, as it's obtained from  let query = await ModelRange.selectRecordsAsync(); let record = query.getRecord(another[0]); where another[0] is a record ID. Let me know if the rest of my code is needed to diagnose the error, although I can imagine that I'm missing something obvious.Thank you!

I have a formula field that calculates a value (results a number) based on a lookup field (e.g. a date). When the lookup field is empty due to a missing input, formula field results with a NaN or ERROR.In that case, I want to highlight RED color to that record so operators understand that they should fix the missing input. Unfortunately, there isn't a way to enter a color condition statement such as "WHEN formula field IS ERROR, SET COLOR red".Is there a solution for this? @dashler78 

I have a button that I would like to trigger two tasks: 1 - trigger a webhook that passed the data to zapier (done) 2 - automatically update the value of a single select field to ‘true’ While the webhook script runs when I click the button, it seems that the other script I am using (to update the single-select) wants to take in a user input every time and it does not run automatically. Is there a simple way to set the value of a single-select field automatically to true without me having to specific the field and the value every time with a script?

Hi 👋 I'm not an expert with formulas, but I think I'm overthinking this / making it more complicated than it needs to be.I have a CSAT (customer satisfaction score) form in Typeform that is being automatically sent to Airtable. What I'm trying to do is:a) calculate the CSAT score (# of 4/5 star ratings, divided by # of total responses, multiplied by 100)b) have it calculated every calendar week (so there should be a new rating every Monday)I can do this manually for now, but I want a way that each week, people can look and see what the score is, without anyone having to manually calculate it.I'm totally lost, although I think I am overthinking this. Any ideas? &#x1f605I've attached screenshots of the set-up currently. We have two sources for the feedback form, so there's one table for each of those.Then I have the 'Score' table, which has 5 records, one for each rating (1-5 stars), plus another one for 'TOTAL' which I added in my efforts to calculate the score (it can be d

Hello,Want to strike through text in a cell if a given string is found in another cell in that row.  Is this possible?The cell whose text I want to strike through is already a concatenation of various other field values.  It's looking in a different field for the term "OMIT", and if that is found I'd like the text strike-through'd.The "OMIT" will be searched for in a single select field, but I don't think that matters since I've already successfully searched for and IF'd stuff based on single select text contents.TIA for any assistance.

HiI have some linked tables. The first link is between "People" and "Working Groups". When in the Working Group table I can look up people's names and add them to the field "Members". When in the People table, I can make a link back to Working Groups, and a field called working groups will self populate with all the Working Groups that people are members off. This is all working fine.I have a second link between "People" and "Training". When in the Training table I can look up people's names and add them to the field "Attendees". When in the People table, I can make a link back to Training, but I can't make a field that will self populate with all the training people have received. What am I doing wrong?The only difference in layout between Working Groups and Training, is that training's unique identifier is a date.Any light you could shed would be very helpful.Thanks,Davva

Hello, I am trying to write some automation that should call some function in our server through a script and pass as input the image data that is stored in some field. How can I pass the image data ?The url I get there is private and  inaccessible through the server. I tried to convert it to base64 but FileReader is not available, also to pass the binary data using FormData but it is unavailable as well .Any help will be much appreciated.Yaniv

For some reason I cannot select the text within my formulas, meaning I cannot choose which text to copy for pasting later. It is only within the formula editing that I cannot edit the text. I can select text everywhere else:Selected text on here:Can't select text hereOkay to select text here:  

I have these two fields Formula on Contract Yield % is this:({Contract Rent PW}*52)/{Purchase Price}I need to replace the formula in the Contract Yield, to an IF formula.More likely:When the result on Contract Yield is 0%,   If Contract yield = above 1%, use Contract yield formula, but if it's 0% I need to use Appraised Yield Can you please help me what is the correct formula to make it work?I already tried (IF({Contract Yield}=0,{Appraised Yield}but it's not showing the correct results