Tuesday, April 16, 2013

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

clip_image001[4]

0X0

0000

2

clip_image002[4]

0X1

1000

3

clip_image003[4]

0X2

0100

4

clip_image004[4]

0X3

1100

5

clip_image005[4]

0X4

0010

6

clip_image006[4]

0X5

1010

7

clip_image007[4]

0X6

0110

8

clip_image008[4]

0X7

1110

9

clip_image009[4]

0X8

0001

10

clip_image010[4]

0X9

1001

11

clip_image011[4]

0XA

0101

12

clip_image012[4]

0XB

1101

13

clip_image013[4]

0XC

0011

14

clip_image014[4]

0XD

1011

15

clip_image015[4]

0XE

0111

16

clip_image016[4]

0XF

1111

2 comments:

David Musgrave said...

Hi Leslie

Have a look at my blog on working with Multi-Select List Boxes.

Determine Company_Options from DYNAMICS.dbo.SY01500 (Reading a Multi-Select List Box)

David

Curtis Beethe said...

Very helpful post.