I am a beginner, so be gentle.  I recently tried my first Browse Dialog and I am very impressed, but I want to develop a similar dialog that would allow the end user to select a specific record from a table based on criteria from several other tables.  For example, imagine a situation where we have a customer table, an invoice table, an invoice line item table, and a table containing shipping dates and addresses relating to the invoices.  I would like to use a modified Browse Dialog where the end user could enter the following criteria:

An invoice date range

A shipping date range 

A shipped to state

A product code (relating to invoice line items)

I want the end user to be able to select a specific customer record to edit from a list of customers where the list of customers is based on the criteria entered.

Hey Sam



I'm sure you'll get other answers but just to get you started, think in terms of creating a view in SQL much as you would for a report, denormalizing the data so you have a record for each line item that includes invoice and customer data. Do your search against this as all the fields will be available. Return a set that is a select distinct for the customer, if that's what your looking for and let the user select from that to return one customer record. Greg or Ivan will probably best advise as to where to intervene to return only one row for each distinct customer for the final selection but you can get started by just returning the entire result as that will work too, just will have multiple rows possibly for the chosen customer based on an invoice data range, for example.

I should add, just so there isn't any confusion, that my suggestion assumes that once you have the pk of the chosen customer you'll used that in ( event name escapes me and I can't look it up right but fires on closing the browse dialog ) to pull the customer record in the regular customerBO using FillbyPrimaryKey()



( you can also return a set of PKs and fill the CustomerBO using that - ask if want a set of customers and we can give you the code for filling based on a list of PKs returned from a browse dialog that is itself based on something other than the BO you are filling. )

Thanks Charles,

I wondered if the solution might be an SQL view, but I have never created an SQL view so now more questions come to mind.  Can the view be parameterized so that the only records in the view are records that match the criteria the end user entered?  What happens if one of the criteria is left blank by the end user?  So many questions and so little experience, but I am beginning to get the picture.

As far as fetching data, you can do pretty much anything with parameters on views you can do on tables - very much like remote views in VFP. Not a problem at all. In the case of the browse dialog, the sql query gets built from your params so only those you enter get used ( like VFE dynamic view params if you stuck around in VFE that long )



So to the browse dialog the view is just a table. So if you have an invoice date for 100 invoices ( denormalized to have the customer info as part of the invoice info ) and you pass the params of invoice date range and customer state you'll get back the rows of that view that match. After that you need the unique customer_pks ( i.e. one per customer even if they have 4 invoices in the return set.



You can display those customers to the user and let him pick one or you can fill the BO of the customers form with all the customers that match the BD pick.



Make sense?



( play with views a little in the Management Studio and you'll see how easy it is to create something the browse dialog can eat )

Charles,

Please excuse my ignorance, but I am confused about views.  Are you talking about views created in SQL Server where the query statement is actually stored in the database?  Or are you referring to building the SQL query from the parameters entered as needed but where the query statement is never actually stored in the database?

Assuming you are talking about ad hoc queries rather than formal SQL views, then I am confused about how a Browse Dialog can handle parameters relating to multiple tables that will be used in building the ad hoc query.  Aren't Browse Dialogs related to only a single business object and therefore only one table?

Sorry I am so slow, but I do appreciate any additional help you can provide.

No, I am talking about SQL Server objects, views that are actually a permanent part of the SQL database. Once you have that, you can create a SF BO just as you would for a table and use that as the source for your browse dialog. Since it has all the detail information you need to do your selection, it is one stop shopping for any params your browse dialog uses to build the query that will get your records.



Once you have those records, you also have the keys that identify the customers in the set. Now you can present the user with :



All the returned rows, in which the customer may be represented more than once, but if that isn't a problem, selecting a row selects a customer.

or

A row for each single customer in the result set so the user sees just customers and picks one

or

All the customers represented in the returned set being used to fill the CustomersBO.



So, important points are :



Customers' "child" info denormalized by using a SQL View ( not ad hoc )

Browse dialog using that view for the ad hoc query it builds from your params

result set identifies customer or customers

customer keys used to fill the customersBO, which is a regular table based BO.



This is not the out of the box implementation of the browse dialog, which at its simplest uses one table for both the search and final BO on the form, but it is the most flexible for doing what you want, which is to find customers based on "child" records of customers.