cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver II
Resolver II

GroupBy error

Morning, I've posted this before but had no response and the thread has disappeared down many pages I imagine.

I have a screen with 3 separate galleries

2020-08-17 09_41_08-Goods In - Saved (Unpublished) - Power Apps.png
 Items property of each is - 

1 - GroupBy(SortByColumns(Filter('[dbo].[Lichfield_Transactions]',Sts="A"&&!IsBlank(BOOKING_REF)&&PICKUP_DATE>=DateAdd(Today(),-3,Days)),"PICKUP_DATE",Ascending),"BOOKING_REF","DATA")

2 - GroupBy(Gallery12.Selected.DATA,"Sales_Del_No","DATA2")

3 - Gallery16.Selected.DATA2

All works as expected whilst editing, but if I save the app and close, when I reopen gallery "2" (gallery16) will only show one entry rather than the multiple that i know there are.

To fix I change its items property to Gallery12.Selected.data then back to GroupBy(Gallery12.Selected.DATA,"Sales_Del_No","DATA2")

This is not useable though, why is this behaving like this, is it an error I have somewhere?

Desperate for some help on this please!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @WarrenBelz .

Thank's for your help here, managed to get it all working reliably again.

I used a series of Lookup's instead of First(xx. )in the end to get what I needed.




View solution in original post

8 REPLIES 8
Highlighted
Super User III
Super User III

Hi @PPSA ,

Firstly I can see a Delegation warning there caused by the !IsBlank and probably also the Date (I don't think it is Delegable in SQL). There is a workaround on this if the filter Sts-"A" produces less than 2,000 records (and you have your limit set to this in Advanced Settings). Try the code below if this is the case.

With(
   {
      vData:
      Filter(
         '[dbo].[Lichfield_Transactions]',
         Sts="A"
      )
   },
   GroupBy(
      SortByColumns(
         Filter(
            vData,
            !IsBlank(BOOKING_REF) &&
            DateDiff(
               PICKUP_DATE,
               Today()
               Days
            )=3
         ),
         "PICKUP_DATE",
         Ascending
      ),
      "BOOKING_REF",
      "DATA"
   )
)

 

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.

Highlighted

Hi @WarrenBelz , thanks for the advice,

My delegation warning has now gone, so thank you, I'll look into the With function to understand its use.

My second gallery is still behaving in the same way though, works as expected until I close and re open.

Highlighted

Thanks @PPSA ,

I firstly wanted to make sure that Delegation was not the issue. For more on the logic of With() in this context, I did a blog recently that may be of assistance to you.

Now to your issue. I assume in this code

GroupBy(
   Gallery12.Selected.DATA,
   "Sales_Del_No",
   "DATA2"
)

that Gallery12 is the other (main) gallery. The problem you face is that you cannot "pre-select" a gallery item by any other way other than physically clicking on it. I actually have the same issue with a "dashboard" app where when first opened, a form and a sub-gallery relating tot the form-item are blank. I "solve" it (more for user aesthetics) by "setting" the form Item to the ID of the first item in the collection forming the gallery.

I will have to let you think this through a bit, as you know the structure, but is there any way you can pick up the first record in your GroupBy function - you have to effectively set Gallery12.Selected.DATA without selecting the item in the gallery.

Please give me your thoughts.

 

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.

Highlighted

Thanks @WarrenBelz , I'll read your blog post in full later today.

Your assumption that gallery 12 was the main gallery is correct, I'll have a think about trying to achieve what you have suggested now.

Thank you

Highlighted

Hi @WarrenBelz ,

I've been looking at this today after a few days on other projects.

I've fixed the issue I had, the second gallery only showing one item instead of the list of grouped items.

I set a variable on navigating to the page - Set(fix,true);Navigate(xxxx

Then on this page I have Set(fix,false) on the onvisible property.

My second items gallery is now set to

If(fix=true,
Gallery12.Selected.DATA
,GroupBy(
Gallery12.Selected.DATA,
"Sales_Del_No",
"DATA2"
))


This seems to have worked, i consistently get all grouped items displaying as expected in this gallery.

Now it doesn't like my labels in here though.

PPSA_0-1597924953431.png

All labels are along the lines of - First(ThisItem.DATA2).Account , First(ThisItem.DATA2).Del_Name etc

If i change the items back to just the groupby formula all labels work, using it in the IF statement, even though the result is just the GroupBy function I'm getting this.

Any ideas?

Thank you!





Highlighted

Hi @PPSA ,

DATA is already a table and you are grouping by it again, so the First() item will still be a record. You need to "drill down" another level to find the value and you will probably have two First() statements in it. Do a couple of collections instead of the gallery filters and have a look what is inside them.

 

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.

Highlighted

Hi @WarrenBelz .

Thank's for your help here, managed to get it all working reliably again.

I used a series of Lookup's instead of First(xx. )in the end to get what I needed.




View solution in original post

Highlighted

Thanks @PPSA ,

Please consider Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (8,564)