Using ForAll(Filter(...., ....), Collect(......, Filter(...., ....)))

[BenDonahue]

The Code

ForAll(Filter(colChartQGroup, ChartKey = varRecord.ChartKey), Collect(colQuestions, Filter(colQuestionsList, QGroupKey = QGroupKey)));

This code is being used to populate the Items property of a gallery.

The Tables
colChartQgroup

colChartQgroup is a pivot table where ChartKey values appear more than once. 
In that same row/record, there is a column QGroupKey. QGroupKey values are not repeatedly associated with the same ChartKey (Dunno how important that last fact is)

colQuestionsList
colQuestionsList is a static table that contains all the questions. a column in that table is QGroup. This denotes that the question on that row is a part of a particular QGroup.

The Goal

Based on the result of the first filter, Filter(colChartQGroup, ChartKey = varRecord.ChartKey), I want to, row-by-row , access the QGroupKey, use that value to filter the questions in colQuestionslList for only that QGroup value, and append all those questions to the collection colQuestions.

The Problem (as I see it, anyway)

When it comes to the final filter, how do I differentiate between the two QGrou[pKey columns such that they come from different tables? The following code does not work, but it may illustrate to a human what I am talking about:

ForAll(Filter(colChartQGroup, ChartKey = varRecord.ChartKey), Collect(colQuestions, Filter(colQuestionsList, colChartQGroup.QGroupKey = colQuestionsList.QGroupKey)));

The Thanks

Thank you for your help in this. As usual, this seems simple, but the solution is elusive, and I appreciate you and your assistance.

[RandyHayes · Accepted solution]

@BenDonahue 

You look like you're taking a development approach to this - that will be a source of confusion as PowerApps is not a development platform.

Consider the following for your Items property of the Gallery:

AddColumns(
    Filter(colChartQGroup, ChartKey = varRecord.ChartKey),
    "questionList", Filter(colQuestionsList, QGroupKey = QGroupKey)
)

This will give you a table with all of the filtered groups and then a column that will contain all of the questions associated with that.

Beyond that, I would consider as well getting rid of the collections and variables as much as possible and going directly to your datasources and the solutions will flow much more easily and the app will work much better.

I hope this is helpful for you.

[BenDonahue]

@RandyHayes ,

Neat! I like it. There is something about nested tables that is both elegant and, for some reason, a little daunting. This will, with a little finagling to access the currently pertinent table (which is controlled by the user through another control, in my case a gallery, work perfectly for me.

Thank you very much, yet again, Mr. Hayes!

(You bring up some very interesting topics I have been wondering about but haven't yet asked. As they are new topics, I will post them separately and flag you.)

[BenDonahue]

@RandyHayes ,
I ran into a little snafu I really don't understand.

I customized your code such that it returned one table, the one containing the QGroup questions I need to display currently, like so:

Filter(AddColumns(
    Filter(colChartQGroup, ChartKey = varRecord.ChartKey),
    "questionList", Filter(colQuestions, QGroupKey = QGroupKey)
), QGroupKey = varQGroupRecord.QGroupKey).questionList

As this table is in the Items property of a gallery, I expected to be able to access all the columns in the gallery to populate my templates with a simple 

ThisItem.Q

I could not. So I tried this:

ThisItem.questionList.Q

which produced this error (notice that Q is a valid property of questionList):

What am I missing? How do I access the data?

[RandyHayes]

@BenDonahue 

(response to first response)

Yes, these are not technically nested tables, they are columns with table values.  So perhaps that distinction would help some in the thought process.

As for other questions...yes, please do ask.  Get the stuff right in your mind and learn the proper way to do things is key to success.  There is way too much "bad" information out there that people are learning and it is frustrating to see.  So, I'm always happy to answer when I can.

(response to second response)

So, let's get the current formula cleaned up a bit.  I need to know a little more on the origins of the snapshot variables.  How and where are varRecord, colChartQGroup, colQuestions, and varQGroupRecord defined?

[BenDonahue]

Outstanding, thank you, @RandyHayes . Forums are fantastic for getting specific problems solved, but tend not to handle strategic or best practices situations very well, I have found.  Thank you for being receptive to such questions.

colChartQGroup

colQuestions

colChartQGroup and colQuestions are defined in OnStart as their counterparts in a SharePointlist, CCAChartQGroup and CCAQuestions. As these will not change in the course of using the app, I did this for performance reasons only.

varRecord

The first screen the user sees has a curated list of their assignments, listed in a gallery. OnSelect of a button in the template of the assignment they want to address in the gallery is:

Set(varRecord, ThisItem);
Navigate('Chart Auditor Audits Chart',ScreenTransition.CoverRight);

The columns in varRecord include ChartKey.

varQGroupRecord

varQGroupRecord is defined in the gallery on the same screen that the questions gallery appears, and is used to select the QGroup the user is currently addressing:

The current code for the Items in the Question gallery is:

Filter(colQuestions,  QGroupKey = varQGroupRecord.QGroupKey)

//Filter(AddColumns(
//    Filter(colChartQGroup, ChartKey = varRecord.ChartKey),
//    "questionList", Filter(colQuestions, QGroupKey = QGroupKey)
//), QGroupKey = varQGroupRecord.QGroupKey).questionList

Here is the screen showing both of the galleries:

In my current approach, which may not be a best practices approach, is producing promising, if not perfect, results. Selecting "Clinical Documentation" or "Well Child Visit" displays the correct QGroup in the Question gallery. The only issues I have are, when saving the data, I am saving all the questions and not just the QGroups pertinent to this chart, leading to many rows of empty data. 

I could check, in the ForAll() if there is data in the Answer column, and conditionally Patch that row, but I felt that  doing so would not adhere to best practices; I felt it would be better to filter for only the QGroups required. 

[RandyHayes]

@BenDonahue 

Yes, I know that a lot of forum responses are "drive-by", shoot and go.  I do try to emphasize best practices and strategies (as I see a lot that are just bad off the bat).  I want people to adopt this platform and grow it.  If there is a lot of misunderstanding and frustration, people will give up on it and consider it not working.  

So, to go down your list.

You're actually causing performance problems by collecting colChartQGroup and colQuestions in your OnStart.  PowerApps retrieves information from the Datasource only when it doesn't have it.  So, if you reference the datasource directly rather than a collection, PowerApps will get the data as it needs it.  If you put in a collection to get it all, then you are forcing PowerApps to get all the data at once.  This is a hit on start up.

A DataSource is not a lot different from a Collection, except that it "ties" to the session tables (the ones that get created when you start the app in the cloud).  So, the session table is a copy of the real datasource (up to record limit).  The datasource is a blank table with the schema of the real data.  It gets filled on-demand.  Eventually, if all records are requested, then it is a duplicate of the data in the session table and the real data source.

If you add a collection, you are just causing the app to have to forcefully get all the data again and then duplicate it YET another time.

Also, a collection is a special type of Table.  It is just a table but it has the ability to add or remove rows to it.  If you don't need to do that (as in the case of your Static data) then skip the collection and just assign to a variable (again, if for some reason you need to force a fill of ALL of the data rather than on-demand).

As for varRecord, this is acceptable use of a variable, but it is a snapshot at that time.  The gallery has a .Selected property which is the record that is selected...ThisItem  You can rely on the .Selected as long as your Items property of the Gallery is not complex (this is another story on Items property and the .Selected that I will skip for the moment).

I'm not exactly following where you are setting the varQGroupRecord from what you showed, but I assume it is getting you what you need.

As for saving, your save should be based off of the Gallery that has the questions in it.  The Items of that Gallery should be based on a filter of the datasource for the questions you want to display based on the first Gallery selected record and the question group.

Once that is in place, then your save is essentially (not verbatim):

Patch(yourDataSource, ForAll(Filter(yourGallery.AllItems, !IsBlank(answer.Text)) As _item, Patch(_item, {answerColumn: _item.answer.Text})))

So, if your question/answer gallery is only comprised of the questions in need, then the record update/patch will occur just on them.

Speaking of best practices - Keep in mind that a ForAll is not a For/Loop, it is a function that returns a table.  I see WAY too often that people look at it as a programming for/loop, and performance suffers.

[BenDonahue]

Wow. Very informative, to be sure, thank you. With development training, this is my first job and Power Platform is my assignment to master and through which I need to shine, which makes me a huge supporter of it and of its general acceptance and support.

varGroupRecord is set here; QGroup is one if the columns passed in this single record. This value of QGroup is what is used to filter the colQuestions, which is the entirety of the Questions table. So, I can successfully display in the Questions gallery only the questions of the selected QGroup, which, again, is set by selecting "Clinical Documentation" in the mini-gallery in the bottom left of the screen:

The problem is, I still have all the questions from all QGroups stored in colQuestions. This list can grow and grow over time. What I want is a collection of just the questions relating to the QGroups; having those listed in one table would be convenient for Patching purposes.

In pseudo code, what I want kind of looks like this:

Filter(colQuestions, QGroup = (any of the values in a column, QGroup, of a pivot table). There may be 1, 2 3 or more matching values

colQuestions 

Question             QGroupKey

who?                     1

Where?                 2

why?                     1

How?                    3

colChartQGroup

ChartKey       QGroupKey

1                     1

1                     2
2                     1

2                     3

3                     1

3                     2

Result desired for chart 1:

colQList

Question

who?
why?

Where?

From there, I can add the Answer column and record the results....pretty sure.

Leaving work for the day; I'll get some of those other questions posted soon, likely over the weekend. Speaking of which, have a great one and Thank you, sincerely, for your help, @RandyHayes.

[RandyHayes]

@BenDonahue 

Yep...stick with it and please keep telling yourself this is Excel.  In Excel you don't set variables for things, you reference the cells.  In Excel you don't collect data you reference it (inserted data sources).  So, stick with that and you will Master PowerApps in no time.  The more you go down a "development" concept, the harder it is and the more frustrating it will be.  This is years of PowerApps experience coming from a .Net development background. Once I threw all the "development" baggage out, it because 1000% easier to understand what is going on.

Now, on to the issues.  So, again, you are talking about collections for the colQuestions.  Ditch the collection concept, you're only going to have to do extra work to constantly update that with information based on the filtering, etc.  Each time you do, you have to do it somewhere, and then you'll just be wasting app time because you're just making another copy of data you already have.

Your Gallery IS a table!  Once you provide it with Items, you have a table that you will use for patching/updating.  The Gallery has a table with a copy of the data it needs.  There is no need to create yet another collection with duplicated data again.

As I see it in your picture, the Question Group gallery in your screen is what will set off the proper filter of questions in your answer gallery.  You seem to indicate that this is working.  The only change I would make there is to get rid of the collection for it as you don't need it.

In my last reply, I mentioned the formula that is needed to patch/update the records associated with the gallery items.  It is all you need.  A collection is not utilized in any way for that and only adds a level of complexity and performance hits.

What is the Items property of the minigallery with the Question group? And if it is another collection only, what is the definition of that collection?

Happy to help, and hopefully you're getting more than just a "drive-by" answer here on this forum today! 😁

I'll be looking for your questions and responses. 

Have a great weekend as well.

[BenDonahue]

@RandyHayes 
I had a bit of an epiphany that lead to a day of straight coding, with very few pauses.

I think I (finally) get what you mean when you say that this is Excel, not a scripting language. Everything happens at once, an there is not before or after,not really, even though ForAll might look like it. It came to me when I realized that there is no "re-render" ... as I was making changes in the code, changes just happened on my screen. This was completely lost on me until today. Though I can't really say precisely how that changed my approach, in specifics (and I've had 9 hours of straight and furious advancement in my project, so am a bit frazzled), I can say that I made INCREDIBLE headway with my new mindset.

(At least for now),) I get it!!

At some point I'd still like to filter a collection based on a dynamically populated array, which would help shorten my collection list, I will not be reaching 2000 rows of questions for another 5 years, at the very earliest, so Ithink I'm good for now.

Filter(colCollection, ColumnA = any value in ArrayY)

or

Filter(colCollection, Contains(ColumnA, any value in ArrayY)

, or somethign like that.

Thank you for giving me the fish and for teaching me to fish....and for showing me how water works.