| by Ted Sturr |
Over the past few weeks I have had several requests to design some unique reports and in each of these cases using SQL Views was the selected solution. Before I explain when you might want to use a SQL View I should first explain what they are. |
As the name implies a SQL View is just a way to view (or display) your SQL data. What is special about a view is that one view can contain data from several tables, different databases as well as including summary information. Another nice feature of Views is they are objects in your SQL database, which means you can control who has rights to see that information. |
| For the sake of this article we are going to cover a couple of scenarios that I have run into where a View was the perfect solution. The first case we will examine is the Sales Order report. |
We all know that the Sales Order information is held in a couple of different tables. The header information is in the SOFOM table and the details are in the SOFOD table. What if you wanted to display a summary listing of Sales Order data including the sum of the Sales Order? Using the built in tables you would have to group the records by the SO_ID and sum up the Sales Order Detail information. But with a SQL view you can quickly provide this information in a Crystal Report or within an external application such as SalesLogix. |
| I ended up writing this SQL View to give me the information that I needed: |
|
Now inside my Crystal report I can use this View in place of the SOFOM and SOFOD table. I am still able to use the Report Selection Criteria to only show Open Sales Orders, or whatever other criteria that I choose. |
Another situation I ran into was that I needed to link together data from two different database’s for a report. If you have ever tried to link two different data sources in a Crystal Report you know that this can get to be a messy proposition. My solution was to create a view that linked the data together and then use that view in my report. What I needed to join was our CRM solution (SalesLogix) with our ERP solution (Expandable). The CRM system had the marketing segment data that we use to identify if a customer is a Medical office, Spa / Salon, or Other Medical site. We needed to use this information joined with our sales data inside of Expandable. |
| The view I ended up creating was this: |
|
|
This is just a quick overview of just two situations where I used Views to give me access to Expandable and external data in unique ways. I will be posting some follow-up notes on the user group message board with more detailed explanations. |
| If you have any questions on using Views or general SQL query questions please feel free to post them on the user board or if you want you can email me directly at tsturr@palomarmedical.com. |
  |
| The current economic slump is causing more suppliers to ask for partial or full up-front payment of orders. In Expandable those types of transactions are called prepayments and you can use either a Handwritten Checks Entry or Check Print to make the vendor prepayment. |
The following support note walks through two different prepayment scenarios – Prepayment on an Inventory Part and Prepayment on an Expense Item.
|
 How to Process AP Prepayments, Wire Transfers, COD, Etc.
|
|
| Expandable offers a free introductory training class to all new users of the system. The "Expandable for New Users" class is held at the beginning of each month and is targeted to new hires who will be using Expandable at existing customer sites. |
| The two-hour class is presented online using LiveMeeting and features the same great instructor-led experience as the rest of the Expandable classes. |
| Registration is available through the Expandable website. Visit the class schedule for times and dates. |
  |
|