Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics SL (Archived)

Using Customization Manager to link a new table to an existing SL screen

Posted on by Microsoft Employee

I would like to add several text fields to the employee maintenance screen and what is to be added exceeds the user defined fields available so it requires the creation of a new SQL table, linking that table into the screen and then adding fields from that table to the screen.  The SL documentation of how to do this and what the VBA code needs to include is sparse at best.  I have the table created and introduced to the screen such that Customization Manager now sees the new table and the fields can be added to the screen.  Since it appears that SWIM will not process these fields, I understand that code needs to be added to handle the display, inserting, updating and deleting of these fields but there is no documentation on what such code should look like and under what events it needs to be added.  I have the populating of the fields working but not the updating or inserting and was hoping someone has done something similar (with an SL screen) and would be willing to share the VB code they created to do that.

By the way, the new table is a one-to-one relationship to the employee.  It is holding items like emergency contact name and number, email address of the employee, cell number for the employee, etc.

 

*This post is locked for comments

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Using Customization Manager to link a new table to an existing SL screen

    Thanks again for the quick response.  Having something to compare my changes to is most helpful.  The screen is now populating these fields as I navigate.  It is not updating however.  The OnUpdate event is not even firing but I am sure I will find out why that is with a little experimenting.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Using Customization Manager to link a new table to an existing SL screen

    I used this as the last command of the empid chk event:

     Call DispFields("form1", "")

    The level property on the new fields is NOLEVEL.

    I did add a module to the project for the DH definition of the new table.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Using Customization Manager to link a new table to an existing SL screen

    Thanks.  this is basically what I had with the exception of the "+SqlUpdate on the SqlCursorEx call.  I added that but I am noticing that the new fields are not populating when I select the employee even though the SFETCH1 SQL command is constructed properly for the selected employee.  What is strange is that, when I then navigate to another employee the values for the previous employee show.  We have written over 200 SL screens using the VB toolkit and have never seem this symptom before.

    One questions, just to confirm.  Your sample used NOLEVEL for the cursor.  Did you leave the level on the new fields blank or set them to 0?  Also, did you place an employee id field (hidden or not) on the form to go along with the new table?

    Finally, I am assume you added a module to the project for the DH definition of the new table.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Using Customization Manager to link a new table to an existing SL screen

    Here is form_load

    Call VBA_SetAddr("bxEmployeeXtra", bxEmployeeXtra, nxEmployeeXtra, LenB(bxEmployeeXtra))

     Call SqlCursorEx(CSR_xEmployeeXtra, NOLEVEL + SqlUpdate, "CSR_xEmployeeXtra", "xEmployeeXtra", "xEmployeeXtra")

    Here is the Empid_Chk event

    Dim GetxEmployeeXtra As Integer

       If Trim(ChkStrg) <> "" Then

          GetxEmployeeXtra = SqlFetch1(CSR_xEmployeeXtra, "xEmployeeXtra_Empid" & SParm(ChkStrg), bxEmployeeXtra, LenB(bxEmployeeXtra))

       If GetxEmployeeXtra = NOTFOUND Then

    FoundXtra =false

         bxEmployeeXtra = nxEmployeeXtra

    else

    FoundXtra = true

          End If

     End If

    Here is the OnUpdate event

    If Level = Finished Then

       Call TranBeg(True)

       If FoundXtra Then 'update existing

         Call SUpdate1(CSR_xEmployeeXtra, "xEmployeeXtra", bxEmployeeXtra, LenB(bxEmployeeXtra))

       Else

         Call SInsert1(CSR_xEmployeeXtra, "xEmployeeXtra", bxEmployeeXtra, LenB(bxEmployeeXtra))

       End If

       Call TranEnd

     End If

    And the Delete event

    Dim Empid As String * 10

     Dim SqlStr As String

     If Level = LEVEL0 Then

       Empid = GetObjectValue("cempid")

       SqlStr = "Delete xEmployeeXtra where Empid = " & SParm(Empid)

       Call TranBeg(True)

       Call sql(c1, SqlStr)

       Call SqlFree(c1)

       Call TranEnd

     End If

    Hope this helps

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans