cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Julien2
Post Prodigy
Post Prodigy

Filter N:1 or 1:N Dataverse fields for a gallery

Hello everyone,

I have two tables Users and Resources, the user's table has a relationship type with resource table as 1:N.

From the resource table, I want to fetch the user that he belongs to a specific resource.
ResourceUserID.PNG

Once I try to filter my data using the WIth function to fetch first the user ID and compare it with another "With function" for the resource.

The functions that I used is:

With(
    {
        UserID: Filter(
            'Timesheet Users',
            'User Name' = username_text.Text
        ).'User ID'
    },
    With(
        {
            ResID: Filter(
                Resources,
                'User ID'.'User ID' = UserID
            ).'Res. ID'
        }
)

ResourceFilter.PNG


The error is clearly saying that the variable which I defined for the Used ID is returning a table result as a value and it's failing because I am comparing it to a text.

How can I match their type to solve this error and get the right result?

Can please someone provide an example?

 

Any help will be highly appreciated.

Thanks in advance!

24 REPLIES 24
KvB1
Solution Specialist
Solution Specialist

KvB1_0-1630062992744.png

So the highlighted numbers are 1x1 records since the column is a lookup column, but they will only contain one value right?

 

If so we have to define the GrabID to be text, something like

 

With(
        {
            GrabID: First(ThisRecord.'Project ID').'Project ID'
        };

 

 

To better understand how this column behaves in PowerApps you can go to collections, select ColProjResources, and have a look at the column 'Project ID', or store the first 'Project ID' of the collection in a variable and have a look at that.

@KvB1 ,

Thanks for your prompt reply.

Exactly they will not contain more than one value.

I have tried what you mentioned and it highlighted another error:

Error2.PNG

Any step we're trying to do keeps on failing 😂 but the good thing is we are very close to solvingit.

Thank you!

KvB1
Solution Specialist
Solution Specialist

I don't really know how PowerApps reads a LookUp column from SharePoint =/

 

Does ThisRecord.'Project ID'.'Project ID' work? Or else Text(ThisRecord.'Project ID')?

Also, if ColProjResources is filled, can you make a button with: Set(varProjectIDtest,First(ColProjResources).'Project ID') and then go to the overview of the variables in your app so we have a better idea what were dealing with?

@KvB1 

I am using Dataverse as Datasource and not SharePoint.

Well, after hours spent on this issue I tried a different way and now the issue is resolved easily by implementing the below:

Collection used:

ClearCollect(
ColProjResources,
Filter(
'Project Resources',
'Resource ID'.'Res. ID' = varResourceID
)
);

Label of the gallery:
ThisItem.'Project ID'.'Project Name'


So since the tables have already the relationships configured it's not needed to write those complex filter functions that we tried to get the expected results.

I would like to thank you for helping me out and I appreciate it.

KvB1
Solution Specialist
Solution Specialist

Brilliant, can't wait to use dataverse as well, looks really neat

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (2,084)