Open Forum

Adding a table extension to an Existing Dynamics SL Screen

By Mark Schrier posted Feb 27, 2017 04:47 PM

  

Adding a table extension to existing SL screen:

Introduction:

I have found over time that there are never enough ‘User’ fields in a Dynamics SL table. The solution to this problem is to create a ‘sibling’ table in the database and integrate it into then maintenance screen, using VBA and the VBA API functions. The process does not take a great amount of code, but must be done carefully to get the desired result. This blog will go through the steps to add this table extension

A real world application will be used in explaining this process. We had a need to add many new columns to the Sites (10.310.00) screen in the Inventory Module. Earlier in the life of SL at Mackay, a process was created that increased all prices for our ‘Parts’ product class. It was fairly crude and there were no override or controls on the process, other than it ran every night as a SQL job. If the cost, with margin applied, resulted in a higher price, the price is updated by this overnight process. All was good until someone entered an incorrect cost that increased cost over the last cost and did not catch the error. The price was updated by the automatic process and overstated by a large amount.

Consulting with the inventory manager, we came up with a plan to
    1) Modify the existing process to use database values to determine:
        a) Which receipts were are considered for price modification processing.
        b) Create a threshold (percentage increase) that would cause the receipt row to be flagged for review by the inventory staff.
    2) Modify the Sites screen to hold these values for each inventory site. This put the burden of managing process in the hands of the inventory staff. Adding the new    
        columns became a problem because there are not any additional user columns available to hold these values.

Number 1 is a topic for another blog, but this blog will address how I added additional columns to the Site screen, using Customization Manager and the SL VBA tools.

Steps involved in adding a new table to a screen:

    1) Define and create a new table in SQL Server Management Studio.

    2) Create a Clustered, Unique index that matches the Clustered, Unique index on the SL table.

    3) Create Data header (DH) file.

    4) Make the new table visible to the SL Screen

    5) Add Controls to the screen

    6) Declare a Public variables

    7) Add Code to the cSiteID_chk event (Index key for the Site table)

    8) Add Code to the Update1_OnUpdate event

    9) Add Code to the Update1_OnInsert event

This blog assumes that you already have a working knowledge of 
    1. Sql Server Management Studio
    2. Customization Manager
    3. Visual Studio Editor

I have worked hard to make sure all this cide is correct, but there is always a chance that there is a type or error.  If you have trouble using this code, let me know and I will be happy to respons to your questions and or concerns.

Step by step instructions

Conventions: All SQL or VBA code is shown in Blue

    1. Define and create a new add-on table.
        a. The table was entered, using the Table Designer in SQL Server Management Studio

        b. Below is the create script for the add-on table
            i. Create table script:
                SET ANSI_NULLS ON
                GO
                SET QUOTED_IDENTIFIER ON
                GO
                SET ANSI_PADDING ON
                GO
                CREATE TABLE [dbo].[xMKSite](
                    [Crtd_DateTime] [smalldatetime] NOT NULL,
                    [Crtd_Prog] [char](8) NOT NULL,
                    [Crtd_User] [char](10) NOT NULL,
                    [IncludeInPricing] [smallint] NOT NULL,
                    [Lupd_DateTime] [smalldatetime] NOT NULL,
                    [Lupd_Prog] [char](8) NOT NULL,
                    [Lupd_User] [char](10) NOT NULL,
                    [MaxPricePct] [float] NOT NULL,
                    [MaxGMPct] [float] NOT NULL,
                    [SiteID] [char](10) NOT NULL,
                    [User01] [char](30) NOT NULL,
                    [User02] [char](30) NOT NULL,
                    [User03] [float] NOT NULL,
                    [User04] [float] NOT NULL,
                    [User05] [char](10) NOT NULL,
                    [User06] [char](10) NOT NULL,
                    [User07] [smalldatetime] NOT NULL,
                    [User08] [smalldatetime] NOT NULL,
                    [User09] [smallint] NOT NULL,
                    [User10] [smallint] NOT NULL,
                    [User11] [char](30) NOT NULL,
                    [User12] [char](30) NOT NULL,
                    [User13] [float] NOT NULL,
                    [User14] [float] NOT NULL,
                    [User15] [char](10) NOT NULL,
                    [User16] [char](10) NOT NULL,
                    [User17] [smalldatetime] NOT NULL,
                    [User18] [smalldatetime] NOT NULL,
                    [User19] [smallint] NOT NULL,
                    [User20] [smallint] NOT NULL,
                    [tstamp] [timestamp] NOT NULL
                ) ON [PRIMARY]
                GO

            ii. Set-up Defaults
                1. This is an extra step that I do for all tables.
                     i) It removes any worries related to failure due to a null value.
                     ii) As the programmer, you do not have to populate every value in the table buffer when a new row is inserted.  
                          Only the values that will be different from the default.
                2. The defaults are entered, using the Table Designer
                3. Below is the code to set the default values for the columns in the table
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_Crtd_DateTime] DEFAULT ('01/01/1900') FOR [Crtd_DateTime]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_Crtd_Prog] DEFAULT ('') FOR [Crtd_Prog]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_Crtd_User] DEFAULT ('') FOR [Crtd_User]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_IncludeInPricing] DEFAULT ((0)) FOR [IncludeInPricing]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_Lupd_DateTime] DEFAULT ('01/01/1900') FOR [Lupd_DateTime]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_Lupd_Prog] DEFAULT ('') FOR [Lupd_Prog]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_Lupd_User] DEFAULT ('') FOR [Lupd_User]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_MaxPricePct] DEFAULT ((0)) FOR [MaxPricePct]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_MaxGMPct] DEFAULT ((0)) FOR [MaxGMPct]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_SiteID] DEFAULT ('') FOR [SiteID]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User01] DEFAULT ('') FOR [User01]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User02] DEFAULT ('') FOR [User02]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User03] DEFAULT ((0)) FOR [User03]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User04] DEFAULT ((0)) FOR [User04]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User05] DEFAULT ('') FOR [User05]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User06] DEFAULT ('') FOR [User06]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User07] DEFAULT ('01/01/1900') FOR [User07]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User08] DEFAULT ('01/01/1900') FOR [User08]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User09] DEFAULT ((0)) FOR [User09]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User10] DEFAULT ((0)) FOR [User10]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User11] DEFAULT ('') FOR [User11]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User12] DEFAULT ('') FOR [User12]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User13] DEFAULT ((0)) FOR [User13]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User14] DEFAULT ((0)) FOR [User14]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User15] DEFAULT ('') FOR [User15]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User16] DEFAULT ('') FOR [User16]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User17] DEFAULT ('01/01/1900') FOR [User17]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User18] DEFAULT ('01/01/1900') FOR [User18]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User19] DEFAULT ((0)) FOR [User19]
GO
ALTER TABLE [dbo].[xMKSite] ADD CONSTRAINT [DF_xMKSite_User20] DEFAULT ((0)) FOR [User20]
GO

        c. Copy the 2 sets of scripts above into the SQL Server Management studio.
        d. Run the scripts to create the table and column defaults

    2. Create the Unique Clustered Index
        a. Must be tablename0 (xmkSite0) this is a Dynamics SL requirement
        b. Create Index Script
            /****** Object: Index [xMKSite0] Script Date: 02/10/2017 15:29:30 ******/
            CREATE UNIQUE CLUSTERED INDEX [xMKSite0] ON [dbo].[xMKSite]
                ([SiteID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF,
                IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
            ON [PRIMARY]
            GO
        c. Run the script to create the index
        d. Be sure to close and restart Dynamics SL to the new objects are visible to the program.

3. Create Data header (DH) file in the format for the version of SL to define the data to customization manager
        a. I have a scripting tool to create the dataheader: scripted below…
        b. Data Header
            ' created by VBA_DHMAKER.sql
            Type xmksite
                Crtd_DateTime As Sdate
                Crtd_Prog As String * 8
               Crtd_User As String * 10
               IncludeInPricing As Integer
               Lupd_DateTime As Sdate
               Lupd_Prog As String * 8
               Lupd_User As String * 10
               MaxPricePct As Double
               MaxGMPct As Double
               SiteID As String * 10
               User01 As String * 30
               User02 As String * 30
               User03 As Double
               User04 As Double
               User05 As String * 10
               User06 As String * 10
               User07 As Sdate
               User08 As Sdate
               User09 As Integer
               User10 As Integer
               User11 As String * 30
               User12 As String * 30
               User13 As Double
               User14 As Double
               User15 As String * 10
               User16 As String * 10
               User17 As Sdate
               User18 As Sdate
               User19 As Integer
               User20 As Integer
            End Type
            Public bxmksite As xmksite, nxmksite As xmksite
            Public err_xmksite As Integer, csr_xmksite As Integer

        c. Save the file with a .dh extension (extension ‘DH’ not required, except to identify it.)

    4. Make the new table visible to the SL Screen.
        a. Now the VBA programming starts. This section will include that new table structure so it can be seen by Customization Manager.
        b. Set the Customization Level: This must be set each time SL is started.
            i. Go to Administration Menu and select ‘Select Customization Level’
            ii. OR double click on the level indicator on the SL Menu.
        c. Set the correct level (for this example, ALL USERS)

        d. Open the Sites Screen (10.310.00) and go into customize mode (Ctrl-Alt-C)

        e. Add the DH file to the project.
            i. Press F7 to open the Visual Basic Editor
            ii. Follow the steps in the illustration below

            iii. The file will be added with a name of Module(n) where n is a number sequence. In my example, it shows as xMKSite_VBA since it was created in a different
                version.

        f. Add code to the Form1_Load event
            i. Add the code below to the event
                ' setaddr and cursor for xmkSite table
                Call VBA_SetAddr("bxmksite", bxmksite, nxmksite, LenB(bxmksite))
                ‘Declare the cursor used to retrieve and save the data
                Call SqlCursorEx(csr_xmksite, NOLEVEL, "csr_xmksite", "xmksite", "xmksite")

            ii. Screen shot of the Visual Basic Editor

            iii. Select Debug/Compile project from the Visual Basic Editor

        g. Save the customization.
        h. Close and reopen the screen.
        i. Open the Sites Screen (10.310.00) and go into customize mode (Ctrl-Alt-C)
        j. Press F6 to open the Insert Objects Wizard

        k. On the next screen you will see the new table name

        l. The table is now visible to Customization Manager

    5. Add Controls to the screen

         a. Object Property settings

    6. Add Code to the Module xmksite DH module
        a. Here is the code to add at the bottom of the xMKSite definition
            'Declare variable used to hold if the row exists
            Public RowFound As String
            'variables to hold update information
            Public Userid As String
            Public ProgramID As String
            Public BusDateStr As String
            Public BusDate As Sdate

        b. Below is the Visual Basic Editor screenshot (code is in red box)

        c. Add code to the Form_Display event - This code gets values that will be used to update the ‘Crtd_’ and ‘Lupd_’ columns in the table on an insert or update
            'set the constants
            Call GetBufferValue("bpes.today", BusDateStr)
            Call GetBufferValue("bpes.scrnnbr", ProgramID)
            Call GetBufferValue("bpes.Userid", Userid)
            BusDate.val = val(BusDateStr)
       
        d. Below is the Visual Basic Editor screenshot 

    7. Add Code to the cSiteID_Chk event
        a. Here is the code
            ' fetch the row from the extension table
            err_xmksite = SqlFetch1(csr_xmksite, "Select * from xmksite where siteid = " & SParm(ChkStrg), bxmksite, LenB(bxmksite))
             If err_xmksite = 0 Then
                'row found, set flag
                RowFound = "Y"
                'display the form so all fields will populate
                Call DispFields("Form1", "")
            Else
                ' no row blank out the buffer
               bxmksite = nxmksite
               'set the key value into the buffer
               bxmksite.SiteID = Trim$(ChkStrg)
               'set the RowFound to 'N'
               RowFound = "N"
               'Display the columns
               Call DispFields("Form1", "")
               'set the level dirty flag so it will execute the Update1_OnUpdate event
               If TestLevelChg(LEVEL0) = NOTCHANGED Then Call SetLevelChg(LEVEL0, UPDATED)
            End If

        b. Below is the Visual Basic Editor screenshot 

    8. Add code to the Update1_OnUpdate event.
        a. Here is the code
            If level = LEVEL0 Then
            Dim BusDateStr As String
                'get the constants for the update columns
                Call GetBufferValue("bpes.today", BusDateStr)
                Call GetBufferValue("bpes.scrnnbr", ProgramID)
                Call GetBufferValue("bpes.Userid", Userid)
                BusDate.val = Date
                'process based on the RowFound flag
                If RowFound = "Y" Then
                    'update data and update row
                    bxmksite.Lupd_DateTime = BusDate
                    bxmksite.Lupd_Prog = ProgramID
                    bxmksite.Lupd_User = Userid
                    'update the row
                    Call TranBeg(True)
                   Call SUpdate1(csr_xmksite, "xmksite", bxmksite, LenB(bxmksite))
                Else
                    'not found, update fields and insert row
                    bxmksite.Lupd_DateTime = BusDate
                   bxmksite.Lupd_Prog = ProgramID
                   bxmksite.Lupd_User = Userid
                   bxmksite.Crtd_DateTime = BusDate
                   bxmksite.Crtd_Prog = ProgramID
                   bxmksite.Crtd_User = Userid
                   Call TranBeg(True)
                   Call SInsert1(csr_xmksite, "xmksite", bxmksite, LenB(bxmksite))
               End If
            End If
            If level = Finished Then
                ‘This pass only happens if the update is committed to the database
                'SET THE ROW FLAG TO FOUND
                RowFound = "Y"
            End If
       
        b. Below is the Visual Basic Editor screenshot


    9. Add Code to the Update1_OnInsert event.
        a. Here is the code
            If level = LEVEL0 Then
                'Set the flag
                RowFound = "N"
                'clear the buffer
                bxmksite = nxmksite
                'redisplay the form
                 Call DispFields("Form1", "")
            End If

        b. Below is the Visual Basic Editor screenshot


All that is left is to test and test some more.

Wrap-up:

Adding an additional table to an existing SL screen has proved to be a very valuable tool in enhancing

and customizing the Dynamics SL.

When I add a new table, I always add a lot of User defined columns. This example shows 20 user fields.

In an new tables I add now I include 24 user fields.

In addition to the user fields in this example, user 21 – User24 are added as String * 100.

This assures that for most purposes, I will not need to alter the new table because I ran out of user fields.



Thank you for reading this blog.  So far this has been what I want to present.  I am always looking for suggestions for future blogs.  

You can send me a message directly through this web site with your questions and/or suggestions for blogs.


0 comments
397 views

Permalink