cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mattn4962
Level: Powered On

accessing grouped column values in collection

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: accessing grouped column values in collection

@mattn4962 

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.

 

Untitled.png

 

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

13 REPLIES 13
Super User
Super User

Re: accessing grouped column values in collection

@mattn4962

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."

 

mattn4962
Level: Powered On

Re: accessing grouped column values in collection

"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. 

 

Super User
Super User

Re: accessing grouped column values in collection

@mattn4962

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

mattn4962
Level: Powered On

Re: accessing grouped column values in collection

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

Super User
Super User

Re: accessing grouped column values in collection

@mattn4962 

All of those 3 pictures are the same.

mattn4962
Level: Powered On

Re: accessing grouped column values in collection

Sorry about that - fixed

Super User
Super User

Re: accessing grouped column values in collection

@mattn4962 

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."

mattn4962
Level: Powered On

Re: accessing grouped column values in collection

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

Super User
Super User

Re: accessing grouped column values in collection

@mattn4962 

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."

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (5,016)