StrataFrame Forum

BrowseDialog

http://forum.strataframe.net/Topic23458.aspx

By Larry Caylor - 6/11/2009

Can you provide a outline on how to use a view in a search with the new BrowseDialog or a sample?
By Trent L. Taylor - 6/12/2009

Here is a good example of when and how it should be used.  One of the issues that you run into with a BrowseDialog is that is represents a single table in the database.  However, there are times that you need to pull back a foreign key value, etc. Also, you don't really want to do this manually for each row through the RowPopulating event, as this would be slow and require the number of queries as you have rows.  So in this example, I would want to create a view with all of my JOINs so a single query pulls back all of the information that I need.

Moreover, I want to be able to SEARCH on those foreign key values.  Using a view I can make this happen.  In our medical application all of our phone numbers are stored in a foreign key table, yet our users wanted to be able to search on the patient home phone.  Well, this was not possible until this feature was added.  I now have a view that brings back the patient record as well as the 3 different phone types, and I can then add these as search fields and search.

Using this example, here is the view.  You will see that is strips out the dashes, parens, etc. since it will not be captured.

View Named PatientBrowse

SELECT
 PT.*,
 
 -- Get the top home phone
 REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(
 (
  SELECT TOP 1
   PN.ph_PhoneNumber
  FROM PhoneNumbers AS PN
  WHERE PN.ph_ParentType = 2 AND
     PN.ph_pk_Parent = PT.pt_pk AND
     PN.ph_PhoneType = 0
  ORDER BY PN.ph_Rank
 Wink, ''),'(',''),')',''),'-',''),' ','') AS pt_HomePhoneBrowse,
 
 -- Get the top work phone
 REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(
 (
  SELECT TOP 1
   PN.ph_PhoneNumber
  FROM PhoneNumbers AS PN
  WHERE PN.ph_ParentType = 2 AND
     PN.ph_pk_Parent = PT.pt_pk AND
     PN.ph_PhoneType = 1
  ORDER BY PN.ph_Rank    
 Wink, ''),'(',''),')',''),'-',''),' ','') AS pt_WorkPhoneBrowse,
 
 -- Get the top mobile phone
 REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(
 (
  SELECT TOP 1
   PN.ph_PhoneNumber
  FROM PhoneNumbers AS PN
  WHERE PN.ph_ParentType = 2 AND
     PN.ph_pk_Parent = PT.pt_pk AND
     PN.ph_PhoneType = 4
  ORDER BY PN.ph_Rank
 Wink, ''),'(',''),')',''),'-',''),' ','') AS pt_MobilePhoneBrowse
 
FROM Patients AS PT

This view is then tied to the BO as the overriding tablename:

I will also add the search fields even though they do not exist as part of the original table structure:

Now I can go and do a search and I will be able to search on these foreign key fields.  For display purposes in the results, you can just pull back another field in the view that leaves the formatting...or add the formatting back in on the display.  I recommend the 1st option as it requires far less work.  Hope this helps!

By Larry Caylor - 6/12/2009

Thanks for the sample; got it working and it's a lot easier than what I was doing beforeBigGrin However there are a couple of quirks that would be nice if they could be fixed. First if you go back and edit a field that doesn't exist in the original table structure, the Search Field Criteria dialog doesn't "remember" the field name, it just defaults to the first field in the Search Field drop down. It also doesn't "remember" the Search Style which also defaults to the first field in the Search Field drop down. Second I was wondering if the DataType override is suppose to set the appropriate available Search Styles, currently it has no impact. To get the appropriate Search Styles when adding a view field, you first have to select a field of the same data type that exists in the original table as the view data type you're adding.
By Trent L. Taylor - 6/15/2009

First if you go back and edit a field that doesn't exist in the original table structure, the Search Field Criteria dialog doesn't "remember" the field name, it just defaults to the first field in the Search Field drop down. It also doesn't "remember" the Search Style which also defaults to the first field in the Search Field drop down.




Yeah, I noticed this as well and it is already on the list. Smile



Second I was wondering if the DataType override is suppose to set the appropriate available Search Styles, currently it has no impact.




No, this is for return types and casting. Most times this is never needed. We actually ended addressing some of the underlying issues that this caused within the class itself. But this just enforces the data type on the return values for testing purposes.