Open Forum

Adding a table extension to an existing Dynamics SL grid control

  • 1.  Adding a table extension to an existing Dynamics SL grid control

    Posted 11-01-2017 01:27 PM
    Hello,

    I found this blog post by Mark Schreir to be most insightful and it motivated me to join DSLUG.

    Does anyone know of a similar example for adding a custom table to an existing grid control through Customization Manager?

    Thanks,



    ------------------------------
    Darren Baker
    Fujimi Corporation

    ------------------------------


  • 2.  RE: Adding a table extension to an existing Dynamics SL grid control

    GOLD CONTRIBUTOR
    Posted 11-02-2017 07:59 AM
    ​Darren,

    I will pass your question along to Mark Schrier.  He may be able to answer your question (and make another blog post too!).

    ------------------------------
    Jennifer Pippin
    DSLUG Content Chair
    Content@DSLUG.com
    ------------------------------



  • 3.  RE: Adding a table extension to an existing Dynamics SL grid control

    Posted 11-02-2017 03:05 PM
    @Jennifer Pippin -- thank you for the help in making the connection.

    ------------------------------
    Darren Baker
    Fujimico Corporation
    Tualatin OR
    ------------------------------



  • 4.  RE: Adding a table extension to an existing Dynamics SL grid control

    SILVER CONTRIBUTOR
    Posted 11-02-2017 09:15 AM
    There is a way to do this.  I will work on that for a blog post.

    ------------------------------
    Mark Schrier
    Information Systems Director
    Mackay Communications, Inc
    Raleigh NC
    ------------------------------



  • 5.  RE: Adding a table extension to an existing Dynamics SL grid control

    Posted 11-02-2017 03:06 PM
    @Mark Schrier -- that would be fantastic. Your previous blog post was one of the most helpful documents I've ever read on SL development and certainly much better than any of the official documentation I've poured over. Thanks!

    ------------------------------
    Darren Baker
    Fujimico Corporation
    Tualatin OR
    ------------------------------



  • 6.  RE: Adding a table extension to an existing Dynamics SL grid control

    Posted 11-02-2017 09:46 AM
    Make the table following the example in the blog with an index.

    import the table dh file to the code.  add the form load code below to the screen.  Close and reopen, this will be enough to add fields to the grid.

    Use the rest of the code below as your guide for displaying and updating data.  This is working code from my Purchase Order entry screen.

    Public gridHandle As Integer
    Public SQLStr As String
    Public MemHandle As Integer, MemFetch As Integer, MemMaint As Integer

    Private Sub Form1_Load()
        Call VBA_SetAddr("bxPurOrdDetQC", bxPurOrdDetQC, nxPurOrdDetQC, LenB(bxPurOrdDetQC))
        Call SqlCursorEx(csr_xPurOrdDetQC, NOLEVEL + SqlUpdate, "csr_xPurOrdDetQC", "xPurOrdDetQC", "xPurOrdDetQC")
    End Sub

    Private Sub Form1_Display()
        MemHandle = GetGridHandle("Spread1")
        DelHandle = GetDelGridHandle("Spread1")
        serr1 = VBA_Mextend(MemHandle, bxPurOrdDetQC, LenB(bxPurOrdDetQC))
    End Sub

    Private Sub Spread1_LineGotFocus(maintflg As Integer, retval As Integer)
        If maintflg = NEWROW Then
            Call GetBufferValue("bpurorddet.ponbr", bxPurOrdDetQC.ponbr)
            Call GetBufferValue("bpurorddet.lineref", bxPurOrdDetQC.LineRef)
            Call GetBufferValue("bpurchord.cpnyid", bxPurOrdDetQC.CpnyId)
            Call GetBufferValue("bpes.today", bxPurOrdDetQC.crtd_datetime.val)
            Call GetBufferValue("bpes.scrnnbr", bxPurOrdDetQC.crtd_prog)
            Call GetBufferValue("bpes.userid", bxPurOrdDetQC.crtd_user)
            bxPurOrdDetQC.qcCOC = 0
            bxPurOrdDetQC.qcDIR = 0
            bxPurOrdDetQC.qcFAIR = 0
            bxPurOrdDetQC.qcRAW = 0
            bxPurOrdDetQC.qcSP = 0

        End If

        Dim cpny As String
        Dim purchasetype As String
        Call GetBufferValue("bPES.CpnyID", cpny)

        Call GetBufferValue("bpurorddet.purchasetype", purchasetype)
    End Sub

    Private Sub Update1_OnUpdate(ByVal Level As Integer, ByVal Updated As Integer, retval As Integer)
        ponbr = Trim$(sivMyApp.Controls("cponbr"))
        Dim tmpxPurOrdDetQC As xPurOrdDetQC
        Select Case Level
        Case LEVEL1
            'Let's handle deletes first...
            serr1 = MFirst(DelHandle, maintflg)

            While serr1 = 0
                Call GetBufferValue("bpurorddet.ponbr", bxPurOrdDetQC.ponbr)
                Call GetBufferValue("bpurorddet.lineref", bxPurOrdDetQC.LineRef)

                SQLStr = "xPurOrdDetQC_all" + SParm(bxPurOrdDetQC.ponbr) + SParm(bxPurOrdDetQC.LineRef)
                serr2 = SqlFetch1(csr_xPurOrdDetQC, SQLStr, bxPurOrdDetQC, LenB(bxPurOrdDetQC))

                If serr2 = 0 Then Call SDelete(csr_xPurOrdDetQC, "xPurOrdDetQC")

                serr1 = MNext(DelHandle, maintflg)
            Wend

            'Now let's handle updates and inserts...
            row = MGetRowNum(gridHandle)
            serr1 = MFirst(gridHandle, maintflg)

            While serr1 = 0
                Call GetBufferValue("bpurorddet.ponbr", bxPurOrdDetQC.ponbr)
                Call GetBufferValue("bpurorddet.lineref", bxPurOrdDetQC.LineRef)

                SQLStr = "xPurOrdDetQC_all " + SParm(bxPurOrdDetQC.ponbr) + SParm(bxPurOrdDetQC.LineRef)
                serr2 = SqlFetch1(csr_xPurOrdDetQC, SQLStr, tmpxPurOrdDetQC, LenB(bxPurOrdDetQC))

                Call GetBufferValue("bpes.today", bxPurOrdDetQC.lupd_datetime.val)
                Call GetBufferValue("bpes.scrnnbr", bxPurOrdDetQC.lupd_prog)
                Call GetBufferValue("bpes.userid", bxPurOrdDetQC.lupd_user)

                If serr2 = 0 Then
                    Call SUpdate1(csr_xPurOrdDetQC, "xPurOrdDetQC", bxPurOrdDetQC, LenB(bxPurOrdDetQC))
                Else
                    Call GetBufferValue("bpes.today", bxPurOrdDetQC.crtd_datetime.val)
                    Call GetBufferValue("bpes.scrnnbr", bxPurOrdDetQC.crtd_prog)
                    Call GetBufferValue("bpes.userid", bxPurOrdDetQC.crtd_user)
                    Call SInsert1(csr_xPurOrdDetQC, "xPurOrdDetQC", bxPurOrdDetQC, LenB(bxPurOrdDetQC))
                End If

                serr1 = MNext(gridHandle, maintflg)
            Wend

            Call msetrownum(gridHandle, row)
            Call MDisplay(gridHandle)
        End Select
    End Sub

    ------------------------------
    Good luck,

    Ayrin
    ------------------------------



  • 7.  RE: Adding a table extension to an existing Dynamics SL grid control

    Posted 11-02-2017 03:05 PM
    @Ayrin Hilbert -- thank you! This is exactly the kind of thing I was hoping someone would share. I'll give it a try. Thanks a million!​​​

    ------------------------------
    Darren Baker
    Fujimico Corporation

    ------------------------------



  • 8.  RE: Adding a table extension to an existing Dynamics SL grid control

    Posted 02-12-2018 07:00 PM
    Hi Mark

    Could you please publish an example for the sales person screen to add a table screen for budget a sales ? i mean add sales forecast for month to compare to sales report?
    this would be very helpfull for comparing real sales to budget sales per sales person
    thanks

    ------------------------------
    Manuel Vela
    Tec Electronica
    ------------------------------



  • 9.  RE: Adding a table extension to an existing Dynamics SL grid control

    Posted 02-12-2018 07:07 PM
    Hi again Mark

    this woul be very usefull, add an screen similar to budget maintenance (01.250.00) but with information for sales person (08.310.00)

    I hope you can publish how to

    ------------------------------
    Manuel Vela
    Tec Electronica
    ------------------------------



  • 10.  RE: Adding a table extension to an existing Dynamics SL grid control

    SILVER CONTRIBUTOR
    Posted 02-13-2018 03:20 PM
    If you just add an extension to the salesperson table, what do you do when the year changes? What you are asking is probably better served by creating a 'child' table to the sales person.
    If you want long-term reporting, you must have a separate row for each fiscal year.  The SDK toolkit would be a better tool for this.

    ------------------------------
    Mark Schrier
    Information Systems Director
    Mackay Communications, Inc
    Raleigh NC
    ------------------------------



  • 11.  RE: Adding a table extension to an existing Dynamics SL grid control

    Posted 02-13-2018 05:22 PM
    Hi Mark

    Thanks for your rapid answer, this could help to compare real sales to quotes for sales person, Dyn SL does not have this feature and i think this will be very usefull for many users. unfortunately i do not know how to program in VB :(.
    could you give us an example for this??

    Manuel Vela

    ------------------------------
    Manuel Vela
    Tec
    ------------------------------