cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Julien2
Super User
Super User

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!

1 ACCEPTED SOLUTION

Accepted Solutions
KvB1
Solution Sage
Solution Sage

Okay, I tried to change what you made before a bit:

 

Set(
    varResourceID,
    LookUp(
        Resources,
        'User ID'.'User ID' = varTest
    ).'Res. ID'
);

 

This doesn't really match with what you explained in your previous post, but I'm assuming varTest is a variable based on the UserID from timesheet users.

 

 

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

 

This is where multiple project ID's are grabbed from the third list using the one resource ID, and storing them in a local collection called ColProjResources.

 

 

Clear(
     ColTimesheetProjects,
);
ForAll(
      ColProjResources,
      Collect(
            ColTimesheetProjects,
            LookUp(
                  'Timesheet Projects',
                  'Project ID' = ThisRecord.'Project ID'
            )
      )
)

 

This will iterate through each record in the new local collection. For each record, it will match the 'Project ID' from that record with the 'Project ID' from 'Timesheet Projects', and store records found in another local collection based on 'Timesheet Projects', called ColProjResources.

 

It's a bit of a hassle working with all this. Is it possible to add another column in the Project Resources list, where you store the resource ID, or is it possible for one entry to use multiple resource IDs?

 

View solution in original post

24 REPLIES 24
CNT
Super User
Super User

@Julien2 Change the first Filter to LookUp

Hello @CNT ,

As mentioned, I changed the first filter to lookup but I get another issue which is the below:

CaptureError.PNG

Could you please assist?

 

Looking forward to your response.

@Julien2 Try this,

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

@CNT , Thanks for your prompt reply.

I tried what you mentioned it keeps on failing:

CaptureErr1.PNG

@Julien2 Just copy my formula. Don't change anything.

Hello @CNT ,

Thank you for your reply.

It seems the first problem was wrong spelling in Lookup as it should be with capital U.

Now the other issue I got once I try to compare a filter vs lookup result from the WIth function it failed because:

CaptureError2.PNG

This is all the formula:

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

 What should be the workaround to solve the above issue?

 

Thank you!

Julien2
Super User
Super User

Hello, any help would be highly appreciated!

KvB1
Solution Sage
Solution Sage

Why did you adapt CNT's formula to include another With( ?

Whenever you use Filter(, it will return a table of values. 

 

In your second With( statement, you are asking the formula to return a table with one column ('Res.ID') and define that as ResID.

 

In your third With( statement, you are asking the formula to filter a text value in SharePoint with the table you just defined. 

 

Before you said you have a 1:N relationship, but you are making the formula to support a 1:N:N relationship. Is this necessary?

Julien2
Super User
Super User

Hello @KvB1 ,

From the beginning I am using the WIth function formula because the main goal of the formula is the below:

  1. Get the User ID from the User Custom table.
  2. Get the Resource ID from the resource custom table based on the user ID returned from Step 1. (A user belong to a specific resource)
  3. Get the projects ID'S from the ProjectResources custom table based on the resource ID returned from step 2. (A resource can have many projects assigned for it).
  4. Get the project names from the Project custom table based on the project ID returned from step 3.

The tables are created on dataverse and between those tables I have custom relationship types.

I still have one more issue and still struggling with that which is the below:

CaptureError2.png
And, this is my updated formula:

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


It would be highly appreciated if you can assist.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

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