0 votes


When I commit Document I have to update product on Opportunity :
- if a DocuProd is remove I want to remove it from the PRODOPPO
- if a DocuProd is update I have to update PRODOPPO
- if a DocuProd is added I have to insert it in the PRODOPPO

important notes :
- PRODOPPO have a FKDOCUMENT field which contains the KDOCUMENT to know which Document linked it

Unless I'm wrong, in the After or BeforeCommitDocu event, with GetDetailDataSet(EditHandle, ntProd) we are not able to know the removed relation, so, to remove the product on the Opportunity my workflow (example with Oppo as container) get:

  1. Trigger AfterCommitDocu(EditHandle)
  2. Check the container (ntOppo op ntProj based on the reference of the document)
  3. Get the container key and open the edit context (containerEdit)
  4. First I check the products to delete on the Oppo :
    4.1 Execute an SQL Query to get the products of the Oppo
    4.2 Iterate through the result Dataset
    4.3 To check if the product is not in the document I use PositionDetail(EditHanle) (cf workflow below)
  5. Link and update products of the document
  6. If the container is an Opportunity I update its BUDGET
  7. Commit the changes of the Oppo

I have just a problem in the 5th bullet point : PositionDetail always return false and at the and all the PROD_OPPO obtained with the request are deleted.
I think the CommitChanges calls all InsertDetails first, and then DeleteDetail..

Why PositionDetail is not able to return me true ? I checked with the Logger and and the KPRODUCT and KRELATION are correct. I can not find example of PositionDetail (edn, standard code, ...)

P.S : in DeleteProdOnContainer what is the better way to delete the record ? Database.DeleteDetail() or or the TDataSet.delete() method ?

Workflow :

