Scripting Conditional Formatting

[PatrickDes]

Hello everyone,

I have a Preference table with status options. The user can create a new status and through a script can assign a color (see attachment). The calculation is the following: 

Allow user abort [off]

Set error capture [on]

Set variable [$start ;  value: Get ( ActiveSelectionStart )]

Set variable [$end ;  value: Get ( ActiveSelectionStart ) + Get ( ActiveSelectionSize ) -1 ]

Insert Calculated Result [ TextColor ( Middle ( Get ( ActiveFieldContents ) ;  Get ( ActiveSelectionStart ) ; Get ( ActiveSelectionSize) ) ; Get ( ScriptParameter ) ) ]

Set Selection [ StartPosition : $start ; End Position : Send$ ]

The ScriptParameter has the color values in RGB: RGB ( 237; 41; 190).

The Status field is pulled into a Value List for the Product table. Is it possible, once the status is used in the Product table , to convert the text color into a conditional formatting? Is my approach wrong? I am trying to give the user some flexibility.

Thank you for guiding me.

[comment]

Not sure I understand what exactly is your question. The colors used in conditional formatting must be preset in advance. They cannot be calculated or set by script. 

If - as it seems from the screenshots - you want to allow users to pick a color from a fixed selection of preset colors and assign it to a product code, you could store the selection in your preference table as a simple number (from 1 to 8, in your example). Then you can define 8 rules for your conditional formatting along the lines of:

Preferences::ProductCodeColorNumber = 1

and assign one of the preset colors to each rule.

Note that this requires a relationship between Products and Preferences matching Status to ProductCode.

 

Edited March 28, 2020 by comment

[PatrickDes]

Thank you Comment,

Sorry, I lost you at "store a selection in your preference table". As per now, each color dot on the Preference table is a button. 

Do I need to change that and create a number field named "CodeColorNumber" with a repetition of 8 or maybe create 8 number fields, each one with a different value? How could I assign a visual cue to a number field?

For the second part I understand the relationships between Products and Preferences with the matching Status - Color Code. 

Thank you

[comment]

34 minutes ago, PatrickDes said:

As per now, each color dot on the Preference table is a button. 

it would still be a button. But it would simply do:

Set Field [ Preferences::ProductCodeColorNumber ; 1 ]

or another number between 1 and 8.

 

34 minutes ago, PatrickDes said:

Do I need to change that and create a number field named "CodeColorNumber" with a repetition of 8 or maybe create 8 number fields, each one with a different value?

No. Each product code stores a single color number, so you only need a simple Number field, with no repetitions.

 

34 minutes ago, PatrickDes said:

How could I assign a visual cue to a number field?

The number field needs no visual cues. It doesn't even need to be shown. You can apply conditional formatting to the ProductCode field, using the same rules and same colors as for the Status field in Products.

--
Note: This is all assuming you want to use conditional formatting. If you prefer, you could store the RGB values in the Preferences table and apply them by auto-entering a calculated value into the Status field using TextColor(). But then changing the colors in Preferences becomes more difficult to apply to existing records in Products.

 

Edited March 28, 2020 by comment

[PatrickDes]

Ok, got it! Thank you so much Comment.

Cheers,

 

P.