Collection from many-to-many relationship table within a nested gallery

[barbossa_94]

Hi guys,

Calling onto the pros within this forum as I have a situation that is a bit more complicated how I find, but I am sure someone knows how.

The Case:

A table “employee”, holding the name of employees in the “Name” column. One record/employee is selected in Gallery1 on a previous page in my app.

On the next page the user is relating technical expertise to his name. I did so in having a table named “'CV-Technical Expertise'” that stands in a many-to-many relationship with my employee table.

 The table looks like this:

Competence	Field of Expertise
Data Management	Digitalization
Networks	Digitalization
System integration	Digitalization
Labor Relations	ESG
Renewable Energy	ESG
Power Network Design	Electrical Engineering
Gas Boiler Control	Electrical Engineering
…	…

To relate it to his name I have a Gallery (named: Technical_Distinct) that lists all distinct values of Field of expertise and a gallery within the gallery (named: Technical) that filters the competence for the distinct value. (I added an image of this gallery). There is a button that either allows a user to relate or unrelate the record with this formula: If(ThisItem.Competence in Gallery1.Selected.'CV-Technical Expertise'.Competence,Unrelate(Gallery1.Selected.'CV-Technical Expertise',ThisItem),Relate(Gallery1.Selected.'CV-Technical Expertise',ThisItem))

The solution I require:

For a power automate into word export I want to create a collection that looks as follows:

Digitalization	Data Management | Networks | System Integration
ESG	Labor Relations | Renewable Energy
…	…

The competence would be displayed as a string with “|” as a separator. Of course I only want to do so If the competence is related to the user and any field of expertise that has no competence selected should not be listed in the collection.

I started, trying around and this gets me a collection with a table in my competence column but obviously there are some steps missing to consider not having any field of expertise listed that don’t have anything selected in the inner gallery.

My tried code so far (but missing a lot)

ClearCollect(

    col_relatedRecords,

    ForAll(

        Technical_Distinct.AllItems,

        {

        Fieldofexpertise: ThisRecord.Value,

        Competence: Concatenate(

            Concatenate(

                Filter(

                    'CV-Technical Expertise',

                    'Competence' in Gallery1.Selected.'CV-Technical Expertise'.Competence

                ).cr1f3_column2,

                " | "

            ),

            " "

        )

    }

))

Can someone help me with this?

Best regards,

[barbossa_94]

Hi @Ali_SZ365 ,

This didn't work unfortunately. 

Firstly, he brings in all the field of expertise (all distinct values) into the collection even tho none of the competencies to that field of expertise are selected. 

Secondly, the string column is empty within my collection.

Any idea?

[barbossa_94]

It still doesnt work at all. He lists the 15 distinct values i have within the field of expertise and the column for the competency is empty.

Is this tried on your end or is this just AI feed? 

Regards,