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.

Published by

bSuperior System Ltd Experts

We are experts in customizing Excel files and VBA programming. We create Excel files for specific needs. We develop VBA programs to automate tasks and execute processes automatically . We are based in Vancouver BC, Canada.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.