cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PhilPfei
Helper I
Helper I

Trouble getting a related record of a Filter

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.

 

PhilPfei_0-1655297690273.png

 

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.

 

PhilPfei_1-1655297769402.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
PhilPfei
Helper I
Helper I

@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

View solution in original post

6 REPLIES 6
WarrenBelz
Super User
Super User

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

 

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?

@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
Helper I
Helper I

@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

Hi@PhilPfei ,

So this is now solved?

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

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (3,705)