Skip to main content

Hi all, I have been struggling to figure out a way to solve this all day. I am wanting to track checked out laboratory media in an interface graph so that team members who create the media can see how much is being used per week. I’d like this to be automated for the previous week, but specifically Monday through Sunday of the previous week (so that you can check any day of the current week and the numbers would remain the same for the previous week). My plan for this was to set an automation trigger for early Monday morning, that then finds all records within the last 7 days (all checked out items have creation dates + times for the check-out record), and then marks them with a date for the previous week’s Monday, using the Update Record command (which would be found using a formula field consisting of DATEADD(NOW(), -7, 'days'), which will return the previous Monday (because the trigger to pull from this field is occurring Monday morning). The interface would then pull from values with the previous week’s Monday date. Here is the automation view: 

As you can see, the list of record IDs is being used for each record that matches these conditions. The error I’m currently struggling with is that I’m getting an invalid input error. This occurs even if I change the field to static and input a test date, so I believe it’s an issue with the record IDs. I’ve also tried this in a repeating group and using the list of records as the list to repeat through, similar issues. Any help with this would be greatly appreciated. 

Just from your screenshot, it’s returning invalid inputs because you’re putting the Record ID into a date field, which is won’t do. You said though that putting a static date to populate in the Week Marker field still returned an error? 

Anyway, the much simpler method of doing this is scrapping the automation entirely and adding a formula field with WEEKNUM({Creationfield}, "Monday"). That’ll make the week count run Mon-Sun, and then you can group or sort by the Week number (could make that a single select if you’d like too).

You can only give the “Update Record” action one record ID at a time, so you will first need to add a “Repeating Group” action into your automation.

The “Repeating Group” will tell Airtable to “loop through” all of the records that it found in the “Find Records” action.

Then, inside of your repeating group, you will need to place your “Update Record” action, and give it the Record ID from the “CURRENT RECORD” of your repeating group. You’ll see “CURRENT RECORD” in the left margin of your update record action.

Hope this helps!

If you have a budget and you’d like to hire the best Airtable consultant to help you with this or anything else that is Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

I just saw ​@DisraeliGears01’s message above, and he caught the other problem with your automation as well. Airtable won’t accept a Record ID into a Date field… you can only put a date into a date field.

- ScottWorld, Expert Airtable Consultant

​@DisraeliGears01 Thanks for the advice, I think your idea makes a lot of sense, although I’m trying to figure out how I go about getting that info into my interface graph. If I go by week number, is there a way to create a quantity rollup field with each week number to provide an easy data source for my graph? Thanks again!

​@DisraeliGears01 Thanks for the advice, I think your idea makes a lot of sense, although I’m trying to figure out how I go about getting that info into my interface graph. If I go by week number, is there a way to create a quantity rollup field with each week number to provide an easy data source for my graph? Thanks again!

For a rollup I’d need a bit more information on your base structure…

Presuming you’re using a dashboard interface for graphs/charts, I would just format the formula output as Single Select options, and then you can add a dropdown user filter fed by that field. That should let interface users select a week and the charts will filter for only that week (note that you’ll need to get more specific after a year because it’ll conflate week 5 2025 and week 5 2026)

​@DisraeliGears01 Okay perfect, that makes a lot of sense. Thanks again!

Terms & ConditionsAccessibility statement