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.

  • 0

    I have had a quick look through the Integration guide for CRM 2020 and Sage 300 (I'm in the UK so only work with Sage 200) and unfortunately it seems territory is never mentioned as something you are able to configure so I think you might be out of luck. 

    I don't know if this is something that can be scripted (e.g. if Account is from Financial Company A then the company needs territory X) there is a chance you can do a table level script for it. 

  • 0 in reply to Matthew Shaw

    I've got it part way there now. I added a custom field to the integration table in CRM which is a drop down and I've added the territories to that drop down. Then, in a table script I've added a script that runs on update (long story why it isn't insert) so that when the comp_database field gets populated which is when the records are integrated, it will default the territory. It works if I hard code the territory. Getting it to work it out, I have a mistake somewhere. In reality, the integration should have a field where you set the default territory for those records but it doesn't.

  • 0 in reply to Vega

    I understand your pain, with Sage 200 Integration we have the field there by default, so I know what you're trying to do and what benefits it offers. 

  • 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