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

[Julien2]

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.

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'
        }
)

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!

[CNT]

@Julien2 Change the first Filter to LookUp

[Julien2]

Hello @CNT ,

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

Could you please assist?

Looking forward to your response.

[CNT]

@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'
        }
)

[Julien2]

@CNT , Thanks for your prompt reply.

I tried what you mentioned it keeps on failing:

[CNT]

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

[Julien2]

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:

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]

Hello, any help would be highly appreciated!

[KvB1]

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]

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:

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.