cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Fier
Level: Powered On

Adding a value to AllowedValues

I have an existing datastore with some dirty data. I would like to use the drop-down field in my form to enforce allowed values.  The problem is that I can't expect the users to fix all fields in any given record all at one time, therefore I would like to include the current field from the datastore into the drop-down even if it is not an ideal value.

 

This is the current formula for AllowedValues: 

Distinct(Lookups; Fruit)

 

 

I want to try something like this:

Concat(Distinct(Lookups; Fruit); SelectedItem.Fruit)

 

But it complains that the formula expects a table. 

 

How can I include the existing field at the bottom of the AllowedValues so that it does not get overwritten by an incorrect choice when the user fixes other fields but does not know the right value for this field?

 

Thanks all!

2 ACCEPTED SOLUTIONS

Accepted Solutions
mdevaney
Level 8

Re: Adding a value to AllowedValues

Here's one more idea @Fier.  I wrote a formula that you can add to the Items property of your DropDown or ComboBox.  When you click the control it will 1st display your list of options, then the current value, and finally a blank option.  Substitute SPList with the name of your Sharepoint List, Fruit with your fieldname and DataCardValueID with the value label of the ID DataCard (usually looks like DataCardValue1).

 

Split(Concatenate(Concat(Distinct(SPList,Fruit),Result & ", "),LookUp(SPList,ID=Value(DataCardValueID.Text),Fruit),", "),", ")

View solution in original post

Fier
Level: Powered On

Re: Adding a value to AllowedValues

Awesome! I did not think of Concatenate and Split! Great thinking.

 

OK, so because one of the fields involves a format of "Surname, Firstname" the comma caused your formula to split the names into two. So I replaced the comma with a pipe in your formula (I'm sure that's a safe character). Then also, the extra field that I want to include in the Allowed Items is in the SelectedItem variable, so I could avoid referring to the datacard. Now I have exactly what I need - a table that contains all allowed items plus the one extra right at the bottom of the dropdown.  I decided to add a Sort. 

There is my new code:

Split(
    Concatenate(
        Concat(
            Sort(Distinct( Lookups; Fruit ); Result);
            Result & "| "
        );  SelectedItem.Fruit
    );  "| "
)

Now that is a neat piece of code - thank you for your input!

 

View solution in original post

6 REPLIES 6
mdevaney
Level 8

Re: Adding a value to AllowedValues

@Fier 

You can avoid overwritting AllowedValues when the user fixes another field by using a ComboBox control instead of a Drop-Down. Make sure you change the ComboBox Allow multiple select and Allow searching to false and that way it'll behave similar to a drop down.

img1.png

The best part is you don't have to build a complicated formula. You can just set the Items property of the ComboBox to the following

 

Distinct(Lookups, Fruit)


Let me know if this worked!

 

---

Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

Fier
Level: Powered On

Re: Adding a value to AllowedValues

Your proposal very helpful @mdevaney  and I have tried it out. Thumbs up for your first and quick answer.

I am using a form with DataCards autogenerated from my SharePoint list. So you can guess the amount of work required to edit each field on my form where I want to change a free-form text to a ComboBox. The controls for the field (the Y position, the width, the parent knowing what it's resulting text value should be, and some other things to boot) need to be changed for each field that I edit. I had hoped to just allow the framework to do the hard work when I change it from a control type of "Edit text" to "Allowed values" which is why I was using the Drop Down. 

 

My concern with your proposal is that the ComboBox using the same/simplified formula will still not show the user the field that was previously in the SP list. They might try to find some other field, and possibly change it to something worse.  Let me give an example based on the Fruit element... Smiley Happy

 

If the existing record has the fruit "Avocado Pear" but for consistency, we want everybody to just use the name "Avocado" (and certainly not "Avo"), then the user may know from the PowerBI report that it used to say "Avocado Pear" and can't find Avocado, so they just select "Pear". Now it's the wrong fruit, instead of an alternative name for the right fruit.  In some cases, I don't know what the clean data should be, so I can't do a background cleanup, which would also have been an easier solution. For humour, I want to add that one of the original lookup lists is actually a list of people's names, so I should perhaps have avoided the category fruit!

 

So the best solution will be for all the fields in my form that need to be cleaned to show the old text value even if it is not part of the respective permitted lookup list.

mdevaney
Level 8

Re: Adding a value to AllowedValues

I liked your explanation very much and I thought the fruit example was humorous. It gave me a very good idea of what you are trying to do.
The difficulty I have with adding the current value to the drop down is does not allow us to use a self-reference, meaning I can’t add the value of the drop down to itself. This is what I am trying to overcome to help get you an answer.
I do have an alternate idea though: could you add another label to the DataCard showing the previous value from the SharePoint List? That way they could always know what the previous value was? Let me know what you think.
mdevaney
Level 8

Re: Adding a value to AllowedValues

Here's one more idea @Fier.  I wrote a formula that you can add to the Items property of your DropDown or ComboBox.  When you click the control it will 1st display your list of options, then the current value, and finally a blank option.  Substitute SPList with the name of your Sharepoint List, Fruit with your fieldname and DataCardValueID with the value label of the ID DataCard (usually looks like DataCardValue1).

 

Split(Concatenate(Concat(Distinct(SPList,Fruit),Result & ", "),LookUp(SPList,ID=Value(DataCardValueID.Text),Fruit),", "),", ")

View solution in original post

Fier
Level: Powered On

Re: Adding a value to AllowedValues

Awesome! I did not think of Concatenate and Split! Great thinking.

 

OK, so because one of the fields involves a format of "Surname, Firstname" the comma caused your formula to split the names into two. So I replaced the comma with a pipe in your formula (I'm sure that's a safe character). Then also, the extra field that I want to include in the Allowed Items is in the SelectedItem variable, so I could avoid referring to the datacard. Now I have exactly what I need - a table that contains all allowed items plus the one extra right at the bottom of the dropdown.  I decided to add a Sort. 

There is my new code:

Split(
    Concatenate(
        Concat(
            Sort(Distinct( Lookups; Fruit ); Result);
            Result & "| "
        );  SelectedItem.Fruit
    );  "| "
)

Now that is a neat piece of code - thank you for your input!

 

View solution in original post

mdevaney
Level 8

Re: Adding a value to AllowedValues

Awesome, I’m glad we were able to figure it out together!

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Demo Extravaganza Championship Voting Open

Voting Ends: October 30, 2019!

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

Top Kudoed Authors
Users Online
Currently online: 470 members 5,231 guests
Please welcome our newest community members: