cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

SharePoint lookup column with powerapps

Hi All,

Hope everyone is doing well! 
Since we all are working from home, we have got a new project which has a small requirement in regard to a Lookup column.


Requirement

I have 2 List naming "PPN" and "Resource Tracker". Resource Tracker has one lookup column from PPN. 

  1. The problem we are facing is PPN Column as a lookup in resource tracker is not unique, so there are duplicate PPNs available in lookup column. i thought of using distinct for lookup but i am not sure how to update that lookup column as i am not sure about the id it would require to save that column. 

  2. Second Requirement is, PPN list contains 3 other columns like Region and Project Classification etc. We would want to populate our textbox(allowed values) / dropdown with Value selected based on PPN Column in Resource Tracker New Form.
    For example: If i select a PPN : PPN29 , it should populate Region with all the values corresponding to PPN29 from PPN List. 

 

Hope my problem statement is clear. if you have any queries do tell me. Hope folks here can help me out in this!

 

Cheers!

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @jayant1:

Could you tell me:

Is “PPN Column” in the share point list “” allow multiple values?

What are Region and Project Classification’s data type?

Do you use a field with duplicate values as the search field of the combo box control?

Do you want to deduplicate the list in the combo box?

Do you want to add all of the related records depend on you selection in the combo box?

Do you want to automatically fill in the textinput control based on your selection in the combo box?

I’ve made a simple test for your reference:

Resource Tracker

Column (click to edit)

Type

Required

Title

Single line of text

 

PPNColumn

Lookup

 

Region

Single line of text

 

PNN

Column (click to edit)

Type

Required

Title

Single line of text

 

PPNN

Single line of text

 

Region

Single line of text

 

Project Classification

Single line of text

  

Step1:

Add a form

DefaultMode:New

Step2:

Set the combo box control:

Items: Distinct(Choices('Resource Tracker'. PPNColumn),Value)

 

Set the data card control:

Update: Filter(Choices('Resource Tracker'.PPNColumn),Value in DataCardValue2.SelectedItems.Result)

 

b.png

Step3:

Set the TextInput control’s Default property to:

Concat(Filter([@PPN],PPNN in DataCardValue2.SelectedItems.Result),Region,",")

 

c.jpg

Here is the result:

 

d.PNG

 

 

Best Regards,

Bof

View solution in original post

8 REPLIES 8
WarrenBelz
Super User
Super User

Hi @Anonymous ,

Firstly, the Distinct() filter is not delegable, so my first question is if there are less than 2000 records in the list PPN.

You have also stated this is a Lookup column from Resource Tracker - what is the structure here for requiring this to be a Lookup in the data source?

The second item is possible by filtering the list based on the item selected in the first drop-down. I will deal with this once I know the details above.

Anonymous
Not applicable

@WarrenBelz 

For the first question, yes there are currently less than 2000 items but there might be a day when this limit exceeds.

Second question, the ppn column is a lookup in resource tracker as one ppn can have multiple lines of data attached to it and thus in order to make a parent and child relationship i have had PPN as lookup column in Resource Tracker.

 

I hope you have much more clarity now.

 

Thanks @Anonymous
If these are more than 2000 items in the list, it is very challenging to get a Distinct list of values as a Collect function will only gather the first 2000 for a Distinct function to work on the collection. So my next question is how many PPN values (unique) are likely?
While there are less than 2000, you can get a Distinct list for the drop down with firstly a Collection (can be run at App OnStart or Screen OnVisible). You may have to change the names here if they are incorrect.

ClearCollect(
   colPPN,
   ShowColumns(
      'Resource Tracker',
      "PPN"
   )
)

Then the drop-down Items (in order) would be

Sort(
   Distinct(
      colPPN,
      PPN
   ),
   Ascending
)

The reason I asked about the lookup column is that (I assume you are using SharePoint) they are really unnecessary as the same lookup function can be done in Power App with the relevant lists connected and they add a layer of complexity to interacting to them from Power Apps.
For the second drop-down, showing the Region - the Items would be (I have used PPNNo for the column name and PPN for the list name in it - change as appropriate).

Sort(
    Filter(
        PPN,
        PPNNo = YourDropdown1Name.Selected.Result
    ).Region,
    Ascending
)

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.

 

Anonymous
Not applicable

@WarrenBelz Thanks for the solution, let me try it out and get back to you. 

before doing that just a quick question, there will not be many unique PPNs as PPN number can have different region. So for example, PPN5 can have a Region as China or UK. so there are 2 records created for PPN5 in PPN List. 

Also, i am aware of the lookup complexity, however, our ultimate goal is to link a resource tracker list record to a PPN, so that we can get that id and make a relationship in power bi and do our reporting.


However, you said that i can achieve that  through power apps functionality without using lookup column, could you kindly tell me how can i achieve so as i really do not want to use Lookup columns. if you have any resources for achieving this functionality without lookup could you kindly share it or at least guide me towards it?

Hi @Anonymous ,

Yes - great idea - I abandoned SharePoint Lookup columns some time time ago.

If you connect both Lists to Power Apps, the Lookup function can do the same job as SharePoint and much more. you simply Lookup the other list where you have matching values. Please have a read of it - happy to help if you have issues.

Anonymous
Not applicable

@WarrenBelz i might know the solution for it, but i am just concerned about one thing, the PPN unique SharePoint id. How do i get that if i am going by lookup column. is it possible to store the id also in one column when i select that particular ppn from the list. is it possible to write down just an example function so i am aware as to how to go about it so then i can continue further on it. 

 

it would be really helpful!

 

Thank You.

Hi @Anonymous ,'

If you use a Combo Box instead of a drop-down, you can have multiple (all if you like) columns in the data source available as Items and the DisplayFields set to what you want to see. You can then reference the ID as simply

YourComboBoxName.Selected.ID

Happy to guide you further through this.

 

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.

v-bofeng-msft
Community Support
Community Support

Hi @jayant1:

Could you tell me:

Is “PPN Column” in the share point list “” allow multiple values?

What are Region and Project Classification’s data type?

Do you use a field with duplicate values as the search field of the combo box control?

Do you want to deduplicate the list in the combo box?

Do you want to add all of the related records depend on you selection in the combo box?

Do you want to automatically fill in the textinput control based on your selection in the combo box?

I’ve made a simple test for your reference:

Resource Tracker

Column (click to edit)

Type

Required

Title

Single line of text

 

PPNColumn

Lookup

 

Region

Single line of text

 

PNN

Column (click to edit)

Type

Required

Title

Single line of text

 

PPNN

Single line of text

 

Region

Single line of text

 

Project Classification

Single line of text

  

Step1:

Add a form

DefaultMode:New

Step2:

Set the combo box control:

Items: Distinct(Choices('Resource Tracker'. PPNColumn),Value)

 

Set the data card control:

Update: Filter(Choices('Resource Tracker'.PPNColumn),Value in DataCardValue2.SelectedItems.Result)

 

b.png

Step3:

Set the TextInput control’s Default property to:

Concat(Filter([@PPN],PPNN in DataCardValue2.SelectedItems.Result),Region,",")

 

c.jpg

Here is the result:

 

d.PNG

 

 

Best Regards,

Bof

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (2,714)