+ Start a Discussion
rulichnyrulichny 

Getting Started - Web application to fill dropdown list with Salesforce Opportunities

Hi,
I have what appears to be a rather straightforward task but since I'm new to both .NET Web development and salesforce.com, I figured I'd ask before I get started.

We are creating a Web application that needs to show users a list of Salesforce Opportunities and allow them to make a selection. The selection will be stored in our own SQL Server tables along with other non salesforce related data that is entered on the screen.

Is this doable? From what I am reading I need to create and download the enterprise.wsdl. I also saw sample applications but wasn't sure which one was most appropriate for my needs. We are using vb.NET and Visual Studio 2003 Enterprise edition for our development.

Thanks in advance for your help.
J_HinderJ_Hinder
Freaky...I just did something similar the other day, and I rarely use Vb.Net. Sounds like you'll want to store the Id in your database to do lookups, but you might want to consider using salesforce to host the data and not using the SQL database on your side. Oh, and, and the web reference that I created to the Partner WSDL is called Sforce_Account_Lookup. You'll need to create this, and an aspx with a dropdownlist on your end for this to work :



Protected WithEvents Opportunities As System.Web.UI.WebControls.DropDownList

Private Sub (etc....)
'Do our login, Create the session
Dim SSO_Service As New Sforce_Account_Lookup.SforceService
Dim lr As Sforce_Account_Lookup.LoginResult
Dim dlr As Sforce_Account_Lookup.DescribeLayoutResult

'Username and password are stored in the web.config appsettings
Dim strUserName = ConfigurationSettings.AppSettings("Sforce_Username").ToString()
Dim strPassword = ConfigurationSettings.AppSettings("Sforce_Password").ToString()
lr = SSO_Service.login(strUserName, strPassword)

SSO_Service.Url = lr.serverUrl
SSO_Service.SessionHeaderValue = New Sforce_Account_Lookup.SessionHeader
SSO_Service.SessionHeaderValue.sessionId = lr.sessionId

'Create our QueryResult
Dim qrOpps As New Sforce_Account_Lookup.QueryResult

'Hit the API for open Opportunities
qrOpps = SSO_Service.query("SELECT Name, Id FROM Opportunity WHERE IsClosed != true")

'Add them to our Dropdownlist
For k As Integer = 1 To qrOpps.size

iRecord = k - 1
Dim li As New ListItem
li.Text = qrOpps.records(iRecord).Any(0).InnerText
li.Value = qrOpps.records(iRecord).Any(1).InnerText
Opportunities.Items.Add(li)
Next

End Sub
rulichnyrulichny
This sounds like exactly what I was looking for. Thanks.

I cut and pasted your code but I received an error when trying to build the app:

'Any' is not a member of 'WebApplication1.sforce.sObject'.

The error is being produced on line with following code:

li.Text = qrOpps.records(iRecord).Any(0).InnerText

I apologize if this is a dumb question but can you please assist. Thanks.


Rich
SuperfellSuperfell
You're building you code with the enterprise WSDL, but the code above uses the Partner WSDL. I'd suggest you start with the .NET quickstart.
rulichnyrulichny
Thanks..I was able to utilize the .NET Quickstart as a basis for my application and get it to return data.

As it turns out, I will need to show the customer a list of Accounts (not Opportunities) in the drop down box. Once an Account is selected, I am going to fill another dropdown box with Open Opportunities for that Account (using the Account ID from the first selection).

Should I do that via a Query or I've read somewhere that if I have the ID, I should do a Retrieve instead. Anyone have sample code for that?

Also, the customer has about 6,000 accounts and filling a dropdown list with that could be a problem. I seem to time out about half the time. Any suggestions for working around that? Perhaps I can ask them to type in a partial Account name before I go and grab the list of Accounts using the LIKE option in my WHERE clause. Also, since the list comes back unsorted (and there is no 'order by' option from what I can see) I assume I'll have to do some sort with my app. Does anyone have a sample for that?

Thanks
J_HinderJ_Hinder
If you're thinking of going the search route, SOSL has been faster for me than SOQL has and it's really easy pick up. You might want to take a look at it before trying the LIKE statement.

For sorting, you can do a number of things. Commonly, you'll probably find one of these two:

1. Take the results and load them into a dataset create a dataview for the dataset and sort that. Nice if you're using .Net datagrids and don't have a lot of results.

2. You can use an arraylist and sort the arraylist. That code would be something like what's below, but in truth I didn't compile it to test it out. Oh, and this will only create a sorted arraylist. You will need to loop through the list and split it on the ":" to populate your dropdown.

Dim itemsArray As ArrayList

If (qrMyQuery.size > 0) Then
For i As Integer = 0 To qrMyQuery.size - 1
itemsArray.Add(qrMyQuery.records(i).Any(1).InnerText & ":" & qrMyQuery.records(i).Any(0).InnerText)
Next

itemsArray.Sort()
End If
rulichnyrulichny
Thanks for the tips. It seems the more answers I get the more I need to learn cause I'm not clear on many of the things you mentioned. I'll have to read up a bit on SOSL vs SOQL. I'm guessing that'll be the easy part :-).

As far as returning 6000+ rows and sorting them, is it easy to put them into a Datagrid/Dataset? As you can tell by my questions, .NET development is new to me.

Since I don't plan to show the results via a datagrid I assume I would just make that invisible to the customer and use it for sorting, etc. I would then need to add those sorted results to my dropdown list. Does that sound like what you are suggesting?
SuperfellSuperfell
You're just setting yourself up to fail if you continue down the road of putting 6000 entries in a dropdownlist, you need a different UI design.
J_HinderJ_Hinder
No doubt. 6000 records is way too much for a dropdownlist. And, if you were to go the dataset route it would probably take way too long. They can be slow. That said, I've written a .Net control that works alot like the autofill boxes you see in some versions of outlook. I.e., when you start to type into a texbox, the results in your dropdown list will be reduced to match letters you've input. It took a long time to do, though, so just giving it away probably isn't gonna happen. Plus, it would be way too much code to put in here. If you're interested in it, you can contact me at joehinder11@hotmail.com, though. You also might be able to find something off the shelf that would help you out.
rulichnyrulichny
I agree, 6000 records in a dropdown list is not a good idea for lots of reasons. I was thinking about asking customer to input a partial Account Name before going to salesforce.com to grab a list of names that match that crieria. At that point, I could perform a search and only show them names that match their criteria. And perhaps give a warning if number of rows returned exceeded some predetermined maximum.

How's that sound?
J_HinderJ_Hinder
How'd it work out for you?
rulichnyrulichny
After further discussion with the customer, it was determined that data in Salesforce did not need to be accessed 'real time' from our Web application (and into salesforce.com). A nightly (or possibly even a week) pull of data into our SQL Server tables would suffice.

As a result, I am creating a simple VB.NET application (not web app) that can be scheduled to run nightly and pull the necessary data from Account and Opportunity. Once the data is in our tables, we can sort it and present it to the customer via a dropdown and other search options as previously discussed.

So far I've written the code to pull the Account info as needed. Seems fairly straightfoward as we only need a few fields from the table. If I hit any snags I'm sure I'll post other questions! Thanks for your help.
qmanqman
DBAmp does exactly that (table replication) for over 800 companies today. In addition, it uses the SQL Bulk Loader interface to rapidly fill the sql tables and handle schema changes automatically.

Bill Emerson
www.forceAmp.com