Populate table with entries referenced from 2 different tables

I’ll try and explain my question.
I working on a collecting app that allows you to select items from a catalog and place them into your collection, I’m stuck on one concept though.

I have a catalog of “groups” that have sets of grouped pieces “Red Box Set” and “Yellow Boxed Set”.
Then I have a catalog of pieces that can reference back to the group they are part of.

Lastly I have a “Collection” table that I want to populate from the available entries in either the catalog of “Groups” or “Pieces”. I can make a REF column to one or the other but can I make that REF dynamically switch based on a selection of which table to reference?

Something like switch the “Source Table” in reference based on a Enum?

Thanks all for any ideas about how to solve for this!

You can’t make a Ref, but you can still use the value with LOOKUP().

An alternative design approach is to create the “Collection” table so that it can support both “Groups” and “Pieces” together on the same row then show/hide columns on the Form and Detail views based on item type.

You would have your common columns, Name, Description, etc but then also a Group ID column and a Piece ID column. Include an additional column “Type” to specify whether its a grouped item or an individual piece.

When entering or editing a “Group” type item, the Group ID column, and any other group specific columns, are shown and filled out while the Piece ID columns are hidden.

When entering or editing a “Piece” type item, the Piece ID columns are shown and filled out while the Group ID columns are hidden.

1 Like

Thanks @WillowMobileSystems,
This is where my mind went also, but wasn’t sure if that would be the best approach.
I’ll have to try it out later tonight.