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.

Best Practices in Writing VBA Code for Teams

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

As a team of Excel VBA Developers, 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 often used. It’s a good practice to define a constant 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 all over the code to change a sheet name. Simply, they can change the value of the constant.

A Module for Constants

You can create a module called “Constants” and place all your constants in the module. Make sure categorize your constants. For example, place constants for sheet names one below each other. And another section for range names. For each section, make sure to write a comment what this section is about. Reading comments is easier for your team member than codes. A screenshot of a module that I created for constants is shown.

VBA Module for Constants - Excel

 

Declare Variables

In VBA you don’t get an error if you don’t declare variables. However, it’s 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 in the beginning of a function because any one else reading the code, they probably look for the list of variables at the start of a function.

Declare Variables with Specific Data Type

And make sure to declare the variable with a specific data type so others have a clue what could be the purpose of it. 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, add comments for variable and write the purpose of them, if the variable is for specific use. And use proper naming. Is a variable called “rep” more meaningful or “reportwb” for declaring a report workbook?

This is a sample of how I 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 to use a specific descriptive name for them? When you find a function called in a middle of another function and want to know what it’s 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 have 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, many times a descriptive function name is not enough. Make sure to add some comments when calling a function and explain what the function does and possibly what it returns.

Descriptive Commenting in Defining Functions

Another programmer is reading your code and finding out why you created a function called “adjust_columns”. They get a clue from the function name that this is for fixing rows but what exactly it does? They could study the function code or you could save their time by writing some comments what the function does. For example you can explain that this function adjust 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 practice to explain

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

Practices for Functions

Short Functions

It’s a good practice to make function shorts. Study your long function might be frustrating for others. Break your long functions into smaller ones. Is it easier to understand three small function rather than a long one? For me, I  prefer reading smaller functions.

Put Repetitive Code in a Function

Avoid repetitive code. If your team-mate wants to change something in a repetitive code, they have to change all of 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. This way, any other programmer 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 save some lives! Reading comments instead of a code reduces frustrations in understanding 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?
  • In the top of a module: write the purpose of the module.
  • For unified block of code: For example, put comments for a loop, or a block of a code used for opening a workbook or a specific goal.

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

How To Parse an Address in Excel by Macro/VBA

If you have a full address and would like to extract address line, city, state (province), country and zip code (postal code), then you came to the right place. In this blog, we explain how to make an excel file to parse address. If you’re interested in a custom solution for your Excel file, please contact our Excel experts for a free quote.

We used Google Maps API to extract the information from full address. Google has a special address structure. When you run the code, both Google’s address and regular address is displayed.

Demo

Feel free to download a demo of the Excel XLSM file.

How the Excel file works?

  1. In range C1, type the address that you want to parse, for example “1500 Marine Drive, North Vancouver, BC”.
  2. Click “Parse Address” button. In the yellow area marked by “Google Map Address Structure”, the processed address by Google will be displayed.
  3. The green area marked by “Regular Address” shows the actual address divided into
    • Address
    • City
    • State/Provide
    • Country
    • Postal/Zip Code

Development

If you are familiar with VBA Editor and creating buttons, you can copy/paste the following VBA code into your VBA editor. And create a button to execute this function.

Then, install Json Converter developed by https://github.com/VBA-tools/VBA-JSON. i.e. create a module called “JsonConverter” and copy/paste the code written in the Github link in the module.

Once you copy/paste the code and created a button, type an address in range C1 and click the button. The address is parsed!

VBA Code

' Written by bsup.ca

' Extract Full Address to
' Street Number, Route, Neighborhood, Locality, Administrative Area, Country and Postal Code
Sub ExtractAddress()
    Dim sURL As String, sResult As String
    Dim oResult As Variant, oData As Variant, R As Long, C As Long

    ' Clear the sheet
    ActiveSheet.Range("B2:C9").ClearContents
    
    sURL = "http://maps.googleapis.com/maps/api/geocode/json?address=" + _ 
        WorksheetFunction.EncodeURL(Range("C1").Value) + "&sensor=false"
    
    Debug.Print "URL: " & sURL
    sResult = GetHTTPResult(sURL)
      
    Dim Json As Object
    Set Json = JsonConverter.ParseJson(sResult)
     
    Dim element As Variant
    
    R = 1
    
    For Each element In Json("results")(1)("address_components")
        ActiveSheet.Cells(R + 1, 2) = element("types")(1)
        ActiveSheet.Cells(R + 1, 3) = element("long_name")
        R = R + 1
    
    Next element
    
    Set oResult = Nothing
