relatioships field disabled in related table

[wilsoncastrogil10]

various items.pdf

[CoopperBackpack]

Hello @wilsoncastrogil10,

1) The 'Type' field is disabled since List data types are not available to create Relationships.

You may want to check this article https://howto.caspio.com/tables-and-views/relationships/database-relationships/

List data types have more restrictions:

https://howto.caspio.com/tables-and-views/data-types/list/

This makes sense since according to the so-called First Normal Form of database normalization a single cell must not hold more than one value (atomicity).

However, List data types are available in Caspio Tables to cover some use cases when one needs to store multiple values in 1 cell and display these values to the user (on Reports DataPages, for example).

But if you need to manipulate data (to use it in Calculations, Triggered Actions, etc.), the List data type is not the right choice.

 

2) The basic workaround to select multiple options without using the List data type:

* use Text(255) data type instead,

* set the field as a Listbox (since the solution works only with Listboxes),

* add a JavaScript code to the DataPage Footer (disable the HTML editor on the Advanced tab before pasting the code) to add the 'multiple' attribute. 

Multiple values in a Listbox can be selected by holding the Ctrl key on the keyboard while clicking on the required values simultaneously.
https://forums.caspio.com/topic/3148-js-select-multiple-values-from-a-listbox-in-a-webform/

<script>
document.addEventListener('DataPageReady', assignMultiple)

function assignMultiple() {
  document.getElementById('InsertRecordType_ID').multiple=true; //Type_ID is the field name, change it if needed
  document.removeEventListener('DataPageReady', assignMultiple)
};
</script>

This is the example with the applied solution:

 

3) The main benefits of Relationships between the tables are:

* possibility to join the Tables in Views, Triggered Actions, Tasks,

* the 'Use display value on DataPages' option that allows displaying some user-friendly value instead of IDs

This article can be helpful: https://howto.caspio.com/tables-and-views/relationships/relationship-settings/

However, even if you apply the solution from the previous step (Text(255) and JavaScript to select multiple values), I don`t see much sense in a separate lookup table with the list of 'Types'.

Because in the 'cm_tbl_contacts' table you will still have comma-separated values, so Joins and the 'Use display value on DataPages' option will not work as expected. 

 

4) If you need to store several types in the  'cm_tbl_contacts' table for simple scenarios, you may try the following: 

* use the List_String data type for the 'Type' field in the 'cm_tbl_contacts' table with the list of possible values

 

* in this case you will be able to set the field as Multiselect Dropdown or Multiselect Listbox

 

Feel free to update this thread if you have further questions. 

[wilsoncastrogil10]

On 2/24/2023 at 3:39 AM, CoopperBackpack said:

Hello @wilsoncastrogil10,

1) The 'Type' field is disabled since List data types are not available to create Relationships.

You may want to check this article https://howto.caspio.com/tables-and-views/relationships/database-relationships/

List data types have more restrictions:

https://howto.caspio.com/tables-and-views/data-types/list/

This makes sense since according to the so-called First Normal Form of database normalization a single cell must not hold more than one value (atomicity).

However, List data types are available in Caspio Tables to cover some use cases when one needs to store multiple values in 1 cell and display these values to the user (on Reports DataPages, for example).

But if you need to manipulate data (to use it in Calculations, Triggered Actions, etc.), the List data type is not the right choice.

 

2) The basic workaround to select multiple options without using the List data type:

* use Text(255) data type instead,

* set the field as a Listbox (since the solution works only with Listboxes),

* add a JavaScript code to the DataPage Footer (disable the HTML editor on the Advanced tab before pasting the code) to add the 'multiple' attribute. 

Multiple values in a Listbox can be selected by holding the Ctrl key on the keyboard while clicking on the required values simultaneously.
https://forums.caspio.com/topic/3148-js-select-multiple-values-from-a-listbox-in-a-webform/

<script>
document.addEventListener('DataPageReady', assignMultiple)

function assignMultiple() {
  document.getElementById('InsertRecordType_ID').multiple=true; //Type_ID is the field name, change it if needed
  document.removeEventListener('DataPageReady', assignMultiple)
};
</script>

This is the example with the applied solution:

 

3) The main benefits of Relationships between the tables are:

* possibility to join the Tables in Views, Triggered Actions, Tasks,

* the 'Use display value on DataPages' option that allows displaying some user-friendly value instead of IDs

This article can be helpful: https://howto.caspio.com/tables-and-views/relationships/relationship-settings/

However, even if you apply the solution from the previous step (Text(255) and JavaScript to select multiple values), I don`t see much sense in a separate lookup table with the list of 'Types'.

Because in the 'cm_tbl_contacts' table you will still have comma-separated values, so Joins and the 'Use display value on DataPages' option will not work as expected. 

 

4) If you need to store several types in the  'cm_tbl_contacts' table for simple scenarios, you may try the following: 

* use the List_String data type for the 'Type' field in the 'cm_tbl_contacts' table with the list of possible values

 

* in this case you will be able to set the field as Multiselect Dropdown or Multiselect Listbox

 

Feel free to update this thread if you have further questions. 

Hello @CoopperBackpack
Thank you very much for your support. I applied point 2, but it does not let me select several items.
Because the option 3, I do not apply because the list is too large.

[CoopperBackpack]

Hello @wilsoncastrogil10,

The solution described in point 2 requires using the Text(255) field.

If the solution doesn`t work, please check:

* if the field data type is correct,

* if you use the correct field name in this line of code: 

document.getElementById('InsertRecordType_ID').multiple=true; 

In this example, the field name is Type_ID.  You need to change the field name if needed

* additionally, please note that you need to press the Ctrl key to be able to select multiple items.

 

If the list is long, as you mentioned, the option described on p.4 should work: you may add the Type field with List_String data type directly in the Contacts table.