Camille

I am going to follow the white rabbit of your opening metaphor and follow you down the rabbit hole. The team in Dublin are very much aware of the peculiarities of working with phone and email addresses using the SOAP web services. There is a new RESTful API which overcomes these issues. I have written about the API he community.sagecrm.com/.../sage-crm-39-s-restful-api-sdata-for-cloud-and-on-premise-part-1-of-10.aspx

The API was introduced quietly in Sage CRM 2014 R2 and has been extended in Sage CRM 2015 R1 to cover Companies, Persons, and their core information like Phone, Email and Address.

Once the API is fully implemented within Cloud, and there are further architectural things to be made to the core entities, it will be rolled into the on-premise product.

Hello Jeff,

Thank you for the link. I believe I may be looking for specific information regarding the user use cases outside of a simple happy flow test like the one mentioned in community.sagecrm.com/.../adding-emails-and-emaillink-records-using-soap-web-services.aspx

The case mentioned in the following link is quite limited and will fail testing as it also does not update the records stored in the CRMEmailPhoneData table. Nor will conflicts be resolved cleanly at the CRMEmailPhoneData table does not update/sync from the active data but rather relies on a not active procedure being run at set hardcoded stages. Similar examples used across the 7.2 Webservices documentation, community.sagecrm.com/.../sage-crm-7-2-a-round-up-of-articles-about-using-soap-web-services.aspx, seem to ignore the tables used for linking contact details entirely e.g. community.sagecrm.com/.../how-are-transactions-managed-over-multiple-webservice-requests.aspx so perhaps there is an inconsistency in the table entity relationships, or additional backend operations run inside webservice calls (such as the creation of additional non referenced table records that are not described), or the documentation is not synchronized yet across versions.

In either case it would be good to have some description in a mathematical form around the successful case by case management of these entities so that the correct web service calls can be applied on the case by case basis. e.g. adds, queries, updates, deletions. Without the full provision for those stages there appears to be a developing loss of information. Please can you assist with this. The current UAT testing has not been able to confirm version 7.3 as ok to proceed with so instead our current recommendation is to remain with version 6.2 until suitable additional hacks and workarounds can be created to adjust the Sage CRM UI to correctly reference the contact details provided.

It appears that your article community.sagecrm.com/.../sage-crm-39-s-restful-api-sdata-for-cloud-and-on-premise-part-2-of-10.aspx boths references SOAP web service calls as "The relationships between new entities that are created in a cloud instance are not covered by the SOAP web services. This means that the SOAP web services can not be used for inserting and updating new entities and linking them to core entities like a company, person or opportunity."

and "Up until Sage CRM 2014 R2 it was not possible to using REST and SData to carry out Create, Read Update and Delete (CRUD) tasks. Only SOAP had been available as a data manipulation option." . Which poses a much larger migration change which has not been covered at all through the documentation for administrators to migrate; the deprecation of all SOAP web service calls to be replaced by a, as yet not well described, set of functionally similar web service calls community.sagecrm.com/.../sage-crm-39-s-restful-api-sdata-for-cloud-and-on-premise-part-6-of-10.aspx.

In addition the article community.sagecrm.com/.../sage-crm-39-s-restful-api-sdata-for-cloud-and-on-premise-part-10-of-10.aspx does not accurately describe the call parameters for the interface with the restful services or the adequate migration from the SOAP queries to the cute little jquery ajax calls. It appears in your example that the 3rd party making these calls is now expected to completely replicate the db structures in JSON and track and migrate every undocumented change in the duplicated db structures. Surely this is not the case as it is far less future proof and far more susceptible to failures and worse silent failures. Unless there is already a referenced JSON base object library each client user would need to start this construction from scratch making assumptions around the poorly documented core tables. Even worse as each version does not clearly document changes across each object / table it is far more likely that an insufficient migration is performed leading to a deterioration in the customer experience.

This has not been the only unknown across the migration. So perhaps since this would affect a significant part of integration with Sage CRM as a whole this could be described in more functional detail outside of the pretty flow chart pictures, (which do not pertain to the environment architecture coverage).

Camille

Thanks for the reply.

Are you a business partner or a customer? I see that you are based in New Zealand but I don't know how much contact you have with the local Sage team.

I think part of the frustration that may be generated by the SOAP web services is because of the original intention for simplicity and the eventual changes in the datamodel.

The 'Entity' concept underlies the methods like Query() and QueryEntity() and Add() and Update(). There is a very fixed structure defined in the WSDL for each of the entities like Company, Person, that stretches over the core table to some of the child records.

For example 'Company' in its WSDL definition has a hardcoded inclusion of Person, Address, Email and Phone data. The 'Person' entity also has this structure so when retrieving a Company using Query() or QueryEntity() not only the company data is retrieved but also all the child records of every 'Person' that belongs the company.

The hardcoded definitions are supposed to simplify the transactions. This is because different data modelling approaches were taken around different parts of the system. Sometimes the association between one part of the system and another is a classic 'Parent/Child' and in others an intersection entity is used.

The Phone and Email area is obviously an area in which an approach to data takes place not typical of other areas in the system. There were several major changes (in short succession) to the way in which phone/email data was held in the database and de-normalised versions in the past. The addition of CRMEmailPhoneData table and views PersonPE and CompanyPE are the consequence of those changes. Because the Query() and QueryEntity() approaches are hardcoded their intentional simplification of the Entity actually can create problems around phone and email maintenance.

This much you know.

The web services do offer an alternative way of approaching the insert, update and delete of data and this is via the 'Record' approach rather than the 'Entity' approach.

The methods QueryRecord() and AddRecord() for example will work against the simple table definition. So querying or inserting company data only the company table is accessed. This gives a developer enormous freedom to design the interactions in any way BUT it requires that each of the structures that are referenced are exposed to webservices and that the data model is known and that they are prepared to manage the multiple transactions required to insert something like a company, its person, phone and email data and to maintain the referential integrity.

The data model for each of the versions of Sage CRM are available in the Partner area of the community. As are examples of using the record approach.

One of the wrinkles in taking this approach is that the tables need to be exposed to web services. For tables like Address and Company this is easy as the admin screens allow this but many of the intersection entities like person_link or address_link or the phone and email tables are hidden from the admin interface and you would have to either update the meta data using a query run against the database or a component designed to expose all the tables to the WSDL.

If you are working with Cloud then the coming release of Sage CRM 2015 R1.1a should considerably simplify this because of the SData 2.0 approach. But the RESTful API will still require you to understand the underlying data model.

The SData 2.0 API will have a whole suite of documentation available to it published Help centre.

http://help.sagecrm.com/

But it will not be available for on-premise in the short term.

I appreciate this may not be the ideal answer, but for our own mobile development we use a combination of SData 1.0 to fetch data - and this uses new views on the server - and SOAP for update.

I hope this helps. I would encourage you to reach out to the local Sage team if you have not already done so as the support and PSG teams may have access to more information.

I think if you need specific advice then the local Sage office will be able to give you either direct help and advice. I am not in a position to give specific project advice because of other commitments.

I have asked for more information from my development colleagues about the specific behaviour of the CRMEmailPhoneData table. They will either share that with me of comment directly on this thread.

Do contact the local Sage guys as they are very knowledgeable.

Having said that we may get some other comments from partner and customer experts on this thread too.

Hello Jeff,

Thank you for your fast response.

I guess you could say I am contracted to write and manage a third party user customizable form design platform for a registry of other organisations. The client would like communication from the form design platform through to their Sage web services and onto another series of reporting applications, (Sage being used for the mid level admin user to query on). So on our end we code data population and reads through the Sage web service. We do not manage, resell, or tailor parts of the Sage CRM but rather work with what the client has available. The client themselves is a customer who has brought Sage but are currently running through UAT testing and management of migration updates for the reporting components. Consistency and accuracy of data is important for legal purposes. As the transactional history is not available on a per entity query there are several checks in place to ensure validity of the full data transfer. These occur at different stages of the workflow to accommodate for multiple levels of Sage exceptions so that form data is not moved into a completed state until the full series of disparate transactions can be completed across an entire group of data maps. For instance in several cases we experience errors with filenames being inserted into Sage due to characters a user has named the file. So there is a workaround in place to rename a user provided file to remove nonaccepted chars, (e.g. +). However until all files, company, person link records, and related other entity records are updated (e.g. application, expenses etc), the entire group in the update set is not marked as completed. The base types for Sage are primarily used with some customization in additional fields on company/application entities e.g. addition of a district council field.

What I am trying to ascertain by my question, (which perhaps is unclear), is whether there needs to be another workaround to update the separate table data for CRMEmailPhoneData through the CRM after performing updates to the Email and Phone link tables? I am not clear on the management of phone email type information in the CRMEmailPhoneData table, and identification of the correct information to replace/delete or update records. (The companies or people can have different types of primary contact numbers/emails.) I have a few questions about the table e.g. does the CRMEmailPhoneData table only contain those records which are of business type only?

As well as the migration I assist with diagnosis on user entered data not being accepted into the Sage text fields (again characters can cause issues), and provide a manual check when there are separate web service errors that would prevent web service calls. Another task I have been asked to provide is a separate monitoring tool to indicate the status of the current set of different Sage web services across different deployments and different Sage versions. (A web service uptime/downtime monitor). The Sage web service versions we are trying to collaboratively work with span from 6.2 Service patch h to Sage 7.3. As you could imagine since the whole system needs to communicate across a range of different CRMs the development of data mapping must be not only deployment and version dependent but also contain enough checks and balances for the data to be accurate.

It would be great if I could perhaps get some assistance or recommendations for the client on whether they would need to look at a entire system redevelopment for the Sage sData rest methods for those deployments at version 7.1 and above or whether SOAP calls can be used for Sage version 7.1 and up. This is so that there is less maintenance costs long term and elements are able to be kept abstracted between versions with only the web service integration methods changing at the stage needed (data upload into Sage). At the moment the provided Sage WSDLs describe each table, their field names and their field types. However the conversion and management of the records for integration without the WSDL appears to be limited and perhaps an error prone mechanism in which data can easily be excluded, misnamed, or not mapped due to a type mismatch (again something the WSDL and Soap integration currently helps prevent in most cases).

If you have my email I would greatly appreciate some advice in the matter to identify what workarounds can be used and the guiding usage rules for some of the new tables. E.g. the only the CRMEmailPhoneData table is referenced when retrieving an entity's contact details but in the detailed information view for that entity the Email Link and Phone Link tables are used, not the CRMEmailPhoneData. Hence a data mismatch.

Camille

Please see: community.sagecrm.com/.../understanding-the-crmemailphonedata-table.aspx

The reason that nothing is updated in the CRMEmailPhoneData table is that the web services are not passing the type and therefore it is ignored by the triggers.

Thank you Jeff for the new article. It explains nicely the trigger points and locations it will be used. I will get in touch with the local team as hopefully they will be able to resolve why the trigger is not called when the link record with type Business is updated with a new email id.