End Sub

' Get the json result
Function GetHTTPResult(sURL As String) As String
    Dim XMLHTTP As Variant, sResult As String

    Set XMLHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    XMLHTTP.Open "GET", sURL, False
    XMLHTTP.Send
    Debug.Print "Status: " & XMLHTTP.Status & " - " & XMLHTTP.StatusText
    sResult = XMLHTTP.ResponseText
    Debug.Print "Length of response: " & Len(sResult)
    Set XMLHTTP = Nothing
    GetHTTPResult = sResult
End Function

The difference between XLSM and XLSB extensions in Excel

In this part I will explain about XLSB extension which is useful but many users are not aware of its benefits. As you know the default format for data which are stored in Excel is XLSL extension, and if your file includes VBA code or a macro it will be stored in XLSM format. There is another format in Excel for the files which include macro, named as XLSB, but is used rarely. In the following we will learn more about XLSB format and understand when it ought to be used.

Both XLSM and XLSB are in fact compressed files which you can open them in software such as WinRAR. Typically, XLSM format uses XLM to store the data, while XLSB includes a binary workbook which helps significantly to reduce the file size. Here we have discussed the main advantages and disadvantages of using XLSB compared to XLSM format. All in all, if you have a large and complex Excel file, you’d better to store it in XLSB format.

The advantages of XLSB over XLSM:

  • Excel will run the file faster
  • Much Smaller size in comparison with XLSM format

Disadvantage of XLSB format:

  • Not compatible with other Microsoft software such as Openoffice, etc.

 

Combination of INDEX and MATCH functions in Excel

INDEX and MATCH functions can be used together to create flexible and powerful formula in Excel. As said before, the INDEX function can return the value at the juncture of a given row and column. Now instead of manually setting value for the column and row, we can get help from the result of MATCH function to solve the problems with providing formula. By combining INDEX and MATCH functions in Excel, we may search for the lookup value in a column or a row and return a value in the corresponding cell. In the following we will learn more about the applications of these two functions in Excel. Consider the table below to understand easier:

1

If we want to find the name of the product according to its number 1398, for instance, we may use a VLOOKUP function formula like VLOOKUP(1398,A:B,2,FALSE).

But now consider the table below:

2

Here we do not use VLOOKUP function, but rather a combination of INDEX and MATCH functions. Because we cannot set the third parameter as -1 in VLOOKUP function. Therefore, we use INDEX MATCH function as follows:

  • First we write an INDEX function and determine its first parameter as A: A, since we are going to find the name of the product which is located in Column A.
  • we could give the row value to INDEX function before. The row value for the number 1398 is equal to 4. But now we want to find the row value automatically by the Excel rather than manually. So we do this using a MATCH function. Function MATCH(1398,B:B,0) will find the number 1398 in Column B and will return the corresponding row value which is equal to 4.
  • Then we assemble the two functions and get this formula:

INDEX(A:A,MATCH(1398,B:B,0))

Now we can change the number 1398 with another cell or number to find the corresponding product name.

 

How to create a Macro in Microsoft Office and use it?

You might have experienced being required to do repeatedly some tasks in Excel. Examples being tasks such as copying a certain part of a software’s output in another sheet, etc. Doing something repeatedly usually takes too much time and is also boring. You may get rid of this work using macro tool in Excel. A macro is a piece of computer code or a set of actions that is written for Excel using the Visual Basic for Applications (VBA) programming language. By macro you can automate your tasks so that you may not need to repeat them manually. In the following you will learn how to create a macro in Excel.

Here I will show you how to create a simple macro using Excel’s Macro recording functionality. Once you have recorded the macro, you can repeat the set of actions as many times as you like, by simply running the recorded macro in Excel. We can also create macros using VBA which assists us in writing more complex and advanced macros. Creating macro by VBA requires more knowledge, so we will discuss it separately in future. Now let’s create a simple macro by Excel. Suppose that the output given below pertains to our software:

1

As you see the output does not follow an appropriate format. According to the output, if we want to provide a daily report about the product “mouse”, for instance, in a format like below:

2

