As treasurer of a social club I would like to be able on a regular basis to extract a report for our members(customers) showing the following fields:
- Given Names
- VMAA Number - (a custom field set up by myself to record a member register No.)
- Cash Receipt Date - (available from Statement but for 1 member at a time only)
- Cash Receipt Amount - (available from Statement but for 1 member at a time only)
Is it possible that this can be done? If a SQL report is required could you help me out with the syntax and DB fields I would need to access/exact from, or is there another way.
I would like this report to be exportable in .tsv format.
Any help MOST appreciated.
Box Hill, Melbourne
Are all those fields (surname, given names and VMAA Number) entered on cash receipt?
On the customer statement report, it shows:
Debit Date and amount for the invoice
Credit Date (receipt date) and amount received.
It does not show the Custom Field “MAAA Number” which is set at Position 1 on the Customer File (I had previously said “VMAA” but it is in fact “MAAA” in the field I am interested in. Sorry, my error).
Therefore in answer to your question, I receipt funds via the Debtors Invoice “Receive Money” option and the invoice itself includes the above fields (including the MAAA number being the custom field in position 1 on the invoice). Receipt Date & $ amount are entered as the second step of this option.
Am I correct in understanding that you want a single report that shows the listed information for all members in one list?
Because if all you are trying to do is get your MAAA number on a statement, you could put that into the address block and it will appear along with the address. The Transactions option for customer statements shows debits and credits.
Yes I need a single report showing all the listed information for all paid up members.
The background is that we are required to provide this information to the State Co-ordinating body before they will insure us for our activity which is flying RC model aircraft. There is a substantial Public Liability insurance issue with regard to this activity and it is managed at State level. To date we have been cobbling this together for 220 members manually via Excel by drawing data from reports in the Manager system but this is not feasible in the long term as our club grows and attracts more members.
It’s great software and a credit to you but, like all systems, it can’t provide for all user needs off the shelf.
No credit to me. I’m a user like yourself.
I can’t think of a way to get a report like that. Maybe you’ll be able to do it in custom reports, but there isn’t any documentation on that yet. However, if all the necessary data is in the file somewhere, I’d bet it can be done. I just don’t know how.
Could you tell me the data fields and where they are in the schema that I would need and I will have a go at writing my own custom SQL report to cfreate this report.
Here is a thread that you might find useful. SQL schema for creating custom reports
“SHOW COLUMNS FROM Customers” is probably going to give you the information that you need.