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.

Run a query

  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

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
  • $TEXT{text}
  • $NUM{number}
  • $QTY{quantity}
  • $DATE{date}
  • $RANGE{date_range}

  • $PART{Part}
  • $PROD{Product}
  • $LOC{Location}
  • $LG{Location_Group}
  • $BOM{Bill_of_Materials}
  • $CAR{Carrier}
  • $CUST{Customer}
  • $CG{Customer_Group}
  • $SHIP{Shipment}
  • $VEND{Vendor}
  • $SO{Sales_Order}
  • $PO{Purchase_Order}
  • $TO{Transfer_Order}
  • $MO{Manufacture_Order}
  • $WO{Work_Order}

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

  • All of the lookup parameters (the lower section of the parameter list) return the database ID. 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.
  • Because parameters don't have default values, any parameter used in the query will require a value to be entered (except for $TEXT).
  • Because parameters require user input, queries with parameters cannot be used in the Small.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 Small.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 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

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

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