Help building a virtual column expression?

[binaryvisions]

I have 3 relevant tables:

• Breweries (key: BreweryID)
• Beers (key: BeerID)
• Tastings (key: TastingID)

Breweries are independent entities. Every entry in the Beers list has a BreweryID. Every entry in the Tastings list has a BeerID.

Beers and Breweries have Related virtual columns automatically thanks to the direct reference between the BeerID and the BreweryID. What I’d like is a Related Tastings at the Brewery level, so when someone is in the Brewery context, they have two Related sections - Related Beers and Related Tastings.

Thanks for any help you can provide!

[WillowMobileSys]

binaryvisions:

What I’d like is a Related Tastings at the Brewery level,

Carry your Brewery ID into the Tastings table. AppSheet will automatically provide a Related Tastings virtual column.

[binaryvisions]

How?

I shouldn’t copy the brewery actually into the Tastings table because it’s duplicating data in a database.

I tried adding a virtual column to Tastings with the following formula (just taking a stab at it) but the column doesn’t end up having any data in it:

SELECT(Beers[BreweryID], Tastings[BeerID] = Beers[BeerID])

[WillowMobileSys]

binaryvisions:

I shouldn’t copy the brewery actually into the Tastings table because it’s duplicating data in a database.

This is a common misnomer. It is perfectly fine, and even preferred in some instances, to copy certain data elements to make implementation and/or viewing of the data easier.

However, as I think about your use case, Tastings probably should be a child table of the Breweries table anyway. At least the way I think of it, a Tasting is an event at a Brewery. I would go to the event and be presented several Beers to try.

So…add the Brewery ID to the Tastings table and make Tastings a child of Breweries table. You will get the Related Tastings list you want.

[binaryvisions]

I guess I don’t entirely agree here, but it’s probably a little bit of an app usage question. A tasting - in this context - is any sampling of a beer, at a brewery or not (e.g. bottles at home). A brewery is a property of a beer, it has nothing to do with the tasting event.

But in any event, this app is supporting about 5k lines of historical data so I’d rather not revamp my data structure just for this purpose if there’s a way I can gather this data through an expression - which it seems like I should be able to do.

I do really appreciate your feedback on duplicating data across tables, though, and I will consider that in future planning.

[WillowMobileSys · Accepted solution]

binaryvisions:

but it’s probably a little bit of an app usage question.

1000%.

binaryvisions:

But in any event, this app is supporting about 5k lines of historical data so I’d rather not revamp my data structure just for this purpose if there’s a way I can gather this data through an expression - which it seems like I should be able to do.

I agree with this. I guess I saw you were new to the Community so just assumed you were just beginning the app.

---

So how to get Related Tastings? If I understand this part of your data structure, Tasting rows are indirectly related to a brewery through Beers. We just need to “connect the dots”!

An expressionl ike this should do it:

SELECT(Tastings[TastingID], 
                IN([BeerID], 
                      SELECT(Beers[BeerID], [Brewery ID] = [_THISROW].[BreweryID])))

[binaryvisions]

You’re a champ! It’s the IN statement that I was missing when I was trying to write my queries to pull this data. I was trying to connect the dots but never getting it quite right. This works perfectly, thank you so much for both the time discussing it, and the solution.