New Value not showing in Distinct Value

(0) Share

Report

Posted on by StuCundy

377

I have a dropdown reading a collection. Using the screen OnVisible function I have the following clearcollect

ClearCollect(ColHostOwner, {Result: "Select Host Owner"});
Collect(ColHostOwner, Distinct(HOSTFAMILIES,Owner.Value));

My issue is that I just entered a new Item with a new Value in the column "Owner" I have refreshed all data.

It could be about the way I add the new Value... I used the edit column in the sharepoint list to add a new choice. I thought the clearcollect would just pick the new Value up as a new option.

Look forward to someone setting me right.

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

Jeff_Thorpe 6,085 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
I think you may need to refresh the data source to get the new value to show.

Example:

ClearCollect(ColHostOwner, {Result: "Select Host Owner"}); Refresh(HOSTFAMILIES); Collect(ColHostOwner, Distinct(HOSTFAMILIES,Owner.Value));
WarrenBelz 156,520 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Please disregard - @Jeff_Thorpe has already answered
StuCundy 377 on at

Like
a
(0)

Report
Copy link

Link copied!
I have actually refreshed the data source manually before now but now I do indeed have the following
ClearCollect(ColHostOwner, {Result: "Select Host Owner"}); Refresh(HOSTFAMILIES); Collect(ColHostOwner, Distinct(HOSTFAMILIES,Owner.Value))
It does not seem to make a difference. Could it be that because this is a new item No2259 and the Owner Value is a new one that it is not being picked up?
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @StuCundy ,

Do you add a new option for the Owner Choice type column in your SP List?

Do you add a new SP List Item with the new added Owner Choice option in your SP List?

Firstly, you made some changes to columns in your SP List, please consider refresh the SP List data source in your app manually, to make the changes take effect in your canvas app.

Based on the formula that you mentioned, I could not find any syntax error with it. Please make sure you have fired the OnVisible property of the screen properly. The OnVisible property of a screen would be fired only when navigating to current screen from another screen. Please switch to other screen, and then navigate to the current screen which holds the OnVisible formula, then check if the issue is fixed.

You could consider add a button in your app, set the OnSelect property to following:

ClearCollect(ColHostOwner, {Result: "Select Host Owner"}); Refresh(HOSTFAMILIES); Collect(ColHostOwner, Distinct(HOSTFAMILIES,Owner.Value))

press above button. Then add a Data Table control in your app, set the Items property to following:

ColHostOwner

then check if the new added option has been collected into the ColHostOwner collection.

Note: Please make sure the new SP List Item with new added Owner Choice option has been added into your SP List already.

If the issue is still exists, please remove the SP List connection from your canvas app, then re-add a new connection to your SP list from your canvas app, try the formula again, check if the issue is fixed.

Best regards,
StuCundy 377 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @v-xida-msft
Yes I do add the new option for the owner choice type in the SP list and I have selected that option for a new item. I do have over 2000 items. I think what I need to do is Filter the Collection before I create the ColHostOwner. So I have now a new dropdown. see pic.

I would like to filter the ColHostOwner collection by the ColHostStayType.
ClearCollect(ColHostsStayType,{Result:"Any Type"}); Collect(ColHostsStayType,Distinct(HOSTFAMILIES,TypeOfStay)); ClearCollect(ColHostOwner,{Result:"Any Owner"}); Collect(ColHostOwner,Distinct(HOSTFAMILIES,Owner.Value));

I think I need to create a Host collection based on the HostStayDropdown selection called "ColHostbyStayType"
ClearCollect(ColHostsbyStayType,Filter(HOSTFAMILIES, TypeOfStay = HostTypeDD.Selected.Result)); ClearCollect(ColHostOwner,{Result:"Any Owner"}); Collect(ColHostOwner,Distinct(ColHostsbyStayType,Owner.Value));

My old drop down to select a host shows
(If( HostOwnerDD.Selected.Result="Any Owner" And HostTypeDD.Selected.Result="Any Type",HOSTFAMILIES, HostOwnerDD.Selected.Result="Any Owner" And HostTypeDD.Selected.Result<>"Any Type", Filter(HOSTFAMILIES, TypeOfStay = HostTypeDD.Selected.Result), HostOwnerDD.Selected.Result<>"Any Owner" And HostTypeDD.Selected.Result="Any Type", Filter(HOSTFAMILIES, Owner.Value = HostOwnerDD.Selected.Result), HostOwnerDD.Selected.Result<>"Any Owner" And HostTypeDD.Selected.Result<>"Any Type", Filter(HOSTFAMILIES, TypeOfStay = HostTypeDD.Selected.Result And Owner.Value = HostOwnerDD.Selected.Result) ))
I think the Data Source now needs to be the "ColHostsbyStayType" and I will need to patch the result to the SPlist.

and I on the right track as It's now working yet. May nee help in the patch function is we are getting close.
Verified answer

v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @StuCundy ,

Does the New added option value show up within the HostOwner Dropdown box properly?

If the new added option value still could not show up within the HostOwner Dropdown box properly, I think this issue is related to Delegation.

Currently, the Distinct function is not a Delegable function in PowerApps canvas app, so the Distinct(HOSTFAMILIES,Owner.Value) formula could only process first 2000 records of your SP List at most locally. If the new added List Item (with the new added Owner Option value) is more than 2000, the Distinct(HOSTFAMILIES,Owner.Value) formula would ignore it.

In addition, there is also an known limit with the Collect or ClearCollect function -- You could only retrieve 2000 records at most once time from other data source into a collection. So if the records the

Filter(HOSTFAMILIES, TypeOfStay = HostTypeDD.Selected.Result)

formula returned is more than 2000, it could only collect first 2000 records into your ColHostsbyStayType collection.

Note: If the new Owner option could be shown up properly in your HostOwner Dropdown box, please ignore above reply😉.

Based on the screenshot that you provided, I think you are submitting the form data back to your SP List, is it true? Could you please share a bit more about the HostTitle field in your SP list, is it a Text type field or Choice type field?

If the HostTitle field is a Text type field in your SP list, please set the Update property of the HostTitle data card to following:

HostTitleComboBox.Selected.HostTitle

If the HostTitle field is a Choice type field in your SP list, please set the Update property of the HostTitle data card to following:

{ Value: HostTitleComboBox.Selected.HostTitle.Value }

If you want to use Patch function to submit your data back to your SP List, please try the following formula:

Patch( HOSTFAMILIES, Defaults(HOSTFAMILIES), { Title: "xxxxx", ..., HostTitle: HostTitleComboBox.Selected.HostTitle, // HostTitle is Text type column .... } )

or

Patch( HOSTFAMILIES, Defaults(HOSTFAMILIES), { Title: "xxxxx", ..., HostTitle: { Value: HostTitleComboBox.Selected.HostTitle.Value // HostTitle is Choice type column }, .... } )

Best regards,