Trouble getting a related record of a Filter

[PhilPfei]

Hello PowerUsers,

I have some trouble retrieving the records of a related table with a Filter formula.

Lets say I have three tables Machine, Components and MachineComponents. The MachineComponents table has two Lookup columns referring to the Machine and Components table and some additional information like the quantity.

I wanna Filter all components, that are not associated with a specific machine. I would've thought the Filter formula should look like this:

Filter(Components; Not(Name in Filter('Machine Components'; Machine.Name="Sawmill").Component.Name))

The problem is, retrieving the related component of the filtered MachineComponents is somehow not working correctly.

Instead of suggesting the attributes of the Component table, a Component attribute is beeing suggested? When using that Component suggestion again, nothing else is getting suggested.

I've been stuck at this problem for quite some time.

[WarrenBelz]

Hi @PhilPfei ,

That is because you cannot use "Secondary" columns of a lookup field like this - I cannot test this, but try (you may also have an issue with Machine.Name)

Filter(
   Components; 
   !(
      Name in 
      Filter(
         AddColumns(
	    'Machine Components'; 
             "CompName",
            Component.Name
         ),
         Machine.Name = "Sawmill"
      ).CompName
   )
)

Lookup columns are not always the best idea if used as a filtering medium (I do not use them at all) - you are probably better querying the lists directly.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[PhilPfei]

Thanks for your reply

I checked your formula und it does work in the sense that no error is thrown but all components are shown in the gallery, whether associated with the machine or not. So in that sense it does not work.

Okey good to know. I though LookUps create a reference to the actual record kinda like in an ORM. What exactly do you mean by querying the lists directly?

[WarrenBelz]

@PhilPfei ,

The one that the lookup column is point at, although in your case, that would be a triple-nested lookup. I think you really need to design your data structure to be less complex - again I cannot test this or see your data structure, but try this

Filter(
   Components; 
   !(
      Name in 
      Filter(
         AddColumns(
	    'Machine Components'; 
            "CompName",
            Component.Name,
            "MachName",
            Machine.Name
         ),
         MachName = "Sawmill"
      ).CompName
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[PhilPfei · Accepted solution]

@WarrenBelz,

It produced the same result. I collected the result of the AddColumns formula in a collection on a button click and used this collection in the filter and it worked

ClearCollect(
    colItems;
    AddColumns(
        Filter(
            'Machine Components';
            Machine.Name = "Sawmill"
        );
        "Component Name";
        Component.Name;
        "Machine Name";
        Machine.Name
    )
)

Filter(
    Components;
    !(Name in colItems.'Component Name')
)

It seems like the added columns aren't populated with the data when the filter is being executed when using it directly inside the filter

[WarrenBelz]

Hi@PhilPfei ,

So this is now solved?

[PhilPfei]

It is. I still don't understand why you can use attributes of lookup columns in the filter formula but not of the filter result but with this workaround I can make the filter work