Update/Record SharePoint entry using distinct selection from another list

[hnguy71]

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? 

[RandyHayes]

@hnguy71 

Yes, you will not see results with that - the reason is, you are not using the Distinct function.

It is the Distinct function that returns a list of Result values.  (Single column of Result).  So you can use the {Result:""} for that. 

In this case you are using a Filter, so you need to match whatever column you want to make blank.

For example, if it is the Title column you are displaying in your dropdown, then you would replace the result object with {Title:""}.  Or, if it is the GeoMarket column you are displaying, then {Geomarket:""}.

With this formula you are adding a record to a collection and then adding zero or more records to that same collection from another function.  You HAVE to match the types.  In the case of the Distinct, we used a {Result:""} record because that matched the fact that Distinct returns a table of single column records with a Result column.

I hope that is clear and helpful.

[hnguy71]

Hi Randy. 

I think I was editing my previous post when you replied. Do you think you can take a look at that? 

your reply did clear up some confusion but alas I'm still not seeing the result that I require:

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

I'm doing the debugging test you showed me yesterday and this time it's retrieving the wrong records 

[RandyHayes · Accepted solution]

@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.

[hnguy71]

Hi @RandyHayes 

Looks like that did the trick! I also noticed that my Value Property defaulted and I had to reselect my reference column again but I presumed that was because I changed items Property formula.

Whew! What a way to start using PowerApps. Thanks for your help!

[TomCollins]

Hi Randy

I'm sorry, I may come with a stupid question, but I've only been using powerapps this week. I have exactly the same problem and want to update a lookup column with a text value. Your code seems to be exactly what I need. But now I get an error message. In your code snippet I also see a special character after "Value: DataCardValue2.Selected.Result," and there are only 3 bracelets:

Do you have a hint for an advice for a beginner?

Best regards

[RandyHayes]

@TomCollins 

Wow...that's an OLD post!!  So much has changed!

In Red below...

- Get rid of the extra opening parentheses.

- You can eliminate the @odata part...it's no longer needed.

- Get rid of the comma at the end of the ID line

- Change the ID to Id

- Add a closing curly bracket.

[TomCollins]

Thanks a lot