cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ramole
Super User
Super User

Group with filter and search

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
)))

 

Thank you
If this post helps, then Click on the Thumbs Up below. and Accept it as the solution . to help others find helpful.
1 ACCEPTED SOLUTION

Accepted Solutions

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

View solution in original post

6 REPLIES 6
v-bofeng-msft
Community Support
Community Support

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

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 

Thank you
If this post helps, then Click on the Thumbs Up below. and Accept it as the solution . to help others find helpful.

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

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 

Thank you
If this post helps, then Click on the Thumbs Up below. and Accept it as the solution . to help others find helpful.

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

Hi @v-bofeng-msft ,

 

Thank you so so much for your help.

 

Regards,

Ramole

Thank you
If this post helps, then Click on the Thumbs Up below. and Accept it as the solution . to help others find helpful.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,842)