by jbrohinsky

Setting Form Component Flags in SQL

DISCLAIMER: I am about to talk about editing form components directly in the forms database. Under almost every circumstance, this is a terrible idea. Please do not use this knowledge to do anything stupid. If you try to use this, make absolutely positive that anything you touch does NOT have a scope type of 0. We are not responsible for any damage you do to your system with this technique. Take backups and work in a dev environment!

While working on a tab-order issue, we were looking for a way to set the “No Tab Stop” property to True, globally on the form. That way we could turn it off only for the fields that we wanted to tab through. While looking at the Forms database, we discovered that the “No Tab Stop” property was encoded in a “Flags” integer on the FormComponents01 table.

With a little digging, we discovered that the Flags integer is a binary representation of the possible properties for that component. If you set a component to Required, for instance, it flips the 9th (LSB 0) bit on the Flags integer, adding 512 to the value. So a component with all flags turned off except “Second Splitter Pane” would have a Flags value of 1. If you flip the 9th (required) bit, the Flags value is set to 1000000001, or 513.

So with that information, we realized that we could do an update statement with bitwise operators to set these flags globally. (ONLY WHEN SCOPE IS NOT 0!) So for instance, suppose you create a new form with 20 buttons, and you want to set the buttons to “Disable in Filter-in-Place.” The bit that holds that value is bit 12, or 4096. The component type for buttons is 8. So with the following code, you could set that bit in one shot.

/*
DISCLAIMER: I am about to talk about editing form components directly in the forms database.
Under almost every circumstance, this is a terrible idea. Please do not use this knowledge to
do anything stupid. If you try to use this, make absolutely positive that anything you touch
does NOT have a scope type of 0. We are not responsible for any damage you do to your system
with this technique. Take backups and work in a dev environment!
*/
/*
To set the bit, you will need to OR (SQL operator: |) the existing flags value with a mask set
to the bit you want to flip:
Value: 0010010000001
Mask: 1000000000000
OR___________________
Result: 1010010000001
*/
UPDATE [FormComponents01]
SET [FormComponents01].[Flags] = [FormComponents01].[Flags] | 4096 -- Turn Flag ON
FROM [Forms]
INNER JOIN [FormComponents01]
ON [FormComponents01].[FormID] = [Forms].[ID]
WHERE 1 = 1
AND [Forms].[Name] = 'CustomForm' -- Custom Form Only
AND [Forms].[ScopeType] = 1 -- Site Only
AND [FormComponents01].[Type] = 8; -- Buttons only
/*
Additionally, you can AND (SQL Operator: &) with the inverse (SQL Operator: ~) of
the mask to turn that flag off:
Value: 1010010000001
Mask: 0111111111111
AND__________________
Result: 0010010000001
*/
UPDATE [FormComponents01]
SET [FormComponents01].[Flags] = [FormComponents01].[Flags] & ~4096 -- Turn Flag OFF
FROM [Forms]
INNER JOIN [FormComponents01]
ON [FormComponents01].[FormID] = [Forms].[ID]
WHERE 1 = 1
AND [Forms].[Name] = 'CustomForm' -- Custom Form Only
AND [Forms].[ScopeType] = 1 -- Site Only
AND [FormComponents01].[Type] = 8; -- Buttons only
/*
The way we used this technique was to first flip to "No Tab Stop" and set Tab Order to 0
for all components on a specific Site version of a form. Then we used SyteLine edit mode
to set the tab order only for fields we wanted to tab through. Finally, we used the AND
inverse mask technique to set the "No Tab Stop" flag to false for any components that had
a tab-order > 0. Worked OK so far, but I'm not sure what level of impact that will have
with FormSync. We'll have to test that before we push this to Prod.
I did a little reverse engineering, and this is what I could find for bit values. If
anyone could help find the missing bits, that'd be pretty cool.
*/
--2^0 1 --Second Splitter Pane
--2^1 2 --Value is List Index
--2^2 4 --Password
--2^3 8 --Group
--2^4 16 --Use Default Value on Copy
--2^5 32 --Validate Immediately
--2^6 64 --Self Contained Caption
--2^7 128 --Glue: Right
--2^8 256 --Glue: Bottom
--2^9 512 --Required
--2^10 1024 --Do Not Clear Value on New
--2^11 2048
--2^12 4096 --Disable in Filter-in-Place
--2^13 8192 --Read Only For Existing Records
--2^14 16384 --Read Only For New Records
--2^15 32768 --Enable on Non-Modified Records
--2^16 65536 --Enable on Required Data Supplied
--2^17 131072 --TBD
--2^18 262144
--2^19 524288 --No Tab Stop
--2^20 1048576 --Auto Complete
--2^21 2097152 --Message on Status Bar
--2^22 4194304
--2^23 8388608 --Read-Only for Deleted Records
--2^24 16777216 --Lock Position
--2^25 33554432 --Lock Size
--2^26 67108864 --No Interpret Contents
--2^27 134217728 --Interpret Bound Contents
--2^28 268435456 --Glue: Left
--2^29 536870912 --Glue: Top
--2^30 1073741824
--2^31 2147483648
view raw Bulk Edit Flags.sql hosted with ❤ by GitHub

One thought on “Setting Form Component Flags in SQL”

  1. Friend shared this link with me. You are first place I have ever seen this published so well. I’m missing same ones except for 2^22 only applies to grid columns and toggles if it is a DateCombo or not. Maybe when someone else is bored they can map out flags on the Forms table as well starting with 2^0 being Horizontal Splitter and 2^1 = Vertical Splitter. Also collection has flags but only one I’ve needed was 2^4 = Delete but those are probably easy to map if needed.
    Thank you so much for posting this. I’m going to bookmark it!
    Eric(E++)

    Like

    Reply

Leave a reply to Eric Brisnehan Cancel reply