cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
landonjochim
Frequent Visitor

Custom PowerApp list form display only values in Lookup lists if assigned

Info:

I have a SharePoint list (Lookup) with 2 single line of text fields.

  • Program
  • Site

I then have a SharePoint Document Library (Program1Documents) where I have the Site as a lookup column to the Site single line of text column in the "Lookup" List.

 

My Question:

I want to use PowerApps to customize the form for the document library. How I would like this to work is on my Program1Documents doc library I would like the choices in the Site dropdown to only be the ones that Program 1 was listed with. 

 

Lets look at the "Lookup" SP List. You can see Program 1 is listed with Site 1, Site 2, Site 3. Those three options should be the only items available as choices to select in the Site dropdown.

"Lookup" SharePoint list library"Lookup" SharePoint list library

I am OK with hard-coding "Program 1" into my PowerApps customized form. Because I will have other doc libraries like "Program2Documents" where I will need to use the same formula to filter the Sites available. On the Program2Documents it would only give me the options of Site 1, Site 2.

 

Below is the Program1Documents doc library

"Program1Documents" SharePoint doc library"Program1Documents" SharePoint doc library

In my Site dropdown picker I currently have the following formula in the 'Items' property

Filter(
       Choices(Program1Documents.Site),
       Value in Filter(Lookup,Program="Program 1").Program
       )

I am not getting any errors or warnings, just no values returning.

 

Please let me know if you have any questions or I can go into greater detail to help.

 

Thanks..

 

 

4 REPLIES 4
v-xiaochen-msft
Community Support
Community Support

Hi @landonjochim ,

 

According to your description, let me explain why you encountered this problem.

 

Firstly, Your formula does not return a value because the field is wrong.

You should change Program to Site.

You can try the following code:

Filter(Choices(Program1Documents.Site),Value in Filter(Lookup,Program="Program 1").Site)

v-xiaochen-msft_0-1607063059683.jpeg

 

 

Secondly, Could you tell me:

  1. Do you want the drop-down menu to be 'Site 1' ,'Site 2' and 'Site 3' when the hard code is Program 1?
  2. Do you want the drop-down menu to be 'Site 1' and 'Site 2' when the hard code is Program 2?
  3. Do you want to find the corresponding records in document library when you select a site?

 

If so, Unfortunately your code can't achieve it.

The point is to use the dropdown control to select the site. Then use the gallery control to display the records.

 

1\ Add a dropdown control and set its Items property to:

Filter(Lookup,Program="Program 1")

 

2\ Add a gallery control and set its Items property to:

Filter(Program1Documents,Site.Value=Dropdown1.Selected.Site && Last( FirstN(Lookup,Site.Id)).Program="Program 1")

// Dropdown1 is my dropdown control’s name

 

Best Regards,

Wearsky

Thank you for your response. 

 

Wanted to answer your questions to help.

Secondly, Could you tell me:

  1. Do you want the drop-down menu to be 'Site 1' ,'Site 2' and 'Site 3' when the hard code is Program 1?
    • Yes
  2. Do you want the drop-down menu to be 'Site 1' and 'Site 2' when the hard code is Program 2?
    • Yes
  3. Do you want to find the corresponding records in document library when you select a site?
    • No, my app is a Custom Form for my document library. So the Site dropdown is simply for the Lookup metadata field.

What you sent does work to return only results that are for “Program 1”. However, it returns duplicate items of the Site that are listed for other Programs. For example, if “Site 1” is also assigned to Program 2, I will get duplicate Site 1 in the dropdown.

How can we essentially “distinct” these values so there is no more than one of each item?

v-xiaochen-msft
Community Support
Community Support

Hi @landonjochim ,

 

Could you tell me:

  • Do you want to distinguish the same site in different projects?

 

If my assumption is correct, although Sites are same, their programs are different.

So you can distinguish them based on the Site column and the Program column together.

 

When you want to see the sites in Program 1, you can use the following formula:

Filter(Lookup,Program="Program 1")

The dropdown will display: Site1 , Site2 , Site3

 

When you want to see the sites in Program 2, you can use the following formula:

Filter(Lookup,Program="Program 2")

The dropdown will display: Site1 , Site2

 

If you still have any question, please don't hesitate to let me know.

Best Regards,

Wearsky

v-xiaochen-msft
Community Support
Community Support

Hi @landonjochim ,

 

Could you tell me:
Has the problem been solved?
Is there anything else I can help?

 

If my post helps, then please consider Accept it as the solution to help others.Thanks
Best Regards,
Wearsky

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

Top Solution Authors
Top Kudoed Authors
Users online (98,273)