GroupBy Issue

[emberkrumwied]

VERY new to powerapps.  Am trying to build an interactive app/form (?) that users can utilize to produce a printable output of job interview questions.  I have a SharePoint list that has 3 columns Title (a.k.a. Question: this is where the question is listed), Category (multiple choice field to select the topic the question would fall under [note-field only allows a single choice], and CategoryValue (a calculated field to store the choice result).

In my powerapp I have a screen that includes a pull-down field that links to Category.  When a Category is selected those related Questions appear below the field.  To this I have added a CheckBox that has been coded so when checked, that Question if added to a collection colSelectedQuestions and when unchecked, it is removed.  Finally I have a button which opens a 2nd screen with the intent of displaying all the Selected Questions.

This is all working as planned, until I try to group.  On my print screen I'd like for all the Selected Questions to be grouped under their Category.  If I don't introduce any grouping each Selected Question is shown, first with the Category and then the Question.  This causes repetition of the Category names.  Using the GroupBy(colSelectedQuestions, "Category", "CategoryGroup") format I was getting an error that said text was expected.  Thus I created the calculated field and was able to use that to group so that now each selected question category only appears once.

The problem now is that the actual Questions aren't showing!  The error message now says that my name isn't valid that 'Title' isn't recognized.  I have googled and searched using the knowledge I have just started to gain, but have not found a resolution or even a place to start troubleshooting.  I would be greatly appreciative if anyone could point me in a direction on how to correct this error.  Thanks

[EddieE]

@emberkrumwied 

Sounds like you are using GroupBy not in the way it's intended. What you can try is a test inside you app.

1. Add a new blank screen

2. Add a button with this code

ClearCollect( colSelectedQuestions, GroupBy( yourSharePointListName, "Category", "CategoryGroup"))

3. Add a gallery with Items set as colSelectedQuestions

 - change the gallery layout to 'Title'

 - set the Text property of the remaining label to

ThisItem.Category

4. Add a second gallery inside the first gallery. Set it's Items to

Filter( Ungroup(colSelectedQuestions, "CategoryGroup"), Category = ThisItem.Category)

 - change the gallery layout to 'Title'

 - set the Text property of the remaining label to

ThisItem.Question
// Note: you may need to use ThisItem.Title, just check which one works

This will give the basics of how to use GroupBy. You can also check the MS Docs.

What object are you using on your Print Screen? Is it a gallery, a HTML text control, other?

A couple of tips:

1. GroupBy isn't delegable, meaning it will only work on the first 2,000 records of your data source - so long as you have adjust the 'data row limit' to 2,000 in Settings

2. Avoid using Calculated columns in PowerApps where you can, they tend to cause all sorts of issues

[emberkrumwied]

I really appreciate the assist.  I have set up the new screen as described above but not getting any real results.  First I had to use the calculated Category field as using just Category gave me the error that it expected text, numeric, boolean or date/time value.

Added the first gallery, but the items aren't grouped.  Each one was showing as its own line.

Added the second gallery, but no items show at all.

When I run the app, make my selections (via the checkboxes) and navigate to the new screen, only the Category names are showing and they are not grouped.  When I click the new button, the screen refreshes and now only shows just the Category Names, but no items.  Since the way items are added to colSelectedQuestions is via the checkboxes I fear that the ClearCollect statement on the button has undone the selections, thus only the Category names are showing but none of the related questions I previously selected.

As for the print page, it is just another screen that utilizes a Print button.  The list has a few hundred questions and maybe a dozen categories so well under the 2000 item limit.  Printing has it's own issues, as it might be that the number of categories/questions selected would span multiple pages.  Currently, if I don't try to group and just show all the items selected, I will get a scroll bar on the screen.  The print function then will only "screen shot" the current image not the full listing...but that is another problem altogether.

At the end of the day I am just seeking an easy way for users to view the various questions in each category, select those questions they would like to use, and somehow create a printable view of those selections grouped by the category.

Thanks

[emberkrumwied]

So I modified some of the statements and am now getting on my new screen the Category headings for all selected questions and the headings of all the categories.  Under each of the Categories that were selected, all the selected questions are showing.

This is what I see after making my selections.  I chose 2 questions for each category and it is just repeating the results.

I am unable to expand the "parent gallery" (the one showing the Category) to make more room to show the "children gallery" (the one showing the questions), thus each repeated Category has a scroll bar.

[EddieE]

@emberkrumwied 

Sorry, but you haven't provided any code from your solution. Without code, it's too difficult for me to help further.

Types of code I need are gallery Items properties, examples (can be a pic) of the data, OnSelect code ... just anything that's relevant to the problem.

Once you have done this, I can have a better look for you.

[emberkrumwied]

I have made some additional progress, my Categories are now not repeating, but the related questions aren't showing up.  Here is what I have thus far.

App data source = QuestionBank (SharePoint List)

BrowseScreen:

Dropdown field - "CategorySelect": Items = Choices(QuestionBank.Category); Allow multiple selection = Off

[When item is selected, it filters the GalleryAvailableQuestions.]

Gallery - "GalleryAvailableQuestions": Items = Filter(QuestionBank, Category.Value = CategorySelect.Selected.Value)

Checkbox field - "SelectQuestion": OnCheck = Collect(colSelectedQuestions, {Category:ThisItem.CategoryValue, Question:ThisItem.Title}}; OnUnCheck = Remove(colSelectedQuestions, {Category:ThisItem.CategoryValue, Question:ThisItem.Title})

Label field - "Question": Text = ThisItem.Title

Button - "View Selected": OnSelect = Navigate(PrintResults); Set(ResetCheck, true); Reset(CategorySelect)

[Navigates to Print Results screen, unchecks the checkboxes, and returns Category Select to default blank value.]

PrintResults Screen:

Button - "ReturnToSelection": OnSelect = Navigate(BrowseScreen)

Button - "ClearSelections": OnSelect = Clear(colSelectedQuestions)

Button - "Print": Text = "Print"; OnSelect = Print()

Gallery - "SelectedQuestions": Items = AddColumns(GroupBy(colSelectedQuestions, "Category", "CategoryGroup"),"Question",SelectedQuestions)

Label - "CategoryGroup": Text = ThisItem.Category

Label - "SelectedQuestion": Text = ThisItem.Question

Category Group is now only showing once (whereas previously it was repeating for each question selected), but my Question field is blank.  Error message says expected text value.  I also tried to use "Title", but I get the same error.

Indented fields above indicate gallery field instead of screen fields.  Hopefully this is enough information to help troubleshoot.???

Thanks

[EddieE]

@emberkrumwied 

Thanks for this, it helps me greatly.

I would make the following changes:

BrowseScreen:

Check box in gallery

// OnCheck
Collect(colSelectedQuestions, ThisItem);

// OnUncheck
Remove(colSelectedQuestions, ThisItem)

This just helps with the Collect / Remove process.

PrintResults Screen:

Delete the current galleries and do this:

Add a Flexible Height gallery and choose 'Blank' from the layout. This is the Parent gallery

// Items
Distinct( colSelectedQuestions, Category.Value)

- add a label

// Text property
ThisItem.Value

Add a vertical inner gallery to the Parent gallery (not flexible height). Select 'Blank' from the layout

// Items
Filter(colSelectedQuestions, Category.Value = ThisItem.Value)

- add a label

// Text property
ThisItem.Question

// this may also be
// ThisItem.Title
// choose the one that works

Inner gallery height

RoundUp(CountRows( Self.AllItems ),0) * (Self.TemplateHeight+Self.TemplatePadding) + Self.TemplatePadding

// you adjust the Template Size and/or Padding to make things look how you want

Try these things and see how you go

[emberkrumwied]

Thank you EddieE, but the selected questions are still not showing.  The Category group headings are visible but not the questions.  The error I get when using ThisItem.Question or ThisItem.Title is "This formula uses scope, which is not presently supported for evaluation."

Additionally, when I modify the checkbox properties as you have suggested, I get all kinds of data mismatch, invalid use errors.

Perhaps what I am trying to do just can't be done.  I do appreciate the assistance you have given me.

[emberkrumwied]

On further inspection, the questions are now showing, but I had to manually adjust the height of the galleries.  Now my output looks like this.

"Financial Skills" is the Category group, when viewing the app in edit mode that heading appears as shown, but when I select the underlying label field it is as shown below.

Unclear as to why it is doing this weird spacing.

[emberkrumwied]

With some more finessing, I was able to make it look like this:

Would love to get rid of the white space between the questions.  Adjusting the child gallery template size seems to be the only thing that affects that white space, but if I reduce it too much, it doesn't show all the selected questions.  I can set the template to a certain size but I will never know exactly how many questions in each category a user will select.  Not only that, but increasing the template size is what appears to be adding the white space.