Couple quick notes on first viewing of your video. Thanks for making that. It was helpful. I am just going to throw out a couple of ideas, which might or might not be helpful. Often I try to test what I'm saying here before I say it but don't have time to do that. Still hope something here will suggest a solution to you – or perhaps somebody else will join the conversation and give us both the right answer.
Minor point: I suggest you create your year/quarter formula to show the year first, as I showed. I know we tend to talk about "Q1 2022". But in the database, putting the year first makes it easier to sort these values correctly and that makes it easier to group them correctly.
Now, to your video.
Around 2:00 in your video, you show the contents of the field 'Created Date from Master DB' and you say you're not sure why it looks like this. Don't understand what you're wondering about. It's a representation of a bunch of dates. Looks like they're in date order (but I can't remember if Airtable sorts lookup values or not).
But I think there is something going on here which might explain your difficulties. My recollection is that values in lookup fields are somewhat odd. Dates may look like dates, but you can't use them that way without jumping through some other hoops. For example, I did just take 2 secs to create a simple ParentsChildren app in Airtable. Parent Joan is linked to three children Anne (DOB = 5/1/1990), Mary (DOB = 5/19/1983) and Catherine (DOB = 7/17/1995). In the Parents table, after linking the three children to their mother, I created a lookup field that brings over all three DOB values (5/1/1990, 5/19/1983, 7/17/1995). Then I created a formula field in Parents that tries to identify the youngest child by MAX() function and putting in the DOB lookup field as the parameter. Doesn't work. Returns 0. They look (in the lookup field) like they are a bunch of dates. But they're not at the technical data-type level.
I think there's a way to deal with this by adding the lookup values into an array, and I'm pretty sure I've used that myself, but I don't recall it well enough to say more. But I suggest you read up on using the ARRAY functions (there are a couple) in a formula.
Final thought: I'm wondering why you're doing this report in a separate table. One of the great things about databases is that we can sort, filter and present the data in various ways, without having to extract it from where it is stored. In other words, seems to me this report could be done right in what you call the "MasterDB" (i.e., the main data table).
Any of that make sense?
William
You might look into filtering the records as part of the lookup itself. In the field setup dialog for Lookup fields, there's an option at the bottom — easy to miss — that says "Only include records that" [meet certain conditions, which you then specify].
