Filtering by Look up field

Diego,

I'm not sure what the problem is here. But I have couple of questions.

You said you've got a "master database" and a "report table". I take it that the master "database" is also just another table inside the same Airtable app, yes? So we're talking about two linked tables?

Are the tables linked? You said you "drag" info from the main data table into the reporting table. Not sure what you mean by that. Copying and pasting?

Would help me anyway to understand the problem if I knew answers to those questions. In the meantime, quick tip about filtering on years and quarters. Create a formula field — I usually call it "YYYYQ#" — that returns a value like this: "2022Q4". The formula needs to use a NOW() function or reference a current date formula field from which you pull the year, and which you use further to determine the quarter. Once you have that value you can filter like this on that field:

- to filter by year, filter for values in YYYYQ# that contain "2022"

- to filter by quarter, filter for values that = "2022Q4"

or whatever.

But still might find it helpful to know answers to my questions. (Might help somebody else too.)

Hey William!

Thank you for replying so quick, I have recorded a quick loom vid explaining the case more in depth and what I am trying to do

Hopefully it's more helpful than my first post

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]. 

@Diego34 I think your problem is that you are thinking of the field "Created date from Master DB" as it would have the list of dates, but actually, it is a string field and you can not easily filter dates there. I would suggest using the original data table for your reporting, at least it would look more "natural". If you need to make reportimg in a separate table then you can sync data to a new table and make reporting much easier.

Yeap, echoing Andrey here: the filter function only filters records within that table, it doesn't filter within fields, you'd use conditional lookups / rollups for that.  Given the sheer number of rollups you've got compiling data, adding conditionals per field would be pretty tedious though

Besides Andrey's solution of using the original data table, you could also consider using an Interface for this as it seems ideal for this:

Unfortunately if you need to actually use that compiled data for something (e.g. emailing it to someone, adding it to a PDF, sending it through a Zap somewhere else), then I think you're going to have to create rollup fields with conditions I'm afraid

Thank you all for the help! I managed to find a (not so pretty) solution for what I was looking for, I am just learning new things by the hour with Airtable, it's crazy how much you can do with it and how flexible it is haha