What is the Dynamic User Object Store (DUOS)?

I have written a number of articles on my blog showcasing storing data in the DUOS, you may have read a number of postings on Developing for Dynamics GP on the subject, but many of you have asked what exactly is the DUOS and how to store and retrieve the data in the DUOS.

For all its fancy name, simply put the DUOS is a SQL Server table – dbo.SY90000 – a part of your company database. However, there is more to it than meet the eyes. But first, lets take a look at the table definition:


/****** Object: Table [dbo].[SY90000] Script Date: 03/23/2009 14:48:44 ******/

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[SY90000](
[ObjectType] [char](31) NOT NULL,
[ObjectID] [char](61) NOT NULL,
[PropertyName] [char](31) NOT NULL,
[PropertyValue] [char](133) NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PKSY90000] PRIMARY KEY NONCLUSTERED
(
[ObjectType] ASC,
[ObjectID] ASC,
[PropertyName] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

The important aspect to highlight from this table definition is the primary key constraint as, before hand, you will not be able to import records with a duplicate Object Type, and Object ID, and PropertyName.

When do I use DUOS?

To understand when to use DUOS, I will first refer to the methods of accessing and storing data in the DUOS itself.

There are two methods for programming DUOS:

1) Using Dexterity
2) Using Modifier with Visual Basic for Applications.

While Dexterity offers a good support for accessing DUOS (after all, it’s only another Dexterity table) with its standard get, save table, change, and range statements, DUOS was really implemented to be used in conjuction with Modifier and Visual Basic for Applications.

DUOS is primarily designed to support minor customizations (with Modifier and VBA) requiring limited data storage, this is, a few extra fields added here and there. All extra fields and their values are stored as strings in the physical DUOS table, hence the performance overhead that large data sets can bring as their values will require conversion to match proper datatypes.

NOTE: If you have a need to store large data sets it is recommended you explore other methods like independent SQL Server tables with their own data definitions. These tables can be created and accessed with Dexterity, or created in SQL Server and accessed with ADO.

The DUOS object model

The DUOS object model is a standard part of Microsoft Dynamics GP Visual Basic for Applications object library – the library resides in the DEXVBA.DLL assembly file and exposed to COM via an OLE type library file, DEXVBA.TLB.

The library exposes the DUOSObjects and DUOSProperties collections and two objects, the DUOSObject and DUOSProperty. The following image illustrates the object model with its methods and properties.


DUOS Resources

Now that you have an understanding of the object model and how/when to utilize DUOS take a look at some of the coolest samples around the blogosphere.

DUOS examples – David Musgrave at Developing for Dynamics GP. Click here.
VBA Workshop series – Click here.

Until next post!

MG.-
Mariano Gomez, MIS, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: