Populate table with entries referenced from 2 different tables

[Greg_Sanders]

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!

[Steve]

Greg_Sanders:

can I make that REF dynamically switch based on a selection of which table to reference?

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

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

[WillowMobileSys]

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.

[Greg_Sanders]

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.