Open Forum

Issue adding a new table to Dynamics SL Screen then update records

  • 1.  Issue adding a new table to Dynamics SL Screen then update records

    Posted Sep 10, 2019 10:21 AM
    Hello All,

    I am starting this discussion mainly for some assistance, but to also fill a hole from my research where this subject is not 100% covered. At my organization we have a need to add additional information for our vendors. All of our user fields are populated so we decided to create a new table. The new table was added and permissions were set. We were able to add the new table, but our issue seems to be with saving new records. When attempting to fill in the new field, we receive the following message:

    Thank you in advance,
    Mitch

    Our table Info is:
    Type Vendor_NAICS
    VendID As String * 25
    NAICS_Code1 As String * 8
    NAICS_Code2 As String * 8
    NAICS_Code3 As String * 8
    NAICS_Code4 As String * 8
    NAICS_Code5 As String * 8
    User1 As String * 15
    User2 As String * 15
    User3 As String * 30
    User4 As String * 30
    User5 As String * 30
    User6 As Double
    User7 As Double
    User8 As Double
    Crtd_DateTime As Sdate
    Crtd_User As String * 25
    End Type

    Public bVendor_NAICS As Vendor_NAICS, nVendor_NAICS As Vendor_NAICS
    Public err_Vendor_NAICS As Integer, csr_Vendor_NAICS As Integer

    'Declare variable used to hold if the row exists
    Public RowFound As String
    'variables to hold update information
    Public Userid As String
    Public BusDateStr As String
    Public BusDate As Sdate

    Our VB code is listed below. I was unable to pinpoint the issue but feel like I am missing code. If possible, can someone assist us?
    Public cursor_Vendor_NAICS As Integer

    Private Sub Form1_Display()

    Call GetBufferValue("bpes.today", BusDateStr)
    Call GetBufferValue("bpes.Userid", Userid)
    BusDate.val = val(BusDateStr)

    End Sub

    Private Sub Form1_Load()

    ' setaddr and cursor for Vendor_NAICS table
    Call VBA_SetAddr("bVendor_NAICS", bVendor_NAICS, nVendor_NAICS, LenB(bVendor_NAICS))
    Call SqlCursorEx(csr_Vendor_NAICS, NOLEVEL, "csr_Vendor_NAICS", "Vendor_NAICS", "Vendor_NAICS")

    End Sub

    Private Sub cvendid_Chk(ChkStrg As String, retval As Integer)

    End Sub

    Private Sub Vendor_Chk(ChkStrg As String, retval As Integer)

    err_Vendor_NAICS = SqlFetch1(csr_Vendor_NAICS, "Select * from Vendor_NAICS where VendID = " & SParm(ChkStrg), bVendor_NAICS, LenB(bVendor_NAICS))
    If err_Vendor_NAICS = 0 Then
    RowFound = "Y"
    Call DispFields("Form1", "")
    Else
    bVendor_NAICS = nVendor_NAICS
    bVendor_NAICS.VendID = Trim$(ChkStrg)
    RowFound = "N"
    Call DispFields("Form1", "")
    If TestLevelChg(LEVEL0) = NOTCHANGED Then Call SetLevelChg(LEVEL0, UPDATED)
    End If

    End Sub

    Private Sub NAICSCode1_Chk(ChkStrg As String, retval As Integer)

    End Sub

    Private Sub TemplateFormBtnClose_Click()

    End Sub

    Private Sub Update1_OnUpdate(ByVal Level As Integer, ByVal InsertFlg As Integer, retval As Integer)

    If Level = LEVEL0 Then
    Dim BusDateStr As String
    Call GetBufferValue("bpes.today", BusDateStr)
    Call GetBufferValue("bpes.Userid", Userid)
    BusDate.val = Date
    If RowFound = "Y" Then
    bVendor_NAICS.Crtd_DateTime = BusDate
    bVendor_NAICS.Crtd_User = Userid
    Call TranBeg(True)
    Call SUpdate1(csr_Vendor_NAICS, "Vendor_NAICS", bVendor_NAICS, LenB(bVendor_NAICS))
    Else
    bVendor_NAICS.Crtd_DateTime = BusDate
    bVendor_NAICS.Crtd_User = Userid
    Call TranBeg(True)
    Call SInsert1(csr_Vendor_NAICS, "Vendor_NAICS", bVendor_NAICS, LenB(bVendor_NAICS))
    End If
    End If
    If Level = Finished Then
    RowFound = "Y"
    End If

    End Sub

    Private Sub Update1_OnCancel(ByVal Level As Integer, retval As Integer)

    Dim VendID As String
    Dim SqlStr As String

    'Get the Vendor ID
    VendID = GetObjectValue("cvendid")

    If Level = 0 Then
    SqlStr = "SELECT * FROM Vendor_NAICS where VendID = " & SParm(VendID)
    serr1 = SqlFetch1(csr_Vendor_NAICS, "Vendor_NAICS", bVendor_NAICS, LenB(bVendor_NAICS))

    If serr1 = 0 Then
    Call SUpdate1(csr_Vendor_NAICS, "Vendor_NAICS", bVendor_NAICS, LenB(bVendor_NAICS))
    Else
    Call SInsert1(csr_Vendor_NAICS, "Vendor_NAICS", bVendor_NAICS, LenB(bVendor_NAICS))
    End If
    End If

    End Sub

    Private Sub Update1_OnInsert(ByVal Level As Integer, retval As Integer)

    If Level = LEVEL0 Then
    RowFound = "N"
    bVendor_NAICS = nVendor_NAICS
    Call DispFields("Form1", "")
    End If

    End Sub



    ------------------------------
    Mitchell Winter
    Rhoads Industries, Inc.
    Philadelphia PA
    ------------------------------


  • 2.  RE: Issue adding a new table to Dynamics SL Screen then update records

    SILVER CONTRIBUTOR
    Posted Sep 10, 2019 11:25 AM

    Hi Mitchell,

    Mark Schrier wrote a great DSLUG blog post about this awhile ago (link below)...I haven't looked through all your code, but I would probably try moving the Vendor_Chk code into the cvendid_chk.

    Good luck! 

    Marc

    https://www.dslug.com/blogs/mark-schrier/2017/02/27/adding-a-table-extension-to-an-existing-dynamics-sl-screen?CommunityKey=73716490-9afa-4cd7-a04d-e9f9d75c95c2&tab=



    ------------------------------
    Marc DeLozier
    Finance SysAdmin
    David Evans and Associates Inc.
    Portland OR
    ------------------------------



  • 3.  RE: Issue adding a new table to Dynamics SL Screen then update records

    SILVER CONTRIBUTOR
    Posted Sep 11, 2019 09:01 AM
    So before worrying about the customization layer, I have some questions about the SQL layer.  Could you post the create statement for your table in SQL?  Do you have the required SL timestamp field?  Did you create the required index which by convention would be named Vendor_NAICS0?  I know you said you gave permissions, but can you confirm that you gave the permissions to the E8... account and the MSDSL role?

    ------------------------------
    Joe Miller
    Director of Systems Architecture
    Harrison, Walker & Harper
    Paris TX
    ------------------------------