elasticio/sugarcrm-component

SugarCRM component for elastic.io platform

Sugar CRM Component

SugarCRM is a CRM system with a a simple user interface, industry-leading customer experience, and an intuitive customization platform.

This is an open source component template for Sugar CRM which is developed specifically to run on elastic.io platform. You can clone it and change it as you wish.

Authentication

In order for the elastic.io platform to authenticate with SugarCRM, the following tasks need to be completed:

• An admin must manually create an OAuth App on the SugarCRM instance through SugarCRM's UI (step by step instructions below)
• If using a version of SugarCRM that was released after Winter '18, you must register a platform on your SugarCRM instance
  • With Sugar 8, this can be done through the UI (step by step instructions below)
  • It is also possible to load new platform values by creating and installing a module Developers use the platform parameter to distinguish their custom platform from other clients when using the Sugar API. For example, when a user accesses Sugar via the desktop client and the mobile client simultaneously without being logged out of either, it is because the API recognizes that they are different platforms due to their unique platform identifiers. The platform identifier also makes it easy for a Sugar administrator to configure which API platforms are allowed to access their instance via Admin > Configure API Platforms. To learn more about platform identifiers, please refer to the Sugar Developer Blog in the SugarCRM Community.
• Finally, authentication information for your Sugar instance must be entered into the elastic.io UI (step by step instructions below)

Creating an app on a SugarCRM instance

In order the platform to connect to your SugarCRM instance, an app needs to be created on that instance. Below are the steps to do so. Once that is done, you will provide a valid username and password to the elastic.io platform. The platform will exchange that username and password for a token. In a production system, the best practice is to create a dedicated user for the elastic.io platform. This user should have the minimum required permissions.

1. As an admin on your SugarCRM instance, go to the Administration panel

   

2. Select OAuth Keys

   

3. In the top bar, select the dropdown for the now visible OAuth Keys option

   

4. Select Create OAuth Key

5. Fill in the following values:

   1. Consumer Key Name: Pick a name that is convenient to remember
   2. Consumer Key: Pick a strongly random string. You will need to provide this information as part of the SugarCRM component account information
   3. Consumer Secret: Pick a strongly random string. You will need to provide this information as part of the SugarCRM component account information
   4. OAuth Version: OAuth 2.0
   5. Client Type: Sugar User
   6. Description: Optional value for your convenience

   

6. Click Save

Registering a New SugarCRM Platform Value through the UI

1. As an admin on your SugarCRM instance, go to the Administration panel

   

2. Select Configure API Platforms

   

3. Enter a value of your choosing for the new platform and click Add and then Save.

   

Authentication on elastic.io

Fill in the following for your account:

• Name Your Account: Name to identify this account on elastic.io
• Your SugarCRM domain: URL of your Sugar CRM instance
• Your login: Username used to login to SugarCRM instance
• Your password: Password used to login to SugarCRM instance
• Your OAuth 2.0 Consumer Key: Value created in step 5ii.
• Your OAuth 2.0 Consumer Secret: Value created in step 5iii.
• Custom Platform Value: Platform value registered above.

For real-time tasks please use separate oauth keys to avoid login conflicts.

Parameter platform should be set to some custom string but should be unique per sugar component in order to avoid any potential login conflicts. So now platform is "$TASK_ID:$STEP_ID".

Triggers

Fetching New and Updated Objects from SugarCRM - Polling

It is possible to fetch any type newly created and/or updated object in your SugarCRM instance. Select the trigger Fetch new and updated objects and then configure the following:

• SugarCRM module to fetch: Type of object to fetch
• Number of records to fetch: Maximum number of records to fetch per call. If left blank then the default set on your SugarCRM instance.

Fetching New and Updated Objects from SugarCRM - Webhook

It is possible to have new and updated objects be pushed to the elastic.io platform via webhooks. In order to do so, select Fetch new and updated objects (getObjects - Webhook). When a flow with this trigger is started, the elastic.io platform will register webhooks on the SugarCRM instance. When the flow is stopped, this webhook will be destroyed by the platform.

Fetch Deleted Objects from SugarCRM - Webhook

It is possible to have the ids of deleted objects be pushed to the elastic.io platform via webhooks. In order to do so, select Fetch deleted objects (getDeletedObjects - Webhook). When a flow with this trigger is started, the elastic.io platform will register webhooks on the SugarCRM instance. When the flow is stopped, this webhook will be destroyed by the platform.

Actions

Lookup Object By ID

Given an object, looks up the object with that ID. You must select the type of object to lookup.

Input field description

• SugarCRM module to lookup - dropdown list where you should choose the module, which you want to lookup. E.g. Accounts.
• Pass binary data to the next component (if found object has it) - a checkbox, if it is checked and found object has a binary field (type of file) then its data will be passed to the next component as a binary attachment.

Lookup Objects

Lookup a list of objects satisfying specified criteria.

Input field description

• SugarCRM module to lookup - dropdown list where you should choose the module, which you want to lookup. E.g. Accounts.
• Output method - dropdown list with following values: "Emit all", "Emit page", "Emit individually".
• Number of search terms - text field where you can specify a number of search terms (not less than 0 and not greater than 99). Default value is 0 (if provided value is not allowed).

Metadata description

Depending on the configuration field Output method the input metadata can contain different fields: Output method - "Emit page": Field "Page size" - required positive integer that defaults to 1000; Field "Page number" - required non-negative integer (starts with 0, default value 0);

Output method - "Emit all": Field "Maximum number of records" - optional positive integer (default value 1000);

Output method - "Emit individually": Field "Maximum number of records" - optional positive integer (default value 10000);

Note that the number of records the component emits may affect the performance of the platform/component.

Groups of fields for each search term go next:

Field "Field name" - string represents module's field (a list of allowed values is available); Field "Field value" - string represents value for selected field; Field "Condition" - one of the following: "=", "!=", "<", "<=", ">", ">=", "STARTS WITH", "ENDS WITH", "CONTAINS", "IS NULL", "NOT NULL", "IN", "NOT IN";

Between each two term's group of fields:

Field "Logical operator" - one of the following: "AND", "OR";

Output data depends on the configuration field Output method: "Emit page", "Emit all" - an array of records; "Emit individually" - a record;

Delete Object By ID

Given an object, deletes the object with that ID. You must select the type of object to lookup.

Upsert Object By ID

Update an existing entry if the id provided. Otherwise create a new entry. You must select the type of object to lookup. Input metadata is fetched dynamically from your SugarCRM account. Output metadata is the same as input metadata, so you may expect all fields that you mapped as input to be returned as output.

Input fields description

• Module - Input field where you should choose the object type, which you want to find. E.g. Note
• Utilize data attachment from previous step (for objects with a binary field) - a checkbox, if it is checked and an input message contains an attachment and specified object has a binary field (type of file) then the attachment is put into object's binary field.

Limitations

When Utilize data attachment from previous step (for objects with a binary field) is checked and this action is used with Local Agent error would be thrown: 'getaddrinfo ENOTFOUND steward-service.platform.svc.cluster.local steward-service.platform.svc.cluster.local:8200'

Bulk Create Objects

Provides a simple interface for quickly creating large amounts of objects.

Input field description

• Module - dropdown list where you should choose the object type to perform bulk create operation. E.g. Cases.

Metadata description

• Objects - an array of the objects that will be created. Example of format SugarCRM objects

Result is an object with a property result: array. It contains the list of newly created objects.

Bulk Update Objects

Provides a simple interface for quickly updating large amounts of objects.

Input field description

• Module - dropdown list where you should choose the object type to perform bulk update operation. E.g. Cases.

Metadata description

• Massupdate_params - an object contains array of uid's and new values for updated objects. Action allows not only update primitive fields but also add or replace values in the lists. Please, take a look SugarCRM Mass Update documentation for examples.

Result is an object with the 2 properties:

• failed - numeric, how many objects were failed to update.
• status - string, if operation were successful status="done". Operation could consider successful even if failed > 0

Note: SugarCRM server doesn't return errors in case of a wrong uid.

Bulk Delete Objects

Provides a simple interface for quickly deleting large amounts of objects.

Input field description

• Module - dropdown list where you should choose the object type to perform bulk delete operation. E.g. Cases.

Metadata description

• Massupdate_params - an object contains array of uid's for deleted objects. Please, take a look SugarCRM Mass Update documentation for examples.

Result is an object with the 2 properties:

• failed - numeric, how many objects were failed to delete.
• status - string, if operation were successful status="done". Operation could consider successful even if failed > 0

Query

Retrieve a set of records filtered by an expression utilizing the SugarCRM REST API filter endpoint. (See for details - https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_9.1/Integration/Web_Services/REST_API/Endpoints/modulefilter_POST/)

Input field description

• SugarCRM module - dropdown list where you should choose the module, which you want to lookup. E.g. Accounts.
• Output method - dropdown list with following values: Emit all, Emit individually.

Metadata description

In the Intergator mode a request can be built by filling the following fields:

