Invoices -> Orders (Transactions) -> Payments -> Deposits (Settlements) API and CSV Exports are missing integral Foreign Keys to link data together

Hi there,

I am trying to pull our Square data and reconcile payments into our bank account, plus some other accounting automation.

Ultimately, this means connecting these datasets: Invoices → Orders → Payments → Deposits.

  • Invoices [/w Invoice ID]
  • Orders [/w Order ID] - a.k.a. Transactions [/w Transaction ID]
  • Payments [/w Payment ID]
  • Deposits [/w Deposit ID] - a.k.a. Settlements [\w Settlement ID]

There appears to be two major Foreign Key disconnects in both the “Export CSV” and “V1/V2 API” approaches to reading our financial data stored within Square …

  1. Using “CSV Export” to acquire data:

The problem with the “CSV Export” approach seems to be a missing Foreign Key connecting “Invoices” to “Transactions”. Either the “Export Invoices as CSV” data set needs to include the corresponding “Order ID” (aka “Transaction ID”) -OR- the “Export Transactions as CSV” data set needs to include the corresponding “Invoice ID” …

Note: The Invoices to Transactions data is correctly linked in the V2 API data calls. The Invoices V2 API results contain an “Order ID” that correctly identifies the corresponding Order/Transaction. This shows that the data is available, it is just not being included in the “Export CSV” data sets.

  1. Using “V1/V2 API” data calls to acquire data:

The problem with the “V1/V2 API” approach seems to be a missing Foreign Key connecting the “Deposits” (aka Settlements in V1 API) to either the “Orders” or “Payments” data sets. The Deposit ID (or Settlement ID) does not appear in any other related API call.

Note: Deposits are correctly linked to Orders/Transactions in the “Export Transactions as CSV” data set. Again, this shows that the data is available, it is just not being included in the “V1/V2 API” data results.

Neither the “Export CSV” approach, nor the “V1/V2 API” approach can be used to read our financial data out of Square for accounting purposes.

Each approach is missing a different required Foreign Key that is needed to actually connect corresponding records together.

At the same time, it is obvious that the underlying data is present and available within Square’s databases.

It is critical to be able to pull accurate and complete financial records out of the system of origin.

What can be done to fix these API and/or “Export CSV” missing foreign key bugs?

We can definitely help with the API approach. If you are looking to go Invoices → Orders → Payments → Deposits you will need to:

  • Get the order_id from the invoice. That order will have the payment_id.
  • You can then ListSettlements and then RetrieveSettlement with the returned settlement_ids
  • In the RetrieveSettlement response is the entries.

This array of entries has all the payment_ids that went into the settlement.

Now if you are looking to go the opposite way Deposits → Payments → Orders → Invoices you’ll need to:

Hi Bryan,

Thanks so much for your reply. I’ll check out the RetrieveSettlement-> entries data to find the Payment IDs!