cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hnguy71
Level 8

Update/Record SharePoint entry using distinct selection from another list

Hi All. 

 

I have two lists: Team Request (List A) and Document Upload (List B). Every field in List A is a TEXT field and the idea is to grab all distinct values from List A to populate for List B and save to a SharePoint list.

 

The issue: List B can save all records except the items saved from List A and I'm not sure how to resolve this.

I have two test columns: 
1-GeoMarket (Lookup Column) to List A Column Geomarket
2- GM, Choices Column, Allow-Fill in Choices

 

I'm retrieving distinct values from List A in two ways:
Test 1 with GeoMarket Column - In Items, Distinct('Team Request', Geomarket)
Test 2 with GM Column - On Start, ClearCollect(_GM, Distinct('Team Request', Geomarket)), In Items, _GM

 

Both are able to retrieve the distinct values, however, I am unable to save it to SharePoint. When I click save all the other values in other columns gets saved except these two test columns. The error I'm receving is this:
"The property expects RECORD values, but this rule produces incompatible TEXT values"

I think I need to change my DefaultSelectedItems to something else. It's currently set to Parent.Default and Parent Default is ThisITem.Geomarket
 
How do I change this to return record values? stage1.PNGstage2.PNGstage4.PNGstage5.PNGstage6.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Update/Record SharePoint entry using distinct selection from another list

@hnguy71 

I believe in this case, your issue is with when you are collecting that list.  You mention you are doing it OnSelect.  That means that a user has to actually select the control for it to update...but what happens when they change it?

Try this on the OnChange action instead.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
13 REPLIES 13
Super User
Super User

Re: Update/Record SharePoint entry using distinct selection from another list

@hnguy71 

Yes, this is because your Distinct formula is going to return a table of Result values for the Geomarket column.

I'm not entirely sure why you would have a Distinct function for this particular column as the values you have in that distinct are coming from another list that would have potentially multiple records with the same Geomarket.  If that is the case, you will be tying your List B record to a record in List A through the lookup.  From what I gather on the lists, you have a master record (List A - Team Request) with a potential for multiple List B records - detail (Document Upload).  

If you use distinct in this case for the List A Geomarkets, you are going to potentially tie your detail records in List B to the wrong records in List A.

I would suggest that you have a List of just Geomarkets besides these lists and tie your lookup to that.  Or have a Team Request lookup column in your List B that will reference a unique record in List A.

 

That all said...again, your Distinct is returning a text value in a result table - none of which is going to be what is needed to update a Lookup column.

So, if this is truly what you need, then change your Update property to the following:

 {
    Value: DataCardValue2.Selected.Result,        
ID: Lookup('Team Request, Geomarket=DataCardValue2.Selected.Result).ID, '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"} }

This will perform the correct update on a Lookup column.  However, again, keep in mind, your lookup will return the first result it finds.  If you are expecting to Link this record to a specific record in List A (if multiple records contain similar Geomarket values) then this will be inaccurate in your final data.

 

Hope this is clear and helpful.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
hnguy71
Level 8

Re: Update/Record SharePoint entry using distinct selection from another list

Hi @RandyHayes 

 

I needed the distinct values without ties to any record to populate cascading options for the second list that way (List A - Team Request) becomes a growing list of master data / unique combinations. Just as you responded, I had this ah-ha moment. I ended up going with a different approach which works for my case currently. 

 

I turned out changing my lookup / choice columns into text columns and converting the control type to "Allowed Values" and finally returning back my distinct values. I will test on another column your method and see how that works out for me! Thanks again for always being helpful!

 

Side issue.... I want to populate / cascade options based on Geomarket. I think my filter formulas is working properly and no errors pop but I'm not seeing any result. Could you help see the issue?

 

Geomarket column is a TEXT field with a dropdown.

Main Card name: Geomarket_DataCard2
Data Value: DataCardValue7

 

SubGeomarket is a TEXT field with a dropdown to retrieve distinct value where Geomarket column is equal to selected value from geomarket card.

filter_result.PNG

 

Super User
Super User

Re: Update/Record SharePoint entry using distinct selection from another list

@hnguy71 

There are two "go to" actions that I take when troubleshooting things like this.

1) for your selected result in the drop down - throw a label on your screen and set the Text property to DataCardValue7.Selected.Result

2) throw a button on your screen and set the OnSelect to Collect(debugGM, Filter(_SubGM, Geomarket=DataCardValue7.Selected.Result))

Now, select the dropdown value you want - verify that the Label has the proper information in it that you expect. Then, click the button and then go into the View->Collections and look at the debugGM collection and see what is in it.

This will *usually* lead you right to where you have a problem.

 

See if that helps you any.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
hnguy71
Level 8

Re: Update/Record SharePoint entry using distinct selection from another list

@RandyHayes 

 

Nice debugging technique! Okay, so...

 

1- The text label is retrieving the information that I wanted.  For this test, if I chose, "GM1", I returned "GM1" in the text label.

2- Using the collection, I was able to retrieve the records that contains "GM1" and I do see what I needed in there but the results aren't being displayed in my dropdown menu.

 

Do I need to change another property somewhere?

test1.PNGtest2.PNG

 

 

Super User
Super User

Re: Update/Record SharePoint entry using distinct selection from another list

@hnguy71 

So if this is a ComboBox, what is your DisplayField property set to?  If it's a DropDown, what is the Value property set to?

Those should be set to one of the Columns that you want to actually display in the list.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
hnguy71
Level 8

Re: Update/Record SharePoint entry using distinct selection from another list

ah **bleep** it.

 

It was the Value property the whole time! Sorry to have taken your time to diagnose such a simple issue. But thanks to you, I've learned where to look for next time around.

hnguy71
Level 8

Re: Update/Record SharePoint entry using distinct selection from another list

@RandyHayes 

 

For the cascading dropdown... how do I add a blank option to SubGeomarket and default it to the blank option?

Super User
Super User

Re: Update/Record SharePoint entry using distinct selection from another list

@hnguy71 

If you want to add an additional item to your dropdown - unfortunately, there is no "in the items" way of doing it.  You will have to create a collection and use that for your items property.

What is the Items property for your SubGeomarket DropDown?  That's where to start.

 

The only thing you need to do is match types.  If you get your values from Distinct, then know that Distinct returns a table of results in a single column named Result.  So, to add an item, you create another collection and first put your value in, then the distinct values.  Like this:

   ClearCollect(colForDropDown, {Result:""}, Distinct(yourFormulaForDistinct))

 

What kind of control is this?  A DropDown control or a ComboBox control?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
hnguy71
Level 8

Re: Update/Record SharePoint entry using distinct selection from another list

Good morning, @RandyHayes !

 

The Items property of my Geomarket is a dropdown and the formula inside of it is:

Filter('Team Request', Geomarket = DataCardValue7.Selected.Result)

 

I'm thinking I should put the collection in Geomarket's onSelect property and then call the collection inside Sub Geomarket's items property (if I'm understanding this correctly).

 

EDIT:

so this is my current formula in Geomarket's onSelect property but I'm not returning any results:

ClearCollect(colForDropdown, {Result:""}, Filter('Team Request', Geomarket = DataCardValue7.Selected.Result))

In SubGeomarket's Item property I have colForDropdown

 

EDIT2:

Turns out that I am indeed getting the blank result back I want but the items returns aren't for the same GM. Any ideas? If I choose, GM1, I should have blank and sGM as options for Sub Geomarket. When I went to GM2 I get my GM1 options.

table.PNGresult.PNG

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 228 members 4,837 guests
Please welcome our newest community members: