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

[hnguy71]

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.

[RandyHayes]

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

[hnguy71]

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

[RandyHayes]

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

[hnguy71]

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]

@RandyHayes 

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

[RandyHayes]

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

[hnguy71]

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.