In This Article:

Salsa Classic API

In This Article:

Salsa provides an API that lets applications read to and write from the Salsa Classic database. Security is provided by an authentication call. The authentication call returns a token in the form of a cookie, and the token cookie is provided with all subsequent calls.

This section of the Salsa documentation

describes all of the API calls,

describes the parameters that the API calls use,

provides best practices and warnings, and

provides examples of how to use the API calls.

Please note:

Salsa Classic API is only available for the Salsa Classic product.

Salsa Classic API is used from external servers. Salsa Classic API will not work in server-side, public-facing pages. Please click here if that's what you need.

Salsa Classic API will not work with Salsa Engage Please click click here if you need to access Engage from external servers.

Best practices

Assign credentials to the application and to the developer. Have the developer provide a way for the app to have its credentials changed. This will provide mutual security for your org and the developer since the developer will not know the app's credentials without the organizations permission after development is complete.

Campaign manager email addresses can contain any non-empty string. Credentials for an application need not be in the format of an email, and can be anything that makes sense to the organization.

The authentication cookie must be presented to all API calls after authentication. The recommended practice is to use an HTTP library that retains and presents the cookies in the correct formats.

Check for success after authentication. The credentials in your app may have been revoked, and your app should be prepared to handle that possibility gracefully.

An authentication session lasts about two hours. If your application will run more than that amount of time, then it should be prepared to authenticate more than once.

<?xml version="1.0"?> <response> <error table="supporter" key="59155521" exc="java.lang.Exception: You do not have access to this row:59155521"> You do not have access to this row:59155521" </error></response>

JSON Results

