Good afternoon!

I have the following code working within a browse dialog.







The problem is, like always, speed. There are 1250 parts in the mPartsBO--more in the other division. The delay seems to be building that array. Is there any easier, faster, more economical way to reference the partindex field in the BO collection?



Thanks!

Bill

(bump)



Table Structure

WorkOrderMaster

woindex (PK)

partindex (FK)

...

PartMaster

partindex (PK)

divisionindex

...



I am trying to limit the browse dialog to only those work orders that have parts in a specific division. The SQL would look like this:



SELECT WorkOrderMaster.* FROM WorkOrderMaster LEFT OUTER JOIN PartMaster ON PartMaster.partindex = WorkOrderMaster.partindex WHERE PartMaster.divisionindex = 1

Sorry about the formatting...apparently, the posts get leading spaces stripped?



Table Structure

.....WorkOrderMaster

..........woindex (PK)

..........partindex (FK)

..........etc.



.....PartMaster

..........partindex (PK)

..........divisionindex

..........etc.

Bill,



I'm not quite clear on what your doing with the code, but perhaps if you rethought how to do this on the server, it'd go faster. I rarely use an IN clause discreet values if there are going to be lots of values. Rather I'd use a join or a sub query. I.e. rather than a statement like this (the resulting SQL statement):







to something like:







Assuming you've got an index on the divisionindex, this should be blazing fast on SQL side, and since no array is being created, blazing fast in code to.

Also, Bill, if you are using SQL 2008 you might take a look at the sample stuff I posted in User Contributed ... regarding passing a datatable as a parameter to a SQL sproc for doing exactly this kind of stuff. Pretty powerful and seems tailor made ( as opposed to Taylor made ) for this kind of thing.

Is it possible to substitute a SQL Stored Procedure for the logic of the Browse Dialog? If so, then my problems are solved.



What I am trying to do involves the Browse Dialog and the use of the WhereStatement.







If there is another way to do this (limit the work orders BO by the divisionindex of the Part assigned to the work order), I'd really appreciate some suggestions. I can do all of it in a query on the server--no problem (and, it is indeed fast that way!). What I need, for now, is the proper way to do it within the Browse Dialog.



Thanks!!

Bill

Just bumping...still looking for some help on this.



Thanks,

Bill

Why not use the OverrideSearchTableName and OverrideSearchTableSchema? You can use a view here which would then allow you to get creative on your query within the view and create any type of result set or output you need. If this doesn't work, then you can also use the Searching event. Between these two methods, I have done some very creative stuff and never had the need to do anything outside of it. Also, keep in mind that you can also dynamically change the OverrideSearchTableName at run-time and use multiple queries if that would help also. If you search the forum for the Override properties mentioned, I know that we had a sample out there somewhere and a long discussion to go with it.

Cool! Thanks for the tip.



Good to see you back on the boards!!

Sorry for the absence! Had a lot going on lately. The good news is that we are really building a strong internal SF team that will eventually be all hands on deck both in development and forum activity...so the absence has been for a greater long-term purpose!