Cascading Dropdown Selection Of State And County In Grid Edit

[AndyYee]

I am able to get the cascading dropdowns to work in the details page for first selecting state, then county.  However, I am having difficulty making it work in the grid edit mode of my tabular report.

 

Since going through some of the Caspio HowTo documentation, I made my County table into more of a cascading table format that includes columns for both state and county.  (I also have a separate table with the list of states in case I need to go back to distinct County and State lookup tables.)

 

When a user selects a county, there is an implied state with it from the County table.  My goal is to have the user be able to select the state first in my Facilities tabular report, then select the county as a cascading dropdown filtered on the state value.  (Sometimes the state is known when a new facility record is added, but the county not until later.)

 

When I try to implement cascading dropdowns in the grid edit page, there is no option to select the county name as the display value.  The column "CountyId" in the Facility table is an integer number data type that stores the autonumber Id from the linked County table.

 

I would appreciate anyone's suggestions!  Screenshot included.

 

[Jan]

Hello Andy,

 

Welcome to Caspio Forum!

 

If I understand correctly, you can define what field is displayed on the "Editing" tab of the "Configure Results Page Fields" step.

As far as I know, these settings defines both Inline editing and Grid Edit mode.

 

Does it work for you?

[AndyYee]

Hi Jan, thanks for the post.  If a record is being edited, the display value appears when in grid mode for that active row. However, for all other rows, the integer ID value is shown instead.  Since posting this question I submitted a support ticket.  The Caspio support team is very helpful and offered a couple workaround solutions including the use of a SELECT SQL statement for the display value in a calculated field (meaning a separate column shows up rather awkwardly next to the ID column).  

 

Unfortunately, neither of the workarounds presented are a good way to go for my particular app.  I am currently building a demo app to test things out, but eventually am looking toward migrating/converting an existing SQL Server app to Caspio.  It has almost 40 lookup tables so I need a solution that I know I will be pleased with down the road.

 

I am coming to terms with the fact that I need to let go of some best practices in defining my db schema, and go with text values for table PKs.  I think this will work as long as I keep table relationships tight with ref integrity and cascading update/deletes in my join definitions.  I am hopeful this experiment will be successful, and that I end up with a more elegant and streamlined UI.  

 

If you have any thoughts or experience with this, I would love to hear it!

[IamNatoyThatLovesYou]

Hello Everyone,! Just an update: Caspio has finally released a feature to show DISTINCT records on Reports (Tabular/List/Gallery).

It is available in R30 version.  You can check it here.