Creating SQL Views of DUOS Data


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

SQL DUOS View Demo.zip

Comments (8)

  1. 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

  2. David Musgrave says:

    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

  3. Wayne says:

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

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

  5. Arnold says:

    Hi there

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

    thanks

    Arnold

  6. David Musgrave says:

    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