Categories
Excel

Maximizer CRM & Database Structure – Mapping Fields to Database Columns

In this blog, we explain how to map fields in a Maximizer CRM page to SQL database. The structure of Maximizer software on your computer might be different and we use our Maximimer structure as a reference.

If you’re looking for help for Maximizer or Excel, please feel free to contact our Maximizer and Excel experts.

Direct Mapping

Our Maximizer includes a page called “Opportunities”. The table on this page is mapped to a SQL table called, “AMGR_Opportunity_Tbl”. Many of the fields on Opportunities page are directly mapped to the columns of the SQL table; however, the naming might be different. For example, Opportunities page contains a field called, “Job Description” and it’s mapped to “Comments” column on the SQL database table. How did we find the mapping between these two? We picked a value in Job Description field and searched the entire database for this value. The search results referred to Comments column of AMGR_Opportunity_Tbl table.

ٔNon-Direct Mapping

However, not all fields are directly mapped to a column. Some fields on Opportunities page are defined by user. These fields are managed by “AMGR_User_Field_Defs” and “AMGR_User_Fields” SQL tables. We explain two examples of user-defined fields.

user fields

Opportunities page includes another field called, “Estimate #”. This field is not directly mapped to AMGR_Opportunity_Tbl database table. The value of this field is stored in “AMGR_User_Fields” database table because it’s a custom field defined by user. Let’s find the Estimate # of a row in Opportunities page. In the first step, find the ID of that row. In our Maximizer SQL database, the ID is stored in “Opp_Id” column of AMGR_Opportunity_Tbl.

Open AMGR_User_Fields SQL table and look for the ID (found in the first step) in Client_Id column. You probably find many rows. Each row in this table is storing some user defined fields and each user defined field has a unique Type_Id. In our Maximizer table, the Type_Id of Estimate # is 44.  Therefore, look for a row in AMGR_User_Fields table that has the specific ID and Type_Id that you are looking for. When you find the row, look into “AlphaNumericCol” column which stores the value of the user defined field. Image below shows the process visually.

user field definistions

In addition of the fields discussed, there is another special field in Opportunities page called, “Type”. The value of this field is stored in AMGR_User_Field_Defs SQL database table. As explained in USER FIELDS section, look for a row in AMGR_User_Fields that has the ID and Type_Id that you’re looking for. When you find that row, grab the value in “Code_Id” column.

Then open AMGR_User_Field_Defs table and look for the row which has the specified Type_Id and Code_Id. The value of the field that we were looking for is stored in “Description’ column of that row.

Maximizer & Excel Help

If you need help with your Maximizer and Excel files, please feel free to contact us and we provide you a free quote.

Categories
Excel

Best Practices in Writing VBA Code for Developer Teams

Are you part of a development team? Are you writing VBA code for your team project? Are other team members working on the same project? What are the best practices in writing VBA codes so other team members can understand your code efficiently and continue the job smoothly?

As Excel Consultants in Vancouver, we share our experiences within our own group.

Define Constants

A sheet name might be used all over the code. Similarly, a range name is most often used. What if a constant is defined for these names? A team member might have to change a sheet or a range name. With defining constants, they don’t have to look for it all over the code just because they want to change a sheet name. They can simply, change the value of the constant.

A Module for Constants

You can create a module called “Constants” and place all your constants in that module. Make sure you categorize your constants, for example, place constants for each sheet names below each another. Create another section for range names. Make sure you write a comment for each section, and explain what this section is about. Reading comments is easier for your team member than reading codes. You can see a screenshot of a module that I created for constants below.

VBA Module for Constants - Excel

Declare Variables

In VBA you don’t get an error if you don’t declare variables. However, this is a must, especially, if you’re programming in a team based environment. Use “Option Explicit” on top of your code. Using Option Explicit forces you to declare all your variables. Define your variables at the beginning of a function because team members who are reading the code are probably looking for the list of variables at the beginning of a function.

Declare Variables with Specific Data Type

Make sure to declare the variable with a specific data type to give others a clue of what the purpose is. For example, “Dim c” might have multiple meanings. Some might interpret variable “c” as a counter (integer type) but others as a column name (string type).

Comments and Proper Naming for Variables

In addition, include comments for variable and write the purpose, if the variable is for specific use. Try proper naming. Which variable is more meaningful for declaring a report workbook, the one called “rep”or “reportwb”?

This is a sample of how we declared a variable:

' Master workbook
Dim masterwb as Workbook

Function Names & Commenting

Descriptive Function Names & Commenting in Calling Functions

Functions are for specific use. Why not use a specific descriptive name for them? When you find a function called in the middle of another function and you want to know what it is for, what would be the easiest way? I would say the answer is, proper function naming and commenting. If the function name is descriptive, you wouldn’t need to scroll all the way down to find the function and study its structure. A descriptive function name saves a lot of time in scrolling. However, often a descriptive function name is not enough. Make sure to add comments when calling a function, explain what the function does and possibly what it returns.

Descriptive Commenting in Defining Functions

So, another programmer is reading your code and is wondering why you created a function called “adjust_columns”. They get a clue from the function name that it has something to do with fixing rows, but what exactly does it do? They could study the function code or you could save their time by writing some comments about what the function does. For instance, you can explain that this function adjusts the width of all filled columns in a given sheet to make sure the values in the columns are visible.

Other than the purpose of the function in the comment area, it’s a good idea to explain:

  • the inputs,
  • the outputs, if any,
  • and requirements, i.e. a requirement of a function before it’s called that a specific sheet should be active.

Practices for Functions

Short Functions

It’s a good practice to make functions short. Going through a long function might be frustrating for others. Break your long functions into smaller ones. It is easier to understand three small functions rather than a long one, right? I, at least, prefer reading smaller functions.

Repetitive Code in a Function

Avoid repetitive code. If your team member wants to change something in a repetitive code, they must change all of the repetitions. Define a function and add the repetitive code in that function.

Main Function

In some projects, there is a main function which is called first and calls other functions. In C or C++, this function is called main(). In VBA, we could follow the same practice and call the main function as main() and place it above all other functions. Consequently, other programmers can easily identify it.

Indent Style & Next Line

Indent your code specially inside loops, “if” conditions, and other blocks. Additionally, if a line gets too long, use “_” to break it into multiple lines. This helps programmers visually picture the code block in their mind.

Commenting

Commenting a source code could lift pressure! Reading comments instead of a code reduces frustrations and helps other team members understand what your code does. Place comments for,

  • Functions: explain what a function does, the inputs, outputs and requirements.
  • Specific variables: what a variable is specifically used for.
  • At the top of a module: write the purpose of the module.
  • Unified block of code: for example, include comments for a loop, or a block of a code used for opening a workbook or a specific goal.

Finally, it’s a good practice to time stamp each comment so other programmers know when the related code is written or updated.

If you’re looking for a team of Excel VBA Developers, please feel free to contact us for a free quote.