OData API¶

The APIs are all readonly and follow the OData standard. See that link for more details.
All OData calls also include links to return metadata about the return if that is needed. An example can be found by issuing an authenticated GET to https://trialdb.tpsdb.com/api/$metadata#People.
We return a max of 1000 records per request. If more than that match, then the result will include the link to the next page (via the $skip parameter).
Authentication to the OData API requires a user with both the Developer and APIOnly roles (APIOnly might not exist and will need to be created).

Important

The examples below show using curl, a command line tool for transferring data using various URL protocols. This tool comes on a Mac and can be downloaded for Windows.

OData uses a $ (dollar sign) in the URL for filter operations. Curl does not properly encode the $ sign. So, in the following curl commands, we show the dollar sign as escaped with a \ in front of it. The acutal URL that will be used does not have that backslash.

Note

The examples below explicitly set the authorization header, but you can also set it like so:

curl --user "apionly:MyApiPasswordToRuleThemAll" \
    "https://trialdb.tpsdb.com/api/People?\$top=5"

To encode credentials as shown in the examples below, you can use a website such as Base64 Conversion Utility.

People¶

Get the top 5 people records.

curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
    "https://trialdb.tpsdb.com/api/People?\$top=5"

Get the people records that have been modified since 2015-09-01:

curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
    "https://trialdb.tpsdb.com/api/People?\$filter=ModifiedDate+ge+2015-09-01"

Contributions¶

Get the top 5 contribution records.

curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
    "https://trialdb.tpsdb.com/api/Contributions?\$top=5"

Get all contributions since 2014-12-30.

curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
    "https://trialdb.tpsdb.com/api/Contributions?\$filter=ContributionDate+ge+2014-12-30"

Same as above, but with greater time granularity.

curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
    "https://trialdb.tpsdb.com/api/Contributions?\$filter=ContributionDate+ge+2014-12-30T23:59:59.99Z"

Pledges¶

Get the top 5 pledge records.

curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
    "https://trialdb.tpsdb.com/api/Pledges?\$top=5"

Funds¶

Get contribution fund details.

curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA=="
    "https://trialdb.tpsdb.com/api/Funds"

Lookups¶

Lookups are good to find more details about records (i.e. such as campuses and/or family positions). They have defaults in our system, but allow for additional configurable lookups customized by the church.

Get all marital status lookups.

curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
    "https://trialdb.tpsdb.com/api/lookup/MaritalStatuses"

Get all campus lookups.

curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
    "https://trialdb.tpsdb.com/api/lookup/Campuses"

Get all family position lookups.

curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
    "https://trialdb.tpsdb.com/api/lookup/FamilyPositions"

Get all contribution type lookups.

curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
    "https://trialdb.tpsdb.com/api/lookup/ContributionTypes"

Get all gender lookups.

curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
    "https://trialdb.tpsdb.com/api/lookup/Genders"

Organizations¶

Get the organization member under org “2181006” with a people ID of “1”.

curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
    "https://trialdb.tpsdb.com/api/OrganizationMembers?\$filter=OrganizationId+eq+2181006+and+PeopleId+eq+1"

Get all organizations.

curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
    "https://trialdb.tpsdb.com/api/Organizations"

Custom¶

You can Get the results of any SQL query using the URL https://trialdb.tpsdb.com/CustomAPI/file where file is the name of the SQLScript in Special Content.

You can also pass parameters to the script using the QueryString. These parameters will be available inside your SQL as @ variables. For example if you passed the querystring ?dt=5-5-2000&div=121 at the end of the URL, you would have @dt and @div available to your SQL to filter by.
The same Authentication Header applies this case (Basic Authentication, Https protocol, APIOnly role)

Important

The SQLScript you use must begin with the comment line --API as in the example below.

If the following SQLScript called mytest is in your Special Content > SQLScripts

--API
SELECT TOP 5
    PeopleId,
    FirstName,
    PreferredName,
    LastName,
    PrimaryCity,
    Age
FROM dbo.People
WHERE PositionInFamilyId = 10 -- Primary
AND GenderId = 1 -- Male
AND MemberStatusId = 10 -- Member
AND PrimaryCity = @city
AND age > @olderthan

And you have an APIOnly role user with a username of apionly and trialdb is your church’s database, then you can run the following command. This command actually works since trialdb is a real database with fake people. Note that the at the end of the first line may need to be removed if you run this on windows:

curl -H "Authorization: Basic YXBpb25seTpNeUFwaVBhc3N3b3JkVG9SdWxlVGhlbUFsbA==" \
"https://trialdb.tpsdb.com/CustomAPI/mytest?olderthan=65&city=Bartlett"

And your results will be something like the following JSON:

[
  {
    "PeopleId": 2100183,
    "FirstName": "Charles",
    "PreferredName": "Charles",
    "LastName": "Brown",
    "PrimaryCity": "Bartlett",
    "Age": 76
  },
  {
    "PeopleId": 2101567,
    "FirstName": "James",
    "PreferredName": "James",
    "LastName": "Collins",
    "PrimaryCity": "Bartlett",
    "Age": 95
  },
  {
    "PeopleId": 2106865,
    "FirstName": "Blake",
    "PreferredName": "Blake",
    "LastName": "Ministry",
    "PrimaryCity": "Bartlett",
    "Age": 70
  },
  {
    "PeopleId": 2106957,
    "FirstName": "Anthony",
    "PreferredName": "Anthony",
    "LastName": "Damron",
    "PrimaryCity": "Bartlett",
    "Age": 68
  },
  {
    "PeopleId": 2107964,
    "FirstName": "Paul",
    "PreferredName": "Paul",
    "LastName": "Elmore",
    "PrimaryCity": "Bartlett",
    "Age": 74
  }
]