cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Antonio
Level: Powered On

Group by item in multiple formula

Dear all, I have the following statement below:

 

SortByColumns(Search(Filter('Table1',Dropdown1.SelectedText.Value in Choice 1&& Dropdown1_1.SelectedText.Value in Choice 2), TextSearchBox1.Text,"Item"), "Item", If(SortDescending1, Descending, Ascending)),

 

I'd like to insert the group by 'Item' into the above formula but i don't know where i need to insert the statement.

 

Could you please support me?

 

Thanks a lot in advance.

9 REPLIES 9
WarrenBelz
Level 8

Re: Group by item in multiple formula

 

Hello @Antonio ,

You might try something like this - I have not tested it, but it should work.

SortByColumns(
   Search(
      Filter(
         GroupBy(
            'Table1',
            [YourGroupField]
         ),
         Dropdown1.SelectedText.Value in Choice 1 &&
         Dropdown1_1.SelectedText.Value in Choice 2
      ),
      TextSearchBox1.Text,"Item"
   ),
   "Item",
   If(
      SortDescending1, 
      Descending, 
      Ascending
   )
)

 
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Antonio
Level: Powered On

Re: Group by item in multiple formula

Hello,

 

i've been able to do additional progresses and I've changed the formula as follows:

SortByColumns(Search(GroupBy('Table1',"Item","Description1","Description2","Description3"),TextSearchBox1.Text,"Item"), "Item", If(SortDescending1, Descending, Ascending)))

 

In fact it is working for items as below on the DB

 

Item    Description1 Description2 Description3 Location
1               A                 B                      C                 1

 

BUT it is not grouping. In fact, it is not working for repeated items (the reason why I want to groupByItem)

Item    Description1 Description2 Description3 Location
1               A                  B                  C                     1

1               A                  B                  C                     2

1               A                  B                  C                     3

 

Can you help me to check what is wrong?

 

Thanks a lot

 

WarrenBelz
Level 8

Re: Group by item in multiple formula

Hi @Antonio ,

Apologies - I left out the Group name which is required (typing from memory on a phone is never good)

SortByColumns(
   Search(
      Filter(
         GroupBy(
            'Table1',
            [YourGroupField],
            "[YourGroupName]"
         ),
         Dropdown1.SelectedText.Value in Choice 1 &&
         Dropdown1_1.SelectedText.Value in Choice 2
      ),
      TextSearchBox1.Text,"Item"
   ),
   "Item",
   If(
      SortDescending1, 
      Descending, 
      Ascending
   )
)

But as you are grouping by multiple columns, you will also need AddColumns.

Here is a post from last week I did on a related topic - please see if this helps you.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Antonio
Level: Powered On

Re: Group by item in multiple formula

Hello,

 

unfortunately, it is still not working.

 

In particular, when it comes to add fields in the gallery like YourGroupName.Table1FieldName i got an error-

 

How can i get it solved?

 

Thanks a lot

 

Regards

eka24
Level 10

Re: Group by item in multiple formula

I would suggest you take a screenshot of the results or output that you desire if the formula worked as expected. That would help.
WarrenBelz
Level 8

Re: Group by item in multiple formula

Hi @Antonio ,

I assume you have replaced everything [in square brackets] with the name of your field or control without the brackets.

My code was simply an example of the structure you need to use.

Antonio
Level: Powered On

Re: Group by item in multiple formula

Hello,

 

yes i did it.

 

This is the formula:

SortByColumns(Search(GroupBy('Table1',"ITEM","GRPBYITEM"),TextSearchBox1.Text,"ITEM"), "ITEM", If(SortDescending1, Descending, Ascending))

 

Then, when i try to add the to the Gallery1 the fields i need to be displayed i got the following message:"this formula uses scope which is not presently supported for evaluation"

 

Image is attached.

 

Thanks a lot

 

 

WarrenBelz
Level 8

Re: Group by item in multiple formula

Hello @Antonio ,

Sorry I missed your post.

A colleague of mine @Eelman is very good at complex formulas - I have tagged him to see if he can assist.

Eelman
Level 10

Re: Group by item in multiple formula

@WarrenBelz Thanks for the tag .

@Antonio 
Could you please provide a screenshot of your data source and as well as the outcome you are wanting, maybe a simple example built in Excel for the latter? Could you also tell me the Items property of your gallery.

Im not 100% sure of your expectations but is seems to me that you are trying to reference a table by using a record, which is causing the error. I'm thinking a gallery inside your current gallery may suit your needs but can't be sure until I see what is going on.

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,075)