Storing account numbers

You should avoid using the Account Number field in your application’s tables. Because the user sets the length of the account number at installation, account numbers will vary in length from site to site. If you need to store account number information, use the account number’s index instead.

The Account Index field uniquely identifies each account. This index provides a reference that you can use to retrieve and display the actual account number, regardless of its format. There are two primary tables that store account numbers and their corresponding indexes:

• The GL_Account_Index_MSTR table stores all account numbers and their associated indexes.
• The GL_Account_MSTR table stores account numbers, their indexes and all additional information about the account number, such as the account description.

In Microsoft Dynamics GP, the account number indexes are consecutive integers that start at 1. When an account number is deleted, its index is no longer used. When a new account is added, an index for the account is reserved at the end of the index sequence.

Example: Using the account index

This example uses a third-party table for tracking project information, and assigns an account number for each project. The following table shows the layout of the third-party table (IG_Project_MSTR) used in the example. The table doesn’t store the account number, but instead stores the account index using the Account Index field.

The third-party form uses the GL_Account_MSTR table, as well as the third-party’s IG_Project_MSTR table. The following illustration shows the Project Accounts window in the third-party form.

The following steps explain how to display account information using the account index.

1. Retrieve an account number.

Retrieve an account number using the Account_Lookup form. The sample form opens the Account Number lookup form by attaching the following change script to the Account Number field’s lookup button:

open form Account_Lookup return to 'Account Number';
{Set the sort list to Account Description.}
'Sort By' of window Account_Lookup = 1;
{Set an invisible field for the account type. The scrolling
 window will be filled with accounts of this type.}
'(L) Account Type1' of window Account_Lookup of form  Account_Lookup = UNIT_ACCT;
{Run the sort list change script to fill the scrolling window.}
run script 'Sort By' of window Account_Lookup of form Account_Lookup;

2. Retrieve the account number index.

Using the account number retrieved in step 1, retrieve the associated account number index and description. The sample form sets the value of an invisible account index field and the account description using the following change script for the Account Number field:

'Account Number' of table GL_Account_MSTR = 'Account Number';
{Get the Account Master table by key 6. This key uses  
 only the Account Number field.}
get table GL_Account_MSTR by GL_Account_MSTR_Key6;
if err()=OKAY then
	{Retrieve the index and description.}
	'Account Index' = 'Account Index' of table GL_Account_MSTR;
	'Account Description'='Account Description' of table GL_Account_MSTR;
end if;

3. Save the account index with the third-party record.

When the sample form saves the project record, it also saves the account index with the project record:

if required(form IG_Project_Accounts) then
	copy to table IG_Project_MSTR;
	save table IG_Project_MSTR;
	restart form;
else
	warning "Not all required fields have been entered.";
end if;

4. Retrieve the third-party record.

To display the appropriate account with the third-party record, set the account index in the Account Master table to the account index stored with the third-party record, then display the associated account number:

'Project Number' of table IG_Project_MSTR = 'Project Number';
release table IG_Project_MSTR;
change table IG_Project_MSTR by IG_Project_MSTR_Key1;
if err()=OKAY then
	copy from table IG_Project_MSTR;
	{Set the account index in the Account Master table to 
	the index stored with the project table.}
	'Account Index' of table GL_Account_MSTR = 'Account Index';
	get table GL_Account_MSTR by GL_Account_MSTR_Key1;
	if err()=OKAY then
		{Display the account number and description from the
		 Account Master table.}
		'Account Number' = 'Account Number' of table GL_Account_MSTR;
		'Account Description' = 'Account Description' of table GL_Account_MSTR;
	end if;
	clear changes form IG_Project_Accounts;
end if;
lock 'Project Number';

Documentation Feedback

---