Answered

Lookup columns from sharepoint not patching correctly

(0) Share

Report

Posted on by Community Power Pla...

Dear community,

I have been using PowerApps for about a week. I am using a timesheet app from April Dunnham that I have tweaked. 99% fine except for the add new record button.

I am trying to keep the selections from Area DropDown (ddArea) and Project ComboBox (cbProject), both lookup columns from sharepoint. Project filters dependent on what is selected from Area.

I have tried multiple suggestions and videos, but cannot seem to get it to add a new record and keep the old ddArea and cbproject selections intact. Currently the button is not working at all.

Plus button code:

Select(Parent);
Patch(
newTimeEntry,
ThisItem,
{
Employee: {
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
Claims: "i:0#.f|membership|" & currentUser.Email,
Department: "",
DisplayName: currentUser.FullName,
Email: currentUser.Email,
JobTitle: "",
Picture: ""
},
Mon: Value(tbMon.Text),
Tues: Value(tbTues.Text),
Weds: Value(tbWed.Text),
Thurs: Value(tbThurs.Text),
Fri: Value(tbFri.Text),
Comments: tbComments.Text,

Area: {
'@odata.type': "#Microsoft.Azure.Connectors.Sharepoint.SPListExpandedReference",
Id: LookUp(Projects, Area = ddArea.Selected.Area, ID),
Value: cbProject.Selected.Area
},

Project: {
'@odata.type': "#Microsoft.Azure.Connectors.Sharepoint.SPListExpandedReference",
Id: LookUp(Projects, Project = cbProject.Selected.Project, ID),
Value: cbProject.Selected.Project
}
}
);
Collect(
newTimeEntry,
{
Employee: {
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
Claims: "i:0#.f|membership|" & currentUser.Email,
Department: "",
DisplayName: currentUser.FullName,
Email: currentUser.Email,
JobTitle: "",
Picture: ""
},
Mon: 0,
Tues: 0,
Weds: 0,
Thurs: 0,
Fri: 0,
Sat: 0,
Sun: 0,
Comments: "",
Project: Blank()
})

I have also tried:

Area: {
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: ddArea.Selected.ID,
Value: ddArea.Selected.Area
},

Project: {
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: cbProject.Selected.ID,
Value: cbProject.Selected.Project
}

The latter creates an ObjNull, where record is expected. The former removes that error, but does nothing when clicked.

TimeEntries is where the records are kept:

Area (ddArea) advanced options:

Projects (cbProject) advanced options:

TimeEntries sharepoint list lookup to a Projects Sharepoint list here:

Any help would be greatly appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (24)

Answers (1)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@Anonymous

Couple of things:

1) you can get rid of all the odata.type stuff in your formulas as that has not been needed now for almost 2 years.

2) your problem stems from your Items property of your dropdown for area. You are using a Distinct function. That is going to return a single column table that has just a Result column.

So there is never going to be any other column from that except result (the distinct areas).

That is the first question - why are you using Distinct on that dropdown if they refer to Look up records in another list?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@RandyHayes
Thank you for the prompt reply. I am using distinct because it has multiple selections and I wanted unique selections for Area. Should I do it a different way?

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@Anonymous

Yes, that is not logical for a Lookup column.

Think about it this way in your image example...there are two EMEA Projects. So, if you "Distinct" the list, you will have one at that point...all fine. But, then when you go to create a Lookup reference to that list and that Area...how do you know you are referencing the right one? Suppose it was to be related to the White Space Project and not the OSDU project!

