Allowed values from Excel table

[WebPortal]

My app has a dropdown table that lets users select a month in text form like: "Jan-2020", "Feb-2020", etc.

The app is connected to an Excel file in OneDrive.

The AllowedValues property is set to:

["Jan-2020";"Fev-2020";"Mar-2020";"Abr-2020";"Mai-2020";"Jun-2020";"Jul-2020";"Ago-2020";"Set-2020";"Out-2020";"Nov-2020";"Dez-2020"]

Now, I want to restrict this list to exclude the values already recorded in the Excel table (column "Periodo").

So, how to express this logic in the AllowedValues property using PowerApps formula? It should read the column from Excel and then deduct the existing months from the list above, but how? 

[WebPortal]

Hi @mdevaney 

Here it is. I have no ideia why the red underline disappears with the screen shot.

[WebPortal]

@eka24 

Now I get a blank dropdown.

[eka24]

Because it is Onvisible formula, goto a different screen and then go back to the screen. It will work after that. So if the dropdown in screen1 just goto screen2 then back to screen1 

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[mdevaney]

@WebPortal 

By the way, my code goes in the Items property of the ComboBox, not AllowedValues.  It achieves the same thing while hiding the options for the user to select invalid values.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[WebPortal]

@eka24 

I have been doing that, because the OnVisible will create the collection only when the screen is displayed, but it doesn´t work, I always get a syntax error.

All I need is a simple subset of rows, from a table with a single column, how can this be so complicated?

[eka24]

Can you show the error. I tested it before posting

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[WebPortal]

@eka24 

There you go in attachments, tell me I'm crazy.

[eka24]

Please a screen shot or picture of the error.

What you sent is the app itself.

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[WebPortal]

Exactly @mdevaney @eka24 , I sent you the app so you could see for yourself the errror:

[eka24]

Am revising the approach since your date list is not in a table.

OnStar of the App put:

Set(MyDatTable,

Table({MonthYear: "Jan-2020"},

     { MonthYear: "Fev-2020" },

     { MonthYear: "Mar-2020"},

     { MonthYear:  "Abr-2020"},

     { MonthYear: "Jun-2020"},

     { MonthYear: "Jul-2020"},
      {MonthYear: "Ago-2020"},

     { MonthYear: "Set-2020" },

     { MonthYear: "Out-2020"},

     { MonthYear:  "Nov-2020"},

     { MonthYear: "Dez-2020"}

   ))

Then filter the Dropdown as :

Filter(MyDatTable,Not(MonthYear in (PeriodosExistTentes.Perodos)))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.