Circular dependency problem

[Bulubulu]

Hi,

I would like to leave the choice of input mode to the user for some values. I would like the user to be able to switch modes while typing and have the data converted when switching from one mode to another so as not to reset the existing setting.

I have to do this especially for a color picker that leaves the choice of input mode between hexadecimal, RGB and HSL. Or to enter time data either in the form "mm:ss:frames" or in the form "total frames".

But let's take an example with simpler conversions to start with: imagine the user needs to enter a measurement in either meters or centimeters.

I have column [m], column [cm] and Enum column [Mode] allowing both "m" and "cm" values.

I would have liked to use the following formulas on the initial values, but I get a circular dependency :

[cm] :

IFS(
 [Mode]="cm",
 IF(ISNOTBLANK([_THIS]),[_THIS],0),
 
 [Mode]="m",
 [m]*100
)

[m]:

IFS(
 [Mode]="cm",
 [cm]/100,
 
 [Mode]="m",
 IF(ISNOTBLANK([_THIS]),[_THIS],0)
)

 How could I create such a system?

I know that for this example I could get away with just having a [Length] column and a [Mode] column, but the problem is that I would like the value already entered to be converted in real time when we changes mode during input.

This makes more sense for a color picker. Supposing the user wants to change input mode to edit the color, the existing color should not be reset.

[WillowMobileSys]

Represent the value in only a single column, then make use of the [_THISROW_BEFORE] and [_THISROW_AFTER] keywords to handle the conversion/translation when the Mode is switched.  Something like this:

IFS(
    [_THIS] = 0, 0,

    AND([_THISROW_BEFORE].[Mode] = "cm",[_THISROW_AFTER].[Mode] = "m",  [_THIS] / 100,

    AND([_THISROW_BEFORE].[Mode] = "m",[_THISROW_AFTER].[Mode] = "cm", [_THIS] * 100,

    TRUE, [_THIS]
)

 If you are ALSO allowing users to choose a value, remember that on Editing a row, the "Reset on Edit" property must be set for the Initial Value expression to re-fire during the edit.  Also remember, that once a user manually changes a value, that columns Initial Value expression will no longer re-fire.