Edit Form Driven by Drop Downs (versus Vertical Gallery)

[pfinseth]

Is there a way to use multiple drop downs to populate the Edit Form for that selected record?  Example, I want the user to select a Make, Model, and Serial Number through a series of cascading drop downs.  I then want the Edit Form to populate for that selected record.  I have tried filter under "Item" of Edit Form and other variations to make this work and I get all sorts of errors.  I don't want to use a vertical gallery because it takes up too much space and doesn't look as good.  Thanks!

[AmDev]

Hi @pfinseth  - What type of dataset are you using and how many records are in it? - and could be in it?

Is your use case just to edit existing records, or would you be using the same rationale to add new records?

[pfinseth]

It’s SQL Server based.  Table has 40k records. Users will edit the selected record. Creating a new record is a legitimate scenario that needs to be included into business although very infrequent. 

[vwyankee · Accepted solution]

Use the drop-downs filtered to the SharePoint list based on the other drop-down selected values. Presuming they're all in the same list you can do this with the Model drop-down filtered on what's selected by the Make drop-down and then the Serial Number drop-down filtered by both Make and Model selections. Use the Distinct operator so you don't get duplicates in your drop-down selection choices - like this:

SP list - Inventory - has columns Make, Model and Serial Number.

ddMake.Items: Distinct(Inventory.Make, Make)

ddModel.Items: Distinct(Filter(Inventory, Make = ddMake.Selected.Result).Model, Model)

ddSerialNumber.Items: Filter(Inventory, Make = ddMake.Selected.Result And Model = ddModel.Selected.Result).SerialNumber

Form1.DataSource: Inventory

Form1.Item: LookUp(Inventory, SerialNumber = ddSerialNumber.Selected.SerialNumber)

*EDIT* 

I didn't see the question about the data source prior to this, but the same thing should work with SQL DB, but comboboxes will be a better option than drop-downs since you can search within when you're dealing with that many records. I've used them with SQL lookups with 25k records without an issue.

[AmDev]

Hi @pfinseth - I was hoping you were going to say SharePoint. I was half tempted to look into this same solution for SQL, but I risk wasting your time and still getting it wrong, so I am tagging some of the super users in the hope that some of them have SQL experience and can assist - sorry to have wasted any of your time.

Good luck!

@RandyHayes  @StalinPonnusamy  @Pstork1  @WarrenBelz  @CNT  @timl 

[Pstork1]

Instead of Filter() you want to use Lookup() in the Item property of the form.  Filter() will return a collection of records so you would always have to enclose it in something like First() to get a single record instead of a table.  But Lookup(datasource, condition ) will always return the first record that matches.  Condition in this case would use the selected values from the cascading dropdowns to find the record.

[pfinseth]

Didn’t waste any of my time! I appreciate the response and now have a handful more responses to look at! Appreciate the assistance! 

[pfinseth]

Thank you all!  @vwyankee  I switched to Combo Boxes and using the lookup worked great!  I thought I had done the lookup before but I kept getting an error message.  Now everything works well!