then once we get our output every day, we have to categorize our data in accordance with the types of “mouse” product, choose appropriate font for the texts of the output, and highlight the first line in Bold type, etc. which takes a long time and is too boring. Now we want to automate this task by creating a macro in Excel. In order to create a macro, you need to start the recording process. To do this, click on Macros menu which is located in the View Tab, and then select the option “record macro”.

3

Then you will be presented with an option box by which you can modify settings related to the macro which you are going to create. The setting options include:

4

Macro name

you can choose a name for your macro.

Shortcut key

you can assign a keyboard shortcut to easily run the macro. For example, you can write the letter U, so that you will run the macro by Ctrl+U.

Store macro in

By this option you can determine where to store the macro. If you select this workbook, the macro will be stored in current workbook. Remember that you should store the macro in xlsm extension. By Choosing new workbook option, a new workbook will be created and the macro will be stored in it.

If you choose personal macro workbook, the macro will be stored in a personal file named as personal.xlsb. whenever you open Excel, the file will be opened as a hidden file. Therefore, if you want to have your macro available in all workbooks, you should store it in personal macro workbook.

Description

Here you can enter a description for your macro

Once you have modified all the settings, click Ok to start recording. As soon as your macro starts to record, every action that you perform will be captured. Then you will perform the actions that you want to record in your macro. For example, here we change the font of the text, highlight the first line in Bold type, and categorize the types of “mouse” products.

When you have completed the actions, you can stop the macro recording by clicking on the stop button which is located in macros menu.

5

Now your macro will be stored in the location where you had assigned before. From now on, you can click on View macros to run these actions automatically. having clicked on View macros option, a list of created macros will be shown, and you should choose your macro and select run.

6

7

This way you can repeat the set of actions as many times as you like, by simply running the recorded macro. This is much more efficient than repeating the same set of actions manually each time. However, macro has more applications in Excel which I will explain them separately in future.

 

What’s New in Microsoft Office Excel 2016?

Hi. As you probably know, the newest version of Microsoft Office, Microsoft Office 2016 was officially released a few days ago. Similar to the previous versions, this version includes many new features and enhancements. Excel, as a product of Office package, has always included a number of new capabilities and features in the latest version. We will get to know some of the most important new features of Excel 2016 in the following:

  • New Charts

Six new charts have been added to Excel 2016:

Waterfall chart:

waterfall

Histogram:

Histogram

Pareto chart:

Pareto Continue reading What’s New in Microsoft Office Excel 2016?

How to STDEV on different ranges using Excel formulas?

Consider an Excel file in which we have different group of numbers and we want to calculate STDEV for each rang using a formula. The process is as follow,

Group of numbers in an Excel sheet

 

=IF(A1="",STDEVA(INDIRECT(CELL("address",A2)&":A"&SMALL(IF(A2:A200="",ROW(A2:A200),""),1)-1)),"")

Note: This is an array formula and should be inserted with Ctrl+Shift+Enter
STDEV (Sample Excel File)

Inserting blank rows between the rows of a database

This blog post is about inserting rows between the rows of a database. Selecting rows one by one and insert row is very exhausting especially when there are so many rows to do so. A better solution is as follow,

Consider the database shown in the image below.

A sample Database in an Excel Spreadsheet

Step 1:

Insert a column to the left of the database and type numbers from 1 to the last row number of database-1. (here we have data up to row 16, so I will type number from 1 to 15)

Inserting a column to the database

 

Step 2:

Now copy the group of numbers under each other, how many times? Depends on the number of blank row you want. For example, I want to insert 2 blank rows between each row. So I copy and paste these numbers twice under each other:

Inserting group of numbers

Step 3:

Now select whole database and go to Data>sort and do as shown in below figure:

Data & Sort Database

After clicking OK you will have 2 blank rows between your database rows. Then you can delete the helper column (column A)

Database Blank Rows

And after deleting the helper column (column A) we will have this:

After deleting the database helper column

Additionally, for inserting a column between columns in a database, do similar steps above and set the option in sort to left to right, then do as shown above.

Inserting column between columns in a database

\\

Connecting a Shape or WordArt to an Excel Cell

For connecting a shape to a cell in an Excel file, follow these steps.

Step 1

Insert a word art or shape

A sample WordArt in Excel

step 2:

Delete the text in the word art and type ‘=’ in the Excel formula bar. Then click a cell.

Placing a WordArt in an Excel Spreadsheet

step 3:

Now whatever you type in the cell, you will see it in shape or word art

bSuperior System as an Excel WordArt

 

As you see, it’s bSuperior System!