0 votes

Hi all!

I wanted to train me with scripting (import data from Excel file with Data Synchro)
Excel:
Company;External ID;COMPANY;Contact external Id;FIRSTNAME;LASTNAME
1 | COMP & CIE | 1 | BERNARD | A.
1 | COMP & CIE | 2 | BART | B.
2 | IKEA | 3 | ABDUL | C.
2 | IKEA | 4 | WORLD | D.

Objectives: insert this data in the DB, link the Contact to the Company, and link all these new entities to the same Publication

this script
I have some questions:
- Does my script respect good practices
- I have difficulties to understanding the difference between Relation (with AddLink2) and Details (with InsertDetail2). For me Detail is a "type" of relation
A Detail is the sub Entity in the selected Entity right?
- in InsertDetail2 what does the last parameter?? I don't undersand the "automatically link the contact’s main company"

Finally, DataSync Tool thorws an Exception when i run it:

[15:52:44.744]     4 Records Found
[15:52:44.744]   Handling Record "MAXIME"...
[15:52:52.076] 
[15:52:52.076] *** ERROR ***
[15:52:52.076] Error while creating or modifying destination record.
[15:52:52.076] Error: WKFL-2142 Error while executing Script "EOleException" at line 16 char 5.
Message: "SLC_Publ_Cont_750
Cannot add a new Relation

ErrorContext
=============================     
EDatabaseError 0
L'instruction INSERT est en conflit avec la contrainte FOREIGN KEY "FK_PUBLICATIONS_PUBL_CONT1". Le conflit s'est produit dans la base de données "TESTFORMATION", table "ADMINFORMATION.PUBLICATIONS", column 'K_PUBLICATION'

Error
=============================     
EEfficyTableViewException 1116
(ENTT-1616) SLC_Publ_Cont_750
Cannot add a new Relation

my script:

var PUBLICATION_KEY = 2;

function EfficyImportRecord(DataStore) {
  var companyKey = DataStore.FindExistingEntity('Comp');
  var compEdit = Database.OpenEditContext(ntComp, companyKey);
  try {
    var dsComp = Database.GetMasterDataSet(compEdit, 0);
    dsComp.Edit();
    companyKey = dsComp.FieldByName('K_COMPANY').AsFloat; // Dans le cas où on crée un nouvel enregistrement, sinon la valeur était déjà initialisée par la méthode DataStore.FindExistingEntity
    dsComp.FieldByName('F_EXTERNALID').AsString = DataStore.AsString('Company External ID');
    dsComp.FieldByName('NAME').AsString = DataStore.AsString('COMPANY');

    // Pour identifier clairement les sociétés impactées par la datasynchro, la lier à une publication add-hoc
    Database.AddLink2(ntPubl, ntComp, PUBLICATION_KEY, companyKey, true);


    Database.CommitChanges(compEdit, false);
  } 
  finally
  {
    Database.CloseContext(compEdit);
  }


  var contKey = DataStore.FindExistingEntity('Cont');
  var contEdit = Database.OpenEditContext(ntCont, contKey);
  try {
    var dsCont = Database.GetMasterDataSet(contEdit, 0);
    dsCont.Edit();
    contKey = dsCont.FieldByName('K_CONTACT').AsFloat;
    dsCont.FieldByName('F_EXTERNALID').AsString = DataStore.AsString('Contact external Id');
    dsCont.FieldByName('NAME').AsString = DataStore.AsString('LASTNAME');
    dsCont.FieldByName('FIRSTNAME').AsString = DataStore.AsString('FIRSTNAME');

    Database.InsertDetail2(contEdit, ntPubl, PUBLICATION_KEY, false);  //passer false a true??
    Database.InsertDetail2(contEdit, ntComp, compKey, false);

    Database.CommitChanges(contEdit, false);
  } 
  finally
  {
    Database.CloseContext(contEdit);
  }


  return true
}

Thanks a lot!

asked in Database by (259 points)
edited by

2 Answers

+2 votes
Best answer

Hello,

I would suggest to follow an advanced technical training course @ Efficy
In your script you are doing the addlink before committing the CompEditContext, so the Company is not yet present in the Efficy database.

InsertDetail means that, in an editContext, you want to add a relation to another entity
For instance in a Document Edit Context (in the Interface of Efficy), you see at the right part of the screen all the entities that could be linked
In serverside scripting, you use a InsertDetail to do this (you then need to have the Edit context opened)

But for example, in the Company Edit Form, there is no Contact Detail on the right part of the screen so you cannot use the InsertDetail in this case

The Addlink is just adding a link between two entities in Efficy (no need to have the context opened)

You can also use the OpenEditContextRelation (NtCont, NtComp, ...) to open the relation screen between a contact and a company and for example fill in the function of the contact in his company

All these informations can be found in https;//edn.efficy.com -> White pages -> Workflow scripting

Kind regards,

Henry

answered by (325 points)
selected by
0 votes

Hello.

You can follow a step by step manual using the DataSynchroRemote (over SOAP) for loading contacts in Efficy starting from an Excel file.

In combination with the Workflow Scripting API, you should be able to succeed.

DataSynchroRemote guide

answered by (7.4k points)
1,248 questions
1,518 answers
1,858 comments
328 users