// called by AfterCommitDocu
function GestionDocuProd(EditHandle, Inserted) {
  var containerHandle=0;
  var Ref = Database.GetFieldValue(EditHandle, 'REFERENCE');

  // update Oppo or Proj ?
  switch(true) {
    case /^D\d+$/.test(Ref) : // DEVIS
      // pas de traitement si Devis final
      if(Database.GetFieldValue(EditHandle, 'F_STATUS') == 5) {
      containerHandle = ntOppo;
    case /^IPC/.test(Ref) : // COMMANDE
      containerHandle = ntProj;

  // key of the container : Devis ==> Opportunity || IPC ==> Project
  var containerKey = Database.GetMainDetail(EditHandle, containerHandle);

  // Erreur si pas de container
  if(containerKey==0) {
    if(containerHandle==ntOppo) {
      throw new Error("This type of document must be linked to a container (devis = opportunity) / Le document doit être lié a un container (devis = Opportunité");
    else if(containerHandle==ntProj) {
      throw new Error("This type of document must be linked to a container (Commande / IPC = Dossier) / Le document doit être lié a un container (Commande / IPC = Dossier");

  // Document key and container Edit context
  var docuKey = Database.GetEditKey(EditHandle);
  var containerEdit = Database.OpenEditContext(containerHandle, containerKey);

  Database.CopyDetails(containerEdit, EditHandle, [ntCont, ntComp, ntProf], false, false);

  try {
    /* delete product on Oppo/Proj */
    DeleteProdOnContainer(containerEdit, containerKey, EditHandle, docuKey);

    /* Insert & Update product */
    UpdateProdOnContainer(EditHandle, Ref, containerHandle, containerEdit)

    if(containerHandle == ntOppo) {
      Database.CommitChanges(containerEdit, true); // persist link to update BUDGET

      // Get new BUDGET
      var SQLGetNewBudget = "Select Coalesce(Sum(TOTAL),0) as BUDGET from <#TABLE NAME='PROD_OPPO'> where K_OPPORTUNITY=:param1 and F_LOT <> K_PRODUCT";
      var result = ExecuteQuery(SQLGetNewBudget, containerKey);
      var newBudget = result.FieldByName('BUDGET').AsFloat || 0;

      // Update
      Database.UpdateField(containerEdit, 0, 'BUDGET', newBudget);

    Database.CommitChanges(containerEdit, false);
  finally {
function DeleteProdOnContainer(ContainerEdit, ContainerKey, DetailEdit, K_DOCUMENT) {
  var SQLGetProducts = "select * from <#TABLE NAME=\"PROD_OPPO\"> where K_OPPORTUNITY=:param1 and F_K_DOCUMENT=:param2";
  var params = ContainerKey+'\n'+K_DOCUMENT;

  var prodDS = ExecuteQuery(SQLGetProducts, params);

  if(!prodDS.IsEmpty) {

    while(!prodDS.EoF) {
      var k_product = prodDS.FieldByName('K_PRODUCT').AsFloat;
      var k_relation = prodDS.FieldByName('K_RELATION').AsFloat;
      var k_lot = prodDS.FieldByName('F_LOT').AsFloat;
      var k_opportunity = prodDS.FieldByName('K_OPPORTUNITY').AsFloat;

      var debugStr = 'RELATION OPPO_PROD ' + k_opportunity+'-'+k_product + '('+k_relation+') from Batch ' + k_lot

      // if current PROD_OPPO/PROD_PROJ don't exists on Document ==> delete on Oppo
      if(!Database.PositionDetail(DetailEdit, ntProd, k_product, k_relation)) {

        // What is better ? delete() or DeleteDetail() ?
        // prodDS.delete();
        //Logger.Write('Delete Oppo-Prod 136-' + k_product + '('+k_relation+')');
        debugStr += ' | DELETE'

        Database.DeleteDetail(ContainerEdit, ntProd, k_product, k_relation);
  else {
    Logger.Write('No product on container');
// insert and/or update PROD_OPPO from current DOCU_PROD
function UpdateProdOnContainer(EditHandle, Ref, containerHandle, ContainerEdit) {
  var docuProdDataSet = Database.GetDetailDataSet(EditHandle, ntProd);
  if(!docuProdDataSet.IsEmpty) {
    // fields to copy
    var FieldList = ["QUANTITY", "PRICE", "TOTAL", "F_LOT"];

    if(containerHandle==ntOppo) { // PROD_OPPO have more field to copy than PROD_PROJ
      FieldList = FieldList.concat(["RATE", "DISCOUNT", "VAT", "CONSO_RATE", "CONSO_TOTAL"]);

    while(!docuProdDataSet.EoF) {
      var prodKey = docuProdDataSet.FieldByName("K_PRODUCT").AsFloat;
      var relKey = docuProdDataSet.FieldByName("K_RELATION").AsFloat;
      Database.InsertDetail(ContainerEdit, ntProd, prodKey, relKey, false);

      // copy relation fields value
      for (var i = 0; i < FieldList.length; i++) {
        Database.UpdateDetailField(ContainerEdit, ntProd, 0, relKey, FieldList[i], docuProdDataSet.FieldByName(FieldList[i]).AsFloat);
      Database.UpdateDetailField(ContainerEdit, ntProd, 0, relKey, 'F_K_DOCUMENT', docuProdDataSet.FieldByName('K_DOCUMENT').AsFloat);  // --> which document have linked the product
      Database.UpdateDetailStringField(ContainerEdit, ntProd, 0, relKey, 'F_DOCUREF', Ref);  // --> easy way to get the Document reference in the Opportunity.Products grid
      Database.UpdateDetailStringField(ContainerEdit, ntProd, 0, relKey, 'COMMENT', docuProdDataSet.FieldByName('COMMENT').AsString);

  else {
    Logger.Write('No product on document');
closed with the note: solved
asked in How to by (245 points)
closed by

1 Answer

0 votes

PositionDetail should work, I can see no obvious flaw in your code.

Are you testing on an opportunity (devis)? The code initially selects either the main Project or the main Opportunity.
In what follows (DeleteProdOnContainer) only the "Opportunity" case is handled.

DeleteDetail() is the recommended method instead of Dataset.delete (but both methods will work).

answered by (836 points)
Yes, I was in the testing phase so the code was not suitable to work with Project  but, if containerHandle=ntProj, DeleteProdOnContainer would have thrown an exception because 'K_OPPORTUNITY' don't exist in PROD_PROJ

No errors were thrown and all PROD_OPPO with F_K_DOCUMENT=K_DOCUMENT was deleted, so containerHandle=ntOppo

To fix it I just changed the SQL query : I get only the links to delete. Better than loop in all products and check if we have to delete it or not
Welcome to Efficy Overflow, where you can ask questions and receive answers from other members of the community.
1,166 questions
1,424 answers
325 users