Visual Basic for Applications (VBA) can use the Dynamics User Object Store (DUOS) table to store additional data such as user defined fields.
The SY_User_Object_Store (SY90000) DUOS table uses a single row for each field stored, It stores the data in a string field 132 characters long.
Below is a table which shows the columns in the table and example values as well as an example output from SQL.
Select * from SY90000 Output
Object Type Object ID PropertyName PropertyValue DEX_ROW_ID --------------------- -------------- -------------- -------------- -----------Prospect Information APASUPPLY0001 Email Test@test.com 1 Prospect Information APASUPPLY0001 ProspectLevel 3 2
Because the data in the DUOS table is not normalized it can be difficult to use in applications that can work with SQL queries, such as SmartList Builder. The following technique shows how to create a SQL view which converts the multiple rows into a single row and so can be easily joined to other tables.
Using the Modifier and VBA code from Modifier - Sales Prospect DUOS Example I have customized the Sales Prospect Maintenance window. The screenshot is displayed below:
I then created a SQL view using the following code (note the use of a case statement to convert the drop down list position to the static string values it represents):
This is the output from the view based on the data shown in the screenshot above:
SQL View Output
ObjectType ObjectID ProspectLevelValue ProspectLevel Email --------------------- -------------- ------------------- -------------- --------------Prospect Information APASUPPLY0001 3 Warm Test@test.com
[Edit] I had a chat with Mariano Gomez today and he showed me an alternate method which does not use multiple joins to access the DUOS data but rather uses a max() function with a case statement to "flatten" the results returned when grouped. I have taken his technique and created two variants of the script above. The first still converts the ProspectLevel to a numeric value when the second simpler version leaves it as a string.
Second Alternate Code for MBS_DUOSProspectDetails SQL View Example
The SQL Scripts for all 3 versions of the MBS_DUOSProspectDetails view are attached at the bottom of this article.
You should be able to take this example code and adjust it to work with any DUOS data you have. Then you can join this view with any other tables you need to to create complex views that can be used with SmartList Builder, SQL Reporting Services or Crystal Reports.
12-March-2009: Added additional examples using simplified method after discussions with Mariano Gomez.
One note: this method works fine when the ObjectID does not store a combined key created with the DUOSObjectCombineID method. If this is the case, developers would have to parse the key segments and write the data to a SQL Server temp table, then create the view off the resulting temp table.
Mariano Gomez, MVP
Maximum Global Business, LLC
Good point. I did not cover combined key fields.
They could include the logic to decode a DUOSObjectCombineID'd field in the view itself so that the view has the original fields or use a SQL user defined function (UDF) to handle the decoding.
This would allow the view to work without needing to add an extra step or using a temporary table.
Excellent stuff. This just makes my life so much easier. Thankyou
Dave Musgrave has info about using and reporting off of the Dynamics User Object Store (DUOS) in Dynamics
Posting from Vaidy Mohan
Post from Victoria Yudin
With regards to data and storage capacity, would like to ask if there are limitations using DUOS table?
Each DUOS record can store a string up to 132 characters. You can combine more than one record if you need to store more characters. Other than that, there is no actual limit on the data size, other than what your hardware, disk capacity and SQL Server would apply.
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.