Apex Code Development You need to sign in to do that
Don't have an account?
Scot VBScript and Toolkit: some conclusions?
I've come to a number of conclusions on the use of VBScript with the COM Toolkit. I'd like to share them here in the dual hopes that:
1) If anyone has a better answer or a contradiction, I'll find out, and
2) If not, these may help others who follow ...
I'll post each conclusion as a child of this note.
If you have feedback on one of the conclusions, please comment on it.
If you have an additional conclusion, please feel free to add it. If you add new ones as comments to this comment, then we may get a set of useful threads eventually .
Scot
You must put the id(s) for retrieve into an array:
The syntax for Retrieve is
Function Retrieve(fieldList As String, EntityType As String, idList, async As Boolean).
The idList must be an array.
Example:
To retrieve a single opportunity, based on a merge field from an sforce control:
This fails:
dim qrs
set qrs = sfdc.Retrieve("*","Opportunity","{Opportunity_ID}",false)
But this will work fine:
You must use a FOR EACH to obtain the objects from a query result set ...
even if there is only one object ...
There does not appear to be a syntactical way to reference an element of the query result set other than to use the for each construct.
A corollary ... if you need to work with the result as a set, you'll need to transfer the results into array(s) or collection(s) within the scope of a for each.
Example:
Retrieving an opportunity within the context of an scontrol:
' GET THE QUERY RESULT SET
' GET THE OPPORTUNITY OBJECT
Now, you can reference items in the opportunity, as in:
if opp.Item("Area__c").value = "" then .....
or
if CInt(opp.Item("Probability")) > 49 then ....
Hi Scot,
I love this thread. I'm am getting ready to release this toolkit as GA and once I do, these interfaces will be set and difficult to modify. A conclusion is a way of saying, "after trial and error, this is how to work with the toolkit", very diplomatic!
So, If in addition to your conclusions, you can also include suggestions as to a better way to deal with the data, I would really appreciate it. I don't want to go GA and then regret the design decisions or comprimise the utility of the toolkit.
Please add any other "conclusions" within the next 2 days as we are under pressure to GA this thing.
Hi Dave,
Actually, I didn't intend them as a diplomatic complaint. I think my problems are more in the area of learning VBScript and the Toolkit and Style sheets and the HTML DOM model all at once - and finding it, not surprisingly, difficult to pull the knowledge together quickly.
I figured that others might be in the same boat. I wan't expecting to change the Toolkit, but to learn the VBScript techniques to work with it.
I've found the Toolkit pretty reasonable to use within VBA, but VBScript seems much more difficult. If I had better VBScript skills, I could probably suggest more changes... sorry.
What I would like as much as possible, of course, is examples. (This thread was my way to start providing them to others).
Scot
Update: see my reply to this message for a better explanation, and solution ...
Multiple passes at data extraction requires ...??
I don't have a great answer for this problem, and would really be interested in feedback from someone with more VBScript expertise.
The issue is:
Some operations require retrieving information from multiple tables in salesforce, and then iterating through the retrieved information. Handling these in-line (in the FOR loops which extract the sObjects from the queryResultSets) may be difficult.
What I need is a method or technique for caching this information for use outside the FOR loop, and most methods I've tried are pretty cumbersome.
Example:
In the context of an sforce control which will perform data validation of an opportunity, I want to perform tests of the type (pseudo code!)
if opportunity.probability > 50 and
any(oppLineItem.product.customType__c) contains "Maint" and
opportunity.MaintStartDate__c = ""
then ... flag an error ...
This requires
retrieve the Opportunity: one object
retrieve the OpportunityLineItems: multiple objects
for each line item, retrieve the PriceBookEntry: single object for each object
for each PriceBookEntry: retrieve the Product2 object
What I'd like, of course, is to have a single "structure" which combines the fields from the Product2 records with those for the LineItems. Then I could iterate through that structure.
In brief form, what I'm currently working on to perform the "blue" code above follows.
I'll follow that with discussion of how to improve it:
' Not shown: get sfdc as session.
Problems I'd like to solve:
> This will be slow: multiple requests for each line item.
> The code itself is pretty cumbersome for a relatively simple requirement
(The real thing will be much worse, since it has many such tests)
> With the logic located within the FOR loop, it will be difficult to include many tests using the same data sources.
Ideas: reactions?
Subroutines
Clearly, some of this code will be reusable, and should be packaged as subroutines.
Build an array of line items?
I could cut down on queries to salesforce by building up an array of the PBE ID's, and then an array of the product2ID's. This would mean less queries, but problems grouping the data from two objects (line items and products), both of which need to be within the context of their own FOR EACH.
How to get the results together?
If I avoid nesting the retrieve calls, I'll have four QRS's:
opportunities (1)
line items (n)
pricebookentries (not used except to get the fourth ...)
product2s (n)
Both line items and product2s need to be within a FOR EACH to get their values. Yet within each, there are also multiple fields.
I suspect that the right answer is to loop through each separately, transferring the fields from the objects into a collection or multiple arrays. Then I could use the indices of these two arrays to relate the values from each line item.
Where I'm stuck ... what type of structure can handle this two-dimensional data within VBasic. Perhaps a dictionary object to handle the item names, and an array for the field values? Or a dictionary object which has arrays as it's values?
The equivalent of:
' Build dictionary of desired result, to allow reference by name when used
dim dict
set dict = CreateObject("Scripting.Dictionary")
dict.add "Quantity",0
dict.add "UnitPrice",1
.....
dict.add "CustomType__c" 5
dim values()
redim values(6,qrsline.size)
' Add info from the line items
linecnt=0
for each line in qrsline
for indx = 0 to 2
values(indx,linecnt) = line.Item(dict.keys(indx)).value
next
linecnt = linecnt+1
next
' Add info from the products
linecnt=0
for each line in qrsline
for indx = 3 to 5
values(indx,linecnt) = prod.Item(dict.keys(indx)).value
next
linecnt = linecnt+1
next
This seems to be getting awfully complicated. Can anyone point out a better direction?
Thanks,
Scot
Message Edited by Scot on 10-06-2004 04:27 PM
Message Edited by Scot on 10-27-2004 01:45 PM
Dave,
It would be really helpful if one could address the elements of a query result set without putting them in a FOR EACH loop.
Would it be possible to provide an Item function in the QueryResultSet which would accept an index (ordinal) and return an SObject?
This function in the same way as the Item function in the SObject --
Today, we can address the individual fields in the sObject collection. Rather than forcing a FOR loop, as in:
for each field in sobj
if field.Name = "phone" then phone = field.value
next
we can say:
phone = sobj.Item("itemname").value
An example of why:
To relate line items to their corresponding products, I have three QRS's:
lineitems -> from a SOQL request selecting on OpportunityId
pricebookentries -> from a RETRIEVE using PricebookEntryId's from the lineitems QRS
product2s -> from a RETRIEVE using Product2Id's from the pricebookentries.
I need to iterate through the line items, performing logic that needs values from both lineitems and products. Today, I do this by nested FOR loops to get to the objects, and multiple counters to identify which instances match each other:
i = 0
For each line in lineitems
ii = 0
For each prod in product2s
if i = ii then
logic, with references to
line.item("itenname") and prod.item("otheritem")
exit for
end if
ii = ii +1
end for
i = i +1
end for
Message Edited by Scot on 10-11-2004 10:20 AM
I have a simpler - and more efficient - solution, though still pretty kludgy.
It involves using nested for loops with counters.
Goal:
To relate data from a source QRS to items from a related lookup table.
Example:
Step through each line item in an opportunity, referencing both lineitem and corresponding product2 information.
Approach:
Retrieve the two QRS's (in this case, using a third one on PriceBookEntry to obtain the products). Then use nested FOR loops to get at the SObjects in each QRS. Put counters on both FOR loops, and use these to pick out the one iteration of the inner loop which returns the object needed to match the outer loop.
Step by Step:
A. Get the Query Result Sets:
> Use SOQL, with a where on OpportunityId, to get the line items in a lines QRS
> Use a FOR loop on this QRS to build an array of PriceBookEntryId values
> Use RETRIEVE with this array, to get the corresponding PriceBookEntry entries QRS
> Use a FOR loop on this QRS to build an array of Product2Id values
> Use RETRIEVE with this array, to get the corresponding Product2 products QRS
You now have the values for line items in one QRS
B. Nested Loops:
i = 0
For each line in lines
ii = 0
For each product in products
if i = ii then ' only want the one hit
{ logic, with references to
line.Item("itemname") and product.Item("otheritemname")
exit for
end if
ii = ii +1
end for
i = i +1
end for
See the attached for a sample of what I'm current running.
Scot
Is there a way to set the fields back to null through the toolkit? I couldn't find a reference to the FieldsToNull array on the SObject?
IF not, it will be a pretty important capability to add ...
Message Edited by Scot on 10-11-2004 10:18 AM
If an object (record) has known server state (was pulled from the servers) and a field value is set to nothing, the toolkit will add this field to the fieldsToNull section of the update request.
For Example
Set qr = sfdc.Query("select * from opportunity where name='testOpp'", False)
For Each x In qr
Debug.Print x("t1__c")
x("t1__c").Value = Nothing
x.Update
Exit For
Next x
Nice.
Ok, cancel that suggestion.
Scot
It is helpful to perform much of the debugging outside of the sforce control in salesforce.
I've built my vbscript code using the Microsoft Script Editor (other suggestions most welcome). I build the html page, then edit and test (debug/start) the page outside of salesforce.com until it's pretty-well functioning.
To test in the sforce control, I can copy/paste the entire contents into the edit page of the sforce control.
I do all the updates and re-tests back in the script editor, replacing the sforce control as needed.
Two way sessions
To allow the same code to function as an sforce control or an independent html page, I use the following:
Delayed startup
My processing normally wants to start immediately, so the final production version has in it:
To enable better debugging, I found it useful to disable this with a comment, and trigger the call via a link in the html. This allowed setting breakpoints, for example, and made it easier to step through the code using the script debugger. My version was then ...
Showing intermediate values
This is straight vbscript stuff, but new to me took a while for me to discover ...
The use of msgbox(variable) is a bit verbose when debugging stuff. I found it much more useful to build in a dump of information at the end of my html form. I did this by providing a section on the form for the message, and then modifying "innerhtml" to update it. The following exercpt shows:
1) a link back to the original opportunity
2) several variables written out
3) some bolding of a few values...
One more note:
Temporary debugging notes
Early on, I found it helpful to have some "msgbox" debugging prints, which I'd want to disable when posting the results to the sforce control. I did this with a variable, set to false, which could be overriden when you want to see results:
This snippet shows use of the variable (dbf), the constant vbcrlf for new lines, and the use of join to merge together the elements of the array OpRoles into a series of lines.
Gathering multiple instances into a string for later testing
Often you may want to reference an array of values (such as line items or contact roles for an opportunity). Elsewhere I described working with these using for-each loops - or nested for-each loops.
Sometimes, particularly when the same set of values will be used in multiple places, it is easier to store the result in an array or a string. I found the arrays almost as difficult to work with as the objects (most of what I wanted to do with them required a for loop), but a string is quite useful if you're testing for the existence of children.
For example:
Is there an opportunity Contact Role of Coach?
Are any of the product types "RE" (maintenance renewal)
Sample code setting AcctRoles:
Test using it:
Nice summary of tips ! I've got one to add
I use as my editor the html editor in Visual Studio and I wrote a perl script to add to the Tools Menu bar which provides a single menu command that uploads my javascript into the scontrol body in salesforce. To make this work I have the html body file itself contain a comment which holds a string like this :
<!--- scontrol=01N00000000000x specifies the scontrol we want this code loaded into --->
Then my script to upload just looks for the scontrol= spot in the file , and opens the API and loads the updated content directly from the editor to the scontrol body, one click...
Now I my cycle is edit, push, test (or refresh)
I may have skipped this effort if I had thought of your trick of enabling a standalone page to work in an scontrol or outside the system, very cool.
Code samples using vbscript, COM Toolkit, and sforce controls.
The following samples are "snippets" of code from an actual application which use Query and Retrieve calls. It is not intended that you use these exactly as-is - but rather that they may make the development of your code simpler by providing a starting point.
For simplicity, most variable declarations (DIM/PUBLIC) are not shown, except where they are arrays.
The sfdc session has already been obtained, and is stored in "binding". See an earlier example here for how to get this set.
Retrieve: obtain opportunity and account from an opportunity ID
Designed to be called from an opportunity sforce control, this retrieves the opportunity and account information. It shows how to get merge fields from sf.com into vbscript, how to get a test value when not running as an sforce control, how to setup the retrieve call and use the results.Both retrieves use the special value "*" for the field list.
Query: get all the contact roles for the opportunity
When you need more than one, key-identified, value, you need to use query rather than retrieve. This example shows using a query which retrieves multiple contact roles for the opportunity. THe major purpose is to show how to setup and run a query, and a simple method of packaging the results.
In this case, we only care about the list of roles and whether there is an primary role specified, thus the subroutine returns two scalars. In more complex situations, you might need to put more logic in the For Each loop, and possibly use a Retrieve call to get the contact information corresponding to each contact role.
Values returned from this code are highlighted in red ...
Query: find out if the opportunity name is unique
Another example of a query, this one is a function to return whether the opportunity name is unique.
Query: retrieve all tasks, and check for recent ones and overdue ones
This shows a simple query, but with more examples of the use of the results. It includes the use of date fields. At the end, it also shows a method of conditionally displaying debugging information.
The example sets variables for:
TaskCnt: number of tasks for this opportunity
TaskOpen: number of open tasks
TaskNext30: number of tasks for the next 30 days
TaskLast30: number of tasks completed in the last 30 days
IsTaskOverdue: are there any tasks overdue
The intent is to provide information to a main-line routine which rates opportunities.
Code sample using vbscript, COM Toolkit, and sforce controls to generate a new record
The following sample is a subroutine which generates a new task for an opportunity. It shows the use of the CreateObject and Create methods. It is not intended that you use it exactly as-is - but rather that it may make the development of your code simpler by providing a starting point.
For simplicity, most variable declarations (DIM/PUBLIC) are not shown, except where they are arrays.
The sfdc session has already been obtained, and is stored in "binding". See an earlier example here for how to get this set.
The real-world context is that previous code has checked an opportunity (the opportunity values are in the object "opp") and resulted in an array of error and suggestion messages (Xerror, Xwarn). This code generates a new task, assigned to the owner of the opportunity, with a comment explaining what needs to be done to update the opporunity to a "valid" state.
Message Edited by Scot on 11-01-2004 05:29 PM
Code sample using vbscript, COM Toolkit, and sforce controls to update a record
The following sample is a subroutine which updates two fields in an opportunity. It shows the use of the Update method. It is not intended that you use it exactly as-is - but rather that it may make the development of your code simpler by providing a starting point.
For simplicity, most variable declarations (DIM/PUBLIC) are not shown, except where they are arrays.
The sfdc session has already been obtained, and is stored in "binding". (See an earlier example here for how to get this set). The opportunity to be updated has also already been retrieved, using a retrieve. (Again, this follows an earlier posted example).
The real-world context is that previous code has checked an opportunity (the opportunity values are in the object "opp") and resulted in a status. This status, along with the date of this check, should be reflected back into the opportunity.
Note: This example used an already existing object. If it had not been previously retrieved, you could perform the update by building a new object, then populating the appropriate fields (including the opportunities ID). The earlier example of creating a new task shows how to build a new object.