Query Many-to-Many Dataverse Tables

[DasLukas112]

I have a simple many-to-many relationship between two tables with a join table between them. So, Table A, Table B, and Table Join AB. I have two goals. On the one hand, I want to display all records from Table A that are connected to Table B. On the other hand, I want to display all records from Table A that are not connected to any data in Table B. My goal is to link conditions from Table A to tasks in Table B. Multiple conditions can be assigned to multiple tasks. Then I want to create two galleries in Power Apps, where one gallery displays the already assigned conditions and the other gallery displays the unassigned conditions.

To retrieve all records from Table A that are connected to Table B, I use the following code:

Filter('Aufgaben Bedingungen'; Aufgabe.Aufgabe = ComboboxCanvas2.Selected.Aufgabe)

However, I cannot filter which records from Table A are not linked to Table B.

[BCBuizer]

Hi @DasLukas112 ,

Should the Aufgabe column in the 'Aufgaben Bedingungen' table be blank for unassigned items?

If yes, the formula for this is:

Filter('Aufgaben Bedingungen'; IsBlank(Aufgabe.Aufgabe))

[DasLukas112]

Negative. The 'Aufgaben Bedingungen' table is only filled with the currently joined data records. Accordingly, 'Aufgabe' in 'Aufgaben Bedingungen' is not Blank.

I tried the following to get only the records that are not related to 'Tasks'. But I get several error messages.

AddColumns(
    Bedingungen;
    'Related';
    !IsBlank(
        LookUp(
            Filter(
                'Aufgaben Bedingungen';
                Aufgabe.ID = ComboboxCanvas2.Selected.ID
            ); 
            ThisRecord.ID = ID
        )
    )
) 

In general, it is not quite clear to me how to work with Many to Many tables as one is used to with sql, for example.

For example, I can create new entries with the following code.

Patch(
    'Aufgaben Bedingungen';
    Defaults('Aufgaben Bedingungen');
    {
        Aufgabe: ComboboxCanvas2.Selected;
        Bedingung: ThisItem
    }
)

Deleting an entry with this code does not lead to any result and no error message is generated.

Remove(
    'Aufgaben Bedingungen';
    LookUp(
        'Aufgaben Bedingungen';
        Bedingung.ID = ThisItem.ID
    )
)

[BCBuizer]

Hi @DasLukas112 ,

Can you please share a mapping between Table A, Table B, Aufgaben, Aufgaben Bedingungen, Tasks, and Conditions. Maybe that helps understand what you are trying to achieve, because it is not clear to me.

[DasLukas112 · Accepted solution]

I understand that my last post was confusing because English and German terms were mixed. Therefore, I'm now trying to name all tables and relationships in German, since these terms are also in the code. I have a table called 'Aufgaben' (Tasks), and another table called 'Bedingungen' (Conditions). Multiple tasks can have many conditions, and a condition can be assigned to many tasks. So, it's a classic many-to-many relationship, which I connect using a join table in the middle (Aufgaben Bedingungen).

I’ve been experimenting a lot over the last few days and have come up with a solution.

Here is the code to retrieve all the conditions associated with a task;:

Filter('Aufgaben Bedingungen'; Aufgabe.Name = GalleryAufgabe.Selected.Name)
 

This is the code to query all conditions that are not yet linked to the task:

If(
    IsBlank(GalleryAufgabe.Selected);
    Blank();
    Filter(
        AddColumns(
            Bedingungen As Temp;
            Related;
            !IsBlank(
                LookUp(
                    'Aufgaben Bedingungen';
                    Aufgabe.Aufgabe = GalleryAufgabe.Selected.Aufgabe && Bedingung.Bedingung = Temp.Bedingung
                )
            )
        );
        Related = false
    )
)

The Code to relate all unrelated conditions:

ForAll(
    GalleryAufgabenBedingungen.AllItems As Item;
    Patch(
        'Aufgaben Bedingungen';
        Defaults('Aufgaben Bedingungen');
        {
            Aufgabe: GalleryAufgabe.Selected;
            Bedingung: Item
        }
    )
)

And the code to unrelate all related:

ForAll(GalleryAufgabenBedingungenRelated.AllItems As Item; Remove('Aufgaben Bedingungen'; Item))

And here is the result of the UI: