GP-DYNAMICS
 
 Custom Programming
Home
Dynamics TechBrief
MSSQL Deepsearch

How these can help you

How to purchase

Free Dynamics GP Sql Scripts

 



Product Technical Description


 

gp-dynamics-dissectionOur product is a 45-page technical documentation of five selected areas of Dynamics GP. Specifically, these are the areas of Customer records, Inventories, Pricelists & Pricelevels, Order Documents and Invoice Documents.This documentation is not about how to use the dynamics program. Instead, this is a highly technical description and presentation of information on how to create custom program extensions for Dynamics GP. It gives you a clear layout and information of the corresponding tables, columns and specific business rules in dealing with them.

 

Just so you'll have a better idea of how interesting our unique technical brief can give you, here is a concised description of the contents of each chapter:

 

 

MODULE 1. Working with Customer Records

This section will show you the common tables involved in maintaining customer records like the Customer Master file, Master Address Files, Internet or web address table, etc. Knowing the details of these tables and how they are interrelated will help you as a programmer to create programs that can pull customized set of information regarding customers.


Useable Queries- Example of queries that you can copy verbatim that you use in accessing customer information directly from Dynamics database.

 

Example: A ready-made sql querythat will return all customer records and all their corresponding address entries that you can use for generating mail-merge letter envelopes, etc.


This section also presents a detailed design schema of the tables, and their index keys. You will have all the columns and types that you need so you will never have to guess or wonder where a certain table property is located. The lists include a descriptive name of each column, the physical name of each column, plus of course the data types and their corresponding sizes.

 

Also, this section shows in detail the required static values of each column where a finite number of values are expected for a column. Knowing all the possible values for those columns is very important when developing custom programs because it ensures that your program will have a consistent match with the way Dynamics Program handle its records. You wouldn't want to create a record entry that Dynamics will not understand or they can potentially crash the main Dynamics Program.

 

Here's just one example:

 

There are exactly 7 different values possible for the Statement Cycle column RM00101.STMTCYCL, any other values will be invalid.

 

These fixed values for Statement Cycle - [STMTCYCL] are:

  • 1 - No Statement
  • 2 - Weekly
  • 3 - BiWeekly
  • 4 - Semimonthly
  • 5 - Monthly
  • 6 - BiMonthly
  • 7 - Quarterly

The rest of this section will show you all other columns that require a finite list of values.

 

 

Module 2. Working with Inventories


This section will show you the common tables involved in maintaining inventory records like the Inventory Master table, and the table that holds quantities for each different company locations. You will also be able to distinguish the specific key values that tell the inventory types apart whether they are Sales Inventories, Kits, Services, etc.
Useable Queries- Example of queries that you can copy verbatim that you use in accessing customer information directly from Dynamics database.

 

Example: A ready-made sql query that you can use to return all inventory records with descriptions, standard cost and current cost.


Design Schema of tables involved - This section presents a detailed information about the tables, all their index keys, all column names, column data types & column sizes.This section also shows in detail the required static values of each column where a finite number of values are expected for a column. Knowing all the possible values for those columns is very important when developing custom programs because it ensures that your program will have a consistent match with the way Dynamics handle its records.

 

Example: There are exactly 6 different values possible for the inventory Item Type, column IV00101.ITEMTYPE, any other values will be invalid.

 

These fixed values for Item Type [ ITEMTYPE ] are:

  • 1 - Sales Inventory
  • 2 - Disconnected
  • 3 - Kit
  • 4 - Misc Charges
  • 5 - Services
  • 6 - Flat Fee
 

 

MODULE 3. Working with Customer PriceLevels and Custom Pricelists


Common tables involved in programming around Pricelevels and Pricelists like the main pricelevel table (IV40800), Pricelist Table (IV00108) and its corresponding pricelist options table (IV00107). This section will give you a better understanding of how Dynamics use the combination of columns to come up with a custom pricelist for a particular customer based on custom pricelists, etc, and how you can create your own program for maintaining pricelevels and pricelists.


Useable Queries- Example of queries that you can copy verbatim that you use in accessing price schedule information directly from Dynamics database.

 

Example: A query to list all custom pricelist of a particular customer.


Design Schema of tables involved - This section presents a detailed information about the tables, all their index keys, all column names, column data types & column sizes.This section also shows in detail the required static values of each column where a finite number of values are expected for a column. Knowing all the possible values for those columns is very important when developing custom programs because it ensures that your program will have a consistent match with the way Dynamics handle its records.

 

Here's just one example: : There are exactly 3 different values possible for the Unit of Measure Sales, column IV00107.UMSLSOPT, any other values will be invalid.

 

These fixed values for U of M Sales Options [ UMSLSOPT ] are:

  • 1 - Not Available
  • 2 - Whole
  • 3 - Whole and Fractional

The rest of this section will show you all other columns that require a finite list of values.

 

 

MODULE 4. Working with CURRENT or WORK ORDER DOCUMENTS


Will show you the main tables involved when developing a custom program for current work orders, with emphasis on ORDER documents. You will be able to learn how to distinguish order documents from the rest of other types of documents (quotes, orders, invoices, returns & backorders) and identify what other tables and columns are being used to come up with an order document report.

 

You will be surprised to know that to generate an order document, you will need to access at least 10 different tables. These 10 different tables will comprise information like main order information such as (but not limited to) Order Number and Date, Billing Address, Shipping Address, Order document notes, per item comments, Customer Item Numbers, etc. It also shows check tables that tell you when an order is a valid one, like the tables that contain the 'holds' and 'lock' records. All these important information will be presented in details so you can avoid common mistakes or traps when developing your own custom programs.


Just like the other sections, this part will present in details all the table schema that details all column names, indexes, column types and sizes.


This section also lists all possible values for columns that require a finite set of values.

Here's a free quick example: Column SOP10100.SOPYTPE has 5 specific values which are: "1", "2", "3", "4" and "5". They correspond to "Quotes", "Orders", "Invoices", "Returns", and "Back Orders" documents respectively.

 

The rest of this section will show you all other columns that require a finite list of values.

 

 

MODULE 5. Working with INVOICE DOCUMENTS


Will show you the main tables involved when developing a custom program concerns historical documents, with emphasis on INVOICE documents.

 

You will be able to learn how to distinguish invoice documents from the rest of other types of documents (quotes, orders, invoices, returns & backorders) and identify what other tables and columns are being used to come up with an invoice document report.

 

Just like an order, you might be surprised to know that to generate an in invoice document, you will need to access at least 10 different tables. These 10 different tables will comprise information like main order information such as (but not limited to) Invoice Number and Date, Billing Address, Shipping Address, Invoice document notes, per item comments, Customer Item Numbers, etc.

 

It also shows check tables that tell you when an invoice is a valid one, like the tables that contain the 'holds' and 'lock' records. All these important information will be presented in details so you can avoid common mistakes or traps when developing your own custom programs.


Just like the other sections, this part will present in details all the table schema that details all column names, indexes, column types and sizes.

This section also lists all possible values for columns that require a finite set of values. Example: Column SOP30200.VOIDSTTS has 2 different values to tell Dynamics when an invoice is void or not. A value of 1 means it's void and therefore should not be processed, otherwise it is assigned a value of "0" - meaning it's a valid invoice.

 

The rest of this section will show you all other columns that require a finite list of values.

 

 

Cost of the entire technical brief: $29.95

 

Format: PDF file

 

Get this unique documentation now and start learning in minutes what it takes other developers a few weeks or even months to discover.

 

GP Technical Brief plus MSSQL Deep-search program (discounted): $39.95

 

Format: Zipped installer file

 

TechBrief + DeepSearch Recommended

Trademarks: Microsoft™, Microsoft Dynamics™ and/or Microsoft logos, products, and services referenced in this site are either trademarks or registered trademarks of Microsoft Corporation.

Disclaimer: This product is not officially endorsed by Microsoft. Furthermore, this website does not imply representation of Dynamics™ or relationship with Microsoft in any form.