Sage CRM
The following functions are specific to SageCRM.
- SageCRMLookup
- SageCRMLookupReturnMultipleValues
- SageCRMLookupCustomWhere
- SageCRMValidateDropDown
- SageCRMTranslateDropDownValue
SageCRMLookup
Description
Looks up and returns a single value from the SageCRM database. The lookup is made in a similar fashion to Lookup queries a value in a SQL database.
Syntax
- SageCRMLookup( systemid, entityid, returnfld, queryfields, queryvalues, mustreturn )
Arguments
- systemid
- As defined in Setup/System Connectors for the SageCRM database to be queried.
- entityid
- The table or entity to be queried.
- returnfld
- The field to return.
- queryfields
- A single string or array of strings for the fields used to specify the query.
- queryvalues
- A single value or an array of values used to browse of filter the view. The number of values specified in queryfields and queryvalues must match.
- mustreturn
- When set to true the function must return a value, if the function does not return a value an exception will be raised.
- When set to false, if the function will return an empty value when there is no matching record.
Example
To query the Company entity using the company name as the filter for the companyid field.
SageCRMLookup(“CRM70”, “Company”, “comp_companyid”, Array(“comp_name”), Array(%CompanyName), true)
SageCRMLookupReturnMultipleValues
Description
Looks up and returns multiple values from the SageCRM database. The lookup is made in a similar fashion to Lookup queries a value in a SQL database.
Syntax
- SageCRMLookup( systemid, entityid, returnflds, queryfields, queryvalues, mustreturn)
Arguments
- systemid
- Defined in Setup/System Connectors for the SageCRM database to be queried.
- entityid
- The table or entity to be queried.
- returnflds
- An array of the fields to return.
- queryfields
- A single string or array of strings for the fields used to specify the query.
- queryvalues
- A single value or an array of values used to browse of filter the view. The number of values specified in queryfields and queryvalues must match.
- mustreturn
- When set to true the function must return a value, if the function does not return a value an exception will be raised.
- When set to false, if the function will return an empty value when there is no matching record.
Example
To query the Company entity using the company name and website as the filter, returning the companyid and website fields.
SageCRMLookupReturnMultipleValues(“CRM70”, “Company”,
Array(“comp_companyid”, “comp_website”), Array(%CompanyName), “comp_companyid”, true)
SageCRMLookupCustomWhere
Description
Looks up a value from the SageCRM database using a custom where clause. The lookup is made in a similar fashion to Lookup queries a value in a SQL database.
Syntax
- SageCRMLookup( systemid, entityid, returnfld, whereClause, mustreturn )
Arguments
- systemid
- Defined in Setup/System Connectors for the SageCRM database to be queried.
- entityid
- The table or entity to be queried.
- returnfld
- The field to return.
- whereclause
- A filter clause to in the same form as the filter clause on the SageCRM Reader.
- mustreturn
- When set to true the function must return a value, if the function does not return a value an exception will be raised.
- When set to false, if the function will return an empty value when there is no matching record.
Example
To query the Company entity using the company name and website as the filter, returning the companyid field.
SageCRMLookupCustomWhere(“CRM70”, “Company”, “comp_companyid”, “comp_name = ‘3G Homes Ltd.’ And comp_website = ‘http://www.3GHomes.co.uk’”, true)
SageCRMValidateDropDown
Description
Validates or checks that a value for a particular drop down field within SageCRM is valid.
Syntax
- SageCRMValidateDropDown( systemid, entityid, field, value, mustreturn )
Arguments
- systemid
- As defined in Setup/System Connectors for the SageCRM database to be queried.
- entityid
- The table or entity to be queried.
- field
- The drop down field in SageCRM.
- value
- The value to be validated.
- mustreturn
- When set to true the value must be valid. If the value is not valid an exception will be raised.
- When set to false, if the value is not valid false is returned.
Example
To validate the country drop down field for 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
- Defined in Setup/System Connectors for the SageCRM database to be queried.
- entityid
- The table or entity to be queried.
- field
- The drop down field in SageCRM.
- value
- The value to be validated. The description being translated is based on the user’s language as per the logon details defined in the system connector.
- mustreturn
- When set to true the value must be valid. If the value is not valid an exception will be raised. When set to false, if the value is not valid false is returned.
Example
The following translates the value ‘Australia’ to return ‘AU’ for the country field for the Address entity.
SageCRMTranslateDropDownValue(“CRM70”, “Address”, “addr_country”, “Australia”, true)