0 votes

Hi all,

I put my customization at the bottom
Some product can be a batch (Product that contains other Products) (PRODPROD : KPRODUCT = Batch, K_PRODUCT2 = Simple product)
Note : a Batch have FAMILY = 1, other product have other value (can be empty)

I customized the DocuEdit window : when a product is linked, the WF checks if FAMILY=1 and then it link all products to the Document
--> DOCUPROD have a custom field "FLOT" that contains the K_PRODUCT of the Batch (can be NULL too)

Important to notice :
- I configured LKPRODFAMILY.KSORT to help me to order the display :
ID | K
SORT | FAMILY
1 | 4 | Batch
2 | 1 | Presta
3 | 2 | Option
4 | 3 | Expense

I want to customize the grid (macros) and the display order of products on DocuEdit like this :
Products of Batch 1 (Family <> 1; FLOT=KPRODUCT of Batch 1) order by NAME
Batch 1 (Family = 1; FLOT = its KPRODUCT)
Products of Batch 2 (Family <> 1; FLOT=KPRODUCT of Batch 2) order by NAME
Batch 2 (Family = 1; FLOT = its KPRODUCT)
Product directly linked to Document (F_LOT = NULL) order by NAME

To do this I have to customize SYSTABLEVIEWS.DocuProd (K_TABLE = 31030). The pure MS SQL I need is :

select Prod.NAME, Prod.FAMILY, DP.*
    from DOCU_PROD DP
    join PRODUCTS Prod on DP.K_PRODUCT=Prod.K_PRODUCT
    join LK_PROD_FAMILY family on Prod.FAMILY=family.K_PROD_FAMILY
    where DP.K_DOCUMENT=@DocumentID
    order by case when DP.F_LOT is null then 1 else 0 end, /* product linked directly to Document at bottom (F_LOT=NULL) */
        DP.F_LOT,   /* order by Batch */
        family.K_SORT, /* family.K_SORT=1 --> Presta, family.K_SORT=2 --> Option, family.K_SORT=3 --> Expense, family.K_SORT=4 --> Batch */
        Prod.NAME /* the products are listed by NAME */

I have 2 problems :

1) USE CASE IN SYSTABLEVIEWS :
To use "CASE" in SYS
TABLEVIEWS I translated it like this :

SELECTFROM='DOCU_PROD DOCU_PROD, PRODUCTS PRODUCTS, LK_PROD_FAMILY LK_PROD_FAMILY'
SELECTFIELDS='DOCU_PROD.*, PRODUCTS.NAME, PRODUCTS.OPENED, PRODUCTS.FAMILY'
SELECTJOIN='DOCU_PROD.K_PRODUCT = PRODUCTS.K_PRODUCT, PRODUCTS.FAMILY=LK_PROD_FAMILY.K_PROD_FAMILY'
SELECTORDERBY='<#IFDB DB="MS" THEN="case when DP.F_LOT is null then 1 else 0 end, ">DOCU_PROD.F_LOT, LK_PROD_FAMILY.K_SORT, PRODUCTS.NAME'

Problem 1 : SYSTABLEVIEWS don't support neither pure MS SQL (case) nor IFDB query tag. How can I use CASE in SYSTABLEVIEWS ?

I tried to use this query in a RunQuery in MacroEditCustom.txt and call it in TabContentProds, but I can't : this request looks directly in the DB, but when a user add a product, the application don't persist the relations in the DB until the user clicks the save/apply button, so the DocuEdit is refresh but the user can't see nothing.

2) Use FAMILY and F_LOT to define style
To insert the products on the Document, the WF use InsertDetail() and some UpdateDetail() without CommitChanges (if I use it, the relations will be persisted and the "cancel" button don't delete these links)

Problem 2 : the overriden columns definitions use F_LOT and FAMILY to set css classes, etc. It seems hat Efficy can't find FAMILY until the relations have not been commited.
enter image description here
On the screen :
- FAMILY = R_FAMILY ; LOT = F_LOT ; FAM = LK_PROD_FAMILY.K_SORT
- The first 3 lines are already in the DB, and F_LOT=39, so they must be ordered by FAM
==> why '39 - Lot 2' is the first line and not the third ? After search a bit, It like Efficy sort them by K_RELATION and ignore SELECTORDERBY
- others have not been comitted in the DB, so Efficy can't find the value of FAMILY
==> how can I set the my css classes in macros ?

CUSTOMIZATIONS :
Current SYSTABLEVIEWS.DocuProd :

update SYS_TABLEVIEWS
    set ISCUSTOM=1,
    SELECTFROM='DOCU_PROD DOCU_PROD, PRODUCTS PRODUCTS, LK_PROD_FAMILY LK_PROD_FAMILY',
    SELECTFIELDS='DOCU_PROD.*, PRODUCTS.NAME, PRODUCTS.OPENED, PRODUCTS.FAMILY, LK_PROD_FAMILY.K_SORT FAM',
    SELECTJOIN='DOCU_PROD.K_PRODUCT = PRODUCTS.K_PRODUCT, PRODUCTS.FAMILY=LK_PROD_FAMILY.K_PROD_FAMILY',
    SELECTORDERBY='DOCU_PROD.F_LOT, LK_PROD_FAMILY.K_SORT, PRODUCTS.NAME'
    where NAME='Docu_Prod'

InsertBatchChildren()

function OnInsertDetailDocuProd(EditHandle, Detail, DetailKey) { 
  InsertBatchChildren(EditHandle, Detail, DetailKey) 
}
function InsertBatchChildren(EditHandle, Detail, BatchKey) {
  var prodConsult = Database.OpenConsultContext(ntProd);
  try {
    // get family of current product
    var batchDS = Database.Consult(prodConsult, BatchKey, false),
    family = batchDS.FieldByName("FAMILY").asInteger;

    // don't do anything if it's not a Batch
    if (family != GLOBAL.LK_PROD_FAMILY.BATCH) return;

    // else set its F_LOT and FAMILY and link its children
    Database.UpdateDetail(EditHandle, ntProd, 0, -1, "F_LOT", BatchKey); // define Batch F_LOT
    Database.UpdateDetail(EditHandle, ntProd, 0, -1, "FAMILY", family); // define Batch F_LOT
    //TO DO : define K_SORT Database.UpdateDetail(EditHandle, ntProd, 0, -1, "K_SORT", int); // define Batch K_SORT

    var queryHandle,
        childrenDS = Database.ConsultDetail(queryHandle, prodConsult, BatchKey, ntProd, false, true, 0);

    childrenDS.First;
    while (!childrenDS.Eof) {
      var k_product = childrenDS.fieldByName("K_PRODUCT").AsFloat;
      Logger.Write("Inserting " + k_product + " from " + BatchKey);
      Database.InsertDetail(EditHandle, ntProd, k_product, -1, false);                                    // insert Batch child on Document
      Database.UpdateDetail(EditHandle, ntProd, 0, -1, "NAME", childrenDS.fieldByName("NAME").asString);  // define child NAME
      Database.UpdateDetail(EditHandle, ntProd, 0, -1, "F_LOT", BatchKey);                                // define child F_LOT
      Database.UpdateDetail(EditHandle, ntProd, 0, -1, "COMMENT", "Inserted from WORKFLOW");              // define child COMMENT

      // Update FAMILY in DOCU_PROD
      var childFamily= childrenDS.fieldByName("FAMILY").AsInteger; // ERROR : "Field 'FAMILY' not found"
      Logger.Write("childFamily="+childFamily);
      Database.UpdateDetail(EditHandle, ntProd, 0, -1, "FAMILY", childFamily);

      childrenDS.Next;
    }
  } finally {
    Database.CloseContext(prodConsult);
  }
}
asked in How to by (245 points)
edited by

1 Answer

0 votes

In the OnInsertDetailDocuProd workflow event, loop through the detail dataset of Product and manipulate K_SORT in such a way that the products will be sorted according to your desired logic. To speed up, instead of consulting PRODUCTS each time to know the family, you could insert the FAMILY value as well in the Docu_Prod table. If the column in DOCU_PROD has the same name as in PRODUCTS, I believe it's automatically filled when inserted into the document.

The interface will sort automatically based on the sorting value.

answered by (6.8k points)
Thanks for your answers
I try to do it :
var consProd = Database.OpenConsultContext(ntProd),
var dsProdProd = Database.ConsultDetail(queryHandle, consProd, BatchKey, ntProd, false, true, 0);
Database.UpdateDetail(EditHandle, ntProd, 0, -1, "FAMILY", dsProdProd.fieldByName("FAMILY").AsInteger);

and it throws "Field 'FAMILY' not found"
After search it's "dsProdProd.fieldByName("FAMILY").AsInteger"

In which query can I add "PRODUCTS.FAMILY" to get this field with ConsultDetail() ?

And how UpdateDetail() can update "NAME" ? It's not a field (neither virtual nor normal) in PROD_PROD ?
You have to add the field FAMILY via Designer in DOCU_PROD, else it will not work. The NAME field is a temporary column that is added by Efficy in the detaildataset because it's typically required to display the name. It's all based on the TableView configuration.
Ok, so that is why "dsProdProd.fieldByName("FAMILY").AsInteger" does not work.
I added "InsertBatchChildren" in the first post
To set the FAMILY in DOCU_PROD, I have to get FAMILY from PROD_PROD, but if I understood what you just told me, I can not get FAMILY in GetDetailDataSet() so I must open the consult context in the"while"
Because opening the consult context in the while is a bad idea, that's why I suggested to add the field FAMILY to the table DOCU_PROD with Designer. When you add it, it will be added as F_FAMILY but you can change that afterwards in SYS_FIELDS to FAMILY and manually in the DB using an alter table.
I believe that if the column in DOCU_PROD shares the same name with the column from PRODUCTS, it will be initialized automatically.

Imo, you don't have to configure anything in the SYS_TABLES views and certainly nothing in PROD_PROD.
My problem is in InsertBatchChildren (you give me in an other Question and I change it a bit).
Open a Consult context for Products, consult the current linked Product and get its family.
If it's a Batch : Consult product detail of batch with the same context
==> I can not get the family because of ""Field 'FAMILY' not found"" (look at the first post)


ConsultDetail() get the masterdataset of all products linked to the batch or the detaildataset ?
Welcome to Efficy Overflow, where you can ask questions and receive answers from other members of the community.
1,166 questions
1,424 answers
1,715 comments
325 users