Where is payment information stored in Slate?

Information related to payment transactions is stored in two places.

• First, activities or interactions on the person or application record are used to indicate when an applicant owes a payment, whether a payment is waived, and when a payment has been received or refunded. These activities could be considered as collectively representing the state of an applicant's "account", and Slate will add them up (by payment account) appropriately to determine what is owed. Payment received activities can show the history of payments accepted, either through Slate or (in the case of manually added Payment Received activities) elsewhere. In some ways this layer functions as a very simplified ledger for a given payment account prompt.

• Second, for online payments made through (or facilitated by) Slate, we store payment items in the payment table. The section where this data is displayed is called the Payment History. This history can be viewed either on the Payment History page, or by creating a query using the Payment query base. This history will include only transactions made online through Slate, or facilitated via Slate (using the external payment processor set up for your database), and will show payments that were received (i.e. successfully completed) and (for certain payment providers) payments that were rejected. If you are using our built-in Slate Payments product, the payment history will also include refunded and disputed payment transactions, as well as pending items and certain adjustments.

Which type of data do I want to look at?

If you are interested in data stored as a result of online payments coming through Slate (either using our built-in Slate Payments tool or using an integration with an outside payment processor), then you'll want to look at payment history. Payment history can (depending on the processor) give additional information, such as whether the payment was a credit card or bank account charge. Payment history also allows you to break out the data more easily; for example, the transaction number is typically part of a comment on a payment received activity, but in the payment history, the transaction number is a separate data point or query export. If using our built-in Slate Payments product, the payment history has even more detail, such as fees, net received amount, abbreviated account number, etc.

If you are primarily interested in the state of an applicant's account, or if you want to know about payment received both online through Slate and otherwise (e.g. who owes money right now, who has recently completed their deposit, how many deposits have you received including paper checks or other external payments), then you'll want to look at the payment activities on the applicant record. This may include information that was entered manually.

How can I see the payment history for a specific individual?

You can go to the Payment History page and search for an individual record. Note that you will see both person-scoped and application-scoped transactions for the selected person.

Alternatively, when viewing a payment received activity on someone's application record, you may also see a link to "View Payment History Details", assuming you have the relevant permission (see below). If you click that, there will be a link to view all payments for this individual (i.e. a pre-filtered payment history page).

Drilling down into a particular transaction will give you access to certain transaction details for a particular transaction (for most payment processors). The example below is for a Slate Payments transaction.

Clicking "Show" will display a raw data dump of whatever information we received from the payment processor with the postback notification. This can be useful in troubleshooting or (for Slate Payments in particular) obtaining additional information about why a payment was declined, for example.

How can I query on payment history for multiple individuals?

You can see the history easily by visiting the Payment History page, available from your Database page. This will show the full payment history and can be filtered in a variety of preset ways in the right-side menu (payment account, result). The Views (abacus) icon next to the search bar allows you to add custom columns to the Payment History, and to filter beyond the options in the right-side menu. This page can also be exported as an Excel spreadsheet.

More frequently, you will want to start in the query tool, which will give you more opportunity to customize the display of payment data, and, when necessary, schedule exports.

1. When creating a query, ensure the Type is set to Configurable Joins, select a Category of Related, and a base of Payment.

2. The direct filters and exports will correspond to the payment history:

There are many base joins available from the Payment base to expand the type of information you can view. To view data for the person, applicant, or dataset record associated with the payment transaction, join to Person, Application, or Dataset Row at the base of the query. This can be useful, for example, if you want to include the intended program, or application round, along with each payment transaction, or details from a donor record. If you are using Slate Payments, you can also join to Transfer/Settlement (i.e. payouts) or Recurring Payment Details when seeking additional information, and to Source Form or Source Form Response when the payment was collected on a form.

How do I view test payments?

When using our built in Slate Payments product, you'll have the ability to make payments in test mode (without using real money). A query from the payment base will include all payments, including test payments, by default. Select Test as a direct filter, and Yes to view only Test payments. When working with live payment data, select no to exclude Test payments.

How can I see payment activities for a specific individual?

Application-scoped payment activities will be under Activities on the application record. Person-scoped payment interactions will be in the interactions tab of the Timeline on the person record. Naturally, you can also view activities for an individual by querying as described below and filtering your query by person.

How can I query on payment activities for multiple individuals?

In a query using the Person or Application base, you can join to Payment Activities in a subquery filter to only include records who have a particular kind of payment activity (such as a waived payment), and use subquery exports to display any data associated with payment activities (such as the amount still owed in a given payment account). You can also join at the base of the query if you are interested in viewing details of the most recent payment activity for each of your records.

Example Use Cases

Access all data pertaining to the record’s most recent payment activity:

1. Create or navigate to a query beginning from on the person, application, or dataset record.

2. Click Join at the base of the query, select Payment Activities, and add a descending sort to isolate the most recent payment activity:

   

3. Save the subquery join. Add direct exports and filters to display or filter on details from the most recent payment activity.

Display only records with an outstanding balance in the Enrollment Deposit payment account:

1. Create or navigate to a query beginning from on the person, application, or dataset record.

2. Click the subquery icon next to Filter.

3. Join from the record base to Payment Activities.

4. Add filters to limit the types of payment activities you are looking for on the record. The net amount export for each payment activity treats the payment activity amount as positive if it is owed (i.e. due and refunded), and negative if it is waived or received. This, combined with the aggregate of Sum, tells Slate to look for records with a positive balance (an amount still owed) for the given payment account.

   

Display the total amount owed in the Enrollment Deposit payment account:

1. Create or navigate to a query beginning from on the person, application, or dataset record.

2. Click the subquery icon next to Export.

3. Join from your base to Payment Activities.

4. Add filters to limit the types of payment activities you are looking to display or aggregate. The net amount export for each payment activity treats the payment activity amount as positive if it is owed, and negative if it is waived or received. Therefore, the following export returns the balance (sum) after adding up the net amounts of all payment activities meeting the filter criteria.

   

How can I see information about the payouts/transfers I've received into my bank account?

If you are using the built-in Slate Payments product, you will receive periodic payouts or transfers (on your selected schedule) of funds into your external bank account. When we get an update that a transfer has completed, we update each of the related transactions to link them to that transfer. There are two ways to see information about the payouts and the related transactions.

First, on the Payment History page, in the upper right corner of the sidebar, there's a link to Transfers/Settlements. This will take you to a chronological list of payouts/transfers that you've received, each of which can be clicked on for additional information.

When you pop up the details for a particular transfer, you'll see a link marked View payments in this transfer. Clicking that link will open a new window showing the Payment History with a filter added to only show transactions linked to that specific transfer. See the Payouts article for more information.

Second, when working in the query tool, you will begin on the Payment base and join to Transfers/Settlements to expand your direct exports and filters to include settlement data. Each transaction will be linked to the transfer/payout that ultimately includes that transaction. Note that the ‘settlement details’ are details about the payout itself. So, if you have a row in your payment query results that shows an amount of $50 and a settlement amount of $1750, that means that this particular transaction was for $50, and it was included in a payout that totaled $1750. The payout total is included (along with the settlement date) as a way of identifying in which transfer a transaction was included.