Data

From Fishbowl

Jump to: navigation, search

Icon.Data.png The Data module, located in the General 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 Small.Data.png Data module. Other users can be given access in the Small.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.

Contents

Run a query
Jump to top of page

  1. Open the Small.Data.png Data module and click the 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
Jump to top of page

The Small.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

Below is a list of the available parameters.
Query parameters.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}

Notes

Save a query
Jump to top of page

  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
Jump to top of page

Export a query
Jump to top of page

  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
Jump to top of page

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

Copy data.png

Query history
Jump to top of page

Schedule a query
Jump to top of page

  1. Open the Small.Schedule.png Schedule module and click the New button.
  2. Select Data and then click Next.
    Scheduled Data Export.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
Jump to top of page

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 Small.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 Small.Data.png Data module.
  6. Use the parameters 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
Jump to top of page

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
Jump to top of page

The Server Log Button.png Server Log button on the Small.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.

MODULE OPTIONS

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

General tab
Jump to top of page
Data Module Option General.png
Sets the number of queries that are stored in the query history.
Sets the maximum number of lines to display when clicking the Server Log Button.png Server Log button on the module toolbar.
Logging tab
Jump to top of page
Data Module Option Logging.png
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.
Personal tools
Namespaces
Variants
Actions
Navigation
Other Pages
Toolbox
Print/export
Translate