dynamics sl annual user conference san diego, september 18-20, 2013 slide 1 nuts & bolts of...

Dynamics SL Annual User ConferenceSan Diego, September 18-20, 2013 Slide 1

Nuts & Bolts of Customization Manager


Tom MaliaPartnerT&T Data Solutions, LLCHavre de Grace, MD

Tim MaliaPartnerT&T Data Solutions, LLC Hillsborough, NC

About the Presenters


• Upon completion of this session, participants should be able to:– What are Levels and how do they work– Create simple customizations– Add new data fields to an existing screen– Modify attributes of fields– Freeze panes on a grid– Trouble shoot customizations– Methodologies for managing customizations

Learning Objectives


How do you access Customize mode

• Prerequisites:

– Been granted rights to Customization Mgr.

– The only screen open is the one to be customized.


How do you access Customize mode

• Turn on Customize mode:– Select Customize Mode

from Action menu

OR

– Ctrl + Alt + C


Am I in Customize Mode ?


What Are Levels in Customization Manager?


How Levels Work:When a customized screen opens….


Dynamics LS “out of the box”

When do you use it?

Standard


Supplemental Products Level• Designed for 3rd party developers

• You can run the screen but can’t see or modify the customization.

• We will see how to view the code at this level a little later.


This one’s pretty much self explanatory….

All User Level


Q: How many groups?

Group Level

Q: How do you write one?

A: “1” per user

A: Select Group ID


One User Level

• Write a customization for you.

• Allows a user to write customizations for another user that doesn’t have rights to customization manager.


Self Level

• Default level when you log into Dynamics SL

• Customization just for you


Think about what you build on:

A little warning:


• What’s you current Level?

– When you log into SL your set to the Self level

– You see it at the bottom of the main SL menu.

• How do you change your Level?– Double click on the level on the

main SL menu to open the Select Customization Level screen.


Is a screen Customized?

• The screen title has the answer.• Look for the “*”


Lets Do One !!


Remove the clutter!

• Use Customization Manager to remove fields in Vendor Maintenance because you don’t use multi currency or multi company.


Get the data you need

• Require users to fill in the _____ field just like Dynamics SL required fields work.

• Collect information that you need but Dynamics SL doesn’t have a field for.


Get the data you need• Require users to fill in the Description field


Get the data you need

• Require users to fill in the _____ field just like Dynamics SL required fields work.

• Collect information that you need but Dynamics SL doesn’t have a field for.


Get the data you need• Collect information that you need but Dynamics SL doesn’t have a field

for.


Adding new data to a screen

More advanced Customizations:• Adding field when you’re out of “user” fields

• Adding a table to a screen

• Adding a PV to a custom control• Reading data from the current screen• Calling one program from another and passing data• Setting the values of controls on a screen• Programmatically change control properties


Adding new data to a screen

When you need to track more information than can be handled with the user fields available, you can add whole new tables to the database. Then you add that table to an existing DSL screen.


For this demonstration, we will add three more pieces of information to the customer data:1. A 15 character vendor ID2. A 10 character account3. A 24 character subaccount

Assuming no user fields had been used for anything else, you “could” implement this without adding a new table, but we are going to assume that the user fields have already been used for something else and they are not available to us


There are a couple of requirements for tables if you want to include them Dynamics SL screens:• The name must be <= 18 characters with

no spaces• The name can not end in a number• It must have a timestamp field named

tstamp as its’ last field

Don’t forget access rights! If you add a new table, view, etc. and you are using integrated security, you will need to either manually grant the alias user rights to them or, the easier method, run synchronize security from database maintenance.


Steps for adding a new table to a screen1. Import a “DH” file –

• Add a user defined type and “Buffer” variable to the project

2. Add vba_SetAddr – • need to tell Kernel about new table/variable

3. Add SQLCursorEX – • need a “cursor” with which to read and write data from and to the table

4. Add fetch code to Key field – • Whenever the user navigates to a new record in the screen, you need to fetch the

appropriate record from your extra table

5. Add code to save/delete events – • Whenever the user saves or deletes the data in the screen, you need save or delete

the data in the additional table

6. Close and reopen the screen – • The vba_SetAddr call must execute so the kernel will know that your new table is

available to add controls to the screen from

7. Add controls –


Steps for adding a new table to a screen

1. Import a “DH” file – • Add a user defined type and “Buffer” variable to the project



2. Add vba_SetAddr – need to tell Kernel about new table/variable



3. Add SQLCursorEX – • need a “cursor” with which to read and write data from and to

the table



4. Add fetch code to Key field – • Whenever the user navigates to a new record in the screen, you

need to fetch the appropriate record from the additional table.



5. Add code to save/delete events –