As for a different way - the main thing to keep in mind is that Lookup columns in SharePoint are "nice interface" things (in reality they do bring one other thing to the table, but let's ignore that for the moment as it is not relevant). So what does the Lookup in SharePoint bring - interface. What is important to relate a column to a record? The ID of the record in the other table. However, if that is all that it had, then people would go crazy looking at a list that related to another list record because they would just see a number. So, the Lookup column allows you to define a column from the looked up list that you want to "show in place" of the ID number. This is much easier to read.

Enter PowerApps...

When you assign a list to a PowerApp, the intention is that the PowerApp becomes the primary interface to the list and that people will not be interacting with the list itself. So...no need for the "nice" column value not being a number. In fact, with PowerApps, it can just use that number to go get the information and display SO much more in the app itself.

Plus, they become cumbersome in PowerApps because, a Lookup is a Record in the list. It is a record with an Id and a Value (Id being the foreign list ID and Value being the value that is in the column defined in the Lookup column definition in SharePoint from the foreign list). So, to write them you need to always provide those two pieces.

What is easier? Just a number column in the list to hold the ID of the foreign record. Then your app can lookup all it wants from the other list based on that ID.

Doesn't mean you can't keep lookups, but you mentioned "a different way"...that would be it.

But, your real issue is what would you be relating to in a foreign list if you get Distinct values?

Was this reply helpful? Yes No
A-Roosevelt 153 on at

Like (0)

Report
@Anonymous When you are using the Patch() function to create a record you need to always call the Default() function so that your data would not be modified
for example
Patch( TimeEntries, Defaults( TimeEntries ), {Area: { '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Id: ddArea.Selected.ID, Value: ddArea.Selected.Area }}, {Project: { '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Id: cbProject.Selected.ID, Value: cbProject.Selected.Project }}, )

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@A-Roosevelt

The Defaults function is not needed to create new records.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Think about it this way in your image example...there are two EMEA Projects. So, if you "Distinct" the list, you will have one at that point...all fine. But, then when you go to create a Lookup reference to that list and that Area...how do you know you are referencing the right one? Suppose it was to be related to the White Space Project and not the OSDU project!

They do this currently by selecting the Project combobox which is a filter from Area:

If it is the distinct causing the issue I can create a separate list or choice for the area, but it gives me a new direction to go in and think about - thank you!

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report
@Anonymous
Then you need to utilize the GroupBy for those and not use Filters and Distinct

Example, Your Area Items property would be:

GroupBy(Projects, "Area", "_projects")

Then set the Items property of the cbProject control to: ddArea.Selected._projects

Then your Formula you posted at first would become:

Patch(newTimeEntry, ThisItem, { Employee: { Claims: "i:0#.f|membership|" & Lower(currentUser.Email), Department: "", DisplayName: currentUser.FullName, Email: currentUser.Email, JobTitle: "", Picture: "" }, Mon: Value(tbMon.Text), Tues: Value(tbTues.Text), Weds: Value(tbWed.Text), Thurs: Value(tbThurs.Text), Fri: Value(tbFri.Text), Comments: tbComments.Text, Area: { Id: cbProject.Selected.ID, Value: ddArea.Selected.Area }, Project: { Id: cbProject.Selected.ID, Value: cbProject.Selected.Project } } ); Collect(newTimeEntry, { Mon: 0, Tues: 0, Weds: 0, Thurs: 0, Fri: 0, Sat: 0, Sun: 0, Comments: "", Project: Blank() } )

You're not really using the Employee column in your creation of the new row, so it is not needed (unless you are using it somehow).

Your Area and Project will both be pointing at the same lookup record as...well, they are the same.

So, you might want to reinvestigate the use of the lookups in so many places as they will cause you some heartburn as you do all of this.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@RandyHayes Thanks it got a bit late yesterday (1am), so having a look now at your suggestions. Thank you for helping.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
@RandyHayes I have put in the Groupby for ddArea and selected._projects for cbProjects. Both worked a treat and are returning the same results as I had previously.

I used your suggested formula. However there is still an error on the formula for the + to create a new record whilst keeping the previous selection. It simply states for Area:
and Project:

I love the spot on the Employee - it is completely redundant in this section, so removed.

Patch(newTimeEntry, ThisItem, { Mon: Value(tbMon.Text), Tues: Value(tbTues.Text), Weds: Value(tbWed.Text), Thurs: Value(tbThurs.Text), Fri: Value(tbFri.Text), Comments: tbComments.Text, Area: { Id: cbProject.Selected.ID, Value: ddArea.Selected.Area }, Project: { Id: cbProject.Selected.ID, Value: cbProject.Selected.Project } } ); Collect(newTimeEntry, { Mon: 0, Tues: 0, Weds: 0, Thurs: 0, Fri: 0, Sat: 0, Sun: 0, Comments: "", Area: Blank(), Project: Blank() } )

Any help would be greatly appreciated.

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report
@Anonymous

Change your formula to this:

Patch(newTimeEntry, ThisItem, { Mon: Value(tbMon.Text), Tues: Value(tbTues.Text), Weds: Value(tbWed.Text), Thurs: Value(tbThurs.Text), Fri: Value(tbFri.Text), Comments: tbComments.Text, Area: { Id: cbProject.Selected.ID, Value: ddArea.Selected.Area }, Project: { Id: cbProject.Selected.ID, Value: cbProject.Selected.Project } } ); Collect(newTimeEntry, { Mon: 0, Tues: 0, Weds: 0, Thurs: 0, Fri: 0, Sat: 0, Sun: 0, Comments: "", Area: {Id: Blank(), Value: Blank()}, Project: {Id: Blank(), Value: Blank()} } )

See what that results in.

Was this reply helpful? Yes No