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:
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/
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/
Comments
I realize you're busy - thank you for any assistance at all.....
In GP Fixed Assets General Maintenance Window I have added a field that is stored in the DUOS Table.
I am using a combined AssetID and AssetID1 (which is really the Asset Suffix) for ObjectID using the DUOS Combine ID Method
Here's my code:
ObjectID = Trim(DUOSObjectCombineID(AssetID, AssetID1))
Data stored in SY90000 table looks like 000797^@5 instead of 0007975
Obviously I need to trim or do something concerning spaces.....I would really like something that adds the hyphen - 000797-5 where 000797 = AssetID and 5 = Suffix I would settle for no special characters..
Any thoughts would be appreciated....
The idea of using the DUOSObjectCombineID(Key1, Key2) function is to provide abstraction from having to come up with the key on your own to begin with, regardless of spaces, etc. I am not sure if the idea of using a dash in between is so you can run some sort of query externally(?). If not, you should be able to simply use the DUOSObjectExtractID(Key1, Key2) function to return the individual key values stored in the DUOS table.
If you are going to run this from SQL, you could do something like:
SELECT SUBSTRING(PropertyValue, 1, CHARINDEX('^', PropertyValue) - 1) + '-' + SUBSTRING(PropertyValue, CHARINDEX('@', PropertyValue) + 1, LEN(PropertyValue) - CHARINDEX('@', PropertyValue))
FROM SY90000
But again, if it's all from VBA, then there's no need to separate the key values by a dash.
Here's my VBA to 'extract it' and retrieve the value stored in DUOS Table. Note the Asset Book Window populates when the BookID is selected - i assume that's the event I choose to extract data?
Private Sub BookID_Changed()
Dim MayNBVCollection As DUOSObjects
Dim MayNBVObject As DUOSObject
Dim ObjectID As String
Dim AssetID As String
Dim AssetID1 As String
'Return a MayNBV collection
Set MayNBVCollection = DUOSObjectsGet("MayNBV")
For Each MayNBVObject In MayNBVCollection
If DUOSObjectExtractID(MayNBVObject.ID, AssetID, AssetID1) _
= True Then
MayNBVObject.Properties("MayNBV") = MayNBV
End If
Next
End Sub
Any insight would be appreciated - I know you're a busy guy - thanks again.