SP lookup columns customize forms

(0) Share

Report

Posted on by marial16

443

Hallo,

i have 3 SP lists

1) 'Areas' with 2 columns (Lookup fields) "Workstream" & "Subworkstream"

2)'WorkStream' with 1 column "Title"

3) 'SubWorkStream' with 2 columns "Workstream" which is a lookup column to (WorkStream) list and "Sub-workstream"

i want to customize the form and Filter the subworkstream dropdown to match the selected result of the workstream dropdown.

I tried it using text columns "Workstream" & "Subworkstream" and it works like a charm.

But i want both columns to be lookup fields in the SP list. Any ideas people?

Categories:

Building Power Apps

I have the same question (0)

All responses (13)

Answers (1)

Sort by

WarrenBelz 156,456 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @marial16 ,
Just so I get your requirements correct, you have a lookup column which you want to filter even further with the item selected in another lookup column?
My first question is if PowerApps is the only interface used on this list as if so, there may be a much easier solution.

Was this reply helpful? Yes No
marial16 443 on at

Like
a
(0)

Report
Copy link

Link copied!

Hallo,

Yes i only use powerapps and sharepoint so far in my app. i want to be able to update the list when i click on 'New' from the customized form.

if these two columns were SLT columns in SP ,then i could change their text input properties from PowerApps as below:
(WorkStreamTextDataCard):
Distinct(WorkStream,Title)
(subworkstreamTextDataCard):
Filter(SubWorkStream, WorkstreamTitle = DataCardValue6.Selected.Result)

where 'WorkstreamTitle' is an additional SLT column in SubWorkStream List!!

However Lookup Columns as they are are giving me a headache!
So I only came to match the Workstream LookUp column in Tasks List to the Title of the SubWorkStream List which is wrong apparently..
ShowColumns(WorkStream,"Title","ID") in PowerApps workstream datacard and
Filter(ShowColumns(SubWorkStream,"Title","Workstream"), Workstream.Value = DataCardValue3.Selected.Title)

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @marial16 ,

Could you please share a bit more about your scenario?

Do you want the "Subworkstream" Dropdown box (ComboBox) to show up corresponding Subworkstream options related to the selected Workstream option?

I have made a test on my side, please consider take a try with the following workaround:

Set the Items property of the "Subworkstream" ComboBox to following:

Filter( Choices(Areas.Subworkstream), Value in Filter(SubWorkStream, Workstream.Value = WorkstreamComboBox.Selected.Value).Sub-workstream )

Note: The WorkstreamComboBox represents the Workstream ComboBox within your Edit form, you need to replace it with actual control name from your Edit form.

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
WarrenBelz 156,456 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
OK @marial16 ,
I will give you a syntax to work on - I have tested this and it works
Filter( Choices([@Areas].Subworkstream), Value=WorkstreamComboBox.Selected.xxxx //xxxx is whatever the output of this box )

Basically you have to take the existing Lookup choices and filter them with the output of the other combo box.
What I was going to suggest earlier is that if you only use PowerApps as an interface, you can ditch the lookup columns all together (as I have) and write back to plain text fields, doing all the lookups in Power Apps.
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
marial16 443 on at

Like
a
(0)

Report
Copy link

Link copied!

this formula only allows me to use Title or ID.
When i type Value it says that the identifier is not recognized.

i also changed the advance properties of the dropdown on the right menu to Display fields ["Value"]. I still get an error

Was this reply helpful? Yes No
marial16 443 on at

Like
a
(0)

Report
Copy link

Link copied!

and even when i am not getting an error , the data in the subworkstream dropdown arent displayed!

Was this reply helpful? Yes No
WarrenBelz 156,456 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
HI @marial16 ,
That is why I had xxxx. So let's break this down - firstly, if you just put
Choices([@Areas].Subworkstream)
in the box, do you see the values you expect? Then put a label on the screen with (change to your name)
YourWorkstreamComboBoxName.Selected.Title
select something in the other box and see if it reflected in the label.
If so, can you get the same value to appear in both places?

Was this reply helpful? Yes No
marial16 443 on at

Like
a
(0)

Report
Copy link

Link copied!

I am getting the ID value in Subworkstream dropdown with Choices([@Areas].Subworkstream)

Was this reply helpful? Yes No
Verified answer

marial16 443 on at

Like
a
(0)

Report
Copy link

Link copied!

So, i tried matching the ids of the two lookup columns and..

(subworkstreamDataCard):
Filter(ShowColumns(SubWorkStream, "ID", "Workstream","Subworkstream"), Workstream.Id = DataCardValue3.Selected.ID)

i seem to get my results!
However i am still not sure if that is the best practice!

Was this reply helpful? Yes No
WarrenBelz 156,456 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @marial16 ,
Very nice workaround - same sort of principle but you are capturing the ID on both ends and matching it.
The key is the ability (which you have) to understand the principle of matching two values that are generated from a choice made, we could have got there matching text values, but yours will certainly do the job.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No