Hi There,

1)
I need to search on multiple tables and the data from 20 TO 70GB

Since the data is pretty big I found out the best thing is to use a dynamic sql statement where I define on the fly the parameters to be used. I can NOT use a view with constant number of parameters since it will be too great of an impact on the execution plan.

I am trying to see how can I use a BO under this condition. For instant if the first sql gets 0 records, then in the same SP I will pursue with another SQL statement to grab from different set of tables.

How do you assign a BO to this kind of process.

2)
I need to use some audit trail. Like if the user enters FirstName, LastName and State for search purpose. I need to enter that as a record for an audit trail and then the results of the search as well. It could be up 100 records. Obviously that could be as a simple Insert.

How is the audit trail is coming along in the SF? Is that something that I can use?

Regards,

Doron

Hi Trent,

If you look that SP that was attached before it covers 6 tables (for the 2 sql statements) and I need to add more sql statements from other tables as well so this way maybe 12 tables will get involved. If one search in the first data set gets 0 records then it goes to next SQL statement and so forth...

Hundreds of users will be logged into that server and perform search, and then I need constantly to reload too many BOs for one search at time not to mention the over head with all these BOs.

I know that you reduced the foot print of each BO but that also is a concern as well.
I would rather do that in one BO...

Regards,

Doron

 

Do you want to update back to the server with this same result set?

Hi Trent,

When it comes to search no need to update the server. I will need to create some SPs that will do as follows: If the user enters First, Last and State and the search yield 100 records which is the Max.

I will store the one record in table which is called: SearchValues which is the parent where I enter the values that the user entered initially, and then in the child will store only the pk of each found record and the table name. I haven't tried that yet, but that's the idea...

That's it when it comes to search.

I will have many types of search and this link may get you the idea: http://www.PeopleFinders.com

Regards,

Doron

Great!  So my logic is exactly what you want to do then as this is something that we do every day.  We have hundreds of places within our medical software that does this very thing.  It relates to a JOINed query.  At this point, it is just a matter of creating the query, and then within the list, enter the name of the field in the column that you want to show.  You don't even have to create custom properties in this case.  The column name just has to be within the result set to prevent an error when the ListView tries to reference that column.  Another option, if you so choose, is to create custom properties that will expose these columns.  But this is something that we do all of the time within our applications and is very common practice.

I am still a bit confused about how to build the listview to show the data from the query.  Do you mean that when building the listview instead of using {Index} to set the value we have to enter the Field Name of the returned query? or we have to do this in any of the listview event manually?

Edhy Rijo

You would add the column on the left with the name of the column that has been returned in the query.  You could then populate the column with a formatted value using the {index}.  You could also use the populated through even option and populate the column from within the RowPopulating method and use the e.BusinessObject.CurrentRow.Item("MyField") option as well.

Thanks for clearing this up, I just hope Doron can also understand this

Edhy Rijo

I hope you understood that too Mr. Rijo.

Hi Trent,

Thanks for your  reply and let me try that and see how it comes...

Regards.

Doron

I'm not quite sure if I understand the first method you indicated. When you say:







If the column isn't a strongly typed property, it doesn't appear on the left, so how do you do this? Is it just done in code?

Just type it in.  This is a combo box, not a drop down list, so you can type in whatever you want and it will try to use that field name.  If that field name doesn't exist...BOOM!

wow...I'll give that a try!

Hi Trent,

I tried your suggestion using WinForm as a test and it worked just fine and thanks for that.

But in order to use a BO I had to map it at least to one table. I used this code: e.BusinessObject.CurrentRow.Item("MyField") for the list view.  But I also need to do a lot of manipulation with the text, so I think if I have a BO with all fields involved in the query it could be easier. One thought is to use a dummy table with all fields involved in query and use that as the BO of the search, so this way I will have the Strong Type capability to process all fields.

Please advise if you have another or better idea for above.

I also would need to create a listview as shown in the below image. Do you know of any control that I can have such a look while I can create links within a cell etc... Or maybe there is a way of doing so using the SF ListView..

Please see this link to get the idea of how it has to be displayed:  http://www.dfarber.com/joe_smith_data.png

Thanks,

Doron

Hi Trent,

Still waiting for your reply.

Regards,

Doron

Doron,

This is exactly what I had expected to see when I was posting my previous responses.  Here is what you need to do.  Create a SPROC that will retrieve all of the results that you are wanting to see, even if they come back in more than one result set.

Let's keep this simple.  Two tables:

Customers
CustomerPhoneNumbers

Now I want to show in the list you provided all of the phone numbers in a flat list.  So the first thing I would do is get the query the way I like it, create a BO for each that may be used here (i.e. CustomersBO, CustomerPhoneNumbersBO).  Then I might treat this like a report if this is going to be for display purposes and then add a link (or whatever mechanism you intend to use) to an editing page if they want to make changes.  So in this case, I might inherit the CustomersBO and add all of the custom properties that I need for this display while not "messing up" the original BO since these custom properties will not be used everyone.

So now when I call my stored procedure that retrieves all of my result sets, I will dump it into this BO like this:

So now I have a single entry point with two BOs.  This is the same type of logic you would use for a report.  So now, go one further and create a custom property to show all of the phone numbers on the entry point BO:

This should get you going in the right direction.

 

Hi Trent,

Thanks for the informative answer.

Regards,

Doron