Trying to patch a table that is contained within a record

[matthewkyea]

Hello,

I was working on an algorithm where I had records that contained tables as one of their values, and ran into an issue where I could not successfully patch into a record/row's table. I've recreated the issue here with a simpler example, and was curious if anyone understood why this operation doesn't work (some limitation that I'm unaware of?). This example is trivial/doesn't make sense in the real world, so I don't need any help with the specifics of getting it to work - I'm more interested in the general issue of why this patch doesn't work.

Here's the code:

//setup a basic collection of grades
ClearCollect(
    Grades,
    //each grade has a student and letter associated with it
    {
        Student: "Alice",
        Grade: "A"
    },
    {
        Student: "Alice",
        Grade: "A"
    },
    {
        Student: "Alice",
        Grade: "A"
    },
    {
        Student: "Bob",
        Grade: "B"
    }
);

//build a collection of unique students to loop through below
ForAll(
    //for each grade
    Grades As CurrentGrade,
    If(
        CountRows(
            Filter(
                UniqueStudents,
                Name = CurrentGrade.Student
            )
        ) = 0,
        //if UniqueStudents does not yet contain this student's name, add it
        Collect(
            UniqueStudents,
            {Name: CurrentGrade.Student}
        )
    )
);

//the main algorithm
ForAll(
    //for each unique student
    UniqueStudents As CurrentStudent,
    Collect(
        GradeSheet,
        //add a row into the GradeSheet collection
        //each record/row has 2 values - a text name, and a Table of their grades 
        //(i.e. a mapping from a letter grade to how many of their grades were this letter)
        //for example, Alice's GradeTable should have 2 rows, like this:
            //Letter: A, Number: 3
            //Letter: B, Number: 0
        //all students start with their GradeTable set to 0 for A and B
        //we'll ignore all other grades (C, D, etc.) for simplicity
        {
            Name: CurrentStudent.Name,
            GradeTable: Table(
                {
                    Letter: "A",
                    Number: 0
                },
                {
                    Letter: "B",
                    Number: 0
                }
            )
        }
    );

    ForAll(
        Filter(
            Grades,
            Student = CurrentStudent.Name
        ) As RevelantGrades,
        //look at all of the grades for the current student
        If(
            RevelantGrades.Grade = "A", //if the current grade is an A

            //This is where we fail
            Patch(
                First(
                    First(
                        Filter(
                            GradeSheet,
                            Name = CurrentStudent.Name
                        )
                    ).GradeTable
                )
                //try to patch into the row for A in the GradeTable for the current student
                //again, as we are only using A and B in the table, we can access A with 
                //First(GradeTable) - trivial example
                ,

                //try to update the A row by adding 1 to the current value
                {
                    Number: First(
                        First(
                            Filter(
                                GradeSheet,
                                Name = CurrentStudent.Name
                            )
                        ).GradeTable
                    ).Number + 1
                }
            );

            //so, the above will not work and any student's A row will not be updated

            //if the current grade is B, I use this other method for patching the entry, which 
            //does work but is much messier (and I'm not sure why it works)
            ,
            Patch(
                //patch the actual GradeSheet
                GradeSheet,
                //the record is the row for the current student
                First(
                    Filter(
                        GradeSheet,
                        Name = CurrentStudent.Name
                    )
                ),
                //the updated record will contain a new grade table 
                //that uses the previous values
                {
                    GradeTable: 
                    //make a new table with 2 rows -A and B
                    Table(
                        //first row, for A
                        {
                            Letter: "A",
                            //we can still access the previous grade table, and get the correct value
                            Number: First(
                                First(
                                    Filter(
                                        GradeSheet,
                                        Name = CurrentStudent.Name
                                    )
                                ).GradeTable
                            ).Number
                        },
                        //2nd row, for B

                        //as you can see here, I'm actually doing the same as I tried to do 
                        //above, which didn't work (patch directly into GradeTable entry)
                        //it still doesn't work, but the returned results of the patch can be 
                        //used and do work - why?
                        Patch(
                            Last(
                                First(
                                    Filter(
                                        GradeSheet,
                                        Name = CurrentStudent.Name
                                    )
                                ).GradeTable
                            ),
                            {
                                Number: Last(
                                    First(
                                        Filter(
                                            GradeSheet,
                                            Name = CurrentStudent.Name
                                        )
                                    ).GradeTable
                                ).Number + 1
                            }
                        )
                    )
                }
            )
        )
    )
);

I've attached a gallery I made to display the results of this - as you can see, Alice's values are not updated at all while Bob's is done correctly. If anyone has any ideas or has seen this issue before, let me know.

Thank you!

Matt

[WarrenBelz · Accepted solution]

Hi @matthewkyea ,

Without going into your model, you can do this and get the result

ClearCollect(
   Grades,
   //each grade has a student and letter associated with it
   {
      Student: "Alice",
      Grade: "A"
   },
   {
      Student: "Alice",
      Grade: "A"
   },
   {
      Student: "Alice",
      Grade: "A"
   },
   {
      Student: "Bob",
      Grade: "B"
   }
);
ClearCollect(
   GradeSummary,
   DropColumns(
      AddColumns(
         GroupBy(
            Grades,
            "Student",
            "Grade",
            "Data"
         ),
         "No",
         CountRows(Data)
      ),
      "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.

Visit my blog Practical Power Apps

[WarrenBelz]

Hi @matthewkyea ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

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.

Visit my blog Practical Power Apps

[matthewkyea]

Hi @WarrenBelz, 

Thank you so much for the help! I hadn't thought of using some of these actions, and they make for a quick and clean solution. 

Part of me is still curious as to why my example has an incorrect result, but your solution gives me a better idea of how I can adjust my code going forward to keep things simple, so I will accept it as the solution.

Thank you!