cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

Allowed values from Excel table

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? 

24 REPLIES 24

Hi @mdevaney 

 

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

 

Anotação 2020-04-30 190233.jpg

@eka24 

 

Now I get a blank dropdown.

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.

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

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

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.

@eka24 

 

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

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.

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

 

nnnn.jpg

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.

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (54,507)