Show multiple values in one field like a Listbox in tabular or gallery datapage

[DesiLogi]

Hello,

I was hoping at some point soon Caspio would enable Lookup tables/views in ListBox type fields so the user could create their own choices for the Listbox (instead of it being hard-typed in in the table Design for that Listbox field).  It's been awhile though and I can't wait any longer- so I'm using the older method of js and a text field to store multiple values comma delimited. This works fine except I don't have a way to show the related text values to the stored numerical values  in a Tabular or Gallery datapage. This is because the field (text255) only has the option for Text, html, email, url link in a Tabular/Gallery datapage. 

Multiple values can be shown for a text field on a Details page using js and a ListBox type display. Is there any way to utilize something similar for a Tabular/Gallery datapage? I even tried using a trigger to push those multiple values from the text255 field into a Listbox field (because you can display a Listbox type field in a Gallery datapage) but the trigger won't allow updating into Listbox fields. This is frustrating- having dynamic lookups for Listbox fields would solve this issue entirely. 

[Vitalikssssss]

Hi @DesiLogi,

I am not quite understood the logic of your Tabular/Gallery datapages.

Do you have bulk edit option enabled where you would like to use ListBox with multiselect option?

Regards,

vitalikssssss

[DesiLogi]

I need to show a listbox in a Gallery datapage, next to some other data about the record. Each record will be 'assigned' to multiple projects (as values in a text field, comma delimited) and I need to show the list of them at a glance, in the Results section. It's not a problem to show this in Details but I can't figure out how to do it in a Gallery's results, as Listbox is not a display option for a field. 

[Vitalikssssss]

Hi @DesiLogi,

As I have understood you would like to have something similar to this:

You would need a bit of JS to achieve this. Initially I have used this post as the inspiration for the following JS code which produce list of items.

<!––Place this code into HTML block.-->

<div id="demo[@field:Record_ID#]"></div> <!––[@field:Record_ID#] is a unique indentifier of the record-->

<script type="text/javascript">
    
document.addEventListener('DataPageReady', function (event) {

let list_string, arr, arrLen, text, i;

list_string = "[@field:Text]"; //field where you store comma delimited string

arr = list_string.split(',');
arrLen = arr.length;

text = "<ul>";
for (i = 0; i < arrLen; i++) {
  text += "<li>" + arr[i] + "</li>";
}
text += "</ul>";

document.getElementById("demo[@field:Record_ID#]").innerHTML = text; // use unique indentefier of the record i.o. [@field:Record_ID#]


});
</script>

Hope this helps.

Regards,

vitalikssssss

[DesiLogi]

Hi Vitalikssssss,

Thanks for this code- it does as you say and puts the comma delimited values of the text field into a Listbox- very nice.

The issue for me is that I'm storing the ID values (integers) as the comma delimited multiple values but I need to show the related text value (from another table) in the Listbox. So there's a lookup involved (which you can do in the Details field's field when setting it to Listbox).

So basically, if the values I have in the field are something like 2, 3, 34 I need to be able to lookup in myLookupTable where, as separate records, "text field value for the record with ID = 2 ", "text field value for the record with ID = 3 ", "text field value for the record with ID = 34 " and then show those text values in the Listbox you've shown how to create. 

I apologize if I wasn't fully clear on what the multiple values stand for, I should've delineated that in the original post. 

[Vitalikssssss]

Hi @DesiLogi,

It would be more challenging to get display values instead of ID`s.

I have found a solution which might work for your case.

Basically, you would need to add additional Calculated field to your Gallery report Datapage and modify the JS.

1. Calculated Field will use SQL in order to get string of all lookup values. 

You can  use the following expression:

STUFF((SELECT CAST(', ' + Name AS VARCHAR(MAX)) 
         FROM a_1 
         FOR XML PATH ('')), 1, 2, '')

Where:

• a_1 name of your Table which stores lookup values,
• "Name" - name of the field which stores lookup values.

You can hide this calculated field from a user by placing HTML blocks above and below the field. Also, you should place the following code in HTML block:

top HTML block

<div style="display:none;">

bottom HTML block

</div>

make sure you disable HTML editor.

2. Finally, place the following JS code into HTML block in order to show Listbox of display values:

<div id="demo[@field:Record_ID#]"></div>

<script type="text/javascript">
    
document.addEventListener('DataPageReady', function (event) {

let list_string, arr_string, arrLen, text, i, list_values, arr_result;

list_string = "[@field:Text]";
list_values = "[@calcfield:1]";

arr_string = list_string.split(',');
arr_values = list_values.split(',');

arr_result = arr_string.map( value => value-1);

debugger;
arrLen = arr_result.length;

text = "<ul>";

for (i = 0; i < arrLen; i++) {
    
    console.log(arr_result[i]);

    text += "<li>" + arr_values[arr_result[i]] + "</li>";
}

text += "</ul>";

document.getElementById("demo[@field:Record_ID#]").innerHTML = text;

});
</script>

 
Hope this helps.

Regards,

vitalikssssss

[DesiLogi]

Hi Vitalikssssss,

That is a really nice solution and works great! Thanks very much for posting this, I'd never thought of trying to do it that way. 

[futurist]

 Hi, 

Just wanna share this customization solution I came up with if you want to make gallery report datapages look more appealing: