Switch column between Enum & EnumList based on condition

[Jake_Naude]

Morning…

Please advise if it is possible to switch a column type between Enum & Enum List based on a condition.

UseCase:

If a surveyor surveys one set of asset type the assets need to be Enum and for another set of asset types the assets need to be EnumList.

Something like…?

IFS(

IN([select_asset_type], 
  	LIST("Cabinet", "Distribution Board", "FOSE", "HPR", "LPR")),

		[asset_name] = Enum,

IN([select_asset_type],
  	LIST("CAT6A", "Fibre", "Jumper", "Power")),

		[asset_name] = EnumList

)

Thanks…

[LeventK]

Unfortunately you can’t change a column’s base type with expressions.

[Jake_Naude]

Hi @LeventK,

Thanks for confirming that.

[Suvrutt_Gurjar]

Is it possible to know how you are constructing the [asset_name] list ? Is it through a list yielding expression in valid_if of the column or is it by entering individual values in the column setting pane?

Also if you are constructing it through list yielding expression in valid_if, is it possible that you could construct it through values in the column setting, if you have handful of values in the [asset_name] column?

[Jake_Naude]

Suvrutt_Gurjar:

Is it possible to know how you are constructing the [asset_name] list ?

Hi @Suvrutt_Gurjar,

Thanks for stopping by…

[asset_name] is constructed based on a Valid If expression:

IFS(

[survey_type] = "Node",

ORDERBY(
	SELECT(asset[id],
	IN([id],
	SORT(
	UNIQUE(
	SELECT(rf_design[Source Asset ID],
	AND(
	[lcs] = [_THISROW].[lcs],
    [main_asset_name] = [_THISROW].[select_cabinet_to_survey],
    [source_asset_type] = [_THISROW].[select_asset_type]
    ))

	+SELECT(rf_design[Destination Asset ID],
	AND(
	[lcs] = [_THISROW-2].[lcs],
    [main_asset_name] = [_THISROW-2].[select_cabinet_to_survey],
    [destination_asset_type] = [_THISROW-2].[select_asset_type],
    NOT([destination_asset_type]='Cabinet')
    ))
  	),FALSE
	)),TRUE
	),[asset_name]
	),

[survey_branch_number] = 1,

ORDERBY(
	SELECT(asset[id],
	IN([id],
	SORT(
	UNIQUE(
	SELECT(rf_design[Source Asset ID],
	AND(
	[lcs] = [_THISROW].[lcs],
    [main_asset_name] = [_THISROW].[select_cabinet_to_survey],
    [source_asset_type] = [_THISROW].[select_asset_type]
    ))

+SELECT(rf_design[Destination Asset ID],
	AND(
	[lcs] = [_THISROW-2].[lcs],
    [main_asset_name] = [_THISROW-2].[select_cabinet_to_survey],
    [destination_asset_type] = [_THISROW-2].[select_asset_type],
    NOT([destination_asset_type]='Cabinet')
    ))
 	),FALSE
	)),TRUE
	),[asset_name]
	)

	-SPLIT(survey[planned_asset_id], ",")
	-[branch_A_assets]
	-[branch_B_assets]
	-[branch_C_assets]
	-[branch_D_assets]
	-[branch_E_assets],

[survey_branch_number] > 1,

SPLIT(
	SELECT(
	survey[survey_asset_id],
  	AND(
	[survey_type] = "Cable",
	[LCS] = [_THISROW].[lcs],
	[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
	[select_asset_type] = [_THISROW].[select_asset_type],
    [survey_branch_number] = [_THISROW].[survey_branch_number],
    [branch_info] = "new branch"
    )),
    ","
    )

-SPLIT(
	SELECT(
	survey[planned_asset_id],
  	AND(
	[survey_type] = "Cable",
	[LCS] = [_THISROW].[lcs],
	[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
	[select_asset_type] = [_THISROW].[select_asset_type],
    [originating_branch] = [_THISROW].[survey_branch_number]
    )),
    ","
    )
    
    -[branch_A_assets]
	-[branch_B_assets]
	-[branch_C_assets]
	-[branch_D_assets]
    -[branch_E_assets]
    
    )

Suvrutt_Gurjar:

Also if you are constructing it through list yielding expression in valid_if, is it possible that you could construct it through values in the column setting, if you have handful of values in the [asset_name] column?

Could you please elaborate a little on this as I don’t quite follow?

[Suvrutt_Gurjar]

What I meant was if enumlist for Asset_Name coluld be constructed as example below. However you have very extensive valid_if expression ,so listing enumlist values in following manner may not be possible. If it were possible, I would have suggsted a workaround for what you wanted to achieve.

[Jake_Naude]

Hi @Suvrutt_Gurjar

…extensive valid_if expression ,so listing enumlist values in following manner may not be possible.

OK… Thanks… Understood…

If it were possible, I would have suggsted a workaround for what you wanted to achieve.

Could you possibly share the workaround anyway… For future reference…?

[Suvrutt_Gurjar · Accepted solution]

Even in current setup you may wish to try following that is a bit less elegant as it involves another, so to say “checker” column. You could have another column called say [EnumlistOREnum] with a valid_if expression , something like below

IFS(

IN([select_asset_type], 
  	LIST("Cabinet", "Distribution Board", "FOSE", "HPR", "LPR")),

		COUNT([asset_name])<=1

IN([select_asset_type],
  	LIST("CAT6A", "Fibre", "Jumper", "Power")),

	COUNT([asset_name])>=0

)

Please have [asset_name] as enumlist type column.

So, the instead of valid_if of the [asset_name] column whether it is filled with with one value ( enum behavior) or multiple values ( enumlist behavior) by the user will be checked by the [EnumlistOREnum] valid_if expression mentioned above. You could have a suitable error message in [EnumlistOREnum] column’ svalid_if error message setting,

If the enumlist could be populated in the [asset_name] column itself, the extra checker column was unnecessary as valid_if expression could have been entered in the [asset_name] column’s valid_if itself.

[Jake_Naude]

Hi @Suvrutt_Gurjar,

You’ve done it again! Many thanks for that workaround! Works great!

Based on your advice I created a new text column and called [asset_checker] with the following:

• Show?

AND(
	IN([select_asset_type], 
  	LIST("Cabinet", "Distribution Board", "FOSE", "HPR", "LPR")),
	COUNT([asset_name]) > 1
    )

.

• Valid If

IFS(

IN([select_asset_type], 
  	LIST("Cabinet", "Distribution Board", "FOSE", "HPR", "LPR")),

	COUNT([asset_name]) <= 1,

IN([select_asset_type],
  	LIST("CAT6A", "Fibre", "Jumper", "Power")),

	COUNT([asset_name]) >= 0

)

.

• Invalid value error

CONCATENATE("Please de-select ", COUNT([asset_name]) - 1, " ",
           [select_asset_type], " and re-submit."
           )

• Initial Value

CONCATENATE("Too many ", [select_asset_type], "s selected!")

.
Initially the [asset_checker] column is not visible
.

.
It stays hidden unless there is an error to report…
.

.
A further message pops up if the surveyor ignores the warning and attempts to save the form…
.

.
Love the workaround… Many thanks…
.

[Suvrutt_Gurjar]

Hi @Jake_Naude,

Thank you for the detailed update of nice implementation and good to know it works the way as you want.

As a side note, if I may mention, it appears from your posts that you have substantially complex expressions that have multi row functions such SELECTS() in your apps. I hope you are also keeping an eye on app performance parameters. In case your app will have large data when in use, the performance aspect may become vital.