How to Patch the Entire Child Gallery that is nested under Parent Gallery

[Damu_007]

I am working on an a Audit app to store audit results in to an SharePoint list. The audit questions have subcategories, So I have used Nested galleries (Gallery 1: Shows the audit category & Gallery 2 accommodates respective questions from the same category) and text boxes for user to provide input.

Requirement: I have to patch the category from Gallery 1 and questions from Gallery 2 along with the user inputs to a new SharePoint list

Problem: I pulled the category details in to the Gallery 2 using referencing labels(will be hidden in production) and patch the Gallery 2 to the SharePoint list which is not working.

Tried Solutions: -

Tried to patch individual Questions separately using a submit control : Working (This is the last option if Nothing else works) 

I was also trying different ways to patch the entire Gallery 2 in One Go. But couldn't succeed. Any workaround to achieve the same? Kindly let me know.

I tried this: Submitting the Child Gallery. None of the field's data got collected in patch

ForAll(

    SelhaGal3.AllItems,

    If(

        !IsBlank(SelhaScore.Text),

        Patch(

           'SELHA Audit Results',

            If(

                IsBlank(

                    LookUp(

                       'SELHA Audit Results',Title = Selha_Title_1.Text &&

                        'Process Step' = SelhaProcessStep.Text && Year = SelhaYear.Text

                    )

                ),

                Defaults('SELHA Audit Results'),

                LookUp(

                    'SELHA Audit Results',

                   Selha_Title_1.Text &&

                        'Process Step' = SelhaProcessStep.Text && Year = SelhaYear.Text

                )

            ),

            {

                'Question No': Value(QuestioNo.Text),

                 Title: Selha_Title_1.Text,

                Year: SelhaYear.Text,

                'Process Step': SelhaProcessStep.Text,

                'Process Classification': Selha_PC_1.Text,

                Weight: Value(Selhaweight.Text),

                Score: Value(SelhaScore.Text),

                Risk: SelhaRisk.Selected.Value, 

                Comments: SelhaComments.Text,

               'Audit Observations': SelhaAO.Text,

               'PCP Update': SelhaPCPUpdate.Text

            }

        )

    )

    );

[RandyHayes]

@Damu_007 

Can you expand a bit on what you are trying to accomplish?  Specifically, if you have multiple gallery 1 items and multiple gallery 2 items in each, what are you trying to update in your datasource?

Also, from your Record in your formula, what controls are where?  Which are part of Gallery1 and which are part of Gallery2?  

And finally, what are your Items properties for each Gallery?

[Damu_007]

Hi, This is an Audit App. as show in the Image I am using Gallery1 to show the category and gallery to show the subsequent questions under that category. The Source to this gallery is a list of Questions from a Sharepoint List. and I have provided text boxes to enter the inputs. 

Ideally I am trying to patch the Category from Gallery & each of its Questions from Gallery2 along with Users inputs to a new sharepoint list in one go (Becuase user wants to submit once after answering all the Questionnaire.  

I am using validation before updating or creating a new entry to the sharepoint list. Incase the combination exits already, then patch will update the item instead of creating a new one  

Regarding multiple items, Yes there are 12 categories (Gal 1 Items) & around 160 Questions divided in to 12 categories. So I tried to pass the Category to Gallery 2 using a label which will help me in submitting all the required data with the help one gallery itself 

(at the end the Gallery 1 is used only to show Category hierarchy) as I am trying patch the data that are from Gal 2. 

[RandyHayes]

@Damu_007 

Yes, I understand your scenario.  I was looking more for specific formulas for the Items property to help provide a more accurate solution for you.

So, the assumption is then that you have a GroupBy function as your Items property on the Gallery1 and that you are simply supplying the grouped items to Gallery2.  If you could provide those two Items properties, it would be helpful to this process.

I am not seeing what the names of your galleries are in your formula other than SelhaGal3.  Is that the Gallery1 or 2?

Can you supply the names of those Galleries in your app and which is referred to as Gallery1 - I see it in the picture, but don't know what its proper name is to reference in your formula.

I believe with that above information, I will have enough to provide you a working formula.

[Damu_007]

I am using 3 different galleries,

I have not used Group By. I have filtered the info from data source in each of the Galleries depending on the previous gallery items

GALLERY: Isolated Gallery:  Only used to select different Main Categories (Process Type/Title))

Items : Distinct('SELHA Process Performance Questions', Process Type)

GALLERY_1 : I have filtered the Sub Categories (Process Classifications) that belongs to the chosen Process Type in GALLERY.

Items:

Distinct(Filter('SELHA Process Performance Questions',Process Type= GALLERY.Selected.Result),'Process Classification')

GALLERY_2 : This is an nested gallery inside GALLERY_1. I am filtering the Questions(Process steps) that are under the each Subcategory displayed in GALLERY_1.

Items: Filter('SELHA Process Performance Questions','Process Classification'= ThisItem.Result)

So in the formula I am trying to Submit only GALLERY_2. For that I am Passing the Title(Process Type) from GALLERY to GALLERY_2 (Label is hidden)

