If you have Sugar Cloud — Enterprise or higher — and are a systems administrator, you can write your own SQL statement to query the database. One example of when you would use this would be if you wanted to query a backend table. Here’s how to do it:
- Under Reports module in navigation bar, select Advanced Reports
- Under Advanced Reports module, select Create Custom Query
- Enter a name for your query and write your SQL select statement
- Save and run your query. You can export the results to a csv
Tip: If there is an error in your syntax, you will get a generic error message and the unsaved query will be lost. Copy what you have before saving so you don’t lose the work. Start simple and edit to add additional complexity.
Keep in mind that Sugar’s Reports module automatically takes care of a lot of things for you. For example, custom fields are in the _cstm tables not the standard tables – you will need to join to the corresponding _cstm table if your query uses any custom fields. You’ll also typically want to include deleted = 0 in your where clause to exclude deleted records from your query. You can download a copy of your database schema via Admin > Diagnostics tools, which can be helpful for writing queries.