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?

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 

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.

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
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (3,980)