Finding enum fields with string value

[wALF]

We have several enum fields (dropdown to choose from) in several tables having value as string data type. We are wanting to make them numeric for performance reasons. What's the best way to identify all such enum fields?

[CedricRey · Accepted solution]

Hello @wALF 

Can you give a try to this script (for example in a JS workflow activity) please :

var schemaQuery = xtk.queryDef.create(<queryDef operation="select" schema="xtk:schema"><select><node expr="@namespace"/><node expr="@name"/></select></queryDef>);
var allSchem = schemaQuery.ExecuteQuery();

for each(var schem in allSchem)
  {
  checkEnumFields(schem.@namespace + ":" + schem.@name);
  }
  
function checkEnumFields( schemaNS )
  {
  var schema = application.getSchema( schemaNS );
  var root = schema.root;
  if(root && root.children )
    checkChildren( schemaNS, root.children );
  
  }
function checkChildren( name, children ){
  for each(var node in children)
  {
    if( node.hasEnumeration && node.type == "string" )
      logInfo(name + "/" + node.name + " uses enumeration " + node.enumeration.name );
    if( node.isElementOnly )
      checkChildren( name + "/" + node.name, node.children );
  }
}

This should log all fields that use enum, with 'string' type, in all your schemas (with recursivity)

Let me know if it works.

Cedric

[wALF]

Great Cedric ... that worked well for us.

[LaurentLam]

Hello,

First, you do not have to change OOTB enumeration as, in best scenarii, it will complexify maintenance & build upgrade

In data schema views,

You can first filters about all schemas created specifically for the environment (by default, nameSpace is "cus" but it everyone choose its own nameSpace)

You then surfilter on 'mData' / 'Entity content' field using 'contains' operator and 'enum="' string to search
It will provide all the custom schema where you can make a modification 

You'll have to change the enumeration setting in the schema but to change also the field where it related to from string to integer... But, don't forget that it will drop the column and create a new one when "updating the database structure"... So be sure to first create your new column, populate its value through a workflow... Change the enum from field A to field B. And once, you're sure, you can delete field A

[wALF]

This certainly looks helpful. I shall try and revert on this.