Creating SQL Views of DUOS Data
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 |
| 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:
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.