Help with patch (With/For All?)

[zmorek]

Good morning,

I have 2 SharePoint lists.

For the first list, I have a gallery that lists schools, and then their number of students per grade, like this:

Title	Grade1	Grade2	Grade3	...
SchoolA	25	35	45	...
SchoolB	15	25	35	...
SchoolC	50	40	30	...
...	...	...	...	...

The second list I need to update is like this:

Title	PositionID	SchoolA	SchoolB
Grade1	1	2	4
Grade2	2	1	1
Grade3	3	2	3

The number patched to the second list (for example, Grade1 at SchoolA = 2) is determined like this for each row in the first list:

If(
    Value(txtEnrollmentGrade1.Text) < 26,
        1,
        And(
            Value(txtEnrollmentGrade1.Text) > 25,
            Value(txtEnrollmentGrade1.Text) < 51
        ),
        2,
        And(
            Value(txtEnrollmentGrade1.Text) > 50,
            Value(txtEnrollmentGrade1.Text) < 76
        ),
        3
etc.etc.
)

I'm having a hard time figuring out how to get the Patch statement going without copy/pasting those if/then statements over and over again for each column in the second list. Is there a better way to do it than:

Patch(
        List2,
        LookUp(
            List2,
            PositionID = 2
        ),
        {
            'SchoolA': If(
                Value(txtEnrollmentGrade1.Text) < 26,
                1,
                And(
                    Value(txtEnrollmentGrade1.Text) > 25,
                    Value(txtEnrollmentGrade1.Text) < 51
                ),
                2,
                And(
                    Value(txtEnrollmentGrade1.Text) > 50,
                    Value(txtEnrollmentGrade1.Text) < 76
                ),
                3)
        },
        {
            'SchoolB':If(
                Value(txtEnrollmentGrade1.Text) < 26,
                1,
                And(
                    Value(txtEnrollmentGrade1.Text) > 25,
                    Value(txtEnrollmentGrade1.Text) < 51
                ),
                2,
                And(
                    Value(txtEnrollmentGrade1.Text) > 50,
                    Value(txtEnrollmentGrade1.Text) < 76
                ),
                3)
}
)

Any brainstorming or help would be appreciated!

Thanks,

Zak 

[RandyHayes]

@zmorek 

I can most likely give you some guidance on this, but I need to understand more about the relationship of the formulas you have presented with the data.

You mention txtEnrollmentGrade1 - what and where is that?

You have a PositionID in your second list - how is that determined?

[zmorek]

Thanks @RandyHayes . The txtEnrollmentGradeX are text input fields in the first gallery. PositionID in the second list is an arbitrary number that links a given position to a bunch of other SharePoint lists containing other bits of information about that specific position.

Edit: I realized I could explain the text inputs better (sorry) - they're like this in the first gallery:

Title	Grade1	Grade2	Grade3
SchoolA	txtEnrollmentGrade1.Text	txtEnrollmentGrade2.Text	txtEnrollmentGrade3.Text

[RandyHayes]

@zmorek 

Sorry, I should have also asked this in the first reply...

You have Grade 1 through 3 in your columns - is that the extent of it or are there more than 3?  What is the Items property of the first Gallery?

[zmorek]

@RandyHayes no worries, I'm grateful for any insight!

The grade columns go through Grade 6, (however I only posted about 3 because I figured I'd be able to extrapolate on the formula from there, and to keep the post simple). The columns are:

Items(SortByColumns(StudentNumbersEL,"Title",Ascending))

[RandyHayes]

@zmorek 

Thanks for the info. I didn't get a chance to look over this until now and I have a few more questions.

I've, in general, worked out the formula you need, but in verifying my results I have some questions about yours.

In your sample data, you show SchoolA Grade1 as being 2 - based on your formula, that is not correct.  The grade there is 25 and your formula specifically states that if the value is less than 26, then it is a 1 - how did you get to a 2?

Also, just like the Grades, I see that the school list perhaps is longer than 3 schools.  Is that the case?

The biggest challenge with this is that you are transposing from columns to rows - that means you have some hard limits.  So, I want to make sure I am understanding your formula results first as they do not seem to correspond to what I see in the formula.

[zmorek]

Of course, thanks for reviewing - the data in the tables above was purely for demonstration purposes; the values I typed in were just to show the layout. Sorry about the confusion.

In this case there's 13 schools.

I've been playing with With(Patch()), considered using AddColumns(), but I think I keep over-complicating it.

[RandyHayes]

@zmorek 

Yes, you're overcomplicating going down that road.

But, here is where the rub comes in.  How flexible does this need to be?  You have specific column names (i.e. SchoolA, SchoolB, etc)  Does your datalist have those columns as well?  

You have 13 schools now, will they always be present in the data?  What happens if you have 14 schools one day?

In PowerApps we can dynamically fill the values of columns, but you cannot dynamically create columns - they have to exist and have to be entered into any formula.

You are much more familiar with your data, but as I see it, I am wondering if there isn't a more generic way to go about this in the lists.  For example, instead of a list that has a column for each school, instead a list that has a column for the school name and grade.  

Just wondering as that would make the whole process so much cleaner - and flexible (i.e. school 14)

[zmorek]

@RandyHayes  I think the chances of us adding a school are slim (I think our District has added one new school in... decades?), though I see what you're saying. Those schools should always be present in the data.

The list that I'm attempting to patch to (called my PositionsList) has about 120 actual positions in it, something like:

Title	PosID	SchoolA	SchoolB	SchoolC
Campus Monitor	1	3	2	1
Crossing Guard	2	1	2	3
Food Service Attendant	3	3	2	1
Kindergarten	4	4	5	6
FirstGrade	5	7	8	9

(Numbers are made up, but represent the number of positions at the given school. For example, SchoolC has 6 Kindergarten teachers). This allowed me to create 2 or 3 other lists that I can LookUp() to/from to get more information about those Positions using PosID, for example:

• LookUp(AccountCodesList,ThisItem.PosID=PosID,AccountCode)

Would you just say that restructuring that PositionsList is a better way to go? Something like:

Title	PosID	Number
SchoolA	1	2
SchoolA	2	3
SchoolA	3	4