cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate IV
Advocate IV

Use a ComboBox with data from another database

Hi there, I'm getting in the powerapps world and I'm trying to create a simple app that manage customer contracts. So the idea is to create a Sharepoint list to hold the contract information and on top of the SharePoint list to apply a PowerApps  that allow my users to add new contracts. Where I'm stuck is in the form creation 'cos I want to create a ComboBox to display the customer names which exist in a SQL database. So I'm not sure how to show the ComboBox inside the edit form so once the user select the customer name and fill all the others fields the info will be saves into the SharePoint list.

 

Thanks for any guidance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Use a ComboBox with data from another database

Hi @diegoadum

 

Here's a post I wrote the other week on how to use the ComboBox control on a form.

 

https://powerusers.microsoft.com/t5/Creating-Apps/Drop-Down-Box-Lookup/m-p/82049#M827

 

Once you build a form that's based on your SharePoint list, you would change the control type of your customer card to 'Allowed Values'. This changes the control in your customer card from a text input control to a combo control. You would then set the Items property of your combo control to your SQL table, for example, '[dbo].[tblCustomer]'.

 

The final step is to configure the update property of the customer card to save the combo box value to your SharePoint list, and to configure the default value of the combo box control to show the selected customer when the form loads an existing record. The link above provides some sample code that you can adapt to suit your needs.

 

View solution in original post

8 REPLIES 8
Highlighted
Super User
Super User

Re: Use a ComboBox with data from another database

Hi @diegoadum

 

Here's a post I wrote the other week on how to use the ComboBox control on a form.

 

https://powerusers.microsoft.com/t5/Creating-Apps/Drop-Down-Box-Lookup/m-p/82049#M827

 

Once you build a form that's based on your SharePoint list, you would change the control type of your customer card to 'Allowed Values'. This changes the control in your customer card from a text input control to a combo control. You would then set the Items property of your combo control to your SQL table, for example, '[dbo].[tblCustomer]'.

 

The final step is to configure the update property of the customer card to save the combo box value to your SharePoint list, and to configure the default value of the combo box control to show the selected customer when the form loads an existing record. The link above provides some sample code that you can adapt to suit your needs.

 

View solution in original post

Highlighted
Advocate IV
Advocate IV

Re: Use a ComboBox with data from another database

Thanks for your help and the post. It's very self explanatory and help me a lot. Nevertheless I'm having some hard time trying to figurate out how to handled the step #8 in your post.

 

LookUp(Instructors, ID = Parent.Default).Name

 

In my case CustomerDim instead Instructors, but the issue since to be with the Parent.Default. I got a red line below Default so I'm pretty much sure I'm clicking not the right place to do this.

Highlighted
Super User
Super User

Re: Use a ComboBox with data from another database

Hi @diegoadum

 

I'm assuming that you're using a text column to store your customer name in SharePoint. Therefore, I suspect you're seeing this error because Parent.Default refers to a text column in SharePoint and ID possibly refers to the numeric primary key value in your SQL data. The mismatch in data types would be the cause of this problem.

 

The Lookup function finds the first record in a data source that matches a condition. Let's assume that this is the schema of your SQL table:

 

CustomerDim(ID, Fullname, Address)

The Lookup syntax you would use would look something like this:

 

LookUp('[dbo].[CustomerDim]', Fullname = Parent.Default).Fullname

If you hover your mouse over the red line, the designer should give you a more precise description of the error.

Highlighted
Helper I
Helper I

Re: Use a ComboBox with data from another database

I was able to get this to work but the problem I am having is with a multiple select it will only upadte the last value

 

i.e. in the combox the values are A, B but when I submit the form to the sharepoint list only B is being submitted.  Any help is greatly appreciated.

 

 

 

Damien

Highlighted
Anonymous
Not applicable

Re: Use a ComboBox with data from another database

Hi @timl ,

 

It tells me the link is restricted. Is there anywhere I can also see the post you have made with explanation? It would be much appreciated!

Highlighted
Super User
Super User

Re: Use a ComboBox with data from another database

Hi @Anonymous 

I think the orginal post I made must have been deleted. Maybe @Anonymous or one of the other admins might be able to trace it?

Alternatively, if there's anything more specific that you need help on, feel free to make a new post and tag me. I'll hapily take a look for you.

Highlighted
Anonymous
Not applicable

Re: Use a ComboBox with data from another database

I very much appreciate it. Thank you @timl 

Highlighted
Regular Visitor

Re: Use a ComboBox with data from another database

This thread is very helpful. I'm having one issue where the selected value when filling out the sharepoint form does not save the data selected from the SQL server to the column in the sharepoint list. I suspect it has something to do with my update and default values. Right now I have them both set to ThisItem.Client.

 

I can't access the link provided in this thread. If you have any ideas please let me know. Thanks!

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (5,353)