Combo Box to Return Multiple SharePoint Rows with Patch, collect, forall

[Oscar-W]

Hello,

Please forgive me, as I noticed there are very similar posts but ultimately I can't solve this problem and I'm going on 7 hours on trying to fix it from YouTube to the web and from this forum.

I have a SharePoint list with certain columns that need filling out. On my app on the phone I've used a form which is fine for individual entries to SP. Now we want a system for a user to add multiple entries for employees.

I've decided to go with collect, patch and forall:

My combobox is okay has the following code:

Office365Users.SearchUser({searchTerm:FindEmployee.SearchText}).DisplayName

My Submit button has the following code:

ClearCollect(EmployeeList,
{
Employees: FindEmployee.SelectedItems
}
);

ForAll(

EmployeeList,

Patch
('CodeCentre - Requests',
Defaults('CodeCentre - Requests'),
{Surname: "MCR"}, -Other suggetions on how to split the employee name Surname/First would be good as well. As MCR just stands for mass code request and is title column. App form uses First/Surname split out.
{WBS: Concatenate(Text('73PO-00_1'),Text(Project_1),Text(Section_1),Text(Area_1),Text(Role_1))},
{From: StartDPick.SelectedDate},
{Expire: EndDPick.SelectedDate},
{'Group/Employee': FindEmployee.Selected.DisplayName}))

This returns this multiple rows in my SP List but all with one employees name. The SP List column is text.

Any help on why this is returning this way?

Thanks!

[HenriDesmet59]

Hello @Oscar-W ,

The Column 'Group/Employee' is the employees name column ? 

[RandyHayes · Accepted solution]

@Oscar-W 

To start, your formula has the ForAll backward. You are trying to use it like a ForLoop in some development language - which PowerApps is not.  It is a function that returns a table of records based on your iteration table and record schema.

It is more efficient to use the function as intended and will provide better performance, especially with the Patch function that works best when providing a datasource and a table as the parameters.

Your Formula should be the following:

Patch('CodeCentre - Requests',
    ForAll(FindEmployee.SelectedItems,
        {Surname: "MCR", 
         WBS: Text('73PO-00_1') & Text(Project_1) & Text(Section_1) & Text(Area_1) &Text(Role_1),
         From: StartDPick.SelectedDate,
         Expire: EndDPick.SelectedDate,
         'Group/Employee': DisplayName
        }
    )
)

You have things like Project_1, Section_1, etc. in your formula that do not have any context to the scenario you have.  You might be using these incorrectly in your formula, but I cannot determine that as there is not additional information on what those are.

As for splitting the name - this is an age-old question...how do you split a name properly?  The assumption is first <space> last name...but, you can easily have people that have two-word first or last names, so how do you determine which?  I speak completely in regard to your Surname column in your formula.  You can get sur- and given-names from the Office 365 list.

I hope this is helpful for you.

[Oscar-W]

Hey Randy,

Thank you. Seriously. Thanks. The annoying thing is that I used something very similar to this not sure how I got it so convoluted.

For illustration on the WBS lines they're labels that are determine by a drop down to help staff request booking codes concat'd to give a complete WBS:

So when filled out:

Just wondering, apart from your YT videos wheres the best place to learn this coding part of PAs?

[Oscar-W]

Hey Henri,

Randy helped me out. Thanks for your reply though.

[RandyHayes]

@Oscar-W 

Well, the first stage of learning is to absolutely realize that you are NOT coding!  You are writing formulas just like you would write formulas in Excel (as PowerApps is modeled after Excel).  So, the more "code"-like you go, the harder it gets.  

Understand the basics of PowerApps in this way:

1) Understand JSON basics of what a record and table is.  Realize how that is so relevant to PowerApps data.

2) Understand Data Shaping functions in PowerApps (AddColumn, GroupBy, ForAll, etc.) and how they are so powerful to transform your data as needed for your app.

3) Stay away from collections and variables unless absolutely needed.

4) Avoid doing things in control actions (OnChange, OnSelect, etc.) - use only as basically needed - too much and you are trying to "develop" PowerApps...which it is not made for.

Those are some of the basic starting steps that I always recommend.  There is much more beyond that, but that is the foundation.

As for your response - I am not sure what you are showing in the two photos and what issue you are running in to on those.

[Oscar-W]

Thanks. I'll have a look at all these points listed.

The picture were just to show you how I was using the Project_1, Section_1 labels which I'm sure is quite unorthodox for PowerApps.

Whats interesting is when I try to use the code for surname/givenname I can't seem to find them like how DisplayName shows us:

I haven't seen the surname one so that was a guess but I thought that GivenName was definitely a field I could retrieve. 

Is this because my combobox is only looking at:

Office365Users.SearchUser({searchTerm:FindEmployee.SearchText}).DisplayName ?

[RandyHayes]

@Oscar-W 

Ah...gotcha.  Then if those are controls, you should *always* reference the Property of the control and not just the control.  Without that property, it can cause problems at play time and confuse the app.

So, if they are all text controls, then the formula would change to the following:

Patch('CodeCentre - Requests',
    ForAll(FindEmployee.SelectedItems,
        {Surname: "MCR", 
         WBS: '73PO-00_1'.Text & Project_1.Text & Section_1.Text & Area_1.Text & Role_1.Text,
         From: StartDPick.SelectedDate,
         Expire: EndDPick.SelectedDate,
         'Group/Employee': DisplayName
        }
    )
)

As for the other issue...if you append a formula with a column name, then the table returned will only contain that column.  So, since you are appending DisplayName to your Items property on the Combobox, then it will only ever contain the DisplayName.

Change that Items property to:

Office365Users.SearchUserV2({searchTerm: Self.SearchText}).value

(NOTE: This uses the more current SearchUser action as the one you were using is deprecated)

Then your other formula would change to:

Patch('CodeCentre - Requests',
    ForAll(FindEmployee.SelectedItems,
        {Surname: Surname, 
         Forename: GivenName,
         WBS: '73PO-00_1'.Text & Project_1.Text & Section_1.Text & Area_1.Text & Role_1.Text,
         From: StartDPick.SelectedDate,
         Expire: EndDPick.SelectedDate,
         'Group/Employee': DisplayName
        }
    )
)

[Oscar-W]

Hey Robert,

Wow once again you're helping me a bunch. I do really want to leave you alone now but I implemented your first code fine no problem. Changing the reference point to the property and not the control.

However, when I implemented your bottom 2 amendments the button no longer works. The search bar works exactly like it did before but the button code returns this error:

Invalid argument type (Table). Expecting a record type. ?

Any ideas? No worries if not already helped a bunch.

[RandyHayes]

@Oscar-W 

(Randy actually 😉 not Robert)

No worries at all - happy to help.

So where are you seeing that error?  Specifically, where in the formula (or other formula) is that error occurring?