Updating report templates for use with Atrex 14+

Background

In order to implement some of the new features in Atrex 14, major changes had to be made to the Atrex database structures. These changes rendered reports designed for older versions of Atrex inoperable. To allow these older reports to run without changes, Atrex 14 introduced a compatibility mode that would alter older custom reports at print time to use table views that simulated the older Atrex database structure which allows a majority of custom reports to run in Atrex 14 and newer without modification. The price for this convenience is that custom reports run in compatibility mode are significantly slower than reports not run in compatibility mode.

When enabled, compatibility mode is applied to any custom report template that has not been modified in the Atrex report designer. Once a report has been modified within Atrex 14 or newer and loaded back into the program for general use, compatibility mode is NOT applied.

To regain maximum performance from custom reports, these reports must be modified to use the current database structure.

The biggest change within the table structure is that the individual payment amount fields along with the credit/debit/gift card fields have been moved to a separate payment table.  A more detailed list of the field changes is available in the KB article at http://support.1000years.com/KB/a17/atrex-14-database-structure-changes.aspx.  Reports that reference the payment fields on the main transaction tables need to be modified to retrieve the payment information from the payment table.


Reports with minor customization  

For custom reports that have only minor changes such as hiding a field or adding a logo, the fastest option is to re-customize the report in the newer Atrex instead of trying to convert it. Pull a copy of the standard report from the Standard Reports tab on the Report Maintenance window, modify it to include your customization, and then reload the report.

Custom reports that do not reference specific payment fields

Some custom reports may not be affected by the changes in the database structure. To test a custom report to see if it will run properly without modification, open it in the Report Designer and select the Preview tab. If it does not display any error message, simply save the report back down to the drive, close the Report Designer, and re-load the newly saved report back into Atrex.


Reports that only need total payment or remaining balance

In older versions of Atrex, retrieving the total amount paid on a transaction required a calculation to add all of the payment values together and then calculating the balance of a transaction required another calculation to subtract the total amount paid from the transaction total. In version 14, a new field was added to the transaction and AR tables named TotalPaid. This is the sum of the total paid for a transaction at the time it was created or last edited.

When converting reports for use with newer Atrex that only require the TotalPaid field, you can add this field to the list of selected fields on the dataview and then replace the calculation that adds all of the payment amounts with the TotalPaid field.


Transaction templates

Transaction templates (i.e., invoices, orders, service orders) are among the most difficult to convert as they require a significant amount of change to be compatible with Atrex 14 or newer. The following is a set of basic instructions on how to convert an invoice template. These instructions are based upon a the standard SLS5001 invoice template. Your layout may change depending on your customization and which template your report is based on. Converting other transaction templates such as sales order and service order templates is very similar, with the major differences in the instructions being table names.

Open the report to be converted.

Select the Data tab.

Edit the Primary dataview and add the TransType and TotalPaid fields from the main transaction table (invoice, ord, or so). Then remove all of the payment specific fields from the Selected Fields list. You can reference the database changes listed in the link above.

Create three new dataviews using Query Designer.
 
  • Create a dataview named CardDetails.  Select all of the fields from the Payment table.  Add the speed table linking Payment.Transnum to speed.number.  Set the search criteria to Payment.PaymentTypeInd In List with the value 2,3,4.   Add a sort field on Transnum. 
  • Create a dataview named PaymentDetails.  Select the TransType, TransNum, PaymentTypeInd, and PaymentType, fields from the Payment table. Add the speed table linking Payment.Transnum to speed.number.  Add a group on PaymentTypeInd, and add a Sum calc field on the Amount field. 
  • Create a dataview named ARPayments. Select the TransType, TransNum, and PaymentsTo fields from the AR table. Add the speed table linking AR.Transnum to speed.number.  Add a sort field on Transnum.
 Do the following for all three new dataviews.  Link the new dataview back to the Primary dataview on TransNum to Primary.Number.  Double click on the new link.  Add an additional link between TransType on both dataviews.  Change the link type to include all records from Primary.  Click on the SQL tab and select the Manual SQL option.  Click OK.

Select the Design tab and locate the DBMemo that displays the transaction notes. If this object is NOT named Notes, rename the object to Notes.

Locate and delete the following regions if they exist on your custom template: CreditCardRegion, SignatureRegion, and DebitCardRegion, TotalOnlyRegion, and AllPaymentsRegion.

Save your report to the drive. This will give you a partial conversion point to get back to if problems arise further on in the conversion.

To save a lot of time and effort manually creating objects and positioning them, it's best to simply copy several components from a standard report template. In a separate instance of the report designer, open up the standard Atrex template that your report was based upon. But please be aware that in some cases, drastically modified reports may be different enough to cause the report designer problems when copying and pasting the subreports. If you run into problems you may need to manually create the subreports and manually copy the objects across to your custom report individually. If you do have to copy the objects manually, please remember to go back and copy the calculations code for variable objects over to your custom report.

In the standard report, select the CardDetail subreport. Open the Edit menu and select Copy. Paste this subreport beneath the Notes in the custom report. Right-click on the subreport and select the ShiftRelativeTo option and make sure that it is set to shift relative to the notes DBMemo object.

In the standard report, select the PaymentDetails subreport along with the Paid and Prepaid labels and the TotalPaid and Prepaid variable objects. Paste these in the appropriate position in the footer of your custom report. As the calculation code doesn't copy from objects not inside of regions, copy and paste the calculations code for the Paid and Prepaid variable objects from the standard report into the custom report.

Click the Calc tab and set the view to Events.

Locate the following objects and modify the events associated with these objects as follows:

CardDetail - OnPrint: Add the following code:
CardDetail.Visible := (CompanyInformation['PrintCC']=True) and (Primary['CreditCard']<>0.00);

GroupFooter - OnPrint:
Add the following code (replacing code that references the Prepaid object if present):
  
Prepaid.Visible := (Primary['Prepaid'] <> 0.00) or (ARPayments['PaymentsTo'] <> 0.00);
PrepaidLabel.Visible := Prepaid.Visible;
 
if (Primary['Prepaid'] <> 0.00) and (ARPayments['PaymentsTo'] <> 0.00) then PrepaidLabel.Text := 'Prepaid/Payments:'
else if (Primary['Prepaid'] = 0.00) and (ARPayments['PaymentsTo'] <> 0.00) then PrepaidLabel.Text := 'Payments:'
else PrepaidLabel.Text := 'Prepaid:';


If present, remove the code that references the AllPaymentsRegion and the TotalOnlyRegion.

PaymentDetail - OnPrint: Add the following code:
PaymentDetail.Visible := CompanyInformation['AllPayment'];
 
Prepaid Object: Modify the calculation code for the object to:
Value := Primary['Prepaid'] + ARPayments['PaymentsTo'];
 
After you have made all of these changes, make sure to test your custom report before loading it back into Atrex for live use. Depending on how close your custom report is to the standard formats, you may need to do additional tweaks to the code for various objects in order to get the report to work properly.

 If you run into errors that you cannot resolve with regards to object names and/or variable objects that won't compile, you will need to close the report and start back with the copy of the report that you saved after deleting the region objects.