Whenever the user saves or deletes the data the screen, you need save or delete the data in the additional table


Steps for adding a new table to a screen6. Close and reopen the screen –

• The vba_SetAddr call must execute so the kernel will know that your new table is available to add controls to the screen from

The call to the VBA_SetAddr callis what tells the kernel that your newtable should be included in the list oftables from which you can add controls.

So, until that call executes, you will notsee your table in the list as shown here.

To get that call to execute, you must get the form1_load event to fire and to dothat you need to close and reopen thescreen.



7. Add controls – The last step is to actually add your controlsfrom your extra table to the screen.

When you do this, you will need to selecta “level” that your controls should beassociated with. You determine this byasking yourself “if my table was actuallyjust more fields in one of the tables alreadyin the screen, which table would that be?”That is the level you should choose.

In our case, we’re basically adding more fields to the “customer” table so that isthe appropriate Level.


Add a PV to custom fieldClearing Account


Add a PV to a custom fieldSteel it from another control

The exact structure of a PV property is notalways simple, however a lot of times you can“steel” a PV from another control.

In our case, we want to have PV’s on theclearing account and subaccounts fields we added. The PV’s on these controls shouldgive the same lists as the account & subaccount fields on the Defaults tab of the same screen.

So, you can copy the text from the PVproperties of those controls and simply paste it into the PV properties of ournew controls.


Calling one screen from another and passing data


Passing Data to another programGetBufferValue – to get data from buffervariables that are in the original EXE

ApplSetParmValue – to “queue up” datato be passed to another program

Launch – to actually open the other programand pass it the data


Retrieving Data from another programApplGetParmValue – to retrieve datathat was passed from another program viaApplSetParmValue

SetObjectValue – Use this call to populate the controls of the current screen with the data received from the other program.Note that you “could” use setBufferValue,but if you used that, then any logic in anyof the controls attached to the buffers youpopulate would NOT execute. Where as,by using SetObjectValue, the screen behavior exactly as it would if some onetyped the data in.


Changing control properties programmatically



In our demonstration customizations,we don’t want users to click the “Create Vendor” button if there’s Already a vendor associated with the customer.

So, what we would want is, for that button to be disabled whenever the Vendor ID field is populated but enabled if the vendor ID field is blank.



SetProps – When you want to change a propertyof a control programmatically you actually need to “ask the kernel” to do this for you.

You do that by calling “SetProps” and passing the control you want to modify, the property you want to change and the value you want to set it to.


Section:

Where & How are Customizations stored?


System database:• CustomVBA

• “standard” for current SL versions

• Custom2• “Pre-VBA” Basic Script Language (BSL)

• Custom• OLD! – legacy from 16 bit product

version.

Where are customizations stored?


What identifies a customizations?

• ScreenID • The executable name

• Sequence • 500 = self• 400 = One user• 350 = Group• 300 = All users• 100 = Supplemental product

• Entity ID• Specific 3rd party, group or user


Section:

The OFFICIAL tools for managing customizations


Official Tools/Utilities:Export Customizations (91.500.00)


Understanding CST files:The header:


Understanding CST files:The header:

• Supplemental products:' Begin Customization Screen: 0327000 Version: 5 Sequence: 100 ->CompanyName: "Example Sup Prod" -> Description: "" ->System Administrator: FALSE Secure: 55

• All User :' Begin Customization Screen: 0327000 Version: 5 Sequence: 300 -> Description: "A New All Users Customization" System Administrator: FALSE Secure: 55

• One User :' Begin Customization Screen: 0327000 Version: 5 Sequence: 400 UserId: "TOMMALIA" -> Description: "A New One User Customization" System Administrator: FALSE Secure: 55

• The header must be all on one line in the actual file. The “->” represents a “line continuation” for demonstration purposes and would NOT be present in an actual file.


Understanding CST files:Properties:

Begin Button ButtonHistory'' Version = 6 height = 21 left = 278'' parent = "_SSTab1_TabPage0"'' parentform = "Form1" top = 85 width = 97End

• Changes to the properties of controls are exported in “Plain Text”.

• Here is an example of a customization that simply changed the location of a button.


Understanding CST files:Code/VBA Project:

' Begin Macro Text*** VBA PROJECT BINARY DATA FOLLOWS. DO NOT MODIFY. ***Length: 400384Compiled: 1MT,\1X*&Q&N$ /@ # /[_"0 & 'M &P $ 'P H #^____ !P " ] &P!M "Q 0 @ ( # #_________________________________________M____________________________________________________________M____________________________________________________________M____________________________________________________________M *YY 0 ." H@F\ @( (P 3@ 'L ,0 P #@ 00 Y #$ M,P R "T -P X #@ . M #$ ,0!$ #$ +0 X #@ 1 P "T , P #8 , YM #< ,P Q $( -@ Y #< ?0 M 3 $,

