Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

Creating SQL Views of DUOS Data

Creating SQL Views of DUOS Data

  • Comments 8

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.

Attachment: SQL DUOS View Demo.zip
  • 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

  • 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

  • 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

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

  • Post from Victoria Yudin

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

  • Hi there

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

    thanks

    Arnold

  • 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

Page 1 of 1 (8 items)
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.

Leave a Comment
  • Please add 3 and 1 and type the answer here:
  • Post