Case Statement not working with value selected in a multi select dropdown

[JKSGT]

Hi,

I am trying to perform a simple calculation in Virtual2 (a calculated field) using a value selected in a multi select dropdown. However, the calculation is failing.

Step 1 - I set up a multi select dropdown - List_String

Step 2 - the multi select dropdown field ID cannot be used in a CASE statement therefore I set up [@Virtual1] to capture the selection in the List_String using JavaScript

Step 3 - I then tried some CASE statements in [@Virtual2] to perform a simple calculation based on the List_String selection copied to [@Virtual1]. However, this part isn’t working. I have tried this formula: 

CASE
    
WHEN '[@cbParamVirtual1]' LIKE '%'+'Car'+'%' THEN 300

END

The above formula works with any other field (dropdown, radio buttons, etc) but it will not work with [@cbParamVirtual1]. I wonder if it is because of the data type (when copied from the multi select dropdown) and whether my formula needs to CAST [@cbParamVirtual1] as something that Caspio will recognise as text.

Would anyone have any recommendation?

Many thanks

Jay

Many thanks
Jana

[TellMeWhy]

2 hours ago, JKSGT said:

Hi,

I am trying to perform a simple calculation in Virtual2 (a calculated field) using a value selected in a multi select dropdown. However, the calculation is failing.

Step 1 - I set up a multi select dropdown - List_String

Step 2 - the multi select dropdown field ID cannot be used in a CASE statement therefore I set up [@Virtual1] to capture the selection in the List_String using JavaScript

Step 3 - I then tried some CASE statements in [@Virtual2] to perform a simple calculation based on the List_String selection copied to [@Virtual1]. However, this part isn’t working. I have tried this formula: 

CASE
    
WHEN '[@cbParamVirtual1]' LIKE '%'+'Car'+'%' THEN 300

END

The above formula works with any other field (dropdown, radio buttons, etc) but it will not work with [@cbParamVirtual1]. I wonder if it is because of the data type (when copied from the multi select dropdown) and whether my formula needs to CAST [@cbParamVirtual1] as something that Caspio will recognise as text.

Would anyone have any recommendation?

Many thanks

Jay

Many thanks
Jana

What's the script that you're using to pass the List string to a Virtual field? You need to activate an event when the value is passed, but, let me check your script first

[JKSGT]

Hi,

I have changed the approach and decided to turn a text field (dropdown) into a multi-select with a bit of JS. It has solved all my issues but it is a little bit clumsy because the user needs to hold the CTRL key down to select multiple options. I'd prefer it to be just with a click.

<!-- MULTI SELECT FIELD-->

<script>
 var v_state = "[@field:Field_ID]" ;
 var o_state = document.getElementById("EditRecordField_IDs") ;
 o_state.multiple = true ;

 function f_listbox() {
  if ( v_state.indexOf(",") > 0 ) {
   for (var i=0 ; i < o_state.options.length; i++ ) {
    if(o_state[i].value == v_state) {
     o_state.remove(i);
     break ;
    }
   }
   var o_st = v_state.split(", ") ;
   for (var j=0 ; j < o_st.length; j++) {
    for (var i=0 ; i < o_state.options.length; i++ ) {
     if(o_st[j]== o_state.options[i].value){
      o_state.options[i].selected = true ;
      break ;
     } 
    }
   }
  }
 }
 document.addEventListener('DataPageReady', f_listbox);
 //You can also use the line below instead of the eventlistener 
// setTimeout(f_listbox, 20); 
</script>