Problem executing trigger 'eWaySP_SumarizeCartsPricesOnProjectsAndLeads'

Description

The error in user's log will be displayed like this:

Synchronization failed:
eWay.StandardEdition.Synchronization.SynchronizationException: Could not upload items
---> eWay.Core.DataInterface.StoreException: Uploading of items failed.
---> eWay.Core.DataInterface.StoreException: Cannot save the item to webservice to folder Carts
---> eWay.Core.WebService.eWayWebServiceException: Web service returned an error (rcGeneralError): Problem executing trigger 'eWaySP_SumarizeCartsPricesOnProjectsAndLeads'

In the case of web service log, the error will be noted with this text:

System.InvalidOperationException: Problem executing trigger 'eWaySP_SumarizeCartsPricesOnProjectsAndLeads'
---> System.Data.SqlClient.SqlException: Subquery returned more than 1 value.
This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

This error can come up during price calculation from bookkeeping records (previously Carts) to projects. The calculation procedure eWaySP_SumarizeCartsPricesOnProjectsAndLeads is a select that has two types of relations - GENERAL and CART. The CART relation is made between a cart and a deal/project depending on where the cart was created. The GENERAL relation is created between a cart and a project just in case the cart was related to the deal first (=proposal) and the deal was converted to project later.

After opening a cart, there is a Project field, where should be selected an item the cart was originally related with. If the cart was originally related with deal, the deal should be there. If the cart is related with project, the project is there.

The issue is that if the user is in the Project window, opens a cart that has a deal in the Project field and changes it to a project, it has effect on database. The CART relation type that points to a deal, is deleted and is created new CART relation type with the selected project. Now, there are two different relations (CART and GENERAL) between two same items (cart and project). That is why the eWaySP_SumarizeCartsPricesOnProjectsAndLeads procedure shows an error.

Version

All versions of eWay-CRM

Difficulty

High

Resolution

In the web service log, there is cart ItemGUID before the line with an error where the procedure falls:

2014-09-18 09:06:10,571 [9] DEBUG w3wp [(null)] - Executing AfterSave SP trigger eWaySP_SumarizeCartsPricesOnProjectsAndLeads on folder Carts with parameters '@CartGUID: 224cd6c8-8ce6-11e3-a1e8-0017319697c4'

If the ItemGUID is inserted into following select and is proceeded over server database, there will be two same ItemGUID2 items that will be returned, but each with different type of relation (different RelationDataGUID):

SELECT ItemGUID2, RelationType, RelationDataGUID
FROM EWR_ObjectRelations
WHERE ItemGUID1 = '224cd6c8-8ce6-11e3-a1e8-0017319697c4'
AND RelationType IN ('CART' , 'GENERAL') -- GENERAL: When lead converted to Project
AND ObjectTypeID2 = (select ObjectTypeID from EWE_ObjectTypes where FolderName = 'Projects')

The fastest solution is to delete one of the relations. Because the CART relation type defines what will be shown in the Project field of the Cart window, it is appropriate to delete the RELATION type using this procedure over the server database (as the @RelationDataGUID parameter you need to fill in the value from the RelationDataGUID column of the select above):

EXEC [dbo].[eWaySP_DeleteRelations]
@RelationDataGUID = '7F60A7A2-8CE6-11E3-A1E8-0017319697C4',
@UseItemChanges = 1