Answered

Setting a SharePoint lookup column based on other inputs

(0) Share

Report

Posted on by Yashiki1

Hello,

I have a SharePoint list which has a lookup column to a list of location including buildings and levels in the building.

To make it more easy for the user to select I have created three combo box selects.

One for the location, then one for the building and then for the level. So the items are filtered down.

Now I want to select the correct lookup field in a hidden combo box so when the form is saved it the correct location is stored in the SharePoint list.

I think I do not understand what the value of the update property of the data card for the location has to be. Currently it is a record like {Id: <List entry Id>, Value: <List entry Title>}.

If I have understood it correctly the Update property is what is written back to SharePoint?

Thanks

Categories:

Building Power Apps

All responses (7)

Answers (1)

Sort by

SanmeshG 1,956 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Yashiki1 ,

Yes your understanding is correct. The Syntax to update the lookup value is also correct.

Here the Value and the Id are the values that are looked up from the list that you have used to lookup the data.

I Didn't get what is your question here.
Yashiki1 15 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @SanmeshG ,

thanks for confirming my understanding.
The question is why does it not work. When I save the form with my three combo boxes filled out, the location lookup filed is not saved in SharePoint it stays blank.
The formula I use is like this:
For the OnChange and OnSelect for levelSelect:
Set(locationHelper, First(Filter(<Location list where the lookup points to>,And(location=locationSelect.Selected.Value,building=buildingSelect.Selected.Value,level=levelSelect.Selected.Value)))
For the Update property of the hidden data card:
{Id: locationHelper.Id, Value: locationHelper.Title}
I hope that helps in understanding the question.
SanmeshG 1,956 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Yashiki1 ,

You can select the value and see if the controls have the values in the studio. Make sure that when you select the values , the Update property should show the values selected when you expand it. Here Id is very important if the Value property has the value but if Id is blank then , it won't update the value to the list.

Also check the same in New Form Mode and Edit Form Mode , in edit if it won't work based on controls , then you can use the variable name (item record) or ThisItem.Location.Id and ThisItem.Location.Value in this case(So for new you can use controls and edit you might have to use ThisItem.
Yashiki1 15 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @SanmeshG ,

I checked the Values and they are correct:
Where Id is the Id of the SharePoint list entry and Value is the Title of the entry.
This is the Update field from the Hidden DataCard. It also changes when I change my selects. But still there is no correct save of these values.
Can there be a problem that the "01.00" is the title of multiple entries? It is basically the location.
So my Table looks like this for example: (all columns are single line of text and location is the title)
Location Building Level
01.00 01 00
01.00 01
01
01.00 02 -01
01.10 01 00
SanmeshG 1,956 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Yashiki1 ,

I don't think that should be an issue , as the Id in the lookup value is the ID of the SP List Item from which the data is fetched(looked up).

I am surprised it doesn't work , I hope its not a multi-select lookup(it would through an error if it is multi-select lookup)
Yashiki1 15 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @SanmeshG ,

I am happy that I am not alone with my confusion 😀.
So my Lookup looks like this:
Where "Gebäudeliste" is the location list.
In Power App the combo box looks like this with "Dauerparker" being the list where the data should be stored.
This is the corresponding DataCard which should be connected to the "Standort" lookup column from the first picture. Where "StandortHelper" is the location helper from a previous reply.
If you have any further Idea I am happy to hear it. Otherwise I will have to come up with some solution.
Verified answer

Yashiki1 15 on at

Like
a
(0)

Report
Copy link

Link copied!
With time and the Help of @SanmeshG I found a solution.
So for who ever finds this thread with a similar problem here is my solution.

In my SharePoint I had a list with building data consisting of location identifier, building codes and level numbers. Which are all in 1:n relation to the next so the location identifier will be repeated multiple times and is the title column of my list.
Then I had a list which stores data on long parked cars with a lookup to this building data list.
I wanted to provide the user an easier experience selecting the location consisting of three combo boxes one for location then based on that the buildings and based on that the level.
The problem was to store the correct lookup value in the lookup column.

The solution is to set the update of the Card for the lookup filed to select the first choice from the filtered choices.
Which could look like this:
First(Filter(Choices([@<List with lookup>].<Lookup column>), Value = <Title of what is in the helper>))
Where the <Title of what is in the helper> could also be a Filter on another list but then you have to use "in" instead of "=".
For reference how I come up with the solution please look in this thread.
https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-filter-choices-on-Combobox/td-p/101436