Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Finding enum fields with string value

wALF
Level 2
Level 2
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?
1 Accepted Solution
CedricRey
Correct answer by
Level 5
Level 5

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

View solution in original post

0 Replies
CedricRey
Correct answer by
Level 5
Level 5

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

View solution in original post

LaurentLam
Level 5
Level 5

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
Level 2
Level 2
This certainly looks helpful. I shall try and revert on this.