{ 'object': 'supporter', 'key': '999999999', 'result': 'error', 'messages': ['exc: java.lang.Exception: You do not have access to this row:59155525']

getLeftJoin.sjs

The getLeftJoin.sjs call allows you to use the mapping table to retrieve information about a supporter and the groups, events and actions that the supporter belongs to or attended. For example, you can use getLeftJoin.sjs to

Find all of the groups that a supporter belongs to

Read supporter information for everyone in a particular group

Find the email addresses for supporters that have taken one of many actions

Create a registration list for the supporters that are attending an event

Find all of the supporters in a group

Background

The Salsa database schema can be viewed as the supporter table surrounded by a a ring of other tables such as groups, action or events. The outer ring of tables are logically linked to the supporter table with many-to-many relationships. For example, a supporter can belong to many groups (groups), or may take many actions (action) or atte3nd many events.

The supporter table is linked to these other tables via "mapping tables" in the database. The mapping tables hold a single record for each time that a supporter joins a group, or takes an action or attends an event. Each mapping table record contains at least the primary key for a supporter and a primary key for the other table (groups_KEY, action_KEY, etc.)

Mapping tables are named for the two tables that they link, with supporter generally first in the name. For example, the supporter_groups links the supporter table to the groupstable to indicate the groups to which a supporter belongs. It can also be used to find the supporters that belong to a group.

Parameters

Required parameters

object

parameter that contains a description of the tables to be merged and the fields used to merge them. There are two general formats:

left(left-key)right and

left(left-key)center(right-key)right

The first form is used to join two tables, for example groups and supporter. This is the most common object parameter used when calling getLeftJoin.sjs

The second form is used to join three tables. An example is to retrieve supporter records and the detailed records for the actions that they have taken. In this caser, getLeftJoin.sjs joins the supporter and action table using the supporter_action as the mapping table.

Note: In the second form, the mapping table is always the center table

left

is the table to used to select known values. In Salsa, left is usually the supporter table

We get a lot of questions about how to do specific joins, so here is a sample of common joins that you might find helpful. All of the examples have been broken down to aid legibility. In reality, they are really loooong URLs.

Retrieve the dates and amounts for donations made by the supporter with email address super@rich.person

Retrieve the first name, last name, email, for all supporters who made donations in 2014. Also retrieve the transaction date and amount for each matching donation record. Records are sorted by the amount in descending order.

Keep in mind that Salsa will not return more than 500 records on a single getLeftJoin.sjs call

Remember that you may have to disambiguate field names because the field may appear in more than one of the joined tables

Joining two tables can return a lot of data. Doing the join incorrectly could really bog your application down

If you are just scrolling through a table, then use getObject.sjs instead

getCount.sjs

Count records in the database that match the provided criteria

API_HOST/api/getCount.sjs?object=tablename&condition=..

The getCount.sjs call counts records in the database. In its simplest form, getCount.sjs will returns the count all of the objects available. In its more typical forms, getCount.sjs can count the records that match criteria provided by the developer

At this writing, Salsa does not return the count in JSON format for a call like this:

API_HOST/api/getCount.sjs?object=supporter

If you need to count records, then please use &xml instead of &jsonuntil this problem is fixed.

getCounts.sjs

return counts, maximums and minimums for records that meet the provided criteria

API_HOST/api/getCounts.sjs?object=tablename&condition=&countColumn=

The getCounts.sjs call counts records in the database and does some basic calculations on the field values:

count

minimum

maximum

sum

This can be handy for basic statistical analysis of numeric fields like donation amounts or custom fields. The minimums and maximums work on all data types, and can be used to find upper and lower bounds for names and dates.

In order to pass parameters to a report using getReport.sjs, you'll need to run the report at least once to capture the parameter names and to learn the correct format for their values. See the examples for a sample call to retrieve a Salsa report.

Both inserts and updates are done by using a /save call. If there is a key parameter in the /save call, then Salsa attempts to update the record with a primary key of the key value. If the key parameter is not provided to /save, or if the key value is not a valid primary key, then Salsa automatically creates a new record.

There is an exception. A /save to the supporter table uses three fields to determine if a supporter record exists:

uid

supporter_KEY (primary key for the supporter table)

Email

If any of these fields match an existing record, then the values in /save are used to update the matched record. The fields are checked in the order shown, and the first field match determines the record to use. If no supporter records match, then a new record is created.

Parameters

Parameters are used in the /save call to specify

the database table to update,

the primary key for the record to be updated,

the data to be stored in the record, and

a method to link the record to records in other database tables.

Required parameters

object

The object parameter specifies the database table name.

Please note that some fields in some tables have restrictions and reservations, and may not behave the way that you might expect. You will want to peruse the database table layouts in this section of the documentation to learn abut the field restrictions and reservations that apply to the table to which your application is writing.

fieldName=value

Specify a field namein the object database table and the value that will be stored in it. Many fieldname=value parameters can be used to change or save multiple fields in the record. Here are some items that you'll need to know:

Field names are case sensitive. "Email" is correct, "email", "EMAIL" or "EmAiL" are wrong.

Field names are Camel-cased if they are fields in the object database table.

Lower-case names are custom fields, with one exception...

The "amount" field in the donation table is lower-case. -- "amount" is correct, "Amount", "AMOUNT" or "amOUNT" are wrong.

Optional parameters

key

The key parameter is used to specify the primary key in object where the record will be saved. There are three valid forms of the key parameter.

key=0 (zero)

When a key is zero, Salsa generally creates a new record. The exception to this behavior is when a supporter record is being saved. Salsa may determine that the contents should be used to update an existing record instead.

key=NUMBER

When the value provided in a key parameter is a number, then Salsa attempts to update the record with a primary key of NUMBER. If the provided number does not resolve to a record in object, then Salsa returns an error result.

When NUMBER is the primary key for a record, then Salsa uses the fieldname=value parameters to modify the record. If fieldname is not a valid field for object, then Salsa ignores it. Otherwise, the value of the field in the selected record is changed.

Salsa writes the record to the database when all fields are processed. Please note that Salsa returns both success and error indications that need to be handled by your application.

(key parameter not provided)

If a key parameter is not specified, then Salsa uses the same logic as in the key=0 case.

The link parameter contains the table name for the non-object table in the many-to-many relationship. For example, "groups".

The linkKey parameter contains the primary key of the non-object table. Use a value value of zero in the linkKey parameter to indicate that a new record is being written.

Parameters

the parameters used by API calls

Key

The key parameter is used to specify that an API call is used for only a single record. The key value is the primary key for the record to act on. Primary keys in Salsa are named table_KEY, where table is the database table name. Some examples:

supporter_KEY

donation_KEY

supporter_action_KEY

Object

The object parameter is a required parameter for all API calls. It specifies the database table to use for the call. Here is a list of the most commonly used database tables

The supporter table contains all of the information that Salsa stores about an individual supporter. A record contains both the standard fields (camel-cased field names) and the custom fields (lower-case field name). The suporter table can be viewed as the center of the Salsa data schema, with one-to-one links to some tables and one-to-many links to others

Each donation record contains the information about a single donation. There is a one-to-many relationship between a supporter and donations. Note that there is no call in the Salsa API to submit a donation for processing.

Note:There's not a mechanism in the API to submit a donatio for processing. Adding a record to the donation table simply adds a record.

Notes about the object parameter

Most database names are singular (supporter, donation, action, etc)

The "groups" table is the only table that has a plural name.

The object parameter is case-sensitive. "supporter" and "donation" are correct. "Supporter" and "DONATION" are not correct.

Conditions are used to restrict the records returned from an API call by the contents of the records being retrieved. If a condition is true for a record, then the record is returned.

More than one condition can be provided for each API call. When there are more than one condition, then a record is returned if all of the conditions are true. The conditions can be thought of as being AND-ed together. Note that there is not an OR operator for conditions.

A condition has the general form of

&condition=field_name OPERATOR value

(Spaces are provided for illustration and should not appear in the condition query.)

The field_name parameter is the name of one of the fields in the table whose name appears in the object parameter for an API call. The field names and types are listed in the table schemas for Salsa tables. You can find schemas for the most common tables here.Field_names are formatted based on how they are used.

Generic format

The generic format of a field_name is

table_name.field_name

where table_name. can be optional.

Single table format

The bulk of Salsa's API calls operate on a single database table. In these calls, the table_name and .(dot) are left off, and the format of a field_name is

field_name

.

Multi-table format

The getLeftJoin.sjs call requires that field_names in comparisions have the full generic format. Salsa can use the same field name in more tha one table, and the table_name is required to disambiguate which field to use for comparison. For example, joining the supporter table to the donation table duplicates many field names. In order to compare against the Email field in the supporter table, the condition would look like this:

Custom field_names are all lower_case, and words are separated by an under bar. For example

membership_level

membership_type_code

dietary_choices_

fees___where applicable

Field_names are case-sensitive. Email is not the same field as email.

>In rare cases, database field_namesamount in the donation table). IN this case, amount is correct, Amount is not.

