Is there an easy way to collect indexed values for one field by reducing another field.
My goal is to only show User A the indexed values for a search form for Field A to N based on Field X.
So the table would be reduced based on the index values of Field X and show only the indexed valued for Field A to N for Field X.
Hope that is clear enough. Any help or comments is appreciated.
Pascal Landry
Referring to the previous question, I've been able to get the information I want with the following code, but find its a backwards way and is quite taxing. This is especially taxing when loading from a DotNet page.
retVal =
; Keylist =Extract_SI_Keys(TableName, ReducingField, RFValues, keylist) ;swap @vm with @fm in keylist
keylist = RTI_DistinctList( keylist, @fm )
RetVal = xlate(TableName,keylist,ReportBackField,"X") ; swap @vm with @fm in Retval
retval = RTI_DistinctList( retval, @fm )
The goal is to present to a user only the choices pertaining to their data. If I were to collect the indexed values for "ReportBackField", I could end up with a long list of choices and confuse/aggravate the user. By reducing this list to values indexed on a different field “ReducingField”, the “ReportBackField” values would be reduced substantially.
Thanks,
Pascal Landry
You might want to create your indices based on the reduced criteria that is needed. What I mean is if there are several customers that request information on a daily basis, then the proper index to create would contain a symbolic that contains the "customer" and the "date". That way when the one specific "customer" selects the one specific "date" for the information requested, the index supplies the exact specific information.
Do not create too many indices, do not create multiple indices for specific selection criteria, and do create the 'one" index based on the selection criteria if you can. It definitely works for me when there is a tracing, query or update etc. Careful planning of the database and indices is crucial when designing efficiently.
Hi Pascal,
The Extract / distinct / Xlate / distinct pattern is about as efficient as you will get. I agree with Richard that it is often possible to create a calculated column whose job is to create the indexed values you want to look up. As a variation on that theme, I had a site where I fed a lot of the dropdowns from rows in a LOOKUPS table. They were not official indexes, just a bunch of lists built by brute force. I had a scheduled task to rebuild the lookups periodically. You might be able to kick of a task for a customer to rebuild their lists when they log in to the site, so that the lists are ready when the customer gets to the page which would use them.
Thanks Richard and Bob,
I will look into creating a calculated column to help my start up process. This while creating a relational index to a dummy table could improve my start up time.
As you mention about startup, my pattern above is called at the start of a asp.net session and generated only at the beginning and is in a way a brute force list stored in datatables/dataviews for use with asp.net.
Thank you,
Pascal