From Fishbowl
Jump to navigation Jump to search

Icon.Data.png The Data module, located in the Reporting group, provides the ability to retrieve and export data from the Fishbowl database. All data can be viewed, but the database cannot be modified. The admin user will always have access to the Icon.Data.png Data module. Other users can be given access in the Icon.User Group.png User Group module. A query can be run, saved, exported, and scheduled. The query results can also be accessed through the Fishbowl API.

Run a query

  1. Open the Icon.Data.png Data module and click the New Button.png New button located on the module toolbar.
  2. Type any SQL query, for example, SELECT * FROM customer
    Data Export.png
  3. To see the results, click the Run Query Icon Button.png Run Query button on the toolbar to the right, or press Ctrl + Enter

The columns in the Data section can be dragged, sorted, and hidden.

Query parameters

The Icon.Data.png Data module supports several query parameters. Parameters make it easier to select data from the database. For example, instead of manually entering the ID of a customer directly into a query, a parameter allows the customer to be selected from a drop down list.

Query without parameters
Query without parameters.png

Query with parameters
Query with parameters.png

Query parameters table.png

Parameters are available in queries by using the following format: $CODE{Label|Default Value|Tab}

For example, $PART{Select_a_part|B201|Advanced_tab} would display the following when the query runs.
Query user input.png

These parameters are also available in the Icon.BI Editor.png BI Editor module by clicking the Insert report setting Icon Button.png Insert report setting button on the component toolbar. This provides a graphical input to customize parameters using the same format as above.
Insert Report Setting.png

Below are some examples of queries that use parameters.

SELECT * FROM part WHERE datelastmodified BETWEEN $RANGE{Modified_Date}
SELECT * FROM location WHERE locationgroupid = $LG{Location_Group}
SELECT * FROM customer WHERE id = $CUST{Select_Customer|27|Customer_tab}


  • Most of the parameters return a record ID from the database. For example, $CUST will insert the ID of the selected customer into the query, not the NAME of the customer.
  • The text between the curly braces { } is the label that will be displayed next to the field.
  • Make sure to use curly braces { } around the label and not parentheses ( ).
  • The text in the label can be modified to provide a more informative description. Use an underscore _ to display a space in the label.
  • The default value for a parameter can be included by adding a pipe character and then the value to enter in the field. For example, $PART{Select_a_part|B201} or $CUST{Customer|27}
  • Most parameters cannot be left blank. If a wildcard is used and no input given, the query will not return results. For example, SELECT * FROM customer WHERE id = $CUST{Customer|%} will run but will not return any results unless the user selects a customer.
  • Parameters can also be separated into tabs by including a tab name after the default value. For example, $PART{Select_a_part|B201|Advanced tab} or $PART{Part||Advanced tab}
  • If you want to give a $LIST a default value, you can add the ^ character before the default value. For example $LIST{Select_From_List|Apple,^Orange,Banana}.
  • Because parameters require user input, queries with parameters cannot be used in the Icon.Schedule.png Schedule module.

Save a query

  1. To save a query for reuse, enter a name in the Name field. The description is optional.
  2. Click the Save button on the module toolbar, or press Ctrl + S

Filter a query

  • The data returned from a query can be further filtered by clicking on the right side of any column header.
    Data module filter.png
  • Click (All) to select or unselect all entries.
  • Click an individual entry to show or hide it in the results.
  • Click (Custom...) to add a custom filter to the results.
    Data module custom filter.png
    Possible filters include is anything, is, doesn't equal, is in, isn't in, is empty, is not empty, begins with, ends with, contains, and doesn't contain.
  • When data is exported, only the filtered data that is currently visible in the table will be exported.
  • Filters are temporary. The next time the query is executed, the filters will be reset.

Export a query

  1. After running the query and applying any filters, click the Export to .xls Icon Button.png Export to .xls button or the Export to .csv Icon Button.png Export to .csv button.
  2. Choose where the file should be saved and then click Export.
  3. Browse to the file and open it with a spreadsheet application or a text editor.

Copy data

The text in the Data section can also be copied and pasted into a text document or a spreadsheet.

Copy data.png
  • To copy a single cell, double-click the cell and then press Ctrl + C.
  • To copy a single row, click the row and then press Ctrl + C.
  • To copy all rows, press Ctrl + A and then press Ctrl + C.
  • To copy consecutive rows, click and drag the mouse over the rows and then press Ctrl + C.
  • To copy non-consecutive rows, hold down the Ctrl key while clicking each row and then press Ctrl + C.

Query history

  • To move through the history of executed queries, click the Previous Icon Button.png Previous button or the Next Icon Button.png Next button on the toolbar to the right.
  • The history is deleted each time the module is closed.
  • The number of items stored in the history can be set in the Icon.Data.png Data module options.
  • The history only displays queries that have been executed. To undo a typing mistake, press Ctrl + Z

Schedule a query

  1. Open the Icon.Schedule.png Schedule module and click the New button.
  2. Select Data and then click Next.
    Scheduled Task Type 1.png
  3. Select a saved query, enter the required information, and then click Next.
  4. Set the scheduled time and finish the wizard.

Extract a query

The Fishbowl Server can log many of the queries that are run when a user is using Fishbowl. It is often helpful to start with a query from a report and then modify it as necessary. Below are the steps for extracting a query for inventory quantities.

  1. In a Fishbowl Client, run the Inventory Availability report.
  2. Open the Icon.Data.png Data module and click the Server Log Button.png Server Log button.
  3. Click the Reports tab.
  4. Scroll to the bottom of the tab and copy the query starting right after SQL query string: until the end of the file.
  5. Paste the query into the Icon.Data.png Data module.
  6. Use the parameters that are listed below the query to replace the ? symbol.

Below is an example of how to modify the query. After completing the modification, press the Run Query Icon Button.png Run Query button.

Inventory Query Diff Before.png
Inventory Query Diff After.png

Database tables

Click the Tables tab to see a list of all of the tables, fields, and data in the Fishbowl database. Double-click a table name to see it in a separate window.

Database tables.png

A database dictionary can also be downloaded by clicking here.

Server log

The Server Log Button.png Server Log button on the Icon.Data.png Data module toolbar shows the recent activity of the server, including any errors. The buttons in the window allow the data to be refreshed, copied, or sent to Fishbowl.

Server log.png

The report queries are stored in a separate file and can be viewed on the Reports tab.


Some aspects of the Icon.Data.png Data module can be customized by opening the module, clicking the Tools menu, and then selecting Module Options.

General tab
Data Module Option General.png
  • Query History Size
Sets the number of queries that are stored in the query history.
  • Max Server Log Lines
Sets the maximum number of lines to display when clicking the Server Log Button.png Server Log button on the module toolbar.

Logging tab
Data Module Option Logging.png
  • Server Logging Detail Level
Select the amount of data that is logged. The server log can be viewed by clicking the Server Log Button.png Server Log button at the top of the module.