Problem with Dropdowns in Editform

[Anonymous]

Hi,

Let me preface this by saying I'm totally new to PA, but I'm trying to create a dropdown control in an editform within a canvas app using one Excel table as the "main table" and a second table for a lookup. Both tables have been set up (along with a third table) for all of this, but I'm getting a single blank row when I try to use the dropdown. Please help!

[RandyHayes]

@Anonymous 

I'm assuming that the Machine_Lookup is the correct datasource and MachineTypes is the correct column.

If so, your formula looks correct and would work.

So, what next? You might want to test out what is going on to make sure you are really getting information you think you should.

I would throw a button on the screen and set the OnSelect to : ClearCollect(myTest, Distinct(Machine_Lookup, MachineTypes))  Then click that button (in play mode, or with the "design-time click").  Then go into View->Collections and look for your myTest collection.  Click on it and view the data in it.  Should be a single column table of results.

Nothing there? Change your OnSelect to : ClearCollect(myTest, Machine_Lookup)  Then again, click it to do it and then look at the collection myTest.  

Nothing there? Then there is an issue with your datasource.

At any point in these tests, if you are getting information, make sure it is what you expect.

If you got information all through them, then put your actual Items formula from the DropDown in the OnSelect : ClearCollect(myTest, Sort(Distinct(Machine_Lookup, MachineTypes), Result)) and check the collection again.

I hope this is helpful and leads to an answer for you.  If anything, it will at least show you a pretty common troubleshooting technique. 

[Anonymous]

Thanks for the response. For the myTest, is that a collection I have to create? If so, how do I do that? If not, what should that be referring to?

[RandyHayes]

@Anonymous 

Yes, it will create a collection.  You need not do anything more than those steps and actions I outlined.

If you get stuck on any one in particular, just post back.

[Anonymous]

Ok, ran it both ways and the first time it came up with a single cell that said "Result" with nothing under it; second time with a single cell of "MachineType" and nothing under it. Not sure where to go from there. I should add that my sources show up under the Data Sources view, and when I try to refresh them, they seem to be fine. They are Excel files saved in my work OneDrive folder.

[RandyHayes · Accepted solution]

@Anonymous 

Okay...that's a good test!

You did the second one - ClearCollect(myTest, Machine_Lookup) and that returned nothing.  So, you need to look at your data source called Machine_Lookup.

If this is a Table in your Excel file, then how did you add that to your App?

You might simply delete the Machine_Lookup data source and then add it back in, in case there was some issue when you first added it.

In general, you will be adding a data source by first clicking add data source then selecting your OneDrive for Business connector, then navigating to the Excel file, then selecting the Table.

I'd remove the current data source and add it back, then click on your Button again with that formula in it above. Then look at the collection...it should have a table with all the Machine_Lookup table information.

See where that gets you.

[Anonymous]

Weird, I removed the source and re-added it exactly as I had added it in the first place, but seems to be working now. Thanks for the help! Regarding that test collection, should I remove that? How do I do that?

[RandyHayes]

@Anonymous 

Excellent!

Yes, just delete the button...the collection will go away too.

[Anonymous]

I figured out what the issue is. If you create a table and link it to the PA as a lookup, it doesn't work initially because the PowerApp column hasn't been created yet. I'm not sure if this is a known issue or if there is a more proper way of adding tables as data sources. But simply removing the table after the initial add-in and then re-adding it seems to take care of it.

[RandyHayes]

@Anonymous 

Or possibly something went wrong the first time?  I don't work with the Excel data sources all that often, but what I had noticed is that the first time you add a table from an Excel file to your App through the add data source process, it then creates the PowerApps column.  So, even before you use it for anything.

Odd...but the good news is you're up and working!