00:00:01 Introduction

Good afternoon, everybody. I hope that everybody can hear me. Welcome to this afternoon's session on the API. We're going to be looking at ActivityInfo's API in depth to try and give you a picture of everything that's available, how you can use it, and how you can learn more about it. Typically, I know that many of you probably already use the API for querying data, but there is more to that, so I want to give a more comprehensive introduction.

We're going to walk through a quick introduction, then highlight the key concepts or resources involved in ActivityInfo's API. Third, I'm going to look at how you can find more information on the API because I think this is something that many people maybe miss. Finally, we're going to use most of the time to walk through a couple of specific uses of the API.

First, I'm really curious to know what kind of tools you're using in your work right now and what kind of programming languages, if any, you're using. I see a lot of you are using Power BI, but ArcGIS is also out there, and then we have a handful of people writing scripts in R and Python. We have one Tableau user. I do see that there are three of you who are using R, maybe more, and four of you mentioned that you're using other programming languages.

00:03:46 What is an API?

I want to make sure we give an introduction, as maybe not all of you are developers. What is an API? An API is an Application Programming Interface. It's a way for two different programs to talk to each other. For example, if this is our server, then there are lots of different tools that can talk to our server. The one that you're probably most familiar with is our ActivityInfo web app. Whenever you open up the application in your browser, this is actually a JavaScript application running and it communicates with our server using the API. So, anything you can do through the user interface, you know that you can do through the API because that's essentially how it works. Power BI is something that a lot of our users use, and that uses the API to query data from our server. But you can also write an R script or Python script using the same API to communicate with our server and exchange information.

There are many different types of APIs. The one that we're going to talk about today is the web API, which is an Application Programming Interface that can be accessed using the Hypertext Transfer Protocol (HTTP). Web APIs are organized around URLs. Our API has a series of URLs that your programs can use to get information. That is called the method GET. When you type in www.activityinfo.org in your browser, your browser sends a GET request to the server and asks for a web page.

There are other methods, such as POST. When you want to make changes or update something on our server, you can send a POST request. These are the two that we really use in our API: GET and POST. You will also see placeholders in the URLs, meaning you should be putting in an ID, such as a formId or a databaseId, into your URL.

Just like your browser sends a GET request and gets back HTML to display the page, our API doesn't return HTML. It sends back something in a format called JSON, or JavaScript Object Notation. JSON is a format that's easy to use when you're programming, or Power BI can also consume it. It is a series of objects and lists. If I type in a specific API URL into my browser, I get JSON back. Firefox will "pretty print" it for you, showing lists and objects with their fields.

00:10:01 ActivityInfo's data model

ActivityInfo has quite a lot of features, but at its core, it's a very simple system. There aren't that many different things that the application deals with. The five or six key things that ActivityInfo contains and that you can manipulate and query with the API are databases. You can list databases or get a database. A database has folders and forms in it. Forms are a very important thing that you can manipulate; nearly everything in ActivityInfo is a form. Forms have records, which are individual completed forms that you can submit, add, edit, and delete independently.

For databases, the other important element is database users. Every time that you are added to a database, you're also added as a database user. This determines who has access to a database and what permissions they have. Next to that, we have reports, which allow you to summarize and analyze multiple forms from many databases together in one place.

00:11:44 Mapping concepts to URLs

Each of these concepts maps to a set of URLs. If you want to find the list of databases that you have access to, you could copy and paste the database URL into a browser, and you'll see a list of databases in JSON format. If you want a specific database, you can query /databases/[database ID]. If you want to know the users that are present in a database, you can add /users onto the end. This gives you the contents of the database, including who owns it, your role, and the resources like folders and forms.

You can see each of the users that have been invited, their email, the date they were invited, and the delivery status. You can use this format, and again, each of the highlighted portions between the curly brackets are placeholders where you would put the ID of a form, record, or database. For example, if I want a form, I can paste the form URL with /schema at the end, and that gives me the schema of the fields in this form, such as reference fields, IDs, types, and validation conditions.

00:15:08 Finding documentation and resources

ActivityInfo has many features, and so does our API. The one thing I hope you will take away from this webinar is where to find more information about the API. Your first stop is the API reference. The API reference has details on every single endpoint and the kinds of information that you will get from that endpoint. For example, if you want to list a database, the documentation shows the URL you have to query and the response you'll get, including arrays, lists, and properties. It also shows how to update a database, such as adding a folder, and what kind of response codes (like 200, 401, 403) you might receive.