OPERATION

Specifies the type of comparision to do.

=

the content of field_name is equal to value. For example

First_Name=Judy

Date_Created=2015-09-03

amount=50

amount=12.34

true_or_false=1

<

the content of field_name is less than value

>

the content of field_name is greater than value

<=

the content of field_name is less than or equal to value

>=

the content of field_name is greater than or equal value

IS EMPTY or %20IS%20EMPTY.

the field value has a length of zero or is aYes/No field with a value of null or false. Note that the value is not needed. For example

First_Name IS EMPTY

First_Name%20IS%20EMPTY

IS NOT EMPTYor %20IS%20NOT%20EMPTY

the field value does not have a length of zero. Note that the value is not needed. For example

First_Name IS NOT EMPTY

First_Name%20IS%20NOT%20EMPTY

IN or %20IN%20

the field value is in a list of comma-delimited values in the value. For example

&condition=First_Name IN Bob,Ted,Carol,Alice,Rover

&condition=membership_level%20IN%20Gold,Silver,Tin

NOT IN or %20NOT%20IN%20

the field value is not in a list of comma-delimited values in the value. For example

&condition=First_Name NOT IN Fred,Barney,BamBam

&condition=membership_level%20NOT%20IN%20Platinum

LIKE or LIKE or %20LIKE%20

