Yesterday, a customer asked me how to handle a single record "Company" table. The single record in this table contains information about the Company that is using the software, like the Company Name and the Address. So, if we here at Stonefield were looking at our entry in that table, the Company Name field would contain "Stonefield Software Inc.", and the Address field would be "2323 Broad Street".
The question becomes, "How do we get this information into our reports?". When I first ran across this situation, my initial reaction was to simply create a join/relationship with that table and every other table in the database. There are a couple of problems with doing it this way:
- We don't have a field in the table to join on, so the join would need to be an expression that evaluates to true.
- If the database has a large number of tables, creating a relationship with all of them would be impractical.
- Having to include this company information in the data selection step of Stonefield Query's report wizards would be very clumsy. The information would repeat itself over and over again at the detail level. Information like this generally belongs in the header of a report.
A better way to handle this situation is to use a script that looks this information up for us and stores it in a global variable. Here is an example of such a snippet from a GetCustomMetaData script:
public gcAddress, gcCompanyName
local lnSelect, loDatabase, lcSelect
*store the current work area
lnselect = select()
*get a reference to the database we are currently connected to
loDatabase = SQApplication.DataEngine.Databases.GetMainDatabase()
lcSelect = "select CompanyName, Address from Customers"
*send the SQL statement to the database
loDatabase.ExecuteSQLStatement(lcSelect, .NULL., 'curCompanyCursor')
*store the address and company name we just retrieved
gcAddress = curCompanyCursor.Address
gcCompanyName = curCompanyCursor.CompanyName
*restore the workarea
Once this is done, we have two global variables (gcAddress and gcCompanyName) that we can reference from the output expression of a field object in the advanced report designer. We could even reference these from a template, and have company information like name and address appear in the header of every report by default.