Another source of information is our documentation under "Support." We have a section on integrating with external systems, including a tutorial on how to connect using the API to Power BI. We also have links to the R package. If you're an R programmer, you can install this package from RStudio and use functions like getDatabases() to get the same kind of result that I got through the API directly in the browser. The R package makes these requests for you so you don't have to remember all the URLs.

Regarding the question about needing to know a programming language: ActivityInfo is user-friendly and you don't need to know a programming language to use the application itself. However, the API is essentially for programmers. This session is geared towards people who are programming and want to write scripts to connect to ActivityInfo.

Another resource is the user interface itself. In the export menu, under "Export via the API," you can click "Query all fields as JSON." This gives you a URL that you can paste into your browser or Power BI to query all the data.

Finally, since the ActivityInfo application uses the API to communicate with our server, one of the easiest ways to learn the API is to use the browser's developer tools. Hit F12, go to the Network tab, and perform an action in the UI. You can see the request that was sent, the URL, and the payload. You can even copy the request as cURL and paste it into a terminal to execute the exact same request. This is a great way to reverse-engineer how the application does something.

00:28:46 Querying data

We're going to go through some examples of using the API, starting with querying data. When you're using the API, the first thing you'll probably want to do is create an API token. You need to tell the server who you are. You can create a token in your Profile Settings under "API tokens." This allows you to create a special password just for API use, which is safer than using your main password in scripts. You can choose if the token is read-only or read-write. If you're only querying data, I suggest limiting it to read-only.

The API documentation tells you how to query records in a form. You can use the "Export via the API" feature in the UI to get the URL. This returns a list of JSON objects for each field, as well as reference fields and basic information like the unique record ID. Every record in ActivityInfo has a unique, automatically generated ID that never changes.

A common question regarding querying data is about subforms. The query API returns data in a tabular format, and subforms don't fit into a single table. If you query the parent form, you won't find the subform fields. To get that information, you must navigate down to the subform. A subform is also a form with a special relationship to the parent. You can take the form ID of the subform and query that data. You will see the details of the sub-records as well as information from the parent form, such as parent location or date.

You can be more selective about what you get by adding a query string. It is a bit like SQL. For example, if you only want the site name, you can add ?name=site_name. You can rename columns using aliases. An easier way to build this query is to use the "Select columns" feature in the user interface. You can select the fields you want, rename them, and even drag in fields from the parent form. Then, go to "Export via the API" and select "Query selected fields as JSON." This generates the specific query string for you. We also have a built-in feature that gives you the exact R code for the query, which you can paste directly into RStudio.

For exporting a lot of data at once, like a whole database or large quantities, you might need to use the batch export API. This involves starting a batch job, querying the status, and then downloading the result. The R and Python packages handle this for you with functions like getQuantityTable.

00:48:37 Updating and pushing data

I would like to spend some time on updating records. Some of you may be from NGOs or agencies where you are sending updates through the user interface each month to a coordination database. You might have your own information system, like Access or Microsoft Dynamics. We want to encourage partners to use the API to integrate systems so you can submit coordination data automatically.

Let's look at how that works. I'll create a form for WASH responses with a geographic reference, a donor field, and a monthly report subform. Ideally, these forms are designed with codes (e.g., governorate codes, donor codes) which you can use in the API instead of the internal IDs.

If you want to push data from your existing system into ActivityInfo, you can use the updateRecord API. One challenge is getting the IDs to line up. You need a mapping from your system's sites to the sites in ActivityInfo. You can find the necessary IDs (like governorate IDs) by exporting the data, which includes the reference IDs.

Using the R client, you can use the updateRecord function. You provide the form ID and the record ID. ActivityInfo uses CUIDs (Collision-resistant Universal IDs) which are generated by the client. This allows records to be added offline. You send a list of field values, and you can use the codes you defined instead of IDs. For example, month = "2021-08".

If you try to update a record with a read-only token, you will get a permission denied error (403). You need a read-write token. What is handy about API tokens is that you can revoke them. If a token is compromised, you can just revoke it without changing your main password.

01:00:49 Conclusion and Q&A

We've hit the one-hour mark. I'll point you to the examples section in our repository where you can see some common tasks for automation, such as user management. For instance, there are scripts to delete all users in a database except for specific people, which is useful for archiving databases at the end of the year.

We have more upcoming webinars. Next week, we're doing a general webinar on survey sampling. At the end of the month, we have a webinar for people who are new to ActivityInfo. We are also working on expanding our support for R and Python and will likely have specific webinars for those, as well as Power BI.

Thanks for joining me today. Please leave your feedback in the survey as the webinar ends so that we can continue to improve these sessions. Have a great afternoon, everybody.