Filtering Dropdown Box - using Variables for display/store values - grid/TabbedUI

[Malcolm]

I have read pages of posts and watched all the relevant V10 and 11 vids - so far without resolution. My challenge: I have two related tables - one containing 'Teacher' details and one 'Schools.' The SchoolID primary key is related to SchoolID in the Teachers table. When recording a teacher as being at a particular School (in the teacher details grid), I want the user to have a filtered list of schools to select from. I also the school name to be displayed and the SchoolID to be stored (in the Teacher table). Sounds simple so far.

I've chosen to filter the Schools using the 'Variable, OnGridInitialise rtc and Function' format that is so well explained in the TT videos. The ListSchools() is set out below - where Cluster is the school selection criteria.

FUNCTION ListSchools AS C ( )

cn="::name::CMS"
table="Schools"
filter="ClusterID= " +session.ClusterID
result="SchoolName"
ListSchools = sql_get_values(cn, table, filter, result)

END FUNCTION

This combination produces a list of the school names as I want them in the 'Interactive' window - so I know the Function 'works.' However, when I proceed to select a school name from the dropdown box the response returns a conflict error. This seems to me to be because the Function selects and tries to store a name (as it is 'asked to,') rather than storing the ID. I note that for a 'static' list to display and store seperate values they are separated by a 'bang.'
I'm using a grid (rather than a dialogue) and a TabbedUI rather than A5w pages.
Some posts refer to 'other' ways of filtering dropdowns, but I can't find them. Can anyone point me in the right direction?

[Steve Workings]

Re: Filtering Dropdown Box - using Variables for display/store values - grid/TabbedUI

You're on the way, just have a little more to do:

So you already have your function in the Code tab if I understand? A couple mods are needed:

FUNCTION ListSchools AS C ( )

cn="::name::CMS"
table="Schools"
filter="ClusterID= " +session.ClusterID
result="concatenate(SchoolName, '|', School_ID)"            ' some dbs require "Concat" instead of "concatenate" This format allows for display of Schoolname, store School_ID
ListSchools = sql_get_values(cn, table, filter, result)

' I'll also suggest this to alphabetize:
ListSchools = sortsubstr(ListSchools, crlf())

END FUNCTION

If this is in your code tab, you need to put it into an AEX and publish the AEX. That's covered elsewhere so I won't dwell on it but note it.

Finally then you need, in your grid design, to make this list available. You can do this in the grid's OnInitialize Event as below:

e.rtc.School_List = ListSchools()

Then, have your dropdown list use a Variable as it's source, and the variable is School_List

[Malcolm]

Re: Filtering Dropdown Box - using Variables for display/store values - grid/TabbedUI

Wow! That's fantastic. It now works just like a bought one!
After a very long time and lots of failed experiments I came to the conclusion that the solution had to involve a '|' somewhere - the only question was how to write it in the function! The sorting expression is another step forward too.
Thanks very much Steve - much appreciated. Malcolm

[agillbb]

Re: Filtering Dropdown Box - using Variables for display/store values - grid/TabbedUI

Nice, Steve. The other option would be to define an argument and set it to the session var. Then, for dropdown choices, you could use the genie and filter based on your argument. Thus, no need for a defined function. Not a big difference either way, really.

[Steve Workings]

Re: Filtering Dropdown Box - using Variables for display/store values - grid/TabbedUI

I use this method extensively. If you look at my Code tab you'll find a lot of List_xxxxxx() functions. In one of the current apps I'm building, some of the same lists are used over and over again. This makes the function a great convenience -- I never have to re-write or re-construct it.

[agillbb]

Re: Filtering Dropdown Box - using Variables for display/store values - grid/TabbedUI

Good point...all that genie "clicking" can be eliminated.

[Malcolm]

Re: Filtering Dropdown Box - using Variables for display/store values - grid/TabbedUI

Sorry, one more thing.
The drop down boxes work fine when I publish to the local webroot, but I'm having problems when I publish it to the server. I've followed the TT Vid 59 guidelines on handling .aex files - ie right click in the code editor white space and compile the .aex functions into a file, then I made sure the name of the aex file was correctly recorded in the 'Project Properties,' (it was already there by the time I got there). When publishing to the web server I made sure that 'compile .aex functions' box was checked.

I checked my local webroot files, and the .aex file is there. I then used Remote Desktop to check and make sure the .aex file was on the server, along with all the other files in the webroot folder. It is there, and had recently been published to the server - so it's the same one. (I even copied and pasted the .aex file from my desktop onto the server.

Yet, when I visit the 12(!!) relevant drop downs on the web server they feature only the ID numbers - not the names. The local webroot still returns correctly.

I'm using V11, so there shouldn't be a need to record any .aex detail in the individual grids as I understand it.

Sorry about this little extra - I can't seem to nut it out.

[Steve Workings]

Re: Filtering Dropdown Box - using Variables for display/store values - grid/TabbedUI

It sounds like you've done everything right. So, gotta find some way to debug this. Here are a couple thoughts:

1) Verify that the Schoolname field on your server indeed has the school name in it.

2) Try putting together this same function for some other table values (Teachers maybe?)

3) Put your function directly into an .a5w page:

<%A5
? ListSchools()
%>

run the page, make sure the output is what you expect.

I think any "next" step would be dependent on what's discovered with these kinds of trials

[Malcolm]

Re: Filtering Dropdown Box - using Variables for display/store values - grid/TabbedUI

Thanks for those suggestions Steve.

I compiled another Drop Down Box with Variable, OnGridInitialise rtc and a UD Function in the Code Writer, then put both onto their own A5W pages as you suggested.

When I published the whole package to the web server, the page with the original Drop Down Box (requesting the user to select a SchoolName) returned only the ID of the first school. The number is 666 and the page returned 666.0000 (there is growing significance to the number!) The second page returned a Windows error (The website cannot display the page HTTP500). The Grid with a dropdown box for SchoolName returned a dropdown showing only the school number (also 666).

Both the A5W pages (and the three drop down boxes on actual grids) returned just fine when I published to the local server.

I also have one grid on which both drop down boxes/variables/UDF's appear. The web server returned the error shown below - which suggests that the database can't find the specified table. (You'll need to click on the image to see it)

DDB error.PNG

I have checked (and re-checked) that the data files are on the web server and being interrogated by the A5 database. I've also checked that the table names and session variables are correct on the server data. I've also re-loaded relevant components onto my TabbedUI and re-checked the filters.

I did notice that there is more than one version of each of my UDF's where these are stored - see duplicates of 'ListSchools' and 'ListClusterGroups' appearing below. I can't seem to tidy this up - but I did test both versions of each - and they showed no difference.

UDFs.PNG

My .aex file is just 2kb compared to a much larger one (168 I think) on the A5 V10 video #40 demo, but then I only have two functions listed - and it reduced to 1kb when I deleted one of the functions!

Just to re-affirm, I've pointed to the .aex file in each grid (and tried without doing this too), clicked in the white space of the Code Editor to compile functions into an .aex file, and checked the 'compile into an .aex file' on publish. The .aex file name is also corectly listed in the publish profile.

I'm deducing that because the functions work on the local server, the error must be in the publishing and/or connections on the web server. I'm using Access as a backend so I was able to copy the files from the server, change some data and paste them back again. The A5 database refected the changed data - so A5 is indeed using the correct backend files.

The only thing I cannot actually check is the contents of the .aex - but then its size changes when I delete one function - so it should be carrying something useful.

I've run out of testing options. Any more ideas?