Preparation of information is equal for all thee kind of settings.
Settings for the SQL statement will return columns of the table (record set).
The program will display them as drop-down table in individual fields where you
will be able to pick from.
The base of setting the search procedure for items is in the generation of
the select statement which will return suitable information. Let us see the easiest way, which is predefined in
Pantheon.
select acIdent, acActive, acName, anRTPrice, anWSPrice, anWSPrice2, anPrtPrice
from tHE_SetItem
order by acIdent
This statement will display 7 columns, Item ID, Status, Name,
all possible prices.
Let us see, how to upgrade the statement so the table will have 8 columns. In
the 8 columns would display the first 20 characters from the technological
description of the item:
select acIdent, acActive, acName, anRTPrice, anWSPrice, anWSPrice2, anPrtPrice,
Left(cast(acTechProcedure as varchar),30) as TEH
from tHE_SetItem
order by acIdent
Until this point things are rather smooth, because you always prepare data
with its source is in one table. It is more difficult if you would like to
display data from more different tables, i.e.:
select M.acIdent,M.acName,M.anRTPrice,Z.anStock, Z.acWarehouse
from tHE_SetItem M
left join tHE_stock Z on Z.acIdent = M.acIdent
order by M.acName
The above statement is from the view of SQL queries totally OK, but it is useless
for selection tables.
 |
In select statements, you can use only one table!
If you would like to use data from different tables, you will for this
purpose have to define a view! |
For example, you would like to include search settings the items stock from
the wholesale warehouse. First, create a view which will select all desired data
in one record set:
Example of a View
create view dl_ IdentLookup
as
select M.acIdent, M.acName, M.acClassif, M.acSetOfItem, M.acActive, Sum(Z.anStock) as ZALOGA
from tHE_SetItem M
left join tHE_Stock Z on M.acIdent = Z.acIdent
and Z.acWarehouse = 'Veleprodajno skladišče'
group by M.acIdent, M.acName, M.acClassif, M.acSetOfItem, M.acActive
*This statement has one major problem - the warehouse from
which you are picking stock is predefined and this, cannot be changed. If you
change the name of the warehouse, you will have to change the view as well.
Once, the view is created, you can enter in the Select field the usual select
statement from the view that will select columns.
select acIdent,acName,anStock from _IdentLookup
order by acIdent