Hi all,
Lets say I have 3 companies of the same base currency, Company A, B, and C.
When I add new financial currency on Company A, I want to add to Company B and C as well. So I am using the code below, which I refer to this site https://my.sage.co.uk/Sage200SDKDocs/html/DOC0002_Example.html#ADDING
This code is executed after the form is saved. (using function _form_EndSave() )
foreach(FinancialCurrency _dirtyfc in _dirtyFinancialCurrencies) { string ACTIVECONNECTIONDATA = "Sage.ObjectStore.ConnectionData.ActiveConnectionData"; ConnectionData currentCompany = (ConnectionData)Sage.Common.Contexts.SessionContext.Context.GetData(ACTIVECONNECTIONDATA); ConnectionData connectionData = null; string company = ""; string currency = ""; try { Sage.Accounting.Application app = new Sage.Accounting.Application(); foreach (Sage.Accounting.Company c in app.Companies) { company = c.Name; connectionData = new ConnectionData(DatabaseType.Sql, c.ConnectString); Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, connectionData); FlushPrimaryKeys(connectionData); // Get the base currency of other company FinancialCurrencies financialCurrencies = FinancialCurrenciesFactory.Factory.CreateNew(); if (companySelected.Name == c.Name) { bool isNew = false; foreach (FinancialCurrency newcurrency in _newCurrencies) { if(_dirtyfc.CurrencyISOCode.Code == newcurrency.CurrencyISOCode.Code) { isNew = true; } } if (isNew) { StringBuilder sb = new StringBuilder("\n"); foreach (FinancialCurrency cc in financialCurrencies) { if (_dirtyfc.CurrencyISOCode.Code == cc.CurrencyISOCode.Code) { isNew = false; } } if (isNew) { currency = _dirtyfc.Name; FinancialCurrency financialCurrency = (FinancialCurrency) FinancialCurrenciesFactory.Factory.CreateNew().AddNew(); // Set some details financialCurrency.Name = _dirtyfc.Name; financialCurrency.Symbol = _dirtyfc.Symbol; financialCurrency.UseForNewAccounts = _dirtyfc.UseForNewAccounts; //Set the ISO code financialCurrency.CurrencyISOCode = _dirtyfc.CurrencyISOCode; // Set the amendability type financialCurrency.ExchangeRateAmendabilityType = _dirtyfc.ExchangeRateAmendabilityType; // Set the rate type used to period financialCurrency.ExchangeRateType = _dirtyfc.ExchangeRateType; if (financialCurrency.ExchangeRateType == Sage.Accounting.EnumExchangeRateType.ExchangeRateSingle) { financialCurrency.CoreCurrencyRate = _dirtyfc.CoreCurrencyRate; financialCurrency.EuroCurrencyRate = _dirtyfc.EuroCurrencyRate; } else if (financialCurrency.ExchangeRateType == Sage.Accounting.EnumExchangeRateType.ExchangeRatePeriod) { if (SystemSettingFactory.Factory.Fetch().UsePeriodExchangeRates) { foreach (PeriodExchangeRate per in _dirtyfc.PeriodExchangeRatesToBase) { PeriodExchangeRate periodExchangeRate = (PeriodExchangeRate) financialCurrency.PeriodExchangeRatesToBase.AddNew(); periodExchangeRate.CurrencyType = per.CurrencyType; periodExchangeRate.OneUnitEquals = per.OneUnitEquals; periodExchangeRate.ExpiryDate = per.ExpiryDate; } foreach (PeriodExchangeRate per in _dirtyfc.PeriodExchangeRatesToEuro) { PeriodExchangeRate periodExchangeRate = (PeriodExchangeRate) financialCurrency.PeriodExchangeRatesToEuro.AddNew(); periodExchangeRate.CurrencyType = per.CurrencyType; periodExchangeRate.OneUnitEquals = per.OneUnitEquals; periodExchangeRate.ExpiryDate = per.ExpiryDate; } } } else if (financialCurrency.ExchangeRateType == Sage.Accounting.EnumExchangeRateType.ExchangeRateBoth) { if (SystemSettingFactory.Factory.Fetch().UsePeriodExchangeRates) { foreach (PeriodExchangeRate per in _dirtyfc.PeriodExchangeRatesToBase) { PeriodExchangeRate periodExchangeRate = (PeriodExchangeRate) financialCurrency.PeriodExchangeRatesToBase.AddNew(); periodExchangeRate.CurrencyType = per.CurrencyType; periodExchangeRate.OneUnitEquals = per.OneUnitEquals; periodExchangeRate.ExpiryDate = per.ExpiryDate; } foreach (PeriodExchangeRate per in _dirtyfc.PeriodExchangeRatesToEuro) { PeriodExchangeRate periodExchangeRate = (PeriodExchangeRate) financialCurrency.PeriodExchangeRatesToEuro.AddNew(); periodExchangeRate.CurrencyType = per.CurrencyType; periodExchangeRate.OneUnitEquals = per.OneUnitEquals; periodExchangeRate.ExpiryDate = per.ExpiryDate; } } //add for single also financialCurrency.CoreCurrencyRate = _dirtyfc.CoreCurrencyRate; financialCurrency.EuroCurrencyRate = _dirtyfc.EuroCurrencyRate; } if (financialCurrency.ThisIsEuroCurrency) { // Update the financial currency and period exchange rates. The parameter passed // to the ConfirmChange method is to remove the period exchange rates. financialCurrency.ConfirmChange(false); } else { // Update financial currency and period exchange rates. // Note: If you just call the Update method on FinancialCurrency then the period // history will not be updated. financialCurrency.ConfirmChange(financialCurrency.ExchangeRateType == Sage.Accounting.EnumExchangeRateType.ExchangeRateSingle); /*Error HERE!!!!*/ } financialCurrency.Update(); } } foreach(PeriodExchangeRate er in _newPeriodExchangeRates) { if (er.FinancialCurrency.CurrencyISOCode.Code == _dirtyfc.CurrencyISOCode.Code) { if(er.CurrencyType == Sage.Accounting.EnumExchangeCurrencyType.ExchangeCurrencyCoreCurrency) { foreach (FinancialCurrency cc in financialCurrencies) { if (_dirtyfc.CurrencyISOCode.Code == cc.CurrencyISOCode.Code) { PeriodExchangeRate per = (PeriodExchangeRate) cc.PeriodExchangeRatesToBase.AddNew(); per.CurrencyType = er.CurrencyType; per.ExpiryDate = er.ExpiryDate; per.OneUnitEquals = er.OneUnitEquals; per.Update(); cc.Update(); } } } if (er.CurrencyType == Sage.Accounting.EnumExchangeCurrencyType.ExchangeCurrencyEuroCurrency) { foreach (FinancialCurrency cc in financialCurrencies) { if (_dirtyfc.CurrencyISOCode.Code == cc.CurrencyISOCode.Code) { PeriodExchangeRate per = (PeriodExchangeRate)cc.PeriodExchangeRatesToEuro.AddNew(); per.CurrencyType = er.CurrencyType; per.ExpiryDate = er.ExpiryDate; per.OneUnitEquals = er.OneUnitEquals; per.Update(); cc.Update(); } } } } } //check for updated per foreach(CurrencyPERToUpdate cpu in currencyPERToUpdates) { FinancialCurrencies up_fc = FinancialCurrenciesFactory.Factory.CreateNew(); up_fc.Query.Filters.Add(new Filter(FinancialCurrency.FIELD_SYSCURRENCYISOCODEOBJECT, cpu.currencyCode)); up_fc.Find(); if (up_fc.First != null) { foreach(PeriodExchangeRate per in cpu.periodExchangeRates) { if(per.CurrencyType == Sage.Accounting.EnumExchangeCurrencyType.ExchangeCurrencyCoreCurrency) { foreach(PeriodExchangeRate up_per in up_fc.First.PeriodExchangeRatesToBase) { if(per.ExpiryDate.Date == up_per.ExpiryDate.Date) { up_per.OneUnitEquals = per.OneUnitEquals; up_per.Update(); } } } if (per.CurrencyType == Sage.Accounting.EnumExchangeCurrencyType.ExchangeCurrencyEuroCurrency) { foreach (PeriodExchangeRate up_per in up_fc.First.PeriodExchangeRatesToEuro) { if (per.ExpiryDate.Date == up_per.ExpiryDate.Date) { up_per.OneUnitEquals = per.OneUnitEquals; up_per.Update(); } } } } } } //check for deleted per foreach (CurrencyPERToDelete cpd in currencyPERToDeletes) { FinancialCurrencies up_fc = FinancialCurrenciesFactory.Factory.CreateNew(); up_fc.Query.Filters.Add(new Filter(FinancialCurrency.FIELD_SYSCURRENCYISOCODEOBJECT, cpd.currencyCode)); up_fc.Find(); if (up_fc.First != null) { foreach (PeriodExchangeRate per in cpd.periodExchangeRates) { if (per.CurrencyType == Sage.Accounting.EnumExchangeCurrencyType.ExchangeCurrencyCoreCurrency) { foreach (PeriodExchangeRate de_per in up_fc.First.PeriodExchangeRatesToBase) { if (per.ExpiryDate.Date == de_per.ExpiryDate.Date) { de_per.Delete(); } } } if (per.CurrencyType == Sage.Accounting.EnumExchangeCurrencyType.ExchangeCurrencyEuroCurrency) { foreach (PeriodExchangeRate de_per in up_fc.First.PeriodExchangeRatesToEuro) { if (per.ExpiryDate.Date == de_per.ExpiryDate.Date) { de_per.Delete(); } } } } up_fc.Update(); } } if (isNew) { String codes = "EUR', 'GBP', 'USD"; CurrencyISOCodes iSOCodes = CurrencyISOCodesFactory.Factory.CreateNew(); iSOCodes.Query.FilterText = CurrencyISOCode.FIELD_CODE + " not in ('" + codes + "')"; iSOCodes.Find(); List<long> isocodeids = new List<long>(); foreach (CurrencyISOCode isocode in iSOCodes) { isocodeids.Add(isocode.SYSCurrencyISOCode); } string isocodeids_s = string.Join(",", isocodeids); bool error = false; StringBuilder sb = new StringBuilder(); sb.Append("Cannot save for the following Currency:\n"); FinancialCurrencies FinancialCurrencies = FinancialCurrenciesFactory.Factory.CreateNew(); FinancialCurrencies = FinancialCurrenciesFactory.Factory.CreateNew(); FinancialCurrencies.Query.FilterText = FinancialCurrency.FIELD_SYSCURRENCYISOCODEOBJECT + "ID in (" + isocodeids_s + ")"; FinancialCurrencies.Query.Sorts.Add(new Sage.ObjectStore.Sort(FinancialCurrency.FIELD_SYSCURRENCYISOCODEOBJECT, true)); FinancialCurrencies.Find(); int increment = 10; foreach (FinancialCurrency curr in FinancialCurrencies) { curr.Fields.FindItem("SortIndex").Value = increment; curr.Update(); increment++; } } //check if core financialCurrency is updated } } } catch (Exception ex) { ImportRemark ir = new ImportRemark(company, ex.Message + ex.ToString()); importRemarks.Add(ir); //MessageBox.Show(company + " (" + currency + "): " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); Logger.WriteLog(company + " (" + currency + "): "+ ex.ToString()); Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, currentCompany); FlushPrimaryKeys(currentCompany); } finally { Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, currentCompany); FlushPrimaryKeys(currentCompany); } Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, currentCompany); FlushPrimaryKeys(currentCompany); }
However, I am getting this error on line 131:
Sage.Common.ProgrammingByContract.PreconditionException: 'Contract Failure: A referential integrity exception on update has occured. This method should be overriden by the subclass.'
Which I don't understand which column is giving this error.
Anyone can help me? Thank you