Determine Company_Options from DYNAMICS.dbo.SY01500 (Reading a Multi-Select List Box)
A recent post on DynDeveloper.com caught my eye as I have faced a similar problem before and had to work quite hard to get the correct answer.
The post was titled: Determine Company_Options from DYNAMICS.dbo.SY01500 and asked how to figure out how to interpret the binary(4) field.
I realised that the solution for this issue would be worth posting as it explains how to read any Dexterity Multi-Select List Box datatype.
Boring Theory Bit
In Dexterity, a Multi-Select List Box is a control type which allows up to 32 string items to be displayed and individually selected in a list box. The items can be added as Static Text on the datatype, or programmatically with the add item command. Clicking on the field selects a single item, using Control-Click can select or deselect additional items. You can also use Shift-Click to select multiple items. The data is stored in a 32 bit integer field with each of the 32 bits representing one item. The bit will be set (true or 1) for a selected item and cleared (false or 0) for a non-selected item. In Dexterity, the field can be manipulated using bitwise functions or it can be treated as long (32 bit numeric) datatype.
An example of a Multi-Select List Box is the Include In Lookups field on the Account Maintenance window (see below). This value is stored Display in Lookups (DSPLKUPS) field in the GL_Account_MSTR (GL00100) table.
Account Maintenance window.
At the SQL level, a Multi-Select List Box is stored as a binary(4) providing 32 bits to store the status of each item.
The issue that makes it difficult to work with the field at the SQL level is that 4 bytes are not stored in the same order at the SQL level as they are at the Dexterity level. The following table should help explain how the bytes and bits are mapped:
Dexterity Byte Dexterity Bits SQL Byte SQL Bits 1 1 - 8 4 24 - 31 2 9 - 16 3 16 - 23 3 17 - 24 2 8 - 15 4 25 - 32 1 0 - 7
Note: In Dexterity indices start from 1, so the bits are numbered 1 - 32, I have numbered the SQL bits as 0 - 31
The Problem
So now we come back to the Company Options field available from the Company Setup Options window (Microsoft Dynamics GP >> Tools >> Setup >> Company >> Company >> Options).
Company Setup Options window
While the Company Options field looks like a scrolling window (grid), it is actually stored as a Multi-Select List Box, this allows up to 32 binary/checkbox settings to be stored in a single field without requiring an additional table. For the user interface of this window, the values in the Multi-Select List Box are read into a temporary table and displayed in a scrolling window. The values in the scrolling window are written back to the Multi-Select List Box when the checkboxes are clicked.
Note: When the options from the Multi-Select List Box are copied into the temporary table for this window, not all options are transferred depending on the country installed. So you cannot assume that the position of the line in the scrolling window maps to the item in the Company Options field.
The best way to obtain the items for Company Options Multi-Select List Box is to open the DYNAMICS.DIC with Dexterity. Then in the Resource Explorer under Base, open Fields and double click on the 'Company Options' field to open the Field Definition window.
You can then see the Data Type used. Click Open to open the ML_Company_Options data type and then click on the elipses button to see the Static Text values.
So now we know what the meaning for each item of the Company Options field are, but how can I read the SQL field to know which of the individual options are selected.
The Solution
Firstly, let me provide a list of what the values for the Company Options items are:
Option No. Company Option 1 Use Shipping Method when Selecting Default Tax Schedule 2 Calculate Terms Discount Before Taxes 3 Enable Intrastat Tracking 4 Separate Payment Distributions 5 Merge Trade Discount and Markdown Distributions in Sales 6 Merge Trade Discount Distributions in Purchasing 7 Calculate Tax Rebates 8 Enable Posting Numbers in General Ledger 9 Allow Negative Debits and Credits in General Ledger 10 Enable GST for Australia 11 Enable Tax Date 12 Enable Shipping Document in Sales Order Processing 13 Enable Reverse Charge Taxes 14 Calculate Taxes in General Ledger 15 Allow Summary-Level Tax Edits 16 Require Tax Detail Totals to Match the Pre-Tax Amount 17 Specify Tax Details for Automatic Tax Calculation 18 Enable VAT Return 19 Enable EU Transaction Tracking 20 Enable DDR and European Electronic Funds Transfer 21 Enable Payables EFT 22 Enable Canadian Tax Detail 23 Exclude Tax in Inventory Cost when Tax is Included In Price
So now comes the fun part....
How can I read the binary(4) field and separate this into 32 boolean fields, and while I am at it, create an integer value which matches the numeric value that Dexterity would see for the same field?
Well, the answers are all in the SQL Script below (which is also attached to the bottom of this post).
Display Company Options script
/* Break Down Company Options Multi-Select List Box into individual options. */
/* Script by David Musgrave, Created 01-Aug-2012, Last Modified: 01-Aug-2012 */
select INTERID, CMPANYID, CMPNYNAM, Company_Options
, CASE WHEN Company_Options & 65536 * 256 > 0 THEN 1 ELSE 0 END -- Bit 24
+ CASE WHEN Company_Options & 65536 * 512 > 0 THEN 2 ELSE 0 END -- Bit 25
+ CASE WHEN Company_Options & 65536 * 1024 > 0 THEN 4 ELSE 0 END -- Bit 26
+ CASE WHEN Company_Options & 65536 * 2048 > 0 THEN 8 ELSE 0 END -- Bit 27
+ CASE WHEN Company_Options & 65536 * 4096 > 0 THEN 16 ELSE 0 END -- Bit 28
+ CASE WHEN Company_Options & 65536 * 8192 > 0 THEN 32 ELSE 0 END -- Bit 29
+ CASE WHEN Company_Options & 65536 * 16384 > 0 THEN 64 ELSE 0 END -- Bit 30
+ CASE WHEN Company_Options < 0 /* Negative */ THEN 128 ELSE 0 END -- Bit 31
+ CASE WHEN Company_Options & 65536 * 1 > 0 THEN 256 ELSE 0 END -- Bit 16
+ CASE WHEN Company_Options & 65536 * 2 > 0 THEN 512 ELSE 0 END -- Bit 17
+ CASE WHEN Company_Options & 65536 * 4 > 0 THEN 1024 ELSE 0 END -- Bit 18
+ CASE WHEN Company_Options & 65536 * 8 > 0 THEN 2048 ELSE 0 END -- Bit 19
+ CASE WHEN Company_Options & 65536 * 16 > 0 THEN 4096 ELSE 0 END -- Bit 20
+ CASE WHEN Company_Options & 65536 * 32 > 0 THEN 8192 ELSE 0 END -- Bit 21
+ CASE WHEN Company_Options & 65536 * 64 > 0 THEN 16384 ELSE 0 END -- Bit 22
+ CASE WHEN Company_Options & 65536 * 128 > 0 THEN 32768 ELSE 0 END -- Bit 23
+ CASE WHEN Company_Options % 65536 & 256 > 0 THEN 65536 * 1 ELSE 0 END -- Bit 8
+ CASE WHEN Company_Options % 65536 & 512 > 0 THEN 65536 * 2 ELSE 0 END -- Bit 9
+ CASE WHEN Company_Options % 65536 & 1024 > 0 THEN 65536 * 4 ELSE 0 END -- Bit 10
+ CASE WHEN Company_Options % 65536 & 2048 > 0 THEN 65536 * 8 ELSE 0 END -- Bit 11
+ CASE WHEN Company_Options % 65536 & 4096 > 0 THEN 65536 * 16 ELSE 0 END -- Bit 12
+ CASE WHEN Company_Options % 65536 & 8192 > 0 THEN 65536 * 32 ELSE 0 END -- Bit 13
+ CASE WHEN Company_Options % 65536 & 16384 > 0 THEN 65536 * 64 ELSE 0 END -- Bit 14
+ CASE WHEN Company_Options % 65536 & 32768 > 0 THEN 65536 * 128 ELSE 0 END -- Bit 15
+ CASE WHEN Company_Options % 65536 & 1 > 0 THEN 65536 * 256 ELSE 0 END -- Bit 0
+ CASE WHEN Company_Options % 65536 & 2 > 0 THEN 65536 * 512 ELSE 0 END -- Bit 1
+ CASE WHEN Company_Options % 65536 & 4 > 0 THEN 65536 * 1024 ELSE 0 END -- Bit 2
+ CASE WHEN Company_Options % 65536 & 8 > 0 THEN 65536 * 2048 ELSE 0 END -- Bit 3
+ CASE WHEN Company_Options % 65536 & 16 > 0 THEN 65536 * 4096 ELSE 0 END -- Bit 4
+ CASE WHEN Company_Options % 65536 & 32 > 0 THEN 65536 * 8192 ELSE 0 END -- Bit 5
+ CASE WHEN Company_Options % 65536 & 64 > 0 THEN 65536 * 16384 ELSE 0 END -- Bit 6
+ CASE WHEN Company_Options % 65536 & 128 > 0 THEN -2147483648 ELSE 0 END AS MLSB_Value -- Bit 7
, CASE WHEN Company_Options & 65536 * 256 > 0 THEN 1 ELSE 0 END AS Opt01 -- Bit 24
, CASE WHEN Company_Options & 65536 * 512 > 0 THEN 1 ELSE 0 END AS Opt02 -- Bit 25
, CASE WHEN Company_Options & 65536 * 1024 > 0 THEN 1 ELSE 0 END AS Opt03 -- Bit 26
, CASE WHEN Company_Options & 65536 * 2048 > 0 THEN 1 ELSE 0 END AS Opt04 -- Bit 27
, CASE WHEN Company_Options & 65536 * 4096 > 0 THEN 1 ELSE 0 END AS Opt05 -- Bit 28
, CASE WHEN Company_Options & 65536 * 8192 > 0 THEN 1 ELSE 0 END AS Opt06 -- Bit 29
, CASE WHEN Company_Options & 65536 * 16384 > 0 THEN 1 ELSE 0 END AS Opt07 -- Bit 30
, CASE WHEN Company_Options < 0 /* Negative */ THEN 1 ELSE 0 END AS Opt08 -- Bit 31
, CASE WHEN Company_Options & 65536 * 1 > 0 THEN 1 ELSE 0 END AS Opt09 -- Bit 16
, CASE WHEN Company_Options & 65536 * 2 > 0 THEN 1 ELSE 0 END AS Opt10 -- Bit 17
, CASE WHEN Company_Options & 65536 * 4 > 0 THEN 1 ELSE 0 END AS Opt11 -- Bit 18
, CASE WHEN Company_Options & 65536 * 8 > 0 THEN 1 ELSE 0 END AS Opt12 -- Bit 19
, CASE WHEN Company_Options & 65536 * 16 > 0 THEN 1 ELSE 0 END AS Opt13 -- Bit 20
, CASE WHEN Company_Options & 65536 * 32 > 0 THEN 1 ELSE 0 END AS Opt14 -- Bit 21
, CASE WHEN Company_Options & 65536 * 64 > 0 THEN 1 ELSE 0 END AS Opt15 -- Bit 22
, CASE WHEN Company_Options & 65536 * 128 > 0 THEN 1 ELSE 0 END AS Opt16 -- Bit 23
, CASE WHEN Company_Options % 65536 & 256 > 0 THEN 1 ELSE 0 END AS Opt17 -- Bit 8
, CASE WHEN Company_Options % 65536 & 512 > 0 THEN 1 ELSE 0 END AS Opt18 -- Bit 9
, CASE WHEN Company_Options % 65536 & 1024 > 0 THEN 1 ELSE 0 END AS Opt19 -- Bit 10
, CASE WHEN Company_Options % 65536 & 2048 > 0 THEN 1 ELSE 0 END AS Opt20 -- Bit 11
, CASE WHEN Company_Options % 65536 & 4096 > 0 THEN 1 ELSE 0 END AS Opt21 -- Bit 12
, CASE WHEN Company_Options % 65536 & 8192 > 0 THEN 1 ELSE 0 END AS Opt22 -- Bit 13
, CASE WHEN Company_Options % 65536 & 16384 > 0 THEN 1 ELSE 0 END AS Opt23 -- Bit 14
, CASE WHEN Company_Options % 65536 & 32768 > 0 THEN 1 ELSE 0 END AS Opt24 -- Bit 15
, CASE WHEN Company_Options % 65536 & 1 > 0 THEN 1 ELSE 0 END AS Opt25 -- Bit 0
, CASE WHEN Company_Options % 65536 & 2 > 0 THEN 1 ELSE 0 END AS Opt26 -- Bit 1
, CASE WHEN Company_Options % 65536 & 4 > 0 THEN 1 ELSE 0 END AS Opt27 -- Bit 2
, CASE WHEN Company_Options % 65536 & 8 > 0 THEN 1 ELSE 0 END AS Opt28 -- Bit 3
, CASE WHEN Company_Options % 65536 & 16 > 0 THEN 1 ELSE 0 END AS Opt29 -- Bit 4
, CASE WHEN Company_Options % 65536 & 32 > 0 THEN 1 ELSE 0 END AS Opt30 -- Bit 5
, CASE WHEN Company_Options % 65536 & 64 > 0 THEN 1 ELSE 0 END AS Opt31 -- Bit 6
, CASE WHEN Company_Options % 65536 & 128 > 0 THEN 1 ELSE 0 END AS Opt32 -- Bit 7
from DYNAMICS..SY01500
/*
1 Use Shipping Method when Selecting Default Tax Schedule
2 Calculate Terms Discount Before Taxes
3 Enable Intrastat Tracking
4 Separate Payment Distributions
5 Merge Trade Discount and Markdown Distributions in Sales
6 Merge Trade Discount Distributions in Purchasing
7 Calculate Tax Rebates
8 Enable Posting Numbers in General Ledger
9 Allow Negative Debits and Credits in General Ledger
10 Enable GST for Australia
11 Enable Tax Date
12 Enable Shipping Document in Sales Order Processing
13 Enable Reverse Charge Taxes
14 Calculate Taxes in General Ledger
15 Allow Summary-Level Tax Edits
16 Require Tax Detail Totals to Match the Pre-Tax Amount
17 Specify Tax Details for Automatic Tax Calculation
18 Enable VAT Return
19 Enable EU Transaction Tracking
20 Enable DDR and European Electronic Funds Transfer
21 Enable Payables EFT
22 Enable Canadian Tax Detail
23 Exclude Tax in Inventory Cost when Tax is Included In Price
*/
/* Copyright © Microsoft Corporation. All Rights Reserved. */
/* This code released under the terms of the */
/* Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.) */
Note: You will notice some special handling of certain numbers in the script this is because the integer value in both Dexterity and SQL Server are signed integers and the use the most significant bit as a negative indicator rather than a value.
If you are looking to just check whether a single item is selected and don't mind some procedural style SQL coding, then you can use the method described in Knowledge Base (KB) article 867124.
Hope you found this information and script useful.
David