Setting territory

SOLVED

I must be missing something. I've got a Sage 300 system integrated with CRM. I was sure that when you set up an integration, you can set the default territory for the data coming across rom 300. I've got about 5 companies that will be integrated and each will be for a different country and I need to set the territory accordingly. I swear there was a default territory setting on the data somewhere in the integration.

Parents
  • 0
    verified answer

    I solved this. I can now choose the territory when adding a new integration and when data syncs across from the integration, the new records all have the selected integration. Details below:

    Ok, to fix the inability to set the territory on a newly integrated record that comes across from Sage, I did this:

    My territories are:
    Worldwide
    United Kingdom
    United States
     

    I created a new field on the Sage 300 Integrations table called accp_c_territory of type Selection. In the selection I created the same translations as the territory names. The codes are the same as the captions but without spaces and punctuation. I customised the screen for adding a new integration by adding the new selection list to that screen so you can choose the default territory for that integration.

     

    I then added this tablescript to the company entity which fires when the comp_database changes from a blank/null to a value. When the integration adds a new company it does a load of inserts. The comp_database is done during an update but handily AFTER then person is inserted. So, my update record fires and gets the record from AccPacConfig where the database name matches the entry set on that integration.

     

    It then looks up the caption code record where the family is the custom integration field drop down name and looks for a match capt_code. When it finds the matching code it will then look up the territory where the capt_us value matches the territory caption. If it finds one it will set the comp_secterr to the territory ID. It will then update all person records to set the pers_secterr with the same territory ID where the company ID matches part of the WhereClause. I had to use the WhereClause because the context and the values collection did not contain the matching company ID of the new company.

     

    Now, for any integration, I can choose a default territory. The only downside is that if they add a new Sage company database and want to put that in a new territory, they will have to add that to the custom field on the accpacconfig table.

     

    The table script is below:

    function InsertRecord()
    {
    }
    
    function PostInsertRecord()
    {
    }
    
    function UpdateRecord()
    {
      if(("" + Values("comp_database") != "") &&  ("" + CRM.GetContextInfo("company", "comp_database") == ""))
      {
        var sDB = Values("comp_database");
        var oAccPacConfig = CRM.FindRecord("AccPacConfig", "AccP_Database='" + sDB + "'");
    
        if(!oAccPacConfig.eof)
        {
          var oCapt = CRM.FindRecord("custom_captions", "capt_family='accp_c_territory' and capt_code='" + oAccPacConfig("accp_c_territory") + "'");
          
          if(!oCapt.eof)
          {
            var oQuery = CRM.CreateQueryObj("select * from territories where Terr_Caption = '" + oCapt("capt_us") + "'");
            
            oQuery.SelectSql();
    
            if(!oQuery.eof)
            {
              Values("comp_secterr") = oQuery("Terr_TerritoryID");
              
              var sql;
              sql = "UPDATE person SET pers_secterr='" + oQuery("Terr_TerritoryID") + "' where pers_companyid=" + WhereClause.replace(" Comp_CompanyId = ", "");
              CRM.ExecSql(sql);
            }
          }
        }
      }
    }
    
    function DeleteRecord()
    {
    }
    
    

    To be honest, if the territory field was available to set on an integration, I wouldn’t have had to do this but this at least solves the problem

Reply
  • 0
    verified answer

    I solved this. I can now choose the territory when adding a new integration and when data syncs across from the integration, the new records all have the selected integration. Details below:

    Ok, to fix the inability to set the territory on a newly integrated record that comes across from Sage, I did this:

    My territories are:
    Worldwide
    United Kingdom
    United States
     

    I created a new field on the Sage 300 Integrations table called accp_c_territory of type Selection. In the selection I created the same translations as the territory names. The codes are the same as the captions but without spaces and punctuation. I customised the screen for adding a new integration by adding the new selection list to that screen so you can choose the default territory for that integration.

     

    I then added this tablescript to the company entity which fires when the comp_database changes from a blank/null to a value. When the integration adds a new company it does a load of inserts. The comp_database is done during an update but handily AFTER then person is inserted. So, my update record fires and gets the record from AccPacConfig where the database name matches the entry set on that integration.

     

    It then looks up the caption code record where the family is the custom integration field drop down name and looks for a match capt_code. When it finds the matching code it will then look up the territory where the capt_us value matches the territory caption. If it finds one it will set the comp_secterr to the territory ID. It will then update all person records to set the pers_secterr with the same territory ID where the company ID matches part of the WhereClause. I had to use the WhereClause because the context and the values collection did not contain the matching company ID of the new company.

     

    Now, for any integration, I can choose a default territory. The only downside is that if they add a new Sage company database and want to put that in a new territory, they will have to add that to the custom field on the accpacconfig table.

     

    The table script is below:

    function InsertRecord()
    {
    }
    
    function PostInsertRecord()
    {
    }
    
    function UpdateRecord()
    {
      if(("" + Values("comp_database") != "") &&  ("" + CRM.GetContextInfo("company", "comp_database") == ""))
      {
        var sDB = Values("comp_database");
        var oAccPacConfig = CRM.FindRecord("AccPacConfig", "AccP_Database='" + sDB + "'");
    
        if(!oAccPacConfig.eof)
        {
          var oCapt = CRM.FindRecord("custom_captions", "capt_family='accp_c_territory' and capt_code='" + oAccPacConfig("accp_c_territory") + "'");
          
          if(!oCapt.eof)
          {
            var oQuery = CRM.CreateQueryObj("select * from territories where Terr_Caption = '" + oCapt("capt_us") + "'");
            
            oQuery.SelectSql();
    
            if(!oQuery.eof)
            {
              Values("comp_secterr") = oQuery("Terr_TerritoryID");
              
              var sql;
              sql = "UPDATE person SET pers_secterr='" + oQuery("Terr_TerritoryID") + "' where pers_companyid=" + WhereClause.replace(" Comp_CompanyId = ", "");
              CRM.ExecSql(sql);
            }
          }
        }
      }
    }
    
    function DeleteRecord()
    {
    }
    
    

    To be honest, if the territory field was available to set on an integration, I wouldn’t have had to do this but this at least solves the problem

Children
No Data