Professional Learning Series V35 stops just short - filtering drop down lists

[Malcolm]

Both the Total Training and Professional Learning are fantastic! Congratulations and thanks to Steve and others involved. After 6 weeks I've almost got a complete working database after starting out completely cold - just a bit of 'home-built' Access experience to go on!

Video 35 addresses Filtering Drop-downs via OnGridInitialise and 'choice variables.' Unfortunately Steve hints at the power of filtering but stops just short of where I want to go.

I've attached the code I've written on the Code Editor screen for my udf ListSchools. The session variable 'session.Cluster' is already defined on the Lgin_Processing page (and works fine everywhere else).

The interactive screen reports: ERROR: Not an executable file
Thanks. Malcolm

[Steve Workings]

Re: Professional Learning Series V35 stops just short - filtering drop down lists

As always, there's more than one way to do this, but I think you'll find below to work:

FUNCTION ListSchools as C ()

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

END FUNCTION

There are two primary differences here:

(1) If your session variable is already declared and in existence, you can simply refer to it in your filter expression. You don't need to pass it into the function.

(2) Your filter is using an argument which is generally good form. But, for the argument to exist and have a value, you must make create it and assign it a value. So, the :Cluster doesn't exist and the filter fails. I've provided an alternative that will do the job just fine.

Also note: Advice for those headed toward A5 version 12 is to hold all your session variables as character-type. If you were to do this, and then needed to use a numeric-type in your filter expression here, you would use:

filter = "ClusterID = " + convert_type(session.Cluster, "N")

Hope this helps, and glad you found the instructional stuff useful.

[Malcolm]

Re: Professional Learning Series V35 stops just short - filtering drop down lists

Hi Steve,
Alas, I've worked through your video again - step by step, checked the text for stupid School/Schools errors and am still getting a 'little red writing' error once I've published to the local server, logged in as the user and fired up the component:

Error executing OnGridInitialize event handler: command: e.rtc.vListSchools=ListSchools() Function: 'ListSchools' was not recognized

I've checked the variable name (vListSchools) in the dropdown choices dialogue, and that the text in the Code Editor is exactly as you recommend. Text in the OnGridInitialise reads: e.rtc.vListSchools=ListSchools()

Could the error be related to the fact that my session.Cluster is a numeral?

Again, I can't emphasise how great the video series is. I have literally put together a whole database in the six weeks I've been working on it. With any luck (and some forum guidance) it'll make life a little easier for 40 branches by the end of July. I know a little SQL, no Javascript or HTML (yet!) and am almost up the the XBasic series. Keep up the good work. Malcolm

[Steve Workings]

Re: Professional Learning Series V35 stops just short - filtering drop down lists

I presume this error is occurring on the server? Have you published the function as part of an AEX to the server?

[Malcolm]

Re: Professional Learning Series V35 stops just short - filtering drop down lists

Hmmm. More videos and reading required. Thanks again.

[Malcolm]

Re: Professional Learning Series V35 stops just short - filtering drop down lists

Just in case anyone else follows down this path - AEX files are well explained in V40 of the Version 10 series. After all the above steps it's just a matter of checking the 'compile functions into an AEX file' radio button on publish :)