Filter Dropdown List (or something better...)

[skwaler]

See Table relationships attached

Editing a screening record, I want to choose which site is assigned to the screening.

I can't add a "Choose Site" dropdown  and fill with "sr_tbl_sites" because not all site are assigned to the study.

How can I create a dropdown list that shows only the sites that are assigned to the study that the screening belongs to?

Is this possible? (so frustrating that caspio doesn't give more options to filter a dropdown list) 

 

Is there some other way to do this? Perhaps a popup window that would list all the assigned sites, then allow user to select one?

i.e.(Update the screening record, close pop-up and then refresh parent page)

[CoopperBackpack]

Hello @skwaler,

My first thought is to use the Cascading Dropdown (https://howto.caspio.com/datapages/datapage-components/cascading-elements/), however,  you need to test this. 

If your sr_tbl_study_sites table includes all studies and sites that are linked to them, then you may use this table as a Lookup table for Dropdown to select the Study and Cascading Dropdown to select the Site.

[skwaler]

Hi @CoopperBackpack

Thanks for the tip. 

It works if I create a virtual dropdown that has the study_id selected as parent.

Then using a cascading dropdown with source = sr_tbl_study_sites and filter by study_id

The remaining issue is that the site_id is used as display in the dropdown even though I have site_name set as display value in the relationship.

Is there any way to display the site name?

[astroboy]

Hi, I'm not sure if this is what you are looking for, but have you already considered applying Record Level Security for your dropdown to filter out the dropdown options based on the filtering fields that you selected? If not, then you may want to give this a try:

https://howto.caspio.com/authentications-and-connections/authentication/record-level-security/filter-lookup-dropdown-or-listbox-based-on-user-or-role/

[skwaler]

@astroboy thanks but it does not apply here.

The cascading option is actually the one that makes sense and does work, except for the Display Value not being shown. Instead the foreign key id is shown. 

Is this bug or limitation in Caspio?

[Kurumi]

Hi @skwaler - what is your configuration in the Cascading dropdown? 

[skwaler]

@Meekeee

I don't have site_name in sr_tbl_study_sites

I guess i could try using a view instead that joins the sites table. I would have thought that setting Display value on the relationship should work.