Updating the ‘Display in Lookups’ selections on the Account Maintenance window

 

You may have noticed that Integration Manager does not let you choose the individual selections for the ‘Display in Lookups’ field on the Account Maintenance window. It’s either all on, or all off. I used to use Table import to set the flags, but there is a much better way.

My thanks goes out to Tim Hickey of ConexusSG for showing me how to update this field using SQL. So much easier than table import.

Since the field GL00100.DSPLKUPS is a binary field, Tim converted the values for the multi-select list box to binary. He then used the corresponding value in a SQL UPDATE statement. Now, all of his clients (and you) can easily take advantage of this power feature in General Ledger.

For example, if you wanted to set the Payroll accounts to display only in the payroll module lookups (option 9), you would write something like this:

UPDATE GL00100
SET DSPLKUPS = 0X8
WHERE ACCATNUM in (39,36)

Take another look at your chart of accounts, and use this feature to help speed up account selection. The table below has every option you can choose for the ‘Display in Lookups’ field, along with the correct SQL value.

Enjoy!

Option	Image	SQL	Binary
1		0X0	0000
2		0X1	1000
3		0X2	0100
4		0X3	1100
5		0X4	0010
6		0X5	1010
7		0X6	0110
8		0X7	1110
9		0X8	0001
10		0X9	1001
11		0XA	0101
12		0XB	1101
13		0XC	0011
14		0XD	1011
15		0XE	0111
16		0XF	1111