Group with filter and search

[Ramole]

Hi 

I need to Group my data currently using below formula but interesting to group my gallery without loosing my currant formula which is below

Thanks 

Filter(
collection1Ship,
If(FilterGallery.Selected.Filter = "All",
true,
Status.Value = FilterGallery.Selected.Filter

,
If(
IsBlank(TextSearchBox.Text),
true,
TextSearchBox.Text in Check_x0020_Number || TextSearchBox.Text in InvoiceNumber || TextSearchBox.Text in TrackingNumber || TextSearchBox.Text in Company.Value
|| TextSearchBox.Text in ShippingMethod.Value,OrderDate,Descending
)))

[v-bofeng-msft]

Hi @Ramole :

I am not sure what you mean by Group.Do you want to group data by the "GroupBy" function?If yes, please provide more information, such as "which field to group according to?".

According to your code, I divide it into 4 cases.

Case1:FilterGallery.Selected.Filter <> "All" && !IsBlank(TextSearchBox.Text)

Take the union according to the filtering results of FilterGallery and TextSearchBox

Case2:FilterGallery.Selected.Filter = "All" && !IsBlank(TextSearchBox.Text)

Get the filter result of TextSearchBox

Case3:FilterGallery.Selected.Filter = "All" && IsBlank(TextSearchBox.Text)

Do not filter

Case4:FilterGallery.Selected.Filter <> "All" && IsBlank(TextSearchBox.Text)

Get the filter result of FilterGallery

If(
FilterGallery.Selected.Filter <> "All" && !IsBlank(TextSearchBox.Text),/*Case1*/
Filter(collection1Ship,Status.Value = FilterGallery.Selected.Filter || TextSearchBox.Text in Check_x0020_Number || TextSearchBox.Text in InvoiceNumber || TextSearchBox.Text in TrackingNumber || TextSearchBox.Text in Company.Value || TextSearchBox.Text in ShippingMethod.Value),
FilterGallery.Selected.Filter = "All" && !IsBlank(TextSearchBox.Text),/*Case2*/
Filter(collection1Ship,TextSearchBox.Text in Check_x0020_Number || TextSearchBox.Text in InvoiceNumber || TextSearchBox.Text in TrackingNumber || TextSearchBox.Text in Company.Value || TextSearchBox.Text in ShippingMethod.Value),
FilterGallery.Selected.Filter = "All" && IsBlank(TextSearchBox.Text),/*Case3*/
collection1Ship,
FilterGallery.Selected.Filter <> "All" && IsBlank(TextSearchBox.Text),/*Case4*/
Filter(collection1Ship,Status.Value = FilterGallery.Selected.Filter))

 Best Regards,

Bof

[Ramole]

hI @v-bofeng-msft 

Yes i mean GroupBy by Company.value without loosing existing formula which you helped me below please 

If(
FilterGallery.Selected.Filter <> "All" && !IsBlank(TextSearchBox.Text),/*Case1*/
Filter(collection1Ship,Status.Value = FilterGallery.Selected.Filter || TextSearchBox.Text in Check_x0020_Number || TextSearchBox.Text in InvoiceNumber || TextSearchBox.Text in TrackingNumber || TextSearchBox.Text in Company.Value || TextSearchBox.Text in ShippingMethod.Value),
FilterGallery.Selected.Filter = "All" && !IsBlank(TextSearchBox.Text),/*Case2*/
Filter(collection1Ship,TextSearchBox.Text in Check_x0020_Number || TextSearchBox.Text in InvoiceNumber || TextSearchBox.Text in TrackingNumber || TextSearchBox.Text in Company.Value || TextSearchBox.Text in ShippingMethod.Value),
FilterGallery.Selected.Filter = "All" && IsBlank(TextSearchBox.Text),/*Case3*/
collection1Ship,
FilterGallery.Selected.Filter <> "All" && IsBlank(TextSearchBox.Text),/*Case4*/
Filter(collection1Ship,Status.Value = FilterGallery.Selected.Filter))

 Thank you 

[v-bofeng-msft]

Hi @Ramole :

Please try :

GroupBy(AddColumns(
If(
FilterGallery.Selected.Filter <> "All" && !IsBlank(TextSearchBox.Text),/*Case1*/
Filter(collection1Ship,Status.Value = FilterGallery.Selected.Filter || TextSearchBox.Text in Check_x0020_Number || TextSearchBox.Text in InvoiceNumber || TextSearchBox.Text in TrackingNumber || TextSearchBox.Text in Company.Value || TextSearchBox.Text in ShippingMethod.Value),
FilterGallery.Selected.Filter = "All" && !IsBlank(TextSearchBox.Text),/*Case2*/
Filter(collection1Ship,TextSearchBox.Text in Check_x0020_Number || TextSearchBox.Text in InvoiceNumber || TextSearchBox.Text in TrackingNumber || TextSearchBox.Text in Company.Value || TextSearchBox.Text in ShippingMethod.Value),
FilterGallery.Selected.Filter = "All" && IsBlank(TextSearchBox.Text),/*Case3*/
collection1Ship,
FilterGallery.Selected.Filter <> "All" && IsBlank(TextSearchBox.Text),/*Case4*/
Filter(collection1Ship,Status.Value = FilterGallery.Selected.Filter)),
"Comppanyvalue",Company.value),"Comppanyvalue","NewGroup")

Best Regards,

Bof

[Ramole]

Hi @v-bofeng-msft 

Thank you so much for last can you please tell me how can add a another column on the GroupBy example like 

Company_x003a_Business_x0020_Pho.Value and A Sum column called ShippingFee

Thanks 

[v-bofeng-msft · Accepted solution]

Hi @Ramole :

What fields do you need to sum up? Is it ”ShippingFee“?

I will do a simple demonstration(Create a new column called "SumShippingFee" whose value is the sum of the value of ShippingFee in "NewGroup")

Please try:

AddColumns(
GroupBy(AddColumns(
If(
FilterGallery.Selected.Filter <> "All" && !IsBlank(TextSearchBox.Text),/*Case1*/
Filter(collection1Ship,Status.Value = FilterGallery.Selected.Filter || TextSearchBox.Text in Check_x0020_Number || TextSearchBox.Text in InvoiceNumber || TextSearchBox.Text in TrackingNumber || TextSearchBox.Text in Company.Value || TextSearchBox.Text in ShippingMethod.Value),
FilterGallery.Selected.Filter = "All" && !IsBlank(TextSearchBox.Text),/*Case2*/
Filter(collection1Ship,TextSearchBox.Text in Check_x0020_Number || TextSearchBox.Text in InvoiceNumber || TextSearchBox.Text in TrackingNumber || TextSearchBox.Text in Company.Value || TextSearchBox.Text in ShippingMethod.Value),
FilterGallery.Selected.Filter = "All" && IsBlank(TextSearchBox.Text),/*Case3*/
collection1Ship,
FilterGallery.Selected.Filter <> "All" && IsBlank(TextSearchBox.Text),/*Case4*/
Filter(collection1Ship,Status.Value = FilterGallery.Selected.Filter)),
"Comppanyvalue",Company.value),"Comppanyvalue","NewGroup"),"SumShippingFee",Sum(NewGroup,ShippingFee))

 Best Regards,
Bof

[Ramole]

Hi @v-bofeng-msft ,

Thank you so so much for your help.

Regards,

Ramole