You can use the REQUEST function in a Data Events script to send a GET or POST request to the Query API. The Query API lets you run standard SQL statements against your organization's data, so your script can look up information, such as how many records an app contains, while a user fills out a record.
The Query API is read-only. It cannot change data in your organization.
Prerequisites
An API token. See Authentication in the Fulcrum Developer Docs.
The exact name of the app you want to query.
Don't expose your API token. A token written into a Data Events script is visible to anyone who can view the app's script, and it can do whatever the member who created it can do. Use a token from a member with a custom role that has restricted permissions. For read-only access, clear all permissions in the role. See How can I create custom roles with specific access rights?
Writing the request
The following function sends a GET request that counts the records in an app. Replace YOUR_API_TOKEN with your token and My App with the name of your app.
javascript
const apiToken = 'YOUR_API_TOKEN';
const getTableSize = () => {
const query = 'SELECT COUNT(*) FROM "My App";';
const url = 'https://api.fulcrumapp.com/api/v2/query?format=json&q=' + encodeURIComponent(query);
const options = {
url: url,
method: 'GET',
headers: {
'x-apitoken': apiToken,
'Content-Type': 'application/json',
'Accept': 'application/json'
}
};
REQUEST(options, function (error, response, body) {
if (error) {
ALERT('Error with request. See console for more information.');
console.log(error);
} else {
ALERT('Request successful');
const result = JSON.parse(body);
console.log(result);
}
});
};
The script works as follows:
Query: The
queryvariable holds the SQL statement. Use the name of your app, in double quotes, as the table name. You can also use the app's ID.URL: The
urlvariable builds the Query API address.format=jsonreturns the results as JSON, andencodeURIComponentmakes the query safe to send in a web address.Header: The
x-apitokenheader sends your API token.Callback: The function runs when the request finishes. If the request fails, the user sees an alert and the error appears in the console. If it succeeds, the parsed results appear in the console.
The Query API returns JSON with a fields list and a rows list. For the count query above, the count is in the first item of rows.
To run the function, call it from a Data Events handler. For example, to run it when a record opens, add ON('load-record', getTableSize); to your script.
Testing the request
Save your Data Events script.
Open the app in the Fulcrum web app and open a record.
Open your browser's developer console.
Expected outcome: An alert says Request successful, and the query results appear in the console.
Troubleshooting
The request returns an error
Cause: The token, the URL, or the query is not correct.
Solution: Check that
apiTokenhas your token, that the URL matches your organization's region, and that the app name in the query matches exactly.
An error says a relation does not exist
Cause: App or repeatable section names longer than 63 characters can cause this error.
Solution: Shorten the name, or use the app's ID in the query instead of its name.
Notes
The Query API accepts both GET and POST requests. To learn more, see GET Query and POST Query in the Fulcrum Developer Docs.
The Query API uses the same limit as the REST API: 5,000 calls per hour for each user. A query can also run for no more than 10 seconds. Avoid running a request every time a record opens in apps that many people use.
In the web app, REQUEST needs a secure address (HTTPS) and a server that allows requests from other websites (CORS). The Fulcrum API URL in this example meets both.
The example uses a query string in the URL. REQUEST also has a
qsoption for query string parameters. To learn more, see the REQUEST reference.
The example uses the United States endpoint. If your organization is hosted in another region, see Working with the Australian, Canadian or European Instances for the correct address.