Dataviews in Pane Designer

To execute a script to fill values, for example into a drop-down, you can now choose from dataviews or create a new dataview. The dataviews will first populate in the drop-down for the script or default script. The tooltip for each dataview in the drop-down list is the information field that was entered when adding the dataview, or just “Dataview” if the information field is empty.

To the right of the drop-down list, select "..." to edit a Dataview or create a new one:

The editor for the Dataview is similar to the Dataview designer, except for the following differences:

• You cannot choose to only show text (not the ID) in the drop-down. This is a limitation of the pane drop-down but on other hand you can show more than two columns in the pane drop-down.

• The @variables you put in the SQL can be fetched from other drop-downs (the drop-down will depend on another drop-down).

• You cannot choose an object list. Instead, use dat.p_pane_objlists to get objects so authorities will be used.

When opening the editor you have two choices: make a SQL query or a Static list.

This topic contains the following sections:

• Using @variables in SQL

• Get Values from a Drop-Down with More Than One Value

• $CLIENT and $USER

• Example

• Make the Pane

Using @variables in SQL

When entering a SQL, you may want to get a value from another drop-down. To manage this, enter a @variable in the SQL in the same manner as you write a standard SQL, like:

selectobjval_id,

      descr

from   dat.object_values

whereobj_id=@obj_id

and   client_id=@client_id

When you've entered a @variable in the select, you can hover the Parameters tab to expand it and you will see that the @variable has been added to the list, as shown below:

In the text box, you can enter a value, $CLIENT, or $USER. The parameter values you enter here will be the default value when you select the script for a drop-down. If the parameter should be fetched from another drop-down, you manage that in the same way you would for stored procedures (see $CLIENT and $USER).

Get Values from a Drop-Down with More Than One Value

Due to the complexity of a SQL query, you must write your own SQL that handles drop-downs that can deliver more than one value (drop-down with check boxes). Bizview has some functions that can help you with this task.

If your table that the SQL will fetch values from is not too big, you can use the function app.f_eval_sqlsmart. This function will evaluate an expression and return 0 or 1 if a value matches the expression.

For example, can you write a SQL like this:

The@objval_idcan get a value from a drop-down that may deliver a series of ACCOUNT values (e.g. 3010#3020#3030).

If the table is not too big, the method above is a simple way to write the SQL. If you feel the drop-down takes too long to finish, you must write a dynamic SQL to make the drop-down execute more efficiently.

To accomplish the same result as above, but much faster, it can be done like this:

declare@wherenvarchar(max)

declare@sql   nvarchar(max)

---------------------------------------------------------------

-- Get the where critera. @where = 'objval_id in ('3010','3020','3030')

---------------------------------------------------------------

set@where=app.f_sqlsmart('objval_id',@objval_id)

----------------------------------------------------------------------------------

-- Make sure app.f_sqlsmart return any value and add an 'AND ' before the criteria

----------------------------------------------------------------------------------

if@where<>''

set@where=' AND '+@where

-----------------------

-- Put together the SQL

-----------------------

set@sql='select objval_id, descr from dat.object_values where obj_id = ''ACCOUNT'''+@where

------------------

-- Execute the SQL

------------------

execsp_sqlexec@sql

$CLIENT and $USER

If you want, you can use ‘$CLIENT’ and ‘$USER’ in the SQL.

select*

fromdat.objects

whereclient_id='$CLIENT'

To write a correct query, that you can run in SQL Management Studio, it's recommended that you make a SQL variable and set it to '$CLIENT' instead.

Example

If you don’t use $CLIENT or $USER in your SQL, you can test it in SQL Management Studio before you save it as a Dataview. If you have access to Management Studio, it could be a good practice to use Management Studio to get the SQL as you want it before you go to the Dataview Editor.

Note: The following example gets objects to a drop-down and is just for educational purposes. You should use dat.p_pane_objlists to do this.

Let’s say you have an object tree ITEMCLASS – ITEMGRP, and you want to make a drop-down that lists all ITEMGRP for a specific ITEMCLASS. The client_id should use the logged in client and the ITEMCLASS value should be chosen by the user from another drop-down. The SQL could look like this:

selectot.child_sqlshort as Itemgroup,

      ov.descr as Description

from   dat.object_treesot

      leftjoindat.object_valuesov

            onov.client_id=ot.client_id

                andov.obj_id=ot.child_obj_id

                andov.objval_id=child_sqlshort

whereot.client_id=@client_id

      andot.parent_obj_id='ITEMCLASS'

      andot.child_obj_id='ITEMGRP'

      andot.parent_objval_id=@parent_objval_id

Since client_id and parent_objval_id (ITEMCLASS value) are dynamic, you must add variables to these.

If you try to run this SQL in SQL Server Management Studio, you will get following error:

Msg 137, Level 15, State 2, Line 8

Must declare the scalar variable "@client_id".

All we have to do to make the SQL execute is declare those two variables in the beginning of the SQL (this behavior is only needed when we execute the SQL in Management Studio)

declare@client_id        nvarchar(50)

declare@parent_objval_id        nvarchar(50)

And set them to correct values:

set@client_id='1'

set@parent_objval_id='33'

Now, you can execute the SQL.

Make the Pane

Complete the following steps:

1. Open a new Pane in Pane Designer and add two Lists in the pane:

2. Name the first drop-down "ITEMCLASS" and the second "ITEMGRP":

3. For ITEMCLASS, click on the button to the right of the script combo and enter the following SQL:

selectobjval_id,descr

fromdat.object_values

whereclient_id=@client_id

        andobj_id='ITEMCLASS'

4. Click on the Save button to save the Dataview.

5. Below the script one parameter, @client_id, was created. Enter $CLIENT for the parameter value.

6. Select ITEMGRP and click on the button to the right of the script combo and enter the following SQL (see Example above):

7. Save the Dataview.

As shown below, two parameters have been created.

8. Enter $CLIENT for parameter @client_id. For parameter @parent_objval_id, select the black box to the right and choose ITEMCLASS. The parameter will be fetched from the ITEMCLASS drop-down:

Now, you can test the Pane and see if it works as expected.

If you don't choose an Itemclass, the Itemgroup list empty:

If you choose an Itemclass, you get the itemgroups that have that itemclass as parent: