Jump to content
  • 0

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


JKSGT
 Share

Question

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

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0
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

Link to comment
Share on other sites

  • 0

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>
 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...