0 votes

Hi!

I have some basics questions, tell me if I think wrong:

  • In the DB, SYSENTITYVIEWS is composed of several SYSTABLEVIEWS
  • SYS_TABLEVIEWS store queries used in forms (consult and edit) and define field in the HandleContext (like Databse.ConsultDetail(...); GetDetailDataset(...) ) ?
  • SYS_QUERIES store queries used by list and grids ?

If I have wrong : What is the difference between SYSTABLEVIEWS and SYSQUERIES ? In which context one is used and not the other ?

  1. Why there is [columns] clause in SYS_QUERIES ? The columns used in the grids are defined in the macro (like TabContentProds --> GetRelationGrid)
  2. I can't find documentations in the EDN about the "Efficy SQL" used in these tables, does anyone have any ?

.
I ask that because I want to customise the products grid in DocuEdit (TabContentProds) :
In Efficy some of my products can be a "Batch" which is linked to other Products.

I want to add a column before the others with the batch name, the problem is that the good query uses 2 times the PRODUCTS table :

select batch.K_PRODUCT as K_BATCH, batch.NAME as BATCHNAME, Prod.K_PRODUCT, Prod.NAME, DP.K_RELATION, DP.QUANTITY, DP.PRICE, DP.DISCOUNT, DP.COMMENT, DP.VAT, DP.TOTAL
        from DOCU_PROD DP
        join PRODUCTS Prod on DP.K_PRODUCT=Prod.K_PRODUCT
        join PROD_PROD PP on PP.K_PRODUCT2=Prod.K_PRODUCT
        join PRODUCTS batch on PP.K_PRODUCT=batch.K_PRODUCT
        where DP.K_DOCUMENT=@docuKey

I tried to do it like this :
GridColumnsCustom.txt :

th.BATCH {[
    <th class="text-start nowrap">
            <%GetHeader("NAME", nolabel=T)%>
    </th>
]}
td.BATCH {[<td><a href="javascript:Consult('Prod',<#F=K_BATCH>)"><#F=BATCHNAME;emptytext=T></a></td>]}

MacroEditCustom.txt : (just add 'BATCH' to columns list)

TabContentProds {[
    <div class="content tabs-content-item" id="tab-prod">
        <div class="row">
            <div class="small-12 medium-1 columns">
                <button type="button" class="icon i-add <%IfReadOnly(detail='Prod', then='disabled')%>" title="<%GetLabel('Add Link to Product')%>" data-msg="addProduct"></button>
                <%AddRelationButton(entity='Prod')%>
            </div>
            <div class="small-12 medium-11 columns">
                <h3 class="section-title"><%GetLabel("Linked products")%></h3>
            </div>
        </div>
        <%OnMultiCurrency(0=, else=|<div id="currcyError"><span class="error"><%GetLabel("Warning: The currency must be set before adding products")%></span></div>|)%>
        <%Delay(1)%>GetRelationGrid(columndefs="GridEditColumns.txt", entity="Prod",
            columns="BATCH,FULLNAME,QUANTITY,PRICE,DISCOUNT,COMMENT,VAT,TOTAL,CONSO_RATE{HIDDEN},CONSO_TOTAL{HIDDEN},<%IfReadOnly(else=|SORTDELETE|)%>")%>
    </div>
]}

And add a row in SYSTABLEVIEWS (name=DocuProd; K_TABLE=31030; ISCUSTOM=1)

[SELECTFIELDS]
DOCU_PROD.*, PRODUCTS.NAME, PRODUCTS.OPENED, BATCH.K_PRODUCT K_BATCH, BATCH.NAME BATCHNAME

[SELECTFROM]
DOCU_PROD DOCU_PROD, PRODUCTS PRODUCTS, PROD_PROD PROD_PROD, PRODUCTS BATCH

[SELECTWHERE]
NULL

[SELECTWHEREFLD]
NULL

[SELECTJOIN]
DOCU_PROD.K_PRODUCT = PRODUCTS.K_PRODUCT, PRODUCTS.K_PRODUCT = PROD_PROD.K_PRODUCT2, PROD_PROD.K_PRODUCT = BATCH.K_PRODUCT

[SELECTORDERBY]
BATCH.NAME, PRODUCTS.NAME

One Query in SYSQUERIES : (KMASTER=31; K_DETAIL=30, Name='Docu: Linked Prod'

select batch.K_PRODUCT as K_BATCH, batch.NAME as BATCHNAME, Prod.K_PRODUCT, Prod.NAME, DP.K_RELATION, DP.QUANTITY, DP.PRICE, DP.DISCOUNT, DP.COMMENT, DP.VAT, DP.TOTAL
    from DOCU_PROD DP
    join PRODUCTS Prod on DP.K_PRODUCT=Prod.K_PRODUCT
    join PROD_PROD PP on PP.K_PRODUCT2=Prod.K_PRODUCT
    join PRODUCTS batch on PP.K_PRODUCT=batch.K_PRODUCT
    where DP.K_DOCUMENT=:param1

[Parameters]
0

[Options]
ChangeParameters=0
Distinct=0
NativeSql=1
asked in Other by (245 points)
edited by

1 Answer

+1 vote
Best answer

Some answers below.

SYS_QUERIES contains generic queries that can be multi-table and have complex join or filtering criteria. They are mainly used to retrieve lists of elements (e.g. the Actions linked to a Company) and are always read-only.

The [columns] section in SYS_QUERIES specifies the default columns of the query; as you say this value usually is overwritten in the Macro, so it's not very useful.

"Table Views" are simple views on a single database table and are used to retrieve the data for consulting or editing.
Table Views are grouped in "Entity Views" (defined in SYSENTITYVIEWS); an Entity View contains all the table Views that are used in a consult or edit window. For example the Document window uses the "DocuEdit" Entity View which contains Table Views on DOCUMENTS, FILES, DOCUUSER, DOCUCOMP, DOCUCONT, DOCU_PROJ etc.

The customization of the product list in the Document Edit should normally be done in the DOCU_PROD Table View, but what you want is too complex for the simplified view system.

A solution might be to add in SYSFIELDS a couple of virtual fields KBATCH and BATCHNAME to the table DOCU_PROD. The fields would use a database function (stored proc) to return the batch key or name.

First create the functions/stored proc in the database to return the key of the batch name for a given KPRODUCT.
Then add the appropriate lines in SYS
FIELDS for table DOCUPROD (KTABLE=31030). You can specify the USAGE 'P' (for "procedure") and the name of the function in LOOKUPASSOCIATE. The parameter passed in the function is defined in KLOOKUPTABLE and KLOOKUPFIELD; in your case these would be 31030 and 2 to pass DOCUPROD.KPRODUCT to the function.

Take a look at some other examples in SYS_FIELDS for which USAGE equals 'P', for example for the field USERS we run a function that returns the list of linked users.

I hope this helps,
Robert

answered by (836 points)
selected by
Welcome to Efficy Overflow, where you can ask questions and receive answers from other members of the community.
1,167 questions
1,425 answers
1,717 comments
325 users