This data sheet will help you to create custom reports in Organise Pro. If you are a developer and make a report which might be useful to others, please let me (Shiela) know and I may be able to link to it from the peacockmedia website.
As a starting point I suggest duplicating one of the existing reports. Reports are in ~Library/Application Support/Organise/reports - simply by duplicating and renaming an existing report, it'll then appear in the report manager's drop-down list.
Selection
<key>selectfrom</key>
<string>orders</string>
contacts
itemspurchased (accesses the full list of all items appearing on orders. These are entirely separate records from items)
orders
items
actions (this is the time management table)
bills (this is the accounts management 'money out' table)
These are the selection criteria:
<key>where</key>
<array>
<array>
<string>status</string>
<string>!</string>
<string>Complete</string>
</array>
<array>
<string>balancepaid</string>
<string>=</string>
<string></string>
</array>
</array>
The inner arrays contain 3 elements which must be (in order):
fieldname - must correspond with a valid field name from the table you are using. See fieldname chart for possible values.
operator: possible values "=", "!" (exclamation mark means 'not equal') (these work with strings or numbers), ">", "<" ('greater than' and 'less than' - these work with numbers and dates)
comparator: value to compare with. Note that strings don't need quotes, and numeric values are still contained within <string> tags.
The comparator can be left empty, as in the example above. In this case it means: where balancepaid = ""
ie. where nothing has been entered in the 'balance paid' field
Totals
<key>totalcol</key>
<string>-1</string>
This indicates whether one of the columns should be totalled. If the value is -1 (as in this example) then no column will be totalled. If it contains a value, then the corresponding column will be totalled.
For a second column totalled, add
<key>total2col</key>
<string>4</string>
Columns
<key>colheadings</key>
<array>
<string>Name</string>
<string>Received</string>
<string>Datepaid</string>
</array>
The strings here are used as the column headings. The number of strings appearing here also sets the number of columns; in this case 3.
<key>colwidths</key>
<array>
<string>280</string>
<string>80</string>
<string>100</string>
</array>
The 3 strings here should be numeric and specify (in pixels) the width of the columns. The number of values shouild be the same as the number of column headings
<key>fields</key>
<array>
<string>title</string>
<string>deposit</string>
<string>depositpaid</string>
</array>
The strings here are the names of the fields which are to be displayed.
See Field names for possible values. Field names used should be taken from the table specified in "selectfrom" above
The number of items here should match the number of columns set above.
Date selection
<key>datefiltercol</key>
<string>1</string>
<key>collationqtycol</key>
<string>1</string>
If this key is present then the date selection buttons will be enabled. The number given must match a column containing a date. The user will be able to filter the results of this report by date.
Collation
<key>collationmatchcol</key>
<string>1</string>
<key>collationqtycol</key>
<string>3</string>
These keys determine which column is compared when collating, and which column is summed when items are collated.
An example of a list which isn't collated:
item1 3
item2 2
item1 1
The same list collated looks like this: (column 1 is the match column and column 2 is the qty column)
item1 4
item2 2
The Order record has a special field: 'one' which returns '1'. This can be used with collation for counting items - see the default report 'New vs Returning Customers' for an example.