Sage CRM

These functions are specific to Sage CRM.

• SageCRMLookup
• SageCRMLookupReturnMultipleValues
• SageCRMLookupCustomWhere
• SageCRMValidateDropDown
• SageCRMTranslateDropDownValue

SageCRMLookup

Description

Looks up a single value in the Sage CRM database, in the same way that Lookup queries a value in a SQL database.

Syntax

SageCRMLookup( systemid, entityid, returnfld, queryfields, queryvalues, mustreturn )

Arguments

• systemid
  • The system id, as defined in Setup/System Connectors, of the Sage CRM database to query.
• entityid
  • The table or entity to query.
• returnfld
  • The field to return.
• queryfields
  • A single string or an array of strings naming the fields to query on.
• queryvalues
  • A single value or an array of values used to browse or filter the view. The number of values specified in queryfields and queryvalues must match.
• mustreturn
  • When True, the function must return a value, and raises an error if it finds none.
  • When False, the function returns an empty value when there is no matching record.

Example

To query the Company entity for the companyid field, filtering on the company name:

SageCRMLookup(“CRM70”, “Company”, “comp_companyid”, Array(“comp_name”), Array(%CompanyName), true)

SageCRMLookupReturnMultipleValues

Description

Looks up several values in the Sage CRM database, in the same way that Lookup queries a value in a SQL database.

Syntax

SageCRMLookupReturnMultipleValues( systemid, entityid, returnflds, queryfields, queryvalues, mustreturn)

Arguments

• systemid
  • The system id, as defined in Setup/System Connectors, of the Sage CRM database to query.
• entityid
  • The table or entity to query.
• returnflds
  • An array of the fields to return.
• queryfields
  • A single string or an array of strings naming the fields to query on.
• queryvalues
  • A single value or an array of values used to browse or filter the view. The number of values specified in queryfields and queryvalues must match.
• mustreturn
  • When True, the function must return a value, and raises an error if it finds none.
  • When False, the function returns an empty value when there is no matching record.

Example

To query the Company entity, filtering on the company name and website, and return the companyid and website fields:

SageCRMLookupReturnMultipleValues("CRM70", "Company", Array("comp_companyid", "comp_website"), Array(%CompanyName), "comp_companyid", true)

SageCRMLookupCustomWhere

Description

Looks up a value in the Sage CRM database with a custom where clause, in the same way that Lookup queries a value in a SQL database.

Syntax

SageCRMLookupCustomWhere( systemid, entityid, returnfld, whereClause, mustreturn )

Arguments

• systemid
  • The system id, as defined in Setup/System Connectors, of the Sage CRM database to query.
• entityid
  • The table or entity to query.
• returnfld
  • The field to return.
• whereclause
  • A filter clause in the same form as the filter clause on the SageCRM Reader.
• mustreturn
  • When True, the function must return a value, and raises an error if it finds none.
  • When False, the function returns an empty value when there is no matching record.

Example

To query the Company entity, filtering on the company name and website, and return the companyid field:

SageCRMLookupCustomWhere(“CRM70”, “Company”, “comp_companyid”, “comp_name = ‘3G Homes Ltd.’ And comp_website = ‘http://www.3GHomes.co.uk’”, true)

SageCRMValidateDropDown

Description

Checks that a value is valid for a drop-down field in Sage CRM.

Syntax

SageCRMValidateDropDown( systemid, entityid, field, value, mustreturn )

Arguments

• systemid
  • The system id, as defined in Setup/System Connectors, of the Sage CRM database to query.
• entityid
  • The table or entity to query.
• field
  • The drop-down field in Sage CRM.
• value
  • The value to validate.
• mustreturn
  • When True, the value must be valid, and the function raises an error if it is not.
  • When False, the function returns False if the value is not valid.

Example

To validate the country drop-down field of the Address entity:

SageCRMValidateDropDown(“CRM70”, “Address”, “addr_country”, “UK”, true)

SageCRMTranslateDropDownValue

Description

Translates a drop-down description to the underlying value.

Syntax

SageCRMTranslateDropDownValue( systemid, entityid, field, value, mustreturn )

Arguments

• systemid
  • The system id, as defined in Setup/System Connectors, of the Sage CRM database to query.
• entityid
  • The table or entity to query.
• field
  • The drop-down field in Sage CRM.
• value
  • The value to validate. The function translates the description in the language of the user whose logon details are set in the system connector.
• mustreturn
  • When True, the value must be valid, and the function raises an error if it is not. When False, the function returns False if the value is not valid.

Example

This translates ‘Australia’ to ‘AU’ in the country field of the Address entity:

SageCRMTranslateDropDownValue(“CRM70”, “Address”, “addr_country”, “Australia”, true)