Reference Tables & Keys Hello Community: When...

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] โ€ฆ

0 7 462
  • UX
7 REPLIES 7

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]

@Aleksi_Alkio Would it be easier (and same result) to do a โ€œSUMIFโ€ type statement within the gsheets?

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?

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?

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)

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

Top Labels in this Space