Sage 500 ERP BI View Data Column Field Level Information

Sage 500 ERP Schema Browser is still delivered in its original form. The Sage 500 ERP Schema Browser which contains a wealth of information about the Database architecture including tables, fields, primary index, surrogate key, foreign key, column attributes and columns contain value lists. The issue with the Schema Browser utility is that it is only a 32 bit application. To address this issue Sage created several BIE views along with some image files and created the Schema Brower views located under the System Manager module.

The one missing component in this new set of BIE utilities is the Field Level List’s that you could drill into in the prior Schema Browser. That leads me to this blog post based on 2 sessions I was presenting at Sage Summit 2012.

Summary of Steps to Complete

1. Create SQL vdv View
2. Add new View into Sage 500 ERP using Task Editor
3. Add new Task to the “Maintain Business Insights View / Context Menus”
4. Modify the Column “Maintain Business Insights View / Context Menus” to link prior step
5. Test what we have created

Detail Steps

STEP 1. Create SQL View with ‘vdv’ naming convention required for Sage 500 ERP BIE/BIA.

For more detailed information please refer to the links below:

Adding Custom Views to Sage 500 ERP Business Insights Analyzer

Create a Copy of an Existing VDV Entity in Sage 500 ERP

/*

Expose Sage 500 ERP Schema Field Level Attributes in a vdv so you can add it to BIE

*/

IF object_id(N'dbo.vdvListValidation', 'V') IS NOT NULL

	DROP VIEW dbo.vdvListValidation;

GO

-- Create vdv View for Sage 500 ERP BIE/BIA
CREATE VIEW vdvListValidation
AS
SELECT v.TableName,
v.ColumnName,
s.LocalText AS FieldValue,
v.DBValue As DatabaseValue,
CASE v.IsDefault WHEN 1 THEN 'Yes' ELSE 'No' END AS 'Is Default',
CASE v.IsHidden WHEN 1 THEN 'Yes' ELSE 'No' END AS 'Hidden'
FROM tsmListValidation v
INNER JOIN tsmLocalString s
on v.StringNo = s.StringNo;
GO

-- Grant required Sage 500 ERP rights to vdv
GRANT SELECT ON dbo.vdvListValidation TO ApplicationDBRole;
GO

/*
-- Used for testing purposes only to discover how to best create and link necessary tables
-- Started using this code from Rick Sisk at Sage
SELECT v.*, s.LocalText
FROM tsmListValidation v
INNER JOIN tsmLocalString s
ON v.StringNo = s.StringNo;
GO

-- Look at the base Data Dictionary Columns
SELECT *
FROM tsmDataDictCol;
GO

-- Retrieve table info
SELECT [vdvTable].[DescText] AS [Description Text],
[vdvTable].[ModuleID] AS [Module],
[vdvTable].[TableName] AS [Table Name]
FROM vdvTable WITH (NOLOCK)
WHERE ([TableName] = 'tarCustomer');
GO

-- extract the Table Column View text for review
sp_helptext 'dbo.vdvTableColumn';
GO

-- Retrieve column info
SELECT [vdvTableColumn].[ColumnName] AS [Column Name],
[vdvTableColumn].[Data Type] AS [Data Type],
[vdvTableColumn].
[DataSize] AS [Data Size],
[vdvTableColumn].[Decimals] AS [Decimals],
[vdvTableColumn].[DescText] AS [Description Text],
[vdvTableColumn].[DomainID] AS [Domain],
[vdvTableColumn].[Foreign Key] AS [Foreign Key],
[vdvTableColumn].[Natural Key] AS [Natural Key],
[vdvTableColumn].[Nullable] AS [Nullable],
[vdvTableColumn].[Surrogate Key] AS [Surrogate Key],
[vdvTableColumn].[TableName] AS [Table Name]
FROM vdvTableColumn WITH (NOLOCK)
WHERE ([TableName] = 'tarCustomer');
GO

-- String values for Table Columns
SELECt v.*,
s.*,
dc.*
FROM tsmDataDictCol dc
INNER JOIN tsmListValidation v
ON v.TableName = dc.TableName
AND v.ColumnName = dc.ColumnName
INNER JOIN tsmLocalString s
ON v.StringNo = s.StringNo
WHERE dc.TableName = 'tarCustomer'
AND dc.ColumnName = 'Status';
GO
*/

STEP 2: Add the view Sage 500 ERP Task Editor.

Launch the Task Editor. Click on the ‘Add New Task’ button to see the following screen. Complete the field entry as shown below. The tricky part is the Task ID as this will be different on each system based on what you have installed as as if you are a Master Developer of some level with your own ID range. Then click OK.

You will see the new Task added as shown below.

STEP 3: Now create a New Business Insights View as well as create a relationship between the Columns and your new view the Column Fields List.

Add your new view. You will assign an ID. Below I have named mine ColumnFieldList and attached it to the vdvListValidation I created in Step 1.

Verify the Column mapping was pulled in correctly.

The Navigation Tasks should be populated correctly.

Complete the Navigation Relationships as shown below.

STEP 4: Now go to the Column BI View and create a relationship from it to the new View created in Step 3. On the Navigation Tasks set the Destination View to Column to the new BI View.

On the BI View for Column on the Navigation Relationships tab add 2 new Rows to establish relationships to the new BI View created earlier, ColumnFieldList.

STEP 5: Now verify everything is configured correctly. Select the System Manager –> Schema Browser –> Table task.

In the example below I have select the tapVendor table and then drilled down into the V1099Form column. In the lower grid it shows the valid values for this table column combination.