' End Customization

• The “Macro Text” section contains a UU Encoded Copy of the VBA project which includes any macro code.


Understanding CST files:Code/VBA Project:

• Version 7 FP1 and later have an option to export the code in plain text!!!


Official Tools/Utilities:Import Customizations (91.510.00)


Official Tools/Utilities:Import Options

• Overwrite– Deletes current and replaces with new

• Reject customization– If a customizatoin for a given

Screen/level/entity in the import already exists, the new customization will not be imported

• Merge– “Tries” to combine existing customizations

to a screen/level/entity with one being imported to the same screen/level/entity


Section:

The UNOFFICAL tools for managing customizations

Behind the scenes


What identifies a customizations?

• ScreenID • The executable name

• Sequence • 500 = self• 400 = One user• 350 = Group• 300 = All users• 100 = Supplemental product

• Entity ID• Specific 3rd party, group or user


How do customizations Load?


A little simpler view of the queryThat gets the set of customizations to apply


Get the actual customization code & property settings


1) Supplemental Products

2) All Users

3) Group (GOUPA)

4) One User (TOMMALIA)

5) Self/Save Settings on exit


Customization documentationWhat should you document?

• Why was the customization implemented• What does the customization “look like” • Export of current state of the customizations (CST file)• Plain text exports of all VBA code – Don’t forget DH files also– NOTE! There’s a great new feature in SP1 for this!!! YA!

• SQL Data Definition Language (DDL) scripts to create any needed tables, views, procs, etc.


Customization documentationHow do you export source code?


Customization documentationWhere should you put your documentation?


Something to take home: a script to make project directories

select Distinct 'mkdir "' + ltrim(rtrim(CustomVBA.ScreenId)) + '-' + REPLACE(Screen.Name, ' ', '_') + '\' + case when CustomVBA.Sequence between 0 and 100 then 'SUPPROD\' + LTRIM(rtrim(CustomVBA.EntityId)) when CustomVBA.Sequence = 200 then 'LANG' when CustomVBA.Sequence = 250 then 'LOCAL' when CustomVBA.Sequence between 300 and 349 then 'ALLUSER' when CustomVBA.Sequence between 350 and 399 then 'GROUP\' + LTRIM(rtrim(CustomVBA.EntityId)) when CustomVBA.Sequence between 400 and 499 then 'ONEUSER\' + LTRIM(rtrim(CustomVBA.EntityId)) when CustomVBA.Sequence >= 500 then 'SELF\' + LTRIM(rtrim(CustomVBA.EntityId)) else CONVERT(varchar(4), CustomVBA.Sequence) end + '"' from customVBA left outer join Screen on CustomVBA.ScreenId = screen.Number

mkdir "0101000-Journal_Transactions\SELF\TOMMALIA"mkdir "0327000-Vendor_Maintenance\ALLUSER"mkdir "0327000-Vendor_Maintenance\GROUP\GROUPA"mkdir "0327000-Vendor_Maintenance\ONEUSER\TOMMALIA"mkdir "0327000-Vendor_Maintenance\SELF\SYSADMIN"mkdir "0327000-Vendor_Maintenance\SELF\TOMMALIA"mkdir "0327000-Vendor_Maintenance\SUPPROD\Example Sup Prod"mkdir "0329000-Vendor_Class_Maintenance\SELF\SYSADMIN"mkdir "0826000-Customer_Maintenance\SELF\TOMMALIA"mkdir "TMTCE00-Timecard_Entry\ALLUSER"


Section:

Customizations During Upgrades


Upgrade Management:Did you test ALL the customizations?

• You ran the Dynamics SL upgrade, you upgraded the database and a couple users logged into screens without error…. You’re upgrade’s a success!

• Turn the whole company loose on your shiny new Dynamics SL!

• RIGHT?


Upgrade Management:Keeping track of customization testing results

• Most of you probably already do something like this… A simple spreadsheet to keep track of what needs t be upgraded, tested, etc….



• A slight variation on the same query we looked at for create the “create directory” statements earlier, and you can easily generate data that you can load into Excel to keep track of your customization upgrade.


Upgrade Management:Something to take home


Upgrade Management:to recompile or not to recompile. That might be the question:

• Frequently customizations need no extra attention as a result of an upgrade…. But not always

• VBA “caches” compiled customizations…. This can be a problem if the code “under” the cached customization has changed since it was compiled and cached… but you can force a recompile

• Sometimes, it’s easier to just consistently “re-do” all customizations… but not necessarily from scratch



• You can force a recompile

[Customization]VBASourceSave=NoVBASourceCompile=Yes

