Custom Reports: receiving error when using SQL statement to retrieve data from custom fields

I wanted to be able to create custom reports for the charity I help out and have created custom fields for the customer table and populated the table with sample data. When I run the query SELECT * FROM Customers WHERE Tuesday = “Yes", I get the error:

No property or field ‘Tuesday’ exists in type ‘<>f__AnonymousType219’ (at index 0)`

when using SQL statement to retrieve data from custom fields in Customer table. Should I be referencing the custom field in another way?

Custom reports is a work in progress. There is no documentation of how it works. There may be some other way to obtain the information you want. What are you trying to do?

1 Like

Hi, The charity’s customers are also members with attendance days, specific needs etc so I created custom fields to reflect this but we will need to list the clients based on the days they attend. I might be able to work around this by placing key information in existing Customer fields and using that as the selection basis of a query. It would also be lovely to be able to save the queries as new reports although I suspect that is something “under construction”.

You have not explained what information you are trying to retrieve. Instead, you have stated how you want to screen which customers are included, such as those who attend on Tuesday. Already, if you were to create a customer field, Attendance Day, and set it to show as a column, you could search the Customers tab list for Tuesday and restrict the display to those customers with Tuesday in their records. Export the list and print, if desired.

So, if you tell me what information you want, rather than who you want it for, there may already be a way to recover it without a custom report.

Apologies Tut, I thought reports were the only way to extract the information based on selection criteria.
I want a printed list of Name, Address, Telephone number filtered by attendance day and sorted by transportation method.

The reason why I have chosen to have a binary selection field (drop down list of yes or no) for each day of the week (Monday to Friday) is to assist the users in maintaining accuracy of data. A single field with a drop down list will not allow for multiple selections where the member attends more than one day and a simple text field lays open the opportunity for spelling mistakes to corrupt the selection results.

Does this make sense?

Yes, this all makes sense. Anything you can cause to appear in the Customers tab listing, whether by default or with custom fields, can be searched for. The list can then be exported. Or you can export it all. Regardless of what you export, you can import the TSV file to a spreadsheet and sort and filter however you wish.

Your problem will be that you are trying to use Manager as a customer relationship management database, and it was not meant to be one. So while you may get where you want to go, it will be a cumbersome process. Using an accounting system just to get printed lists of customers/members seems fairly tortured. But that is just my opinion.

Hi Tut,

I get your point totally but we are/will be using the accounting facilities of Manager as well, it’s a perfect fit in that respect. I agree wholeheartedly that if we wanted solely a CRM there would be better ways of achieving this.
I looked to expand the reporting functions of Manager to cover the few requirements they have for registration and transport requirement lists. Given custom fields can be created, this should be relatively simple if straight SQL SELECT statements were possible against the underlying database structure. It is a very rich feature to offer and I really look forward to using it when this feature is fully functioning.
The whole idea was that we should keep the master data in as few places as possible though the interim solution of exporting to spreadsheets to achieve what we want isn’t ideal, it is doable and, with a bit of thought and user guidance, I am sure I can find a solution which the key personnel can use and be comfortable with.
Thanks a lot for your help Tut, very informative :slight_smile: