Filter sharepoint gallery based on two text fields and a choice field.

[rbjames]

I would like to filter a gallery from a sharepoint list based on two text fields and the values from a choice field. Eg Textfield1, Textfield2 and Choice1

The user has a search box called studentSearch.

On the gallery items I have this:

Search(AddColumns(SharepointList, Choices, Concat(SharepointList, Choice1, ",")), studentSearch.Text, Textfield1,Textfield2, Choices)

It's almost there but not quite. Can anyone point out what I'm doing wrong?

Thanks,

Richard

[Amik · Accepted solution]

@rbjames - you were close:

Search(
    AddColumns(
        SharepointList,
        MyChoices,
        Concat(
            'Your Multi Choice Field',
            Value,
            ","
        )
    ),
    studentSearch.Text,
    Field1,
    Field2,
    MyChoices
)

[rbjames]

Thanks - you're a star.

[rbjames]

OK now I want to move the name and form of the student to a dataverse table (one which automatically updates itself) so I want to search on two fields from the dataverse table and one choice field from the sharepoint list. In this situation would it be better for me to create a collection and search on that or can I just extend the query above with a lookup to the dataverse table? I'm not yet ready to move the sharepoint list to a separate dataverse table.

Thanks

[Amik]

@rbjames - you casually mention "name" and "form" and "student" as if I would know what you're talking about (I do not know your app or have any context on what these mean).

If you could provide a better explanation I might be able to offer some insight.

[rbjames]

Sorry - I'd forgotten I'd not fleshed it out at all. Basically the app is a list of students who have extra educational needs so we need to provide teachers extra guidance on how to teach them.

Currently I have one sharepoint list that has all the information about them - name, class, details of conditions and strategies. Because I have a number of different apps relating to students, I have split out common data to another table which is shared by other apps so when the students change class at the end of the academic year I only need to update one list - this app is the only one which doesn't link at the moment. 

I discovered last week that we have an Entra IDs dataverse table which is auto-populated by an integration script we have so all of the extra standard information about the students (name, class and student ID) is automatically maintained by the script in this table so there's now no need for my 2nd sharepoint list which I had to manually update when students arrived/left/moved up years.

So for this app, I would still like my sharepoint list to hold strategies, conditions (eg ADHD) and the student ID then the dataverse table of Entra IDs would hold the students' names, forms and student ID (to link to the sharepoint list).

I'd like my search box to then search for names, forms (taken from the dataverse table) and conditions (taken from the choice field of the sharepoint list).

I hope that makes sense.

Thanks

[rbjames]

Nevermind - I've just set up a collection with the data and it's now working nicely.

Thank you

[rbjames]

The only issue is that I've found out that I need full power apps licenses to link to dataverse - which I only discovered after publishing the app. Really irritating. Is there any way I can get to AAD information directly without using a premium connector? The alternative is to create a local csv on our server then have power automate handle uploading the file to the sharepoint list. Hopefully that doesn't require a premium license of any kind.

[rbjames]

OK this is turning more into a blog. I've found with the following code I can get a table of students:

ForAll (Office365Groups.ListGroupMembers("group-id").value,

Collect(

    Students,

    {name: givenName }

));

When I click on value, I can see all the students in a list - they don't seem to be adding to the collection, though, which remains empty. I suspect I'm close again!

[Amik · Accepted solution]

@rbjames - I do not see what the purpose of using ForAll is here. If you simply want to load the group into a Collection, you would use:

    ClearCollect(
        Students,
        Office365Groups.ListGroupMembers(
            "xxxx",
            {'$top': 999}
        ).value
    )