• Filter expression - JSON object representing the filter expression. E.g.

  [
    {
      "billing_address_country":
      {
        "$in": ["England","France"]
      }
    }
  ]

• Maximum number of records - maximum number of records to return. Default is 20.
• The number of records to skip - number of records to skip over before records are returned. Default is 0.
• How to sort the returned records - how to sort the returned records, in a comma delimited list with the direction appended to the column name after a colon. E.g. "name:DESC,account_type:DESC,date_modified:ASC".

In the Developer mode a request can be built utilizing all features by providing a JSON object (for more information go to the link above). E.g.:

{
  "filter": [{
    "$or": [{
        "$and": [{
            "billing_address_country": {
              "$not_in": ["DE", "India"]
            }
          },
          {
            "acc_float_c": {
              "$is_null": ""
            }
          }
        ]
      },
      {
        "billing_address_country": {
          "$equals": "England"
        }
      }
    ]
  }],
  "fields": "id,name,billing_address_country,parent_name,accdate_c,acccheck_c,acc_float_c",
  "max_num": 42
}

Note that the number of records the component emits may affect the performance of the platform/component.

Output data depends on the configuration field Output method: Emit all - an array of records. Emit individually - a record.

Configuration Info

Required environment variables

For the local testing (e.g. spec-integration) ELASTICIO_TASK_ID and ELASTICIO_STEP_ID envs should be provided.

Version and compatibility information