ForAll(
Gallery2.AllItems,
If(
!IsBlank(Gallery2Score.Text),
Patch(
'Sharepoint List',
If(
IsBlank(
LookUp(
'Sharepoint List', 'Process Type' = Gallery2_ProcessType.Text &&
'Process Step' = Gallery2ProcessStep.Text
)
),
Defaults(''Sharepoint List''),
LookUp(
'Sharepoint List', 'Process Type' = Gallery2_ProcessType.Text &&
'Process Step' = Gallery2ProcessStep.Text
)
),
{
'Question No': Value(QuestioNo.Text),
'Process Type': Gallery2_ProcessType.Text,
'Process Step': Gallery2ProcessStep.Text,
'Process Classification': Gallery2_ProcessClassification.Text,
Weight: Value(Gallery2weight.Text),
Score: Value(Gallery2Score.Text),
Risk: Gallery2Risk.Selected.Value,
Comments: Gallery2Comments.Text,
'Audit Observations': Gallery2AO.Text,
'PCP Update': Gallery2PCPUpdate.Text
}
)
)
);

[RandyHayes]

@Damu_007 

Ah...thanks for the details.  It helps a LOT!  Especially now seeing that there is a third gallery in the mix.

So, you're hitting your data source a lot for all these galleries to get the same data.

I would suggest the following:

For GALLERY, set the Items property to :

GroupBy('SELHA Process Performance Questions', "Process Type", "_classifications")

Then set the Items property of Gallery_1 to:

GroupBy(Gallery1.Selected._classifications, "Process Classification", "_questions")

And your nested Gallery_2 items to:

ThisItem._questions

The above formulas will reduce the performance hits of going to the datasource so many times and will reduce the maintenance of multiple filter formulas.

Next, you don't need any hidden labels to pass things as you already have everything you need in the Galleries.  You just need to reference the right things.  

There is one exception to the above statement - you will need one label to be hidden that will extract values from the nested gallery.  There is no purpose in that label.  It is only there due to a bug/issue with how PowerApps gets values from the nested gallery.  If you don't have it, it thinks the gallery is empty.  If you do, it will then see the galleries.  It's a whacky thing, but it resolves the issue.

Now...moving on to the end goal.

I am not seeing in your photo where you want to have this save button to save the information.  I am assuming that the button would be related to the current Process Type.  So, in other words, you would have a save button at the bottom or someplace that would save the question/answers for that particular Process Type selected in GALLERY.

Also not seeing in your picture where these hidden labels that you mention come into play.  If those hidden labels are referencing some columns of previous filter records, then these labels will have errors with the above changes - and the ultimate goal would be to remove all of them and have your gallery contain the data that it needs.

With that assumption, your formula for that save button would be the following:

Patch('SELHA Audit Results',
    Ungroup(
        ForAll(Filter(Gallery_1.AllItems, !IsBlank(selhaScore.Text)) As _class,
            {Answers: 
                ForAll(_class.Gallery_2.AllItems As _ans, 
                    {ID: LookUp('SELHA Audit Results', 'Process Type' = GALLERY.Selected.'Process Type' &&
                                'Process Step' = _class.'Process Step' && Year =_ans.Year, ID),
                     'Question No': _ans.'Question No',
                     Title: _ans.Title,
                     Year: _ans.Year,
                     'Process Step': _ans.'Process Step',
                     'Process Classification': _class.'Process Classification',
                     Weight: Value(_ans.Selhaweight.Text),
                     Score: Value(_ans.SelhaScore.Text),
                     Risk: _ans.SelhaRisk.Selected.Value, 
                     Comments: _ans.SelhaComments.Text,
                     'Audit Observations': _ans.SelhaAO.Text,
                     'PCP Update': _ans.SelhaPCPUpdate.Text    
                    }
                )
            }
        ),
        "Answers"
    )
)    

A lot of this formula is based on assumptions for things not seen in your picture or noted in your description, but the end result of the above is that it will create new records in the SharePoint list as needed, and update existing records as needed.

Oh...and I mentioned you need only one hidden label.  That is to resolve the issue with referencing nested galleries.

So, in your Gallery_1, add a label and set the Text property to: Concat(Gallery_2.AllItems, Title)

This just creates an outer reference to an inner gallery which will then let the above patch formula see the inner gallery items properly.  The label is not referenced anywhere, it just causes PowerApps to expose the nested gallery properly.

Give the above a shot and let me know if you run into issues or don't understand something.

[Damu_007]

@RandyHayes I tried your formula, but still I am facing issues. Is there anyway we can connect separately, so that you can take look at the app and that might help to resolve this issue.

I tried the solution as it is (by renaming the controls), but faced the same issue. Also one thing I observed is with these formula I could see not all the elements were selected from the galleries that we are trying to patch.

I modified the query a little by providing the actual labels controls from the galleries. In this case all the elements were selected(Except Process Classification from Gallery 1) as you can see in the attached image. But it's still showing the error with patch command. I tried patching only elements from Gallery and Gallery 2, but nothing happened.