Multiple join in a single request

[Mar3]

I come from an SQL background and I have a difficult time to wrap my head around the Database syntax and tools. 

I'll start with a bit of context.

I have 3 tables : 

User,

Home, which has a M:N relationship with User, 

Furnitures, that has a M:1 relationship with Home.

I want to get all of the furnitures that a particular User have.

So far I have tried to use these methods without any success : 

LookUp(User, email = INPUT HERE).Home // Getting all of the Home an user posseses

Filter(Furnitures, furnitures_has_home in LOOKUP RESULTS) // getting all of the furniture that an User have, using the relationship of Furnitures 

I also heard that the Dataverse views allowed me to do these kind of "Query" but I didn't manage to make it work.

Is this kind of interaction possible in Dataverse?

And if yes I would love to know how. 

[v-bofeng-msft]

Hi @Mar3 :

Please try:

With(
    {
        TheHomeList: LookUp(
            Users,
            'User Name' = "XXXXXXX"
        ).'Homes (crba2_Home_SystemUser_SystemUser1)'/*This is the LooUp column in 'User' for 'Home'*/.Home/*The GUID Column in 'Home'*/
    },
    Filter(
        Furnitures,
        ThisRecord.Home/*This is the LooUp column in 'Furnitures' for 'Home'*/.Home/*The GUID Column in 'Home'*/ in TheHomeList
    )
)

Best Regards,

Bof

[Mar3]

Hi @v-bofeng-msft !

First of all I'd like to thank you for taking your time to help me resolve this problem.

However, when testing your answer, I ran into two problems:

•  The first one, being the one that was also in my original post. the "in" operator is saying that it can't work correctly when working on bulk data.
• The second one is with the syntax you used.

Let me elaborate : 

To test your answer I created a new environnement to make sure that I don't corrupt my other work on my main database.

After recreating the tables and their relationships, I couldn't use the relationship name for the lookup function.

I ended up with something like this: 

With(
    {
        TheHomeList: LookUp(
            Users;
            user_names = "X"
        ).Homes
    };
    Filter(
        Furnitures;
        ThisRecord.Home in TheHomeList
    )
)

I'm sorry if this question is a bit of a stretch, but am I missing something in the power app parameters ?

I'm still really new to the power platform and an answer like this makes me wonder If I missed something when I read the documentation...

Here are some additional screenshots if it can help to understand a bit more my current database situation :

 

Again, thank you for your help,

Respectfully,

Mar3

[v-bofeng-msft · Accepted solution]

Hi @Mar3 :

With(
    {
        TheHomeList: LookUp(
            Users;
            'User Name' = "XXXXXXX"
        ).'Homes (cr943_Home_SystemUser_SystemUser1)'.Home
    },
    Filter(
        Furnitures,
        ThisRecord.Home.Home in TheHomeList
    )
)

Best Regards,

Bof

[Mar3]

Hi @v-bofeng-msft ,

Thanks to your help I did managed to make it work using : 

With(
    {
        TheHomeList: LookUp(
            Users;
            'User Name' = "X"
        ).Homes.Home
    };
    Filter(
        Furnitures;
        ThisRecord.Home.Home in TheHomeList
    )
)

However, I still have one last question if that's not too much to ask.

Even with this answer I still get the warning with the "in" operator saying that it can't work correctly when working on bulk data.

Do we have an Idea when this warning will become a real issue? Would this cause problems only on really big databases, or could smaller ones be also affected?

Thank you for your help, I will accept your solution,

Have a nice day,

Mar3

[v-bofeng-msft]

Hi @Mar3 :

The number of records in Furnitures can be up to 2000.

I think these links will help you a lot:

Non-delegable limits 

Changing the limit 

Best Regards,

Bof