Open Forum

Topic: 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 9 days ago
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 9 days ago
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 9 days ago
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 8 days ago
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
------------------------------