Reference Tables & Keys Hello Community: When...

(Mark Dinius) #1

Reference Tables & Keys Hello Community: When referencing tables together, does one of the columns need to be a key? Or as long as values are identical, they can reference each other and pull child fields into the parent table?

My question may not make any sense, so here’s my goal: Table 1 [Unique ID] [Col 1] [ Col 2] [Col 3] [Col 4] [Qty] Table 2 [Unique ID] [Col 1] [Col 2] [Col 3] [Col 4] [Rating]

I’d like to pass the Average [Rating] from Table 2 back to Table 1. The thought was to create a virtual column with [Col 1].[Col 2].[Col 3].[Col 4] in both tables so they reference back and forth, but I’m using a Unique ID in both, so I don’t think I have the typical Parent/Child relationship. Ultimately, I’d like to have a Deck View with: Primary Header:

[Col 1] - [Col 2] Secondary Header:

[Rating] Summary Column:

[Qty] …

(Aleksi Alkio) #2

You need to deside wich one is the parent table. If it’s Table 2, you can then read the Rating with the deref expression like [RefField].[Rating]

(Mark Dinius) #3

@Aleksi_Alkio Would it be easier (and same result) to do a “SUMIF” type statement within the gsheets?

(Mark Dinius) #4

Are derefs one direction or bi-directional?

I ended up referencing Table 2 to Table 1 (I think that’s opposite of your first post). I can bring a column from Table 1 into Table 2 now with a deref expression, but is there a way to bring a Table 2 column into Table 1?

(Mark Dinius) #5

The Unique Key from Table 2 is coming into Table 1, so is there a way to read a column from Table 2, based on that Unique Key?

(Aleksi Alkio) #6

Deref is only with one direction. Of course you can read the value from the child table, but the ref table is meant to work with one to many relationships and if you have more than one child record, it will always read the first record only. For example… LOOKUP([_THISROW].[Unique ID],ChildTable,RelatedID,Column)

(Mark Dinius) #7

@Aleksi_Alkio this ended up being the winner: =AVERAGE(SELECT(Table 2[Rating],[RefField] = [_THISROW].[Table 1 Unique Key])) Thanks again for the help!

(Aleksi Alkio) #8