Cascading drop down list.

[Anonymous]

Hello,

I'm building a inventory app and I'm trying to create a drop down list with my columns from SQL in it and unsure how to get that drop down list containing my column names. I did try the combo box and put Distinct(Datasource, column) in items; however, I want to put more than one column in there, but it gives me an error. I did see some collection ideas but I'm unfamiliar with it and how it works referring back to SQL.

Any ideas on how to create one or formulas?

Thanks,

Jvang

[RandyHayes]

@Anonymous 

Can you provide a little more detail on what it is that you want for your dropdown control?  Also, are the other dropdowns related to the same table in your datasources or are they others?

No collections are needed for this, but Distinct is only going to return results from one column.  So, need to know a little more about what you are looking for.

[Anonymous]

@RandyHayes,

Yes, the other drop downs are related to the same table. I am trying to have it where I can select a Bin which are my columns (a01b, a02a) on the table and then have the user enter the amount per PartNumber in that bin location, when saved it goes back to SQL and have the date default as Today().

Thanks,

Jvang

[RandyHayes]

@Anonymous 

Okay, so I need a little more clarification then on your columns.

Am I guessing properly that the "b" columns are bin numbers and the "a" columns are the amount columns?

Either way, this is a bit of a challenge that all of the varieties of bins and amounts are all columns, and not either related data or rows of the same or different source.

Can you explain the data layout a little more?

[Anonymous]

@RandyHayes,

I have a simple layout.

PartNumber are my PK

Bins are my columns that I am able to Insert/Update/delete items in. The (a01a, a02a) are just names/locations where we store those partnumbers in.

I have date created which is set to a date for all partnumbers and date modified when ever data is modified within power apps default to today().

Thanks,

Jvang

[RandyHayes]

@Anonymous 

This would be better served with a parent child related table.  Are these columns static?  What happens if you change the bin count (remove or add new bins)?  If you are just adding more columns, this will be problematic from the PowerApps standpoint because you cannot reference columns dynamically.  It needs to know actual column names at all times.  So, if you change your table schema columns, then you will always need to change your app at the same time.

Am I guessing properly that the "b" columns are bin numbers and the "a" columns are the amount columns?

Can you clarify what you would shown in the Bin Location dropdown that you have in the app?

[Anonymous]

@RandyHayes,

In the Binlocation dropdown I would like to select/search a Bin number/location according to the columns I've listed.

Then in the Amount section, enter in the amount for that location.

The 1st screenshot is what I have at the moment and works, but I want it to be more user friendly and not scroll down long list of binlocations.

Thanks,

Jvang

1.

2.

[RandyHayes · Accepted solution]

@Anonymous 

Yes, so you're going to be relegated to doing this manually as there is no way to get the column names in a list, nor to determine the corresponding record to write.  This again is another major reason to consider a master/child related table for your bins.  Those you would very easily be able to do in the app.

Otherwise, your Items property of the Bin dropdown is going to have to be a hand-written list of column names, but that by itself will not give you what you want because you're then expecting to lookup the corresponding value for that column to display in the amount entry...and then needing to figure out how to put that back into a usable record for the update.

This can be done with a lot of Switch and If statements, but there is another slightly simpler way to go about it.

Since your Form will default to having all of the bin names, you can utilize the Visible property of the DataCard for each bin datacard.

So first, you would need to manually add all the bin/column names to the dropdown.  So the Items property would be:  ["a01b", "a02a", "a02b", ...etc...]

Then, the Visible property of your Datacards for those bins would be: yourDropdown.Selected.Value = Self.DataField

This would make any datacard corresponding to that dropdown selection visible.

Your form would function normally at that point with a normal SubmitForm.  There should really be no other changes to anything in the datacards for the bins.

[Anonymous]

@RandyHayes,

Ah ok, so I would need to make another table and tie them together in order to do so. I guess I could just add a search bar and filter the bin numbers.

Thanks,

Jvang

[RandyHayes]

@Anonymous 

Well, that is another way to go and would involve changing your data tables. 

What I last mentioned was to utilize the CURRENT table structure that you have right now.  The process I mentioned last will be the most simplistic way to do this in a Form with the table structure that you have.

If you go with a parent/child table structure, then you would have a Bins table with a Name and amount column, plus a PartNumber column.  This could be related in a traditional relationship to a PartNumber table, or left into a "self defined" parent/child table (meaning that there will be multiple rows for PartNumbers.  Filters/queries would determine bins and counts for part numbers)

Now, if you switch to the above type of table for your data, the Form approach would still work, but there would be a whole different way to implement it.