Re: Return value from another table based on today's date
Welcome to the community, @De_Deus! :grinning_face_with_big_eyes: If I understand you correctly, you’re trying to isolate the data in the {Text} field from [Table 1] for the record with the date matching today’s date, and have that show up in [Table 2]. Is that correct? If so, this can be done, but you’ll need to do some setup first.
For starters, every record in [Table 1] will need to link to that single record in [Table 2]. If you don’t care what’s in the primary field in that lone [Table 2] record, just add a period. Or you could label it “Current” or “Summary” or whatever makes sense in your situation. I’ll go with “Current” in this case, as it seems to fit what you’re doing based on your description. Also, you don’t actually need a field with TODAY() in it for this to work, so I’ll leave that out. The actual comparison against today’s date will happen in [Table 1], as I’m going to demonstrate.
Once that’s done, go back to [Table 1] and add a link field pointing to [Table 2]. In the first record, link to that lone [Table 2] record manually, then drag-fill that link into all the other records.
Here’s where the date comparison will happen, but it’s a little trickier than you might think. First off, here’s the formula that I put into a field named {Today's Text}, which will only output the text for the record where {Date} matches today’s date:
IF(DATETIME_FORMAT(Date, "L") = DATETIME_FORMAT(NOW(), "L"), Text)
EDIT: I originally had a long, convoluted description here of how the TODAY() function behaved, only to discover just now that my understanding was off. Not only that, but TODAY() appears to be returning a different date than NOW(), which is really confusing. I’m reporting this to Airtable support. Thankfully NOW() works just fine, so all is well. :slightly_smiling_face: Once set up, you should get this:
Now return to [Table 2], where you’ll change the {Text} field to be a rollup bringing in the data from {Today's Text}, using the ARRAYCOMPACT aggregation function to strip out all the blank entries, leaving only the one you want:
Repeat the formula field in [Table 1] and the rollup in [Table 2] if you want to do the same with the ID field.
Does that get you what you want?
