The purpose of this article is to provide you with a step-by-step process on how to set up, manage and maintain a SQL data view.
Finding SQL Data Views
First, click on ‘File’ and then select ‘Setup’. Under the ‘SQL Data Views, you have three options:
- Market View,
- Product View, and
- Admin View.
In short, the Market/Product Views allow users to view and edit a selected set of information of a specified set of markets/products directly from the database. Since this information is displayed in a grid view, this allows for the standard grid functionality such as printing or exporting the information to CSV or excel formats.
The Admin View is similar to a Product View, except it caters more for a search or add functionality. Admin View also allows users to configure a set of fields which are then mandatory to be set when adding products to this view.
Setting up an Admin View
Click on ‘File’, then ‘Setup’ and select ‘Admin View’ under the SQL Data Views heading.
Doing so brings up the ‘Admin View Setup’ window.
To set up a new view, click on ‘New’ on the right-hand side of the window and begin adding your fields. To add fields, either double-click on the field, drag & drop the field or click on the arrow.
You will notice that you also can add ‘Audit Fields’. It is important to note that these fields must be populated when adding new SKU’s/Products to the database.
Once you have selected all your fields, click Save.
Setting Up A Market View
Click on ‘File’, ‘Setup’, and select ‘Market View’ under the SQL Data Views heading.
This will bring you to the ‘Market View Setup’ window. In order to set up a new ‘Market View’, click on ‘New’ on the right-hand side of the window. To add fields, either double-click on the field, drag and drop the field or click on the arrow.
Navigate to the fields within your database by using the section drop-down menu on the left-hand side of the window to narrow down the selection fields.
Under the top drop-down menu, if you were to select ‘Store detail’, only fields within this attribute would show. Once you are done adding your fields in the correct order, click ‘Save’.
Give your Market Drill a name and click ‘OK’.
Setting Up A Product View
Click on ‘File’, then ‘Setup’ and select ‘Product View’ under the SQL Data Views heading.
Repeat the same steps as previously mentioned when setting up a ‘Market View’.
View Data on SQL Database
If you want to view your data using the view you created, you need to open your SQL database.
First, ensure that you are in the ‘Data’ tab. Then, under the ‘Show’ section, locate and click ‘SQL’ as shown below.
Doing so brings up a dockable window on the left. Here, you can drill down to the data you want to view. Right-click and select ‘View Data/Images’.
Select the product view you created from the ‘Select Data View’ window that pops up. You can also double-click the product to bring up the window. Then click ‘OK’ to view your data. As seen in the gif below, the same process can be applied when wanting to open your ‘Market View’.
Editing an Existing Data View
First, go to ‘File’, then select ‘Setup’. Under the ‘SQL Data View’ heading, select the view that you’d like to edit.
Select the relevant view on the right-hand side drop-down menu. Then add or remove fields as required using the arrows. You can also drag and drop them.
You can also reorder your fields by selecting the field and shifting it up or down. Do so by clicking on ‘Shift Up’ or ‘Shift Down’. You can also drag and drop the fields or reorder them.
Once done, click ‘Save’.
This functionality makes it easy to view any data in the database in any view you wish. Creating a view to suit your requirements is easy to create and edit. Various views can also be created and saved depending on the type of work or data you wish to view.
Should the above steps not work for you, please create a support ticket and DotActiv support will assist you in uncovering the issue.