Using a calculation field to convert text in a category field to a number to be counted in a later step

Follow

Matt Angell

• July 20, 2019 18:08

I have a category field named "Nights Available" with the days of week written out.

I am trying to use a calculation field to convert this text string to a number.  All days of the week equal 1.

I will use another calculation field (or Globiflow) to add these values together in a later step.

 

For example:

If "Nights Available" equals "Monday" and "Thursday" the total number value would equal 2.

 

I have been unsuccessful doing this so far, can anyone help?

0

• 

  Carson Young - Red Cliff Labs

  • July 22, 2019 15:09

  Try using something like this:

   

  var num = @your_category_field.length ;
  num == 1 ? 1 : num == 2 ? 2 : ...etc... : null
  

  0

  Comment actions Permalink
• 

  Rainer Grabowski

  • July 22, 2019 17:15

  Hi Matt, 

  var cat = @Nights Available;
  cat != null ? cat.length : 0

  Shows 0 if no day is selected, otherwise it counts the number of selected days.. 
  
  @Carson
  FWIW: 
  The IF ... ELSE isn't necessary cause .length delivers the right result.  If no day is selected (and the field is empty = null) your code  doesn't return null but an error:
  Script Error: Type Error: Cannot read property "length" of null
  So you first need an IF ... ELSE which says what to do if the field is null and not null. It's not so important in this use case but in other cases whre you have some more code cause the eexecution of the code would stop after the first line.   
  
  Rainer

  0

  Comment actions Permalink
• 

  Matt Angell

  • July 23, 2019 16:36

  Thanks Carson, this worked perfectly and saved me the extra step!

  0

  Comment actions Permalink
• 

  Matt Angell

  • July 23, 2019 16:39

  Thank you @Rainer as well.

  Carson's solution worked better for this application as I could assign point value based on number of options selected (see below), and that this field is required and cannot have null value.

  var num = @Nights Available.length ;

  num == 1 ? 1 : num == 2 ? 1 : num == 3 ? 2 : num == 4 ? 2 : num == 5 ? 2 : num == 6 ? 3 : num == 7 ? 3 : null

  0

  Comment actions Permalink

Please sign in to leave a comment.