Lookup related columns of an inconsistent column lookup

Showcase Tips and Hacks
1

I am creating a content database and calendar to manage content projects and the individual assets that are associated with them. The doc is arranged in a multidimensional schema with separate tables looking up lists and other tables.

I am trying to solve how to create a column (Content Calendar.All Projects) that populates content dependant on a field of a row referenced in another column (Content Calendar.Project Type). The column with the row reference (Content Calendar.Project Type) is a lookup list that multiple tables lookup. I would like to filter (Content Calendar.All Projects) so that only rows on Video Assets or Article Assets table with the corresponding Project Type are available to select in the (Content Calendar.All Projects) list.

I will also add that I’ve been quite intentional about having this schema design in order to separate the different items, rather than having filtered views of a master table.

2

@mattbogaards

Below is one possible approach. See the yellow columns.

Understand that combining different objects into one list like this breaks scalability (for anything other than reporting). That may or may not matter to you, now or in the future. If you’re like me, it will matter to you in the future!:crazy_face: And then you will have to refactor your schema, which will involve finding a way to standardize your “different items” so that they can flow unimpeded throughout your architecture.

Nowadays, when I find myself trying to combine different objects into one list for anything other than reporting, it’s an immediate red flag that my schema is not quite ready. I have learned that it’s much less painful to stop right then and get the schema right, than to fix it later!

1 Like
3

Thanks so much for your guidance and experience!

1 Like