Creating SQL Views of DUOS Data

David Musgrave

10 Mar 2009 8:00 PM

Comments 8

David Meego 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.

Column	Description	Example Values
ObjectType	Used to describe what the Collection of data relates to	Prospect Information
ObjectID	Stores the Key field value(s) for the object in the Collection	APASUPPLY0001
PropertyName	Used to describe the Property being stored	ProspectLevel Email
PropertyValue	String representation of the data being stored	3 Test@test.com

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:

Prospect Maintenance

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):

Code for MBS_DUOSProspectDetails SQL View Example

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MBS_DUOSProspectDetails]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[MBS_DUOSProspectDetails]
GO

create view MBS_DUOSProspectDetails 
as
select D0.ObjectType, D0.ObjectID
    , isnull((case when isnumeric(D1.PropertyValue)=1 then convert(int, D1.PropertyValue ) else 0 end), 0) as [ProspectLevelValue]
    , case isnull((case when isnumeric(D1.PropertyValue)=1 then convert(int, D1.PropertyValue ) else 0 end), 0)
        when 1 then 'Cold'
        when 2 then 'Cool'
        when 3 then 'Warm'
        when 4 then 'Hot'
        else '' end as [ProspectLevel]
    , isnull(D2.PropertyValue, '') as [Email]

from 
(
    select ObjectType, ObjectID
    from SY90000 
    where ObjectType = 'Prospect Information'
    group by ObjectType, ObjectID
) D0

left join SY90000 D1 on D1.ObjectType = D0.ObjectType and D1.ObjectID = D0.ObjectID
and D1.PropertyName = 'ProspectLevel'
left join SY90000 D2 on D2.ObjectType = D0.ObjectType and D2.ObjectID = D0.ObjectID|
and D2.PropertyName = 'Email'
GO

GRANT SELECT , INSERT , DELETE , UPDATE ON [dbo].[MBS_DUOSProspectDetails] TO [DYNGRP]
GO

select * from MBS_DUOSProspectDetails
 

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.

First Alternate Code for MBS_DUOSProspectDetails SQL View Example

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MBS_DUOSProspectDetails]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[MBS_DUOSProspectDetails]
GO

create view MBS_DUOSProspectDetails 
as
select ObjectType, ObjectID
    , isnull((case when isnumeric(max(case when PropertyName = 'ProspectLevel' then PropertyValue end)) = 1 
                then convert(int, max(case when PropertyName = 'ProspectLevel' then PropertyValue end)) else 0 end), '0') as [ProspectLevelValue]
    , case isnull((case when isnumeric(max(case when PropertyName = 'ProspectLevel' then PropertyValue end)) = 1 
                     then convert(int, max(case when PropertyName = 'ProspectLevel' then PropertyValue end)) else 0 end), '0')
        when 1 then 'Cold'
        when 2 then 'Cool'
        when 3 then 'Warm'
        when 4 then 'Hot'
        else '' end as [ProspectLevel]
    , isnull(max(case when PropertyName = 'Email' then PropertyValue end), '') as [Email]

from SY90000
where ObjectType = 'Prospect Information'
group by ObjectType, ObjectID
GO

GRANT SELECT , INSERT , DELETE , UPDATE ON [dbo].[MBS_DUOSProspectDetails] TO [DYNGRP]
GO

select * from MBS_DUOSProspectDetails
 

Second Alternate Code for MBS_DUOSProspectDetails SQL View Example

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MBS_DUOSProspectDetails]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[MBS_DUOSProspectDetails]
GO

create view MBS_DUOSProspectDetails 
as
select ObjectType, ObjectID
    , isnull(max(case when PropertyName = 'ProspectLevel' then PropertyValue end), '0') as [ProspectLevelValue]
    , case isnull(max(case when PropertyName = 'ProspectLevel' then PropertyValue end), '0')
        when '1' then 'Cold'
        when '2' then 'Cool'
        when '3' then 'Warm'
        when '4' then 'Hot'
        else '' end as [ProspectLevel]
    , isnull(max(case when PropertyName = 'Email' then PropertyValue end), '') as [Email]

from SY90000
where ObjectType = 'Prospect Information'
group by ObjectType, ObjectID
GO

GRANT SELECT , INSERT , DELETE , UPDATE ON [dbo].[MBS_DUOSProspectDetails] TO [DYNGRP]
GO

select * from MBS_DUOSProspectDetails
 

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.

David

12-March-2009: Added additional examples using simplified method after discussions with Mariano Gomez.

Attachment:

SQL DUOS View Demo.zip

Modifier, VBA, DUOS, SQL, SmartList Builder, SQL Reporting Services, Crystal Reports

Comments

Mariano Gomez

11 Mar 2009 3:47 PM

David,

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.

Best regards,

MG.-

Mariano Gomez, MVP

Maximum Global Business, LLC

http://www.maximumglobalbusiness.com
David Musgrave

11 Mar 2009 6:56 PM

Hi Mariano

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.

David
Wayne

13 Mar 2009 12:22 AM

Excellent stuff. This just makes my life so much easier. Thankyou
DynamicAccounting.net

16 Mar 2009 12:42 PM

Dave Musgrave has info about using and reporting off of the Dynamics User Object Store (DUOS) in Dynamics
David Musgrave

17 Mar 2009 11:47 PM

Posting from Vaidy Mohan

http://www.vaidy-dyngp.com/2009/03/sql-views-on-duos-data-david.html
David Musgrave

22 Apr 2009 6:30 AM

Post from Victoria Yudin

http://victoriayudin.com/2009/04/20/how-to-use-a-sql-view-in-smartlist-builder/
Arnold

10 Oct 2010 8:51 PM

Hi there

With regards to data and storage capacity, would like to ask if there are limitations using DUOS table?

thanks

Arnold
David Musgrave

11 Oct 2010 8:21 AM

Hi Arnold

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.

David

Comments Information

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.

Dates of Interest

Social Networking & Syndication

Shortened URLs

Disclaimer

Creating SQL Views of DUOS Data

Creating SQL Views of DUOS Data