Welcome to the 3 Rings Community!
In this thread, you can ask questions and discuss the inventory template. If you ask a question, you’ll hear from me. Download the template below.
The template:
The Video:
Don’t be shy… ask your question here!
Hi Julian. Can you show us the formula to add the date received automatically onto the forms connected to gallery cards. I have tried a few variations and I am clearly getting something wrong
Hi Lloyd! Welcome to the forum. Passing dates in a form can be a little tricky because of time zones. A simple way to prefill the Date Received would be to:
-
In the Products table, create a new formula field called “Today”, and enter the formula
Today()
-
Add a prefill to your form URL that says that Date Received is Today, referencing your new Today field. If you’re adding it to the one we created in the video, it would look like this (but make sure to use your own form URL at the beginning):
'https://airtable.com/shros3KUvegxUoIqn'&'?prefill_Product%20ID='&RECORD_ID()&'&prefill_Date%20Received='&{Today}
However! Airtable forms accept dates and times in the GMT time Zone. So if I used this method (I’m in New York in the U.S.), the date is going to be one day off because I entered 12am on 7/5/2023 in GMT, which is actually 8pm on 7/4/2023 in EST. Anyway that’s very confusing, but here’s how to solve the time zone issue:
- Create a formula field called “Time Zone Offset” and use this formula:
DATETIME_DIFF( DATETIME_PARSE(DATETIME_FORMAT(TODAY(),'MM/DD/YYYY h:mma'),'MM/DD/YYYY h:mma'), DATETIME_PARSE(DATETIME_FORMAT(SET_TIMEZONE(TODAY(),'America/New_York'),'MM/DD/YYYY h:mma'),'MM/DD/YYYY h:mma'), 'hours')
If your time zone is different than mine, replace ‘America/New York’ with your time zone name from this list
- Create another formula field called “Today with Offset” and use this formula:
DATEADD(Today,{Time Zone Offset},'hours')
- Update your prefilled form URL to use the offset time:
'https://airtable.com/shros3KUvegxUoIqn'&'?prefill_Product%20ID='&RECORD_ID()&'&prefill_Date%20Received='&{Today with Offset}
Voila! It’s annoying to set up, but hopefully will save you lots of time filling out forms 
I created an example base here that you can copy to see all of this in action.
Thanks Julian. I will give this a go tomorrow and let you know how I get on.
1 Like