• You can add an entry to the Solomon.ini file that will tell Dynamics SL that it should not rely on cached compiled code.



• Sometimes, it’s easier to just consistently “re-do” all customizations… but not necessarily from scratch

• Sometimes it’s easiest to just bight the bullet and “redo” all customizations by importing modified CST files that have the macro section removed.

• Then manually import or copy and past the VBA code back into the projects.

• *Note: there’s a really good chance that with the ability to export vba code as plain text that comes with version 2011 SP1, this will no longer be necessary.


How to “step through” code:

Debugging tips


How to “step through” code:1. Start the program you want to debug2. Start Customize mode (Ctl+Alt+C)3. Open VBA (F7)4. Set a break point (click in gray of line)5. Exit Design Mode6. Do something that causes the event

to fire

Debugging tips


How to “step through” code:

I did what you said!

So why did this happen!?!?

Debugging tips


How to “step through” code (STOP is your friend!):

The VB “Stop” command will put you into “debug mode” in your project without having to start customization manager

Debugging tips


I can’t deploy my customizations with “Stop” commands in it? Can I?

Yes, if you use “magic codes”, or something similar

Debugging tips


Dealing with SQL Statements

Debugging tips


An error in your SQL statement will usually crash the screen…

Using some coding standards and some debugging techniques, you can greatly increase your coding efficiency

Debugging tips:Dealing with SQL Statements


• Always start by clearing the string


• Build querys that are easy to read (multi-line are particularly easy to read and edit)

• Print your queries to the debug window, copy them and test them in SQL Enterprise Manager before executing the SQL API call



• The debug.print command will send your SQL to the “immediate” window (Ctl+G opens this window)

• Then you can copy that SQL and paste it into SQL Enterprise Manager and test the query before it is called by your code (in this case, the SQLFetch1 call that the execution pointer is currently sitting on)



• When in debug mode and stepping though code, you can change the “execution pointer” to a different line in the same subroutine

• You can then keep running the code through the Debug.print line, copy and test the query and testing it in Enterprise manager until you get it right

• If you find a syntax error in your query, you can “fix” you code, and then move the execution pointer back to the line where you clear your SQL statement string to “take another stab at it”.



• A fairly simple Excel formula can make it really easy to build VBA code that builds a SQL statement

• And get your VBA code automatically generated for you, just copy it out of Column C

• Just copy that formula into Column C then paste the SQL query you tested and copied from Enterprise manager into Column B


Buffer variables & tables:

Debugging tips


So you’re getting a message that “size of the record” does not match the “database declared size”.


What does that mean?AndHow can I fix it if this error prevents me from opening the screen and therefore I can’t open customization manager?


Exclude Event Code!


Let’s address the 2nd question first:

If there is event code that prevents your customized screen from running, then there is an option in the Select Customization Levels screen called “Exclude Event Code” that you need to turn on. This will allow you to open the screen (though you may still see a bunch of errors while it opens) so you can get into customize mode and try to fix this.

(!! Don’t forget to turn “Exclude Event Code” OFF after you fix the problem!!)


What does it mean? Buffer variables & tables:

It means that the UDT variable that you passed to the VBA_SetAddr call does not exactly match the structure of the table that has the same name as the first parameter to that VBA_SetAddr call:


What does it mean? Buffer variables & tables:

UDT def. <> Table Def.OR!Watch out for:• No “option explicit “

&• A typo in a variable name!

Notice that the variable inside the lenb() function is NOT the bXSLUGCVXrefSetup variable! It was a mistype, with the “b” left off and since the “option explicit” has not been set for this file, that mistype results in the creation of a “Variant” type variable and does not generate an undefined variable compile time error!


Section:

Source Code Control & Documentations

Dynamics SL Annual User ConferenceSan Diego, September 18-20, 2013 Slide

100

Questions?


101


102

Please complete your evaluations!• This session must be added to

your conference “My Agenda” in order to do your evaluation!

• You must fill out an evaluation in order to obtain CPE credit (if applicable).

• A drawing from survey responses will be held on Friday, Sept 20th and again on Monday, Sept 23rd to award prizes!


103

A word about CPE• In order to get CPE for this session, attendees must:– Complete a session evaluation– Be sure that your registration setting requests CPE credit

• Go to DSLUG website, “My Conference 2013”,”View your registration details”

• Go to the Details tab, and edit the “Would you like to earn CPE” value to “Yes”


105

Action Steps• [Session next steps]– Download session material

• Speaker Contact information: • [email protected]• Please fill out your session

evaluation online – win prizes!

• Please fill in your Session Big Idea cards – win prizes!

dynamics sl annual user conference san diego, september 18-20, 2013 slide 1 nuts & bolts of...

Documents