Filter a gallery based on a gallery.

[Anonymous]

Hello!

I am programming an application and I want to have 2 screens with a gallery on both screens. 

On the first gallery, I want it to automatically filter all the doubles out and only get one of each. 

My SharePoint columns looks like this:
https://gyazo.com/a3a3afca0f2954f7403d087a41949573

We are going to be focussing on 3 out of the first 4 columns.

-Order
-Relatie
-Suborder

I want the first gallery to only show the Relatie with the order number. Example: (Aalberts Bouw B.V. 17056)
As you can see there are more rows with aalberts bouw and that order number. This is basically a project with different sub orders. I want to filter all of these rows and only show 1 of each project name + order number. So There should only be one "Aalberts Bouw B.V. 17056". Whenever I click on this project I want it to show all of the rows that match with the relatie + order number. Then on both gallery I also want to have a search function where I can search on the relatie or order number. How can I do this?

I've tried the code below but it said that the column "Order" and "Relatie" do not exist. 

GroupBy(
        ProjectLijsten;
        "Order";
        "Relatie";
        "GroupData"
)

Thanks in advance, if something is unclear, please tell me and I will explain it better.  

[wyotim]

Hey @Anonymous, for the first gallery you could make use of the GroupBy function like so:

ClearCollect(
    colProjectLijsten;
    GroupBy(
        ProjectLijsten;
        "Order";
        "Relatie";
        "SuborderData"
    )
)

This will create a collection that contains the distinct Order and Relatie values and groups the remaining data in SuborderData. Entering colProjectLijsten in the Items property of your first gallery and adding the necessary labels should produce what you are after for that gallery.

For the second gallery, you could put this in the Items property:

Filter(
    ProjectLijsten;
    Order = Gallery1.Selected.Order;
    Relatie = Gallery1.Selected.Relatie
)

As the galleries will be on different screens, you could also store the selected values in variables and move those to the second screen in a Navigate function. Here is what that could look like:

// In OnSelect of the first gallery
Navigate(
    Overzicht;
    ScreenTransition.None; // or whatever transition you would like
    {
        SelectedOrder: Gallery1.Selected.Order;
        SelectedRelatie: Gallery1.Selected.Relatie
    }
)

// Items property of the second gallery
Filter(
    ProjectLijsten;
    Order = SelectedOrder;
    Relatie = SelectedRelatie
)

As far as the search function goes, you would want a text input object on each screen and then adjust the Items property of each gallery like so:

// First gallery
Search(
    colProjectLijsten;
    TextInput1.Text;
    "Order";
    "Relatie"
)

// Second gallery
Search(
    Filter(
        ProjectLijsten;
        Order = SelectedOrder;
        Relatie = SelectedRelatie
    );
    TextInput2.Text;
    "Order";
    "Relatie"
)

Regarding the search on the second gallery, this might be a bit redundant as there will only be one order and relatie value due to the previous filtering. You could adjust this to search the other fields if desired. 

Hopefully, this gets you going in the direction you want to go! Let me know if I can explain anything further or need more detail on this!

*edits for ; vs. , and typos*

[v-xida-msft]

Hi @Anonymous ,

Do you want to display only one of each project name + order number record within the first Gallery?

Do you want to show all related records within your second Gallery based on the selected item in your first Gallery?

Based on the needs that you mentioned, I think the GroupBy function could achieve your needs in first Gallery. Please set the Items property of the first Gallery to following:

GroupBy(
        ProjectLijsten,
        "Order",
        "Relatie",
        "GroupData"
)

Then within the first Gallery, you could add a Label control, set the Text property to following:

ThisItem.Relatie & " - " & ThisItem.Order

then each individual relatie + order number record would be displayed in your first Gallery.

Within your second Gallery, please set the Items property to following:

Filter(ProjectLijsten, Order = Gallery1.Selected.Order && Relatie = Gallery1.Selected.Relatie)

If you also want to search record within your first Gallery, you could add TextInput box (called "TextSearchBox") in your first screen, then you need to modify the formula within the Items property of the first Gallery to following:

Filter(
      GroupBy(
             ProjectLijsten,
             "Order",
             "Relatie",
             "GroupData"
      ),
      StartsWith(Order, TextSearchBox.Text) || StartsWith(Relatie, TextSearchBox.Text)
)

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[Anonymous]

Hello!

I've tried to do it the way that wyotim adviced me to do. It also tells me my column can't be found?

Thank you so much for your answer. I've put the code and I get a lot of errors and I don't know why.

 Here is a screenshot of the browsegallery names so you can see if I am getting a wrong name or something: 
https://gyazo.com/65b1ee6d0bb56774256a4737be3313fb
Home is the first screen, Overzicht is the second screen.

(OnSelect of the first gallery)

Navigate(
    [@Overzicht],
    None, 
    {
        SelectedOrder: BrowseGallery2.Selected.Order,
        SelectedRelatie: BrowseGallery2.Selected.Relatie
    }
)

 (Items of the first gallery)

ClearCollect(
    colProjectLijsten,
    GroupBy(
        ProjectLijsten,
        "Order",
        "Relatie",
        "Subordernummer"
    )
)

 (Items of the second gallery)

Filter(
    ProjectLijsten,
    Order = BrowseGallery2.Selected.Order,
    Relatie = BrowseGallery2.Selected.Relatie
)

 I get an error on each of these codes. It might be because my language is wrong. I am using a different pc now so I actually don't know if I need to use a , or a ; 

Screenshot of the SharePoint list: https://gyazo.com/790605ccec1b0aa0c9d25e758efe7b18

What am I doing wrong?
@wyotim 

[wyotim]

Hey @Anonymous, I realized that I formatted the code I posted with commas (,) not semicolons (;). I'm guessing that is a big part of the problem. I also noticed at least one typo. I am going to edit my initial post and we'll see if that works.

[wyotim]

One other thing, if you use @v-xida-msft's GroupBy code instead of mine (with ; instead of ,) that should work. My code is to create a collection and won't work in the Items property of a gallery. That is the better way to do it really.

[v-xida-msft]

Hi @Anonymous ,

Yeah, you are right. In some regions, it use ';' as List separator instead of ','.  So please consider take a try to replace the "," with ";" within the formulas I provided above.

Please set the Items property of the first Gallery to following:

GroupBy(
        ProjectLijsten;
        "Order";
        "Relatie";
        "GroupData"
)

Then within the first Gallery, you could add a Label control, set the Text property to following:

ThisItem.Relatie & " - " & ThisItem.Order

then each individual relatie + order number record would be displayed in your first Gallery.

Within your second Gallery, please set the Items property to following:

Filter(ProjectLijsten; Order = Gallery1.Selected.Order && Relatie = Gallery1.Selected.Relatie)

Please consider replace the ',' operator with ';' operator within your Filter formula and GroupBy formula, then check if the issue is solved.

Best regards,

[Anonymous]

Hello!

I've tried it with the following code:

GroupBy(
        ProjectLijsten;
        "Order";
        "Relatie";
        "GroupData"
)

I then get this error, says the columns can not be found:
https://gyazo.com/aac4667cf37acd3e7533ded22e3d5aa1

If I wanted to do it with a , how would my code look then? Just like this? 

GroupBy(
        ProjectLijsten,
        "Order",
        "Relatie",
        "GroupData"
)

 I then get this error: https://gyazo.com/b2f44501d5b30549868ea0697165b33c

And then on the code in the second gallery, I get this error:
https://gyazo.com/ab2b754e41660bb48844014e221fc8ef

@v-xida-msft 

[v-xida-msft]

Hi @Anonymous ,

Based on the issue that you mentioned, I think you have some misunderstanding with the GroupBy function.

The GroupBy function is used to group a table based on one or more columns, and then the rest of columns would be packaged into the "GroupColumnName" that you specified within the GroupBy function.

According to the first screenshot that you mentioned, I think the following formula could achieve your needs (";" as list separator😞

GroupBy(
        ProjectLijsten;
        "Order";
        "Relatie";
        "GroupData"
)

the error you faced is related to the column reference within your Gallery. Within your Gallery, you could only reference column values directly from the "Order" and "Relatie" column, the rest of columns ("ID", "Title", ....) of your SP List is stored within the "GroupData" (GroupColumnName, which is a Table).

So you could not reference the ID column value within your Gallery directly. You could consider add a nested Gallery inside your current Gallery, then set the Items property of the nested Gallery to following:

ThisItem.GroupData

within this nested Gallery, you could add several Labels to display the rest of column values from your SP List using the following formula (set Text property of the Label to following😞

ThisItem.ID

ThisItem.Title

...

...

More details about adding a nested Gallery within a Parent Gallery, please check the following article:

https://www.youtube.com/watch?v=ZzQ1t2sQvj8&t=1346s

Best regards,

[Anonymous]

Sorry. I am not very good at this and I do not understand what you mean with what you've said. @v-xida-msft So how do I use this code if it gives me an error? @v-xida-msft