populate multiple combo-boxes from single table in smartObject app - Forum - OpenEdge General - Progress Community

populate multiple combo-boxes from single table in smartObject app

 Forum

populate multiple combo-boxes from single table in smartObject app

  • Hi,

    I have a smartViewer with 20 numeric fields for which I would like to make available combo-boxes (or smartSelects) to select and display values from a single lookup table. Each field will have a different subset of values associated with it from a single lookup table. I think this may require a lot of reading and writing to create the list-items and so I am wondering (if this is really feasible) what the best approach would be. It seems that to use the smartSelect method would require a different SDO for each smartSelect even though they all reference the same table so am thinking that there should be a more efficient way to do this using combo-boxes, reading the table once and sorting the appropriate values into appropriate lists. I also need to display and make available for update the underlying field and not just its combo-box representation to allow for the option of entering the numeric codes instead selecting the text value but also in some cases the numeric value may be a quantity and not have any lookup values associated with it.

    Are there any good examples I could follow or suggestions on how best to proceed?

    thanks,

    Paul

    Win XP
    Progress 9.1E

  • It seems that to use the smartSelect method would require a different SDO for each smartSelect even though they all reference the same table so am thinking that there should be a more efficient way to do this using combo-boxes, reading the table once and sorting the appropriate values into appropriate lists. 

    Did you try?  It is possible to link all SmartSelects to the same SDO.

    But you may very well be right. The SmartSelect is meant to position its data source. This scenario would most certainly not work if any of the SmartSelects are set up to be viewed as a browser, but if all are combo-boxes it might just work, since the data are collected from the widget and not the underlying data when they are saved.
    ---

    We've added the possiblilty to cache SDO data in version 10.something, so that would be the supported way to have multiple SmartSelects share data. Each would have their own SDO working with its own buffer, but all would work on the same physical table. (Basically the same way you would need to do this in pure ABL code)

  • Thanks Havard,

    I have tried, initially with a browser and a smartSelect but have just tried again with two smartSelects and they do not function when running off the same SDO. They will function when each runs off its own SDO. Are you suggesting that it might be appropriate to use 20 SDOs to deal with this in version 9, or should I be looking at a different solution?

    Paul

  • 20 SDOs sounds like a huge overhead on a single page. Especially on V9 when all the caching is not available.

    I'd suggest to implement a custom - lightweight SmartDataField for the job. Or just Combo-Box widgets and use a function in the Viewer (PostCreateObjects or InitializeObject) to populate the Combo-Boxes.

  • I have tried, initially with a browser and a smartSelect but have just tried again with two smartSelects and they do not function when running off the same SDO. They will function when each runs off its own SDO.

    Where is the limitation/problem when all SmartSelects are using combos and the same SDO?

    Are you suggesting that it might be appropriate to use 20 SDOs to deal with this in version 9, or should I be looking at a different solution.

    There is no way for me to tell. You should test if there really is a performance issue. I've seen apps using more than 20 SmartSelects and SDOs without performance issues and I've heard customers complain about performance with fewer.

  • Hi Mike,

    I have just tried it with 10 SDOs and the performance is not good. One other issue which I may not have mentioned is that the values in these combo boxes are not static but must change when the value in another field in the record changes. This will not be allowed through an update but when browsing records these values may change frequently. This module is essentially for recording attributes of physical objects which are categorized by type.

  • With a single SDO for two smartSelects I was not getting any values returned to the combo box. This may be because they have different filters defined in their instance properties. I added 10 SDOs all linked to individual smartSelects and they were operating correctly but performance was a bit of an issue. Is there some customization of the smartSelect such as Mike has suggested that could address the performance issue or should I try to code something using combo-boxes?

  • With a single SDO for two smartSelects I was not getting any values returned to the combo box. This may be because they have different filters defined in their instance properties.

    Yes, the filters probably confuse the SDO immensly...

    I added 10 SDOs all linked to individual smartSelects and they were operating correctly but performance was a bit of an issue. Is there some customization of the smartSelect such as Mike has suggested that could address the performance issue or should I try to code something using combo-boxes?

    (I assume you are not running on an AppServer without using an AppServer aware container.)

    With the filtering I suspect you might have a hard time using a single SDO even with customizations, so you would need to manage buffers in the customized SDFs. I cannot give you any customization tips from the top of my head. It would probably require a lot of trial and error. If this is the only place you need it you might as well use combo-boxes.

    Another possible alternative is to use TT SDOs that feed from the original table on the client, but I'm not really sure where your current performance issue is, so it might not help at all...            

  • On with V9 are you exactly? And are you using exactly the ADM2 of that version?

    I haven't been talking about customizing the SmartSelect. The APIs of the SmartCombos etc. have changed over time, so since you are on a V9 every modification there might not work on the latest OE10 (without modifications).

    There's a template for new SmartDataFields. That's like a SmartFrame with the difference that it can be added on a viewer and be linked to an SDO field. Like the SmartCombo. You need to implement enableField, disableField, setFieldValue and getFieldValue functions or procedures so that it works as an intelligent replacement for a simple field level widget. Outside of Dynamics I've used that often as a replacement for difficult SmartSelect implementations (that's what I'd call your use case) - including a cache or otherwise enhanced data retrieval.

  • No I am not running on an AppServer so I guess I don't really need to worry about making database calls from a viewer but if I were to follow recommended practise would I be trying to do this within an SDO and then push the sorted values to the combo-boxes? I was thinking of using a case statement to distribute the values from the lookup table either directly to the appropriate combo-box or to set of variables and then assign the appropriate variable to the appropriate combo-box. Any sense of which approach or combination of approaches would be better for this situation (direct assign or variable; SDO or viewer)?

    thanks,

    Paul

  • I would go for Mike's suggestion (below) coupled with a single SDO and figure out a way to use a separate query and buffer in the SDF.

  • Since these are combo boxes you might be able to fill the list-items from the SDO without using a query or buffer. But the fact that you need a different filter for each combo might complicate this somewhat. 

  • I'm using Progress v. 9.1E and ADM2. I don't have any experience with the basic SDF but that does look like an interesting option. I will have to do some looking into it (like how to populate a combo-box inside a SDF). I don't know what exactly what you and Havard mean by cache. Is that something specific to Dynamics? I can't find the SDF maintenance screen where the "use cache" toggle is supposed to be found. But that does bring up something I hadn't considered and that is using a temp-table SDO instead of the database to draw out the values to the combo-boxes. I guess I`ll try out some of these ideas and see what develops. But please do let me know about the cache if that might provide a significant advantage.

    thanks,

    Paul

  • I don't know what exactly what you and Havard mean by cache. Is that something specific to Dynamics? I can't find the SDF maintenance screen where the "use cache" toggle is supposed to be found.

    The "Use cache" option for SDFs is for Dynamics only.  I was referring to the ability to cache SDOs, but this is not available in 9.1E.

  • Just to update this thread, I found that the big performance hit comes not with returning records from the database but with writing those values to the combo-boxes. As a result I haven't bothered with using a temp-table. I don't imagine there is any way to increase performance for writing the list-item-pairs to the combo-boxes (I am using a single Assign statement), but if there are any suggestions I would like to hear them.

    thanks,

    Paul