Mastering complexity - this one is escaping me (1 Viewer)

[cyberman55]

Hi all, I'm hoping one you CodingMeisters can help me out. I've got a very complex form going that updates MS charts based on user selections of a frame control (3) options, a treeview control (free download from http://www.jkp-ads.com/ and very nice all-code control solution) having 5 nodes (5 optiions) and two listboxes which, themselves populate via user selections. It's a complex mess, and I've posted on the query forum for ideas on that part of it. Currently I'm using an unwieldy set of case select statements to manage it. I believe I have 225 permutations of possibilities right now. Specifically 1) Space inventory from the tree (all, selected account, selected building, selected floor, selected room) times three from the option frame (inspected, not inspected, both) and then all the listbox selections (by space inventory, by supervisory zone, etc).

So, by way of example, users pick a node on the tree then:

1. Check the Option Frame selection. (3 choices)
2. Based on the Option Frame Selection (Check the ListShowBy selection) (5-9 choices depending on the Option Frame selection)
3. Based on the ListShowBy selection, sometimes also check the ListSpecifically For selection
5. Update the chart's rowsource (1 of 3 charts depending on the formatting needed)

Otherwise, User picks an Option Frame selection
1. Set the two listboxes and update the chart (the easy one, since I'm forcing the default.

Otherwise, pick a listshowby item then:
1. Check the option frame
2. Based on the option frame
3. Check the tree node level and selection and/or the listspecificallyfor listbox.

I know I could create a function that takes inputs such as which control was selected, and put all this nastiness in one place, but I can't see how I can get around having a huge set of case select statements nested within each other. Right now, I've got one case select statement using the option frame as the first level, the tree node as the second level, and the listshowby as the third level with some cases also requiring a select case on the listspecificallyfor control.

Tell me there's a better way to do this. I'm just a poor mechanical engineer with no CompSci training trying to do a software engineer's job.

Any tidbits would be appreciated, especially if they're tasty.

 

[Pat Hartman]

The better way to do it is to create a table. You will probably also need a form to manage the table should you or the user need to add entries.

Then your code selects the three selection items and pulls a value from the table. A couple of lines of code rather than hundreds. Not to mention, the matrix can now be managed by the user rather than the programmer. If you were planning on retiring on the income from maintaining this matrix, don't give the form to the user. Make him call you.

 

[MarkK]

You've described the problem in fairly general terms, so I'd point out first that it's always tough to offer a response that has any sharper resolution than the question.
So generally then, I'd write a SQL WHERE clause on the fly based on the settings in the various controls you mentioned. A common pattern is that you need to return a constraint if a selection is made in a control, otherwise that constraint needs to simply be absent from the WHERE clause all together. For example, you may want to only see data for active jobs . . .

WHERE [Active] = True

. . . or you might only want data for inactive jobs . . .

WHERE Not Active

. . . but if you want data for all jobs . . .

[COLOR="Green"]'in this case there is no WHERE clause[/COLOR]

I expect I would write a function call for each control, and that function call would determine if that control is selected, and it would call other functions if the selection in the first control necessitates that the settings in other controls inform what is returned for the first control in the chain. In this way it would better if you could nest function calls rather than Select Case blocks, because probably a bunch of nested Select Case blocks have considerable repetition of code that does exactly the same job. That is what leads to impression that the problem is incredibly complex.

So in general, I'd write a SQL WHERE clause on the fly, and I'd use subroutines, functions that call functions, to construct the string I need.

hth

 

[cyberman55]

Thanks Pat and LagBolt for your speedy replies. I'll try one or both of these suggestions and let you know how it worked out!

I love that treeview, it's very powerful and promises to be much more stable (and distributable) than Microsoft's activeX control.

 

[cyberman55]

Hi: I uploaded the form with all supporting tables, queries and code - it's probably worth downloading, if just to see Jan's beautiful subform, a code-based treeview. Download (1MB) zip from here: https://app.box.com/s/amrekqjkwk5kg1jkb2n1 requires AC10, maybe AC07.

I'm beginning to think some problems are simply too complex for me to simplify and structure and I may be best off just making some compromises and reducing the complexity a bit. It's great from a user perspective if they can click any option any time and get the view/result desired, but it just creates a huge headache for me. I'm going to try reducing the sheer mass down a bit by taking the option frame choices down from 3 to 2 and also restricting either the space inventory tree to the list boxes (enabled=false) just to simplify it. For example, when the list show by is set to "space inventory", I could disable running chart updates and queries until the user selects a node on the tree. Likewise, when some of the list show by options are picked, I could disable the tree until they pick something on the tree. At last calculation, if I allowed all permutations -it's something like 240, and I can't handle the "bookkeeping".

Just a thought, but I've seen a lot of competitor's web-based solutions, and even with some restrictions and compromises, this blows the doors off anything I've seen on the web in terms of meaningful output, user interface, and speed of execution. This example has 7900 room records, a typical mid-size facility. Think of trying to throw all that data around on SQL server for multiple users (X times the facilities) and then refresh the browser.... I'd guess it would slow to crawl as the set of users grows.

 

[Pat Hartman]

Complexity is increased rather than reduced when your schema is not normalized. I count a minimum of 13 repeating groups. All of those need to be removed to their own tables. Although some may be sets of repeating data so you probably won't need 13 tables. You also have way more queries than you need and making temp tables just bloats the database. In most cases, queries will work just as well without the bad side effects.

 

[cyberman55]

Thank's Pat - this is legacy and too ingrained now to correct given the size of the application. I've learned a lot since I started way back when, but in development, just as in life, sometimes we have to live with past mistakes since the cost of correcting them is too high. Cash flow on this app is too low to justify the ground up rebuild. That's why I've gotten into new apps were I can design things better and hopefully have less past mistakes to live with in the future. For example, the application for collecting this information is sized to run on a tablet and it's much better in design overall. It has to synchronize to this database, but just a few tables, perhaps 20 or so.