The field value matches a pattern. The pattern may be an exact match (for example "Bob") or it may contain wild card characters.

Salsa uses the percent sign ( %) from MySQL as the wild card. Note that the percent sign must be encoded as %25 if you are working in a browser window. and before the URL is submitted via an application. Here are some examples:

Find names that start with "A"

&condition=FirstName LIKE A%25

Find donations in December

&condition=Date_CreatedLIKE%25-12-%25

Last names that end in "son"

&condition=Last_Name%20LIKE%20%25son

Value

The value parameter provides the value that's compared against the value in the field_name field in the record. The value can be any text or number. The value cannot be another field. The value should not contain the following characters. If you must have them, then escape them as shown:

apostrophe - &apos;

quote - &quot;

less than - &lt;

greater than - &gt;

These operators must have a value:

equals

not equals

less than

greater than

less than or equal

greater than or equal

IN

NOT IN

These operators must not have a value:

IS EMPTY

IS NOT EMPTY

Fields in Salsa have different types, and values in comparisons need to be the same type.

Include

Summary

The include parameter enumerates the fields to return in each record.

Details

The orderBy parameter is a comma-separated list of field names. Salsa returns a record from the database that contains the primary key for the and the values for each of the field names in the list.

Remember that you must disambiguate the field names if you are joining tables. For example, if you are joining the supporter and donation tables, the field names must specify eithersupporter or donation. For example,

&orderBy=supporter.Last_Name,-supporter.First_Name,donation.amount

Limit

Summary

The limit parameter tells Salsa the maximum number of records to return. The generic format for the limit parameter is

&limit=[offset],count

Details

if offset is provided, then Salsa skips that number of records and begins reading. Note that Salsa's record counter is zero-based, so the first record in a table is record zero. Skipping 100 records will start reading on a record with offset of 100.

Salsa attempts to read all count records. Here are the possible outcomes:

If the table is empty, then an empty array is returned. Responses in XML return a count field of zero.

If less than count records are available,

If the number of available records is greater than or equal to count, then Salsa reads count records but not more than 500 records. Responses in XML return countcountfield.

In order ready more than 500 records, then multiple reads need to be done. Each read will return 500 records as long as there are at least 500 records avialable. The last logical read will return a number less than 500. That will be your indication that the end of the table has been reached and that there are no more records available.

Attempting to read past the end of the table returns an empty array. Responses in XML return zero in the countfield.

Usage

A limit can appear in all API calls that return more than one record:

getActions.sjs

getCount.sjs

getCounts.sjs

getLeftJoin.sjs

getObjects.sjs

getTaggedObjects.sjs

A limit parameter is invalid in getObject.sjs because it only returns one record.

Metacode

Here is some metacode to demonstrate how to read more than 500 records from Salsa:

OrderBy

Summary

The orderBy parameter sorts the returned records by field value. Records can be sorted by any combination of fields in ascending or descending order.

Details

The orderBy parameter is composed of comma separated field names. If a minus sign (-) appears before the field name, then records ae sorted by the field value in descending order. The default is to sort in ascending order

Examples

Sort a list of supporter records in ascending order on the Last_Name and First_Name fields.

&orderBy=First_Name,-membership

Sort a list of supporter records in ascending order on the Zip field and in descending order on the First_Name field.

&orderBy=-Zip,First_Name

XML

API_HOST/api/anyAPICall.sjs?xml&object=...

Use &xml or &xml=true in any Salsa API call to return output in XML. If neither &xml nor &json is provided, then the output is returned as XML.