Import into column multiple reference

Standard import does not allow to import into column of type multiple reference to another class or user in the same way as to the columns containing just a single value. This functionality, however, can be achieved by means of a script that is executed during the import.

In our example we will import from the file containing two columns of the class:

There can be more values in the file in the column Server_contacts separated with a comma, that represent reference to a user (e.g. account or code of the user in ObjectGears system). We have to use another character as a separator of the two above stated columns, e.g. tab or semicolon.

In the target class, into which we want to import, we have column of type Multiple reference to a user which has code machine_contacts. Besides that we will create an auxiliary column of type Text, into which we set up the import from the column Server_contacts according to the below example. We will state name of the column from the file but we will not check the import box. Records from the file will be mapped to the records in the class by means of column Server_name from the file and column name from the class.

We will use following script in the event Start after import end in the import.

var colContacts = OGActualImport.ClassDef.Columns['machine_contacts'];
//1.9.0.0 var colImpName = OGActualImport.ImportColumns.FindByColumnName('Server_name');
//1.9.0.0 var colImpContants = OGActualImport.ImportColumns.FindByColumnName('Server_contacts');

var colImpName = null;
var colImpContants = null;
for( var ii = 0 ; ii < OGActualImport.ImportColumns.Count; ++ii)
{
  if ( OGActualImport.ImportColumns[ii].ColumnName == 'Server_name')
  {
    colImpName = OGActualImport.ImportColumns[ii];
  }
  else if ( OGActualImport.ImportColumns[ii].ColumnName == 'Server_contacts')
  {
    colImpContants = OGActualImport.ImportColumns[ii];
  }
}


//read the records
var sql = OG.TextUtils.Format('select col_{1}_s name, col_{2}_s contacts from {0} tab where iserror = 0 and col_{2}_s is not null',
OGActualImportDbTable, colImpName.ColumnClassDefId, colImpContants.ColumnClassDefId);

var dr = OG.Sql.GetExecuteReader(null, sql, OG.Sql.CreateParameterList());
while( dr.Read())
{
  //find mapped record according to the name - has to be just one
  var drf = OG.DataRow.GetDataRowFilter(OGActualImport.ClassDefId);
  drf['name'] = dr.GetString('name');
  var drl = OG.DataRow.GetDataByFilter(drf);
  if ( drl.Count == 1)
  {
    //delete current references to person
    var sql = OG.TextUtils.Format('delete from DataRow{0}LinkPerson where ColumnClassDefId = {1} and ReferentionId = {2}',
    colContacts.ParentId, colContacts.Id, drl[0].Id);
    OG.Sql.RunSql(sql);

    //parse the contacts
    var contacts = dr.GetString('contacts').split(',');

    for( var i = 0; i < contacts.length; i++)
    {
      //insert new contacts
      var sql = OG.TextUtils.Format('insert into DataRow{0}LinkPerson(ColumnClassDefId, ReferentionId, LinkReferentionId) select {1}, {2}, Id from Person where Code =        @personCode and Deleted is null', colContacts.ParentId, colContacts.Id, drl[0].Id);

      var pars = OG.Sql.CreateParameterList();
      pars.AddNvarchar( 'personCode', contacts[i]);

      OG.Sql.RunSql(null, sql, pars);
    };
  }
}