We can use another technique to show details in windows and task panes using the SelectionChange handlers.
Let's switch to the payments-configuration workbook, select the EventHandlers worksheet and add the data:
The HANDLER_CODE of the Company Payments handler contains the code:
SELECT p.[Date] , p.[Sum] , c.Name AS Company , i.Name AS Item , p.Comment FROM dbo67.Payments p LEFT OUTER JOIN dbo67.Items i ON i.ID = p.ItemID INNER JOIN dbo67.Companies c ON c.ID = p.CompanyID WHERE p.CompanyID = @CompanyID
The HANDLER_CODE of the Item Payments handler contains the code:
SELECT p.[Date] , p.[Sum] , c.Name AS Company , i.Name AS Item , p.Comment FROM dbo67.Payments p INNER JOIN dbo67.Items i ON i.ID = p.ItemID LEFT OUTER JOIN dbo67.Companies c ON c.ID = p.CompanyID WHERE p.ItemID = @ItemID
As we can suppose, the add-in executes the specified code on the SelectionChange event.
The handlers use @CompanyID and @ItemID parameters accordingly to filter output data.
Let's switch to the payments workbook, select the Reports worksheet, and select the Payments (view) view.
If the view is already activated, click Reload, Reload Data and Configuration.
Now, when we change a row, the add-in launches windows like this:
We can select another row. Windows will stay on top and show related information.
You can click on the window status line to see the executed SQL command like this:
In this example, we have used the direct SQL codes stored in the EventHandlers table.
This is useful when you do not want to modify your database.
Otherwise, you can use stored procedures with parameters.
The SaveToDB add-in adds great support for editing codes in cells.
When the cell contains a multiline value, the add-in launches the Cell Editor like this:
You can edit the text in the editor and click the Save button to update the underlying cell.
You can turn on/off the editor using the Options, Show Cell Editor Task Pane option.
Use of detail windows is a good solution when you need windows that are always visible.
If you need context windows, you can use task panes.
Let's add the following handlers to the EventHandlers table for the uspPayments procedure:
I have copied the view rows, changed the view name, and added _TaskPane to the TARGET_WORKSHEET field.
The HANDLER_CODE codes remain the same.
Let's switch to the payments workbook, and change the query to the Payments (sp) procedure.
When we change a row, the add-in shows task panes like this:
Users can customize column formats:
Also, users can dock task panes and turn them on/off using the Options, Show Task Panes option.