cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VionMichael
Regular Visitor

Two Collections and join n:m to one (new) collection

Hello all,

I have the following challenge. I need to make two collections into one. But I need to keep the behavior of a n:m relationship.

Here is a mini-example creates two new collections group_m and group_n:
ButtonA:

 

ClearCollect(
    group_m;
    {
        gm_nr: 1;
        gm_atribut: "Hello"
    };
    {
        gm_nr: 2;
        gm_atribut: "Hallo"
    };
    {
        gm_nr: 2;
        gm_atribut: "Gutentag"
    };
    {
        gm_nr: 3;
        gm_atribut: "Moin"
    }
);;
ClearCollect(
    group_n;
    {
        gn_nr: 1;
        gn_atribut: "Foo"
    };
    {
        gn_nr: 1;
        gn_atribut: "Bar"
    };
    {
        gn_nr: 2;
        gn_atribut: "Christian"
    }
);;

 

ButtonB should merge them into a new collection like the following example:

 

Collect(
    group_m_n;
    {
        gm_nr: 1;
        gm_atribut: "Hello";
        gn_nr: 1;
        gn_atribut: "Foo"
    };
     {
        gm_nr: 1;
        gm_atribut: "Hello";
        gn_nr: 1;
        gn_atribut: "Bar"
    };
    {
        gm_nr: 2;
        gm_atribut: "Hallo";
        gn_nr: 2;
        gn_atribut: "Christian"
    };
    {
        gm_nr: 2;
        gm_atribut: "Gutentag";
        gn_nr: 2;
        gn_atribut: "Christian"
    };
    {// Blank or without this Dataset: gm_nr: 3
        gm_nr: 3;
        gm_atribut: "Moin";
        gn_nr: Blank();
        gn_atribut: Blank()
    }
);;

 

 Which is the easiest way?

1 ACCEPTED SOLUTION

Accepted Solutions
timl
Super User
Super User

Hi @VionMichael 

You can do this by filtering the nested ForAll to return only those records where gm_nr=gn_nr. Provided it's acceptable to completely skip gm_nr, the formula would look like this:

 

ClearCollect(
    group_m_n;
    Ungroup(
        ForAll(
            group_m;
            ForAll(
                Filter(
                    group_n;
                    gm_nr = gn_nr
                );
                {
                    gm_nr: gm_nr;
                    gm_atribut: gm_atribut;
                    gn_nr: gn_nr;
                    gn_atribut: gn_atribut
                }
            )
        );
        "Value"
    )
)

 

timl_0-1634587045977.png

 

View solution in original post

4 REPLIES 4
timl
Super User
Super User

@VionMichael 

It looks like you're trying to do a full cartesian join. The approach I would take would be to nest a call to ForAll inside another call to ForAll, like @PowerAddict describes here.

https://powerusers.microsoft.com/t5/Building-Power-Apps/Cross-join-or-Cartesian-Join-of-collections/...

 

Thanks for your answer.

 

I need (unfortunately) not every combination, as requested in the question from the link.
But only the respective combinations if the gm_nr (Collection: group_m) and the gn_nr (Collection: group_n) are identical.

timl
Super User
Super User

Hi @VionMichael 

You can do this by filtering the nested ForAll to return only those records where gm_nr=gn_nr. Provided it's acceptable to completely skip gm_nr, the formula would look like this:

 

ClearCollect(
    group_m_n;
    Ungroup(
        ForAll(
            group_m;
            ForAll(
                Filter(
                    group_n;
                    gm_nr = gn_nr
                );
                {
                    gm_nr: gm_nr;
                    gm_atribut: gm_atribut;
                    gn_nr: gn_nr;
                    gn_atribut: gn_atribut
                }
            )
        );
        "Value"
    )
)

 

timl_0-1634587045977.png

 

Hello,
sorry for the late response, but that looks very good.
Thanks for that.

Unfortunately, the zero value is not included, but that's a 'nice to have' in my case.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (5,381)