Insert/update row to tables of all other companies

SOLVED

Hi all, 

is it possible to do the following:

- user update some foreign exchange rate on Maintain Exchange Rates Form of company A,

- on click Save button, it will update on company C,D, and E as well.

I know it is possible on executable application according to this https://my.sage.co.uk/Sage200SDKDocs/html/DOC0001_Example.html#EXECUTABLE, 

but can we do it from the Sage 200 form?

Parents
  • +1
    verified answer

    Yes, it is possible but be careful! This is basic VB code to loop through all companies while working in one company

            Const ACTIVECONNECTIONDATA As String = "Sage.ObjectStore.ConnectionData.ActiveConnectionData"
            Dim thisCompany As ConnectionData = Sage.Common.Contexts.SessionContext.Context.GetData(ACTIVECONNECTIONDATA)
            Dim connectionData As Sage.ObjectStore.ConnectionData
            For Each company As Company In (New Sage.Accounting.Application).Companies
                'switch to different company
                connectionData = New Sage.ObjectStore.ConnectionData(Sage.ObjectStore.DatabaseType.Sql, company.ConnectString)
                Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, connectionData)
                'do what you need
                Debug.WriteLine(Sage.Accounting.SystemManager.CompanyDetailFactory.Factory.Fetch.Name)
                'dispose all objectstore objects afterwards!
            Next
            Try
            Catch ex As Exception
                '
            Finally
                'Switch back to original company
                Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, thisCompany)
            End Try
            Debug.WriteLine(Sage.Accounting.SystemManager.CompanyDetailFactory.Factory.Fetch.Name)
    
    

    Obviously, you'll need to read the relevant currency in each company in order to updated it.

  • 0 in reply to Geoff Turner

    Thank you Geoff. I use your code, translate to C# and apply Chris method.

    string ACTIVECONNECTIONDATA = "Sage.ObjectStore.ConnectionData.ActiveConnectionData";
    Sage.ObjectStore.ConnectionData currentCompany = (Sage.ObjectStore.ConnectionData)Sage.Common.Contexts.SessionContext.Context.GetData(ACTIVECONNECTIONDATA);
    Sage.ObjectStore.ConnectionData connectionData = null;
    try
    {
    	Sage.Accounting.Application app = new Sage.Accounting.Application();
    	foreach (Sage.Accounting.Company c in app.Companies)
    	{
    		connectionData = new Sage.ObjectStore.ConnectionData(Sage.ObjectStore.DatabaseType.Sql, c.ConnectString);
    		Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, connectionData);
    		FlushPrimaryKeys(connectionData);
    		//do what you need here
    	}
    }
    catch(Exception ex)
    {
    	MessageBox.Show(ex.Message);
    	Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, currentCompany);
    	FlushPrimaryKeys(currentCompany);
    	throw ex;
    }
    finally
    {
    	Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, currentCompany);
    	FlushPrimaryKeys(currentCompany);
    }

    Pasting here for future reference.

Reply
  • 0 in reply to Geoff Turner

    Thank you Geoff. I use your code, translate to C# and apply Chris method.

    string ACTIVECONNECTIONDATA = "Sage.ObjectStore.ConnectionData.ActiveConnectionData";
    Sage.ObjectStore.ConnectionData currentCompany = (Sage.ObjectStore.ConnectionData)Sage.Common.Contexts.SessionContext.Context.GetData(ACTIVECONNECTIONDATA);
    Sage.ObjectStore.ConnectionData connectionData = null;
    try
    {
    	Sage.Accounting.Application app = new Sage.Accounting.Application();
    	foreach (Sage.Accounting.Company c in app.Companies)
    	{
    		connectionData = new Sage.ObjectStore.ConnectionData(Sage.ObjectStore.DatabaseType.Sql, c.ConnectString);
    		Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, connectionData);
    		FlushPrimaryKeys(connectionData);
    		//do what you need here
    	}
    }
    catch(Exception ex)
    {
    	MessageBox.Show(ex.Message);
    	Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, currentCompany);
    	FlushPrimaryKeys(currentCompany);
    	throw ex;
    }
    finally
    {
    	Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, currentCompany);
    	FlushPrimaryKeys(currentCompany);
    }

    Pasting here for future reference.

Children
No Data