This Formula uses scope, which is not presently supported for evaluation - Groupby on Gallery

[Apsmith2]

Hi

I have a canvas app using Azure SQL tables as the data source.  The table attributes are Varchar(255) or Varchar(50).

I am trying to add a gallery with a groupby datasource which works fine as below.

I now want to add a list box within the Gallery to list 1 attribute of all the records within the groupby (following YouTube video).

However, as soon as i type thisitem in the Items attribute i get the following error screen

If i complete the syntax then i still get the error but the Gallery becomes corrupted, i can't see the label content anymore and the list box does not have the groupby context, it displays the full table content.

I am really new to PowerApps but have looked at other similar posts and none of them work (changed browser, rebuilt app).

Any suggestions for next investigative steps or what might be causing the issue?  Alternatively any other approaches i could take to my problem?

[Anonymous]

Hi @Apsmith2 , Do you want to show the ExceptionText of grpAddress of each item? In that case, just add a Label instead of a List box, and set its text property to

ThisItem.grpAddress.ExceptionText

Gallery control automatically creates a list and you can customize the first (top-most) item to get what you want on every item.

Please provide an example of the desired result?

Best,
Zabi
Mark this post as a solution, or kudo it if you found it be helpful. You can choose more than one answer as solutions, including your own answer. 🙂

[v-siky-msft]

Hi @Apsmith2 ,

That is not error message, it's just a statement since ThisItem represents the current row record in the gallery, it is not a constant, so it doesn't support for evaluation and preview.

II don't find any mistake from your snapshots, the listbox seemly works well.

What is the purpose of your application? What do you want to achieve? Please elaborate more about your question.

I suggest you save the groupby table to Collection, and then you will find what actually is it in File-Collection Tab.

sik

Sik

[Apsmith2]

The Crawler.exceptions table is a flat table with 1-N relationships in it.

I want to use the groupby in the gallery to create a virtual 1 address = many exceptiontexts.  

In the gallery i want one gallery item for each unique address and then within the gallery be able to see all the related exceptiontext entries per address.

Does that make sense?

[v-siky-msft]

Hi @Apsmith2 ,

Yes, I can get you point.  But I didn't find any wrong with your scenario, the only difference is that I office use add a the Sub-Gallery(not a listbox) to show the Grouped Data in the Main-Gallery.

Could you please follow my steps to see if it can work for your case?.

1. Put the following code to App.OnStart property, and click the 'Run OnStart'

ClearCollect( CityPopulations, 
    { City: "London",    Country: "United Kingdom", Population: 8615000}, 
    { City: "Berlin",    Country: "Germany",        Population: 3562000}, 
    { City: "Madrid",    Country: "Spain",          Population: 3165000}, 
    { City: "Rome",      Country: "Italy",          Population: 2874000}, 
    { City: "Paris",     Country: "France",         Population: 2273000}, 
    { City: "Hamburg",   Country: "Germany",        Population: 1760000}, 
    { City: "Barcelona", Country: "Spain",          Population: 1602000}, 
    { City: "Munich",    Country: "Germany",        Population: 1494000}, 
    { City: "Milan",     Country: "Italy",          Population: 1344000}
);

2. add a gallery, set Items property to 

GroupBy( CityPopulations, "Country", "Cities" ) 

3. add a Label in main-gallery, set Text: ThisItem.Country

4. add a sub-gallery, set Item property: ThisItem.Cities

5. add a Label in sub-galllery, set Text: ThisItem.City

Sik

[Apsmith2 · Accepted solution]

apologies for the delay in responding!

Your example code worked for me

However when i changed the Item property to my DB tables i got the same behaviour as before where the base gallery is empty/corrupted and the sub gallery just shows all items in the table.  I can only assume it is to do with the use of SQL or something on the SQLDB.

I have found a workaround where i created a groupby view on the SQL DB and based the main Gallery on that.  this has caused another issue which is described here - https://powerusers.microsoft.com/t5/Building-Power-Apps/Gallery-linked-to-form-with-different-dataso...

Thanks for your help it is really appreciated!