This component interacts with version 10 of the SugarCRM REST API. It has been tested with SugarCRM Enterprise, Version 7.9.1.0 (Build 1074) and SugarCRM Enterprise, Version 8.0.0 (Build 211) (Spring '18).

License

Apache-2.0 © elastic.io GmbH

sugarcrm-component's Issues

Extend Upsert Object by ID to Upsert Object by Unique Criteria

Circle CI build should be updated to V2

Currenlty the CircleCI Build does not run as the build config is written against CircleCI V1 which is now deprecated.

Unable to fetch module - "error":"invalid_parameter","error_message":"Invalid platform specified."

After click on drop down list (Select a SugarCRM module), it show an error message. Here's a log information:

`
Starting your Docker container ...

[INFO tini (1)] Spawned child process '/run.sh' with pid '7'

Starting application in apprunner...

Starting slug download ...

Successfully downloaded and extracted slug file

node: /usr/lib/libstdc++.so.6: no version information available (required by node)

node: /usr/lib/libstdc++.so.6: no version information available (required by node)

node: /usr/lib/libstdc++.so.6: no version information available (required by node)

node: /usr/lib/libstdc++.so.6: no version information available (required by node)

node: /usr/lib/libstdc++.so.6: no version information available (required by node)

node: /usr/lib/libstdc++.so.6: no version information available (required by node)

No access token found. Fetching access token with user credentials...

{"error":"invalid_parameter","error_message":"Invalid platform specified."}

at fetchAndSaveToken (/home/apprunner/lib/sugarcrm.js:94:14)

at process._tickCallback (internal/process/next_tick.js:169:7) 'Error: Unexpected response from provider. Status code: 422 Body: \n {"error":"invalid_parameter","error_message":"Invalid platform specified."}\n at wasAuthenticationSuccessful (/home/apprunner/lib/sugarcrm.js:79:15)\n at fetchAndSaveToken (/home/apprunner/lib/sugarcrm.js:94:14)\n at \n at process._tickCallback (internal/process/next_tick.js:169:7)'

Error: Unexpected response from provider. Status code: 422 Body:

at

at wasAuthenticationSuccessful (/home/apprunner/lib/sugarcrm.js:79:15)

[INFO tini (1)] Main child exited normally (with status '0')

`

any suggestions?

Handle Deprications of Uknown Platforms

SugarCRM is depricating the ability to set the platform field for API tokens arbitrarily for future releases of SugarCRM. Currently, we set this value aribitrarily.

The current solution proposed by Sugar is that we must register a new platform for an integration. Currently, this requires installing a package onto the SugarCRM instance on which we want to do the integration. SugarCRM is working on an alternative approach that is not available.

In the meantime, we must:

• Create a package that creates an elastic.io platform. Sample is here.
• Update our instructions so that package installation is included.

Move away from Grunt, Jasmine and other older frameworks

The component still uses old versions of grunt and jasmine even when the company has moved away from both technologies to newer technologies.

Enable Faceless service in the component

Extend Lookup Object by ID to Lookup Object by Unique Criteria

Extend the list of writable fields that can be populated for SugarCRM objects

Currently, when upserting an object in SugarCRM, the inputs are rendered by taking all of the fields for the module and then excluding:

• The _hash of the fields object
• Fields that are set to read only
• All fields which are flagged as non-db (i.e. they do not directly correspond to a normal column in the DB table being upserted to)

There are some problems with the above approach. In particular, there are non-db fields that can be still written via the API. When these fields are written to in the API, there is SugarCRM side logic which causes the data to be written to the underlying DB in a non-trivial way. For instance, writing to the email field of the Contact object causes an email object to be created from the provided email string. We should extend the list of shown fields to include cases such as the above.

Based on my investigations so far, the logic for excluded fields should be such that the following are excluded:

• The _hash of the fields object
• Fields that are set to read only (e.g. date_modified)
• Fields that are compiled from other fields (e.g. full_name)
• Custom fields that are calculated from other fields (i.e. Dependant Fields)
• Fields with type relate as these fields are values read from columns of linked tables (e.g. account_name)
• Fields which signify a link to another object (these are covered in follow up issues).
• Non-simple db fields which do are of source equal to non-db

User password should not be stored in the component

The theory behind OAuth is to avoid having external OAuth apps (such as this component running on the elastic.io platform) store user passwords. Normally OAuth 2.0 supports this through the authorization code flow however SugarCRM only supports the resource owner password credentials grant type which involves the user giving their credentials directly to the external app with the expectation that the external app will fetch a token and then discard the credentials. We don't discard credentials but currently the elastic.io platform does not provide a way for credentials to only be provided once to a component/account.

We should add a trigger that can be used to query any object type on the users SugarCRM instance

We can write a generic trigger that fetches a list of object types on a given SugarCRM instance as part of the configuration step. When then generic trigger is run, it will then poll that object type.

Verify that the generic query trigger still works

SugarCRM Url, the app secret and the app ID should be configured in the same place

Currently sugar URL and app ID are in the credentials but app secret is in the env variables (and this detail isn’t documented yet). Ideally these three pieces of instance specific information should be configured in the same place.

Component Documentation in README is missing sample output

Would be great to have sample output from the action and trigger for that component

Review PR from Magic

Do code review for the pull request created by Magic team #37

Token expires instantly when there are two access points

There is an issue with access_token expiration which can affect almost every integrator using this component. The error is reported during the integration execution:

Error: Authentication error: invalid_grant: The access token provided is invalid.
    at Service.makeRequest (/app/lib/sugarcrm.js:191:19)
    at <anonymous>
    at process._tickCallback (internal/process/next_tick.js:169:7)

Here is a standard scenario:

1. OAuth2 app is created on Sugar CRM admin panel.
2. Credentials are created on elastic.io using the details from step1
3. A simple integration flow like WebHook to SugarCRM is created and activated.
4. We send a payload to WebHook, the integration flow starts working and successfully transferring the payload to SugarCRM.
5. We go back to Sugar CRM to check if the data is there. The data is there.
6. We want to try it again to make sure that everything works OK but bummer, we get the error shown above while the Sugar CRM component tried to access the Sugar CRM instance.

It is as if the token got expired in minutes!

I found the reason for this: the system detects that you are logged-in from your browser (you were checking the data in Sugar CRM) and in addition somebody is trying to access using OAuth2 from somewhere else (our platform). Some additional information can be found here:

• https://community.sugarcrm.com/thread/31763-why-does-the-api-return-invalidgrant-before-my-access-token-has-expired
• http://support.sugarcrm.com/Knowledge_Base/Troubleshooting/Troubleshooting_Sugar_Logging_Out_Unexpectedly/#Resolution

Some temporary solution could be to tell explicitly not to use the same credentials (the admin) during the flow execution, but this is not going to work since people who do the integrations are busy preparing integrations for some other client and creating different credentials might not be an option.

Action / Upsert Account field postal address is limited to 20 characters

When the customer wants to use a JSONata expression in the field like billing_address_postalcode in Action Upsert Account the field limitation 20 characters hinders the proper mapping.

Confirmed by support.

Input structure looks like:

{
  "group_code": "General",
  "disable_auto_group_change": 0,
  "addresses": [
    {
      "default_billing": true,
      "default_shipping": true,
      "lastname": "Meier",
      "firstname": "Markus",
      "city": "Essen",
      "postcode": "45130",
      "telephone": "0201 / 87 26 40",
      "company": "Markus Meier GmbH",
      "street": [
        {
          "name": "str"
        },
        {
          "name": "str"
        }
      ],
      "country_id": "DE",
      "region_id": 88,
      "region": {
        "region_id": 88,
        "region": "alkjdslakdj",
        "region_code": "NRW"
      },
      "customer_id": 7,
      "id": 6
    }
  ],
  "website_id": 1,
  "store_id": 1,
  "lastname": "Meier",
  "firstname": "Markus",
  "email": "[email protected]",
  "created_in": "Default Store View",
  "updated_at": "2019-04-04 10:27:25",
  "created_at": "2019-04-04 10:26:24",
  "default_shipping": "6",
  "default_billing": "6",
  "group_id": 1,
  "id": 7
}

Add an option to `Delete Object` action to throw an error when object is not found

Description: Currently component emits:

{
  "error": "not_found",
  "error_message": "Could not find record: ad591040-1dd0-11eb-a724-06c2ad98138c in module: Contacts"
}

when object with specified ID is not found.
It may be useful to add an option (checkbox) to throw an error in such case

Also it could be useful as in case: id = " " (empty string) action emits message like:

{
  "error": "no_method",
  "error_message": "Could not find a route with 1 elements"
}

Extend the Component to Handle Many to Many Relationships

Investigate account functionality across user password changes

Currently, the sugarCRM component stores the users password and then uses that piece of information to fetch a token to do API requests. In theory, one should be able to use the username/password to fetch a token that can be continuously renewed. Even if the user then changes their password, the token should remain valid.

Extend the Component to Handle One to Many Relationships

Add 'out' metadata to actions/triggers where it is missing

`Verify` credentials button does not really verify credentials

Description: Verify credentials button shows that credentials are verified in case of invalid input into credentials input fields.

STR:

1. Go to the production stage -> credentials section
2. Find your credentials of your sugarCRM component
3. Input invalid values into input fields
4. Click on Verify button
5. See that invalid credentials are 'verified'.

AR: Component credentials verification is broken

Logs:

Starting your Docker container ...
[INFO  tini (1)] Spawned child process '/run.sh' with pid '7'
Starting application in apprunner...
Starting slug download ...
Successfully downloaded and extracted slug file
node: /usr/lib/libstdc++.so.6: no version information available (required by node)
node: /usr/lib/libstdc++.so.6: no version information available (required by node)
node: /usr/lib/libstdc++.so.6: no version information available (required by node)
node: /usr/lib/libstdc++.so.6: no version information available (required by node)
node: /usr/lib/libstdc++.so.6: no version information available (required by node)
node: /usr/lib/libstdc++.so.6: no version information available (required by node)
No access token found.  Fetching access token with user credentials...
Error: getaddrinfo EBUSY 9238.123123123:443
at errnoException (dns.js:55:15)
at GetAddrInfoReqWrap.onlookup [as oncomplete] (dns.js:91:26)
Exception: Error: getaddrinfo EBUSY 9238.123123123:443
at Object.exports._errnoException (util.js:1014:11)
[INFO  tini (1)] Main child exited normally (with status '0')

ER:
1.Invalid credentials are not verified
2. Error message is shown: Error: Your credentials are not valid. Please see logs for details.
3. Verify button is turned to:

Verify that actions work

Field `Filter expression` should be array type but not object

Description: Valid expression cannot be used to retrieve sample or runtime when it is not wrapped into an array, so it seems that the current field type should be changed

AR:

1. for input {"name":{"$in":["C"]}} error thrown: Error! Unexpected response from provider. Status code: 422 Body: {"error":"invalid_parameter","error_message":"Unknown field $in"}
2. for input [{"name":{"$in":["C"]}}] sample retrieved

Update documentation and logo

• A logo that is 128x128 (current logo is only 64 x 64).

• Correct documentation (with screenshots) for all actions and triggers.

[sugarcrm-component] Make sure sensitive data is not logged in components

Test Changes done by Magic

Run component through PS QA process for changes done by Magic development team (#37 ).

Examine which updates are performed when modifying links between two SugarCRM entities

SugarCRM allows generic links to be established between any two SugarCRM entities. If entity A relates to entity B and then I modify that relationship so that it is between entity A and entity C it is not clear which of the three entities will produce updates that can be picked up by polling triggers.

Merge and deploy latest fixes

Images

Allow Email Property to be written for contacts

Other Feature Request

Description

Currently, when upserting a contact, an input for email is not displayed based on the current logic. However, there is an email field in the provided metadata that can be written. (There is specific logic within SugarCRM which maps the data provided in this field to separate email objects.)

Definition of Done

Email appears as one of the inputs for a contact.