Solved: Re: accessing grouped column values in collection

Anonymous · ‎01-14-2020

I have a collection that groups items from my data source and am trying to patch to my data source using:

ForAll(
submitProjects,
Patch(
Project_Time_Master,
Defaults(Project_Time_Master),
{
Title: Name,
WeekStarting: WeekStarting,
Project: Project,
Task: Task,
Monday: Monday,
Tuesday: Tuesday,
Wednesday: Wednesday,
Thursday: Thursday,
Friday: Friday,
Saturday: Saturday,
Sunday: Sunday
}
)
);

I keep getting errors that say the name isn't valid (on line with Task), and "the type of this argument Project doesn't match the expected value Text.

submit projects is a grouped collection and I'm guessing I'm not accessing the nested columns correctly - can someone help me with the syntax for this?

mdevaney · ‎01-14-2020

@Anonymous

The best way to understand how the PATCH statement should be built is by doing this short example I just made up. I would suggest you create a new blank canvas app to try it out.

Create a new button and put this code in the OnSelect property. It will create a new collection that looks similar to yours.

// Create the collection with nested table
    ClearCollect(submitProjects,
    {
        Title: "Mike Nowe",
        WeekStarted: Date(2020,1,13),
        GroupedWeeks: 
            Table(
                 {Project: "Micro-Services", Monday: 10, Tuesday: 20},
                 {Project: "Web Application", Monday: 40, Tuesday: 10}
            )
    }
);

Now create a 2nd button and put this code in the OnSelect property. This will patch it to the existing collection.

// Patch another record to the collection
Patch(
    submitProjects,
    Defaults(submitProjects),
    {
        Title: "Mike Nowe",
        WeekStarted: Date(2020,1,20),
        GroupedWeeks: 
            Table(
                 {Project: "Compile Code", Monday: 15, Tuesday: 20},
                 {Project: "TPS Reports", Monday: 30, Tuesday: 10},
                 {Project: "Fix Computers", Monday: 2, Tuesday: 5}
            )
    }
)

The result will look like this.

Does my example clearly explain the concepts behind patching a "nested" collection? Can you figure out what needs to be done from here?

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

mdevaney · ‎01-14-2020

@Anonymous

You said this item was supposed to receive a text value.

Project: Project

I am not sure what the 2nd Project refers to but you'll need to either put a Text value between quotes or reference something that outputs a Text value

Project: "Project Name"

Project: TextInput1.Text

Can you please explain what the nested columns should look like? Its fairly easy to show you if I know what the goal is.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Anonymous · ‎01-14-2020

"Project" is a column in a grouped field (GroupedWeeks) within a collection. I am trying to update the "Project" field in my SP list which is a single line of text and not grouped - I think I'm just not accessing that nested column within "GroupedWeeks" correctly to get that project text value.

mdevaney · ‎01-14-2020

@Anonymous

I'll need a visual of some kind to truly understand what is going on here 🙂

Anonymous · ‎01-14-2020

The first picture is the grouped collection - the second picture is the expanded grouped column and "Project" is the value I am trying to access, and the third picture is just the title of the expanded column.

I am using a gallery with checkboxes that when checked, adds the entry in the gallery to this grouped collection and removes when they are unchecked. I have a submit button that loops through the collection and submits all the checked items and then clears the collection. The code I have associated with said button is below.

ForAll(
submitProjects,
Patch(
Project_Time_Master,
Defaults(Project_Time_Master),
{
Name: GroupedWeeks.Title,
WeekStarting: WeekStarting,
Project: Project, <-----need to return text value here
Task: GroupedWeeks.WorkCompleted, <-----Attempt at accessing the grouped columns in the collection
Monday: Monday,
Tuesday: Tuesday,
Wednesday: Wednesday,
Thursday: Thursday,
Friday: Friday,
Saturday: Saturday,
Sunday: Sunday
}
)
);
Refresh(Project_Time_Master);
Refresh('Project Time System');
ForAll(
submitProjects,
Remove('Project Time System', LookUp('Project Time System', ID = submitProjects.GroupedWeeks[@ID])));
RemoveIf(submitProjects, WeekStarting in Project_Time_Master.WeekStarting);

Project Time System is an employee view of submitted time on a given project - when the button is pressed the entry is from PTS to a Master sheet where management approves or denies.

The way it was setup until my requirements changed - Project : Project - patched just fine but now because Project is in the grouped column it obviously won't work - I need to access that value and the others in that grouped collection

mdevaney · ‎01-14-2020

@Anonymous

All of those 3 pictures are the same.

Anonymous · ‎01-14-2020

Sorry about that - fixed

mdevaney · ‎01-14-2020

@Anonymous

You could get the project name "Micro-Services" like this. Try it out by putting this in the Text property of a label. I was able to identify the specific row by its ID number.

LookUp(Ungroup(submitProjects,"GroupedWeeks"),ID=205,Project Name))

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Anonymous · ‎01-14-2020

Ok cool - that's a push in the right direction - now since it's in a ForAll statement, that ID column is going to change based on what the SP list assigns to it - so everytime I ungroup something does that mean I have to regroup the collection? This is a company wide time keeping app so there are going to be many more entries

mdevaney · ‎01-14-2020

@Anonymous

The "Ungroup" does not change the original collection. It simply gives you access to the values of a Table stored in the collection. There is no need to "Regroup" 🙂

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."