cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pfinseth
Frequent Visitor

Edit Form Driven by Drop Downs (versus Vertical Gallery)

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!

1 ACCEPTED SOLUTION

Accepted Solutions
vwyankee
Helper V
Helper V

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)

 

vwyankee_0-1637624164915.png

 

*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.

View solution in original post

7 REPLIES 7
AmDev
Super User
Super User

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
Frequent Visitor

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
Helper V
Helper V

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)

 

vwyankee_0-1637624164915.png

 

*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
Super User
Super User

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
Dual Super User
Dual Super User

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Join me at 365EduCon in Chicago on Sept. 26-30 where I'll be presenting on the Power Platform!
pfinseth
Frequent Visitor

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

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!

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

<
Users online (4,247)