I am posting here a very basic “How To” regarding adding additional fields within an existing AyaNova CE 1.9.4.4 report.

Note this is also covered in the AyaNova CE Manual tutorial in the section “Customizing Reports” in the tutorial on creating a new report for use with AyaNova CE which uses the same steps when customizing.

The example below is based on that you want the Acct #: field of the client to show on the standard format workorder's Dispatch Report

1. To determine the field name associated with the Acct #: field for the Client, view the AyaNova Schema Documentation to find details on the clients table. You can also open the AyaNova database file SCDATA.SC directly using Access 2000SP1 or higher, open the clients table. The field acctnumber in the clients table corresponds to the Acct #: field.

2. The report wodispst.rpt is the report file that would be customized (also see the AyaNova Manual section "Customizing Reports" specifically page 243 for a list of report file names)

Open the report file wodispst.rpt in Crystal Reports 9 or higher and expand the Database Fields (within Field Explorer) available for selection

3. You will see that the field acctnumber is not an existing selectable field. If it was, it would display in the Database Fields, and you could just drag and drop it onto the design of the report.

As it is not presently selectable, you will have to add it to the query corresponding to the wodispst.rpt file in rptsmaster table and create a new ttx file, and associate that new ttx file so the report can use it.

4. Close the wodispst.rpt file

5. Open the AyaNova database file SCDATA.SC using Access 2000SP1 or higher and open the rptsmaster table

6. Find the query associated with the wodispst.rpt

7. Edit the query to include the client.acctnumber field – for example, before the RIGHT JOIN section of the query I have inserted the client.acctnumber field

PARTIAL PART OF THE QUERY BEFORE EDIT:

wo.closed, probs.brief, clients.mailaddress, clients.streetaddress, clients.city, clients.stateprov, clients.postal, clients.country, clients.bizphone, clients.extension, clients.technotes, clients.first, clients.last, probstat.notes AS STATUS, probstat_1.notes AS ITEMSTATUS, projects.name AS PROJNAME, projects.notes AS PROJNOTES FROM projects RIGHT JOIN ((((((labor RIGHT JOIN (((units RIGHT JOIN probs ON units.id = probs.unit) LEFT JOIN unitmodels ON units.model = unitmodels.id)

PARTIAL PART OF THE QUERY AFTER EDIT:

wo.closed, probs.brief, clients.mailaddress, clients.streetaddress, clients.city, clients.stateprov, clients.postal, clients.country, clients.bizphone, clients.extension, clients.technotes, clients.first, clients.last, clients.acctnumber, probstat.notes AS STATUS, probstat_1.notes AS ITEMSTATUS, projects.name AS PROJNAME, projects.notes AS PROJNOTES FROM projects RIGHT JOIN ((((((labor RIGHT JOIN (((units RIGHT JOIN probs ON units.id = probs.unit) LEFT JOIN unitmodels ON units.model = unitmodels.id)

This is a very simple insertion.

Another way is to do the following (especially if you are performing more complex query editing):

a.      log into AyaNova as the manager,

b.      open a standard format workorder, select PRINT, clickmark the SHOW QUERIES ON PRINT,

c.      select the Dispatch report to bring up the queries,

d.      copy the query after tokens replaced,

e.      close AyaNova,

f.       open the AyaNova database file SCDATA.SC

g.      create a new query

h.      paste the query you just copied into a SQL view

i.        view Design View so you can see the tables and what fields are presently there, and what joins are occurring

j.        add the clients table acctnumber field to the query by doubleclicking on the acctnumber in the clients table.

k.      View the SQL view

l.        You will see that now the clients.acctnumber is in the SQL query.

m.    You can either just directly edit the existing query in the rptsmaster table;

n.       or copy the edited SQL query and paste into the appropriate field for the wodispst.rpt in the rptsmaster table – BE SURE TO SUBSTITUTE the tokens in the query field in the rptsmaster table. Failure to do so will result in the report always displaying the same data as you copied into there. Information on tokens are outlined starting page 247 in the AyaNova Manual section “Customizing Reports”

QUERY DIRECTLY COPIED FROM SQL VIEW

SELECT "Cleaning - laser printer: $25.00" AS estrate, "< To be assigned >" AS scheduledtech, 0 AS esthours, IIf(IsNull([clients].[company]),[clients].[last] & ", " & [clients].[first],[clients].[company]) AS clientname, IIf(IsNull([clients].[streetaddress]),[clients].[mailaddress],[clients].[streetaddress]) & IIf(IsNull([clients].[city]),"",Chr(13) & [clients].[city]) & IIf(IsNull([clients].[stateprov]),"",", " & [clients].[stateprov] & IIf(IsNull([clients].[postal]),""," " & [clients].[postal])) AS address, "Work required: " & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) & "Unit: " & [nonclients].[company_person] & " " & [unitmodels].[description] & " (" & [unitmodels].[model] & ") SN: " & [units].[sn],"") AS probheader, "AyaNova Evaluation" AS compname, wo.notes AS wonotes, wo.clientrefnum, wo.clientcontact, wo.ourref, wo.prob_reported, wo.prob_found, wo.action_taken, wo.id AS wonumber, wo.created, labor.details, probs.notes, IIf(IsNull(contracts_1.name),[contracts].[name],contracts_1.name) AS contractinfo, IIf([probs].[unit]<>0,"Unit: " & [nonclients].[company_person] & " " & [unitmodels].[description] & " (" & [unitmodels].[model] & ") SN: " & [units].[sn],"") AS Equipment, wo.starttime AS [Booked for], wo.stoptime AS [Booked for END], wo.closed, probs.brief, clients.mailaddress, clients.streetaddress, clients.city, clients.stateprov, clients.postal, clients.country, clients.bizphone, clients.extension, clients.technotes, clients.first, clients.last, probstat.notes AS STATUS, probstat_1.notes AS ITEMSTATUS, projects.name AS PROJNAME, projects.notes AS PROJNOTES, clients.acctnumber FROM projects RIGHT JOIN ((((((labor RIGHT JOIN (((units RIGHT JOIN probs ON units.id = probs.unit) LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id) ON labor.link = probs.id) RIGHT JOIN ((wo LEFT JOIN clients ON wo.client = clients.id) LEFT JOIN clients AS headoffices ON clients.headoffice = headoffices.id) ON probs.wolink = wo.id) LEFT JOIN contracts ON clients.contract = contracts.id) LEFT JOIN contracts AS contracts_1 ON headoffices.contract = contracts_1.id) LEFT JOIN probstat ON wo.status = probstat.id) LEFT JOIN probstat AS probstat_1 ON probs.status = probstat_1.id) ON projects.id = wo.project WHERE (((wo.id)=22));

QUERY AFTER TOKENS REPLACED

SELECT "~ESTRATE" AS estrate, "~SCHEDTECH" AS scheduledtech, ~ESTHOURS AS esthours, IIf(IsNull([clients].[company]),[clients].[last] & ", " & [clients].[first],[clients].[company]) AS clientname, IIf(IsNull([clients].[streetaddress]),[clients].[mailaddress],[clients].[streetaddress]) & IIf(IsNull([clients].[city]),"",Chr(13) & [clients].[city]) & IIf(IsNull([clients].[stateprov]),"",", " & [clients].[stateprov] & IIf(IsNull([clients].[postal]),""," " & [clients].[postal])) AS address, "Work required: " & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) & "Unit: " & [nonclients].[company_person] & " " & [unitmodels].[description] & " (" & [unitmodels].[model] & ") SN: " & [units].[sn],"") AS probheader, "Evaluation" AS compname, wo.notes AS wonotes, wo.clientrefnum, wo.clientcontact, wo.ourref, wo.prob_reported, wo.prob_found, wo.action_taken, wo.id AS wonumber, wo.created, labor.details, probs.notes, IIf(IsNull(contracts_1.name),[contracts].[name],contracts_1.name) AS contractinfo, IIf([probs].[unit]<>0,"Unit: " & [nonclients].[company_person] & " " & [unitmodels].[description] & " (" & [unitmodels].[model] & ") SN: " & [units].[sn],"") AS Equipment, wo.starttime AS [Booked for], wo.stoptime AS [Booked for END], wo.closed, probs.brief, clients.mailaddress, clients.streetaddress, clients.city, clients.stateprov, clients.postal, clients.country, clients.bizphone, clients.extension, clients.technotes, clients.first, clients.last, probstat.notes AS STATUS, probstat_1.notes AS ITEMSTATUS, projects.name AS PROJNAME, projects.notes AS PROJNOTES FROM projects RIGHT JOIN ((((((labor RIGHT JOIN (((units RIGHT JOIN probs ON units.id = probs.unit) LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id) ON labor.link = probs.id) RIGHT JOIN ((wo LEFT JOIN clients ON wo.client = clients.id) LEFT JOIN clients AS headoffices ON clients.headoffice = headoffices.id) ON probs.wolink = wo.id) LEFT JOIN contracts ON clients.contract = contracts.id) LEFT JOIN contracts AS contracts_1 ON headoffices.contract = contracts_1.id) LEFT JOIN probstat ON wo.status = probstat.id) LEFT JOIN probstat AS probstat_1 ON probs.status = probstat_1.id) ON projects.id = wo.project WHERE (((wo.id)=~WOID));

8.                  Close the AyaNova database and Access entirely.

9.                  Log into AyaNova as the manager

10.              Open a standard format workorder

11.              Select PRINT and select the Dispatch report

12.              This will generate the ttx file

a.      Do note if a report is via the Reports screen, you would select the Generate ttx file button)

13.              Close AyaNova

14.              Open the wodispst.rpt file using Crystal Reports

15.              You now need to let Crystal Reports know to utilize the updated wodispst.ttx file

16.              Open the menu Database -> Set Datasource location

17.              You will see that an unedited wodispst.rpt file datasource was C:\data\projects\hermes\data\wodispst.ttx as this was the location when the report was first made by AyaNova development – you need to change this with the location of your updated wodispst.ttx file

18.              Under Replace:, select Create New Connection -> Field Definitions Only

19.              Browse to the location of your wodispst.ttx file which is located in the AyaNova database folder

20.              Now you need to add this new location of the wodispt.ttx to the list of selectable fields

21.              Open Database -> Database Expert

22.              Remove the existing Selected Table that is pointing to C:\data\projects\hermes\data\wodispst.ttx

23.              Make sure your location of wodispst.ttx file is highlight and add it to the list of selected tables

24.              Open the Field Explorer -> Database fields. You will see that acctnumber is now a selectable field that you can drag and drop where needed on the design of the report.

AyaNova Sales & Technical Support

http://www.ayanova.com