Reference a table without Ref_Rows

[cronx-smash]

Here's an overview via Loom video
https://www.loom.com/share/d43158e1ac234d00aff44a3309e79e2a

Correction at 2:00 - I want to show all columns of the Abilities table.

Summary

Each row in Table Figures has a list of references to Table Abilities. They are formatted as Enum List. When in the Figures view and viewing a specific Figure (row), I want to see all rows of the Abilities table that I have reference to in Figures.

Tables

• Figures
• Stats
• Abilities

Table Relationships

• Figures (one) -> Stats (many)
• Figures (one) -> Abilities (many)

Abilities TableStats TableFiltering the Abilities table to display with the respective figure.Figures Table

[Joseph_Seddik · Accepted solution]

Welcome to the Community!

1. Add a new physical column to the Figures tables, name it abilityDetails for example with the following properties:
   • Type: EnumList
   • Base type: Ref pointing to the Abilities table
   • App Formula: 
     FILTER("Abilities", 
       IN([Name], 
         [_ThisRow].[Damage Ability] + [_ThisRow].[Speed Ability] +
         [_ThisRow].[Attack Ability] + [_ThisRow].[Defense Ability]
       )
     )
       
2. Your Ability Description virtual column should have the following formula: [abilityDetails]
3. Go to the settings of the Inline view of your Abilities table, and choose which columns you want to display and in what order. The user will get a clickable, expandable view of the abilities related to this figure. 

[cronx-smash]

WOW thank you, I never would have figured that one out.

For some reason it's leaving out the first item in each [x Ability] list. For example, 

Figure 'War Bard' has 'Weapon Master' listed in [Attack Ability], first item. It also has 'Force March' listed in [Speed Ability], first item. The formula is leaving out these two abilities. Only Magic Enhancement and Demoralized are shown in the result, which are listed in their list at indexes other than 1.

[Joseph_Seddik]

I'd say make sure the same values with the exact spelling with no spaces for example do match in both tables. It is not that the expression is dropping the first column, but rather the values are simply not found in the Abilities table. 

Additionally, I would not use the Name column as key; better have a uniqueID key column and make the Name column as Label, so that users can see names instead of IDs in all app views. That would not be necessary for the expression to function, but it is better to avoid duplication, extra spaces, spelling mismatches, etc.

[cronx-smash]

I think it's a bug. The filter is going to return duplicate values as it did in the above screenshot. It listed:

• Magic Enhancement
• Magic Enhancement (duplicate)
• Demoralized

It did not list:

• Forced March (first index)
• Weapon Master (first index)

In the next example below, you can see that the same filter lists:

• Magic Immunity
• Demoralized

But it leaves out these two because they are in the first index:

• Magic Immunity
• Weapon Master

It should have listed a duplicate of Magic Immunity as it did in the first example. That rules out spelling mistakes (plus I've checked that). Now what's interesting is that the duplicate in example 1 was in the first index and the first filter criteria [Damage Ability]. It seems that the rest of the filter criteria are missing the first index. Perhaps as a result of the concatenation?

This is how the [X Ability] lists are defined:

[cronx-smash]

@Joseph_Seddik and here's the verification... I switched the order of the filters and have different results. Weapon Master is now included, but the third Magic Enhancement is missing.

[cronx-smash]

OK I figured it out. I filtered out any blanks or spaces PRIOR to filtering. It seems that blanks at the end of the list were being joined to the first item of the next list.