Unpaid Orders in Analytics and the Invoice Payment Method

Hi Dara,

Applying an Invoice payment to pay for an order and create a receivable does fully pay the order from a ticketing perspective. The best place to review invoice statuses in an Analytics context is the Finance cube, filtered to that payment method type to look at amounts invoiced, paid, and outstanding.

Invoices.dash

Thanks Chris. I'll try to use the Finance cube to get the info. It's all a bit disjointed as my dashboard is mostly looking at the performance info.

Is there a plan to make the Invoices, Gift Certificates more intuitive and get rid of the 'Zero Adjustment' in the Consolidated Orders Roadmap item?

It would be great to be able to simply pull an invoice or receipt for an order. I know it's a bit complicated as reserved orders would need to have a GL entry which they currently don't have.

Many thanks,

Dara

Hi Dara,

There are roadmap items to explore what it might look like to simplify the transactional data store, and this falls squarely into that.

Best,
Chris

Thanks Chris. That sounds good.

I think I've got it working with the finance cube, to show a paid order as unpaid if it's been paid by 'Invoice'.

The following is my formula for  Amount Paid:

  IF(    MAX([Payment Method ID])  = 92  ,   0,    sum([Transaction  Amount])  ) 

I'm not sure where the 92 is coming from as the ID in my database is actually 101, but when i listed the field in Analytics it shows 92 so that's what I used. It seems to work.

thanks,

Dara

Hi Chris,

I'm back looking at this again as my formula that I thought was working does not take into account when the invoice has been paid.

It DOES work to show the order as 'paid' if paid by a method other than invoice.

It DOES work to show the order as 'unpaid' if the order is paid by Invoice.

HOWEVER, it does not work when the Invoice has been later paid off. It's still showing the order as 'Unpaid'.

I think this is because the rows are grouped by performance, and the payment to pay off the invoice has a blank in this column.

I also tried to group by order_id, but that is -99999 for the transaction that pays off the Invoice.

Is there a way to group it so that I can see the order transactions and the paid off invoice transactions?, and hence know if the order is settled or not.

Many thanks,

Dara

Hi Dara,

It's true that invoices are not paid off by performance because there's no clear way to decide which fees and performances should be paid off first. Instead the invoice pays for everything, and the order then has a balance due and that balance is tracked at the order level. Therefore, grouping the invoice payment methods by performance will not show the payments toward the invoice. 

Still we can absolutely identify invoiced order paid statuses by their non-zero Total Transaction Amount under Payment Method Type = Invoice.

This one is paid:

The detail of that order's invoice transactions looks like this:

The initial order transaction limited to and showing the debits to the invoice payment method from the associated Performances and Fees. Then the subsequent, monthly transactions to credited to that invoice until the remaining Total Transaction Amount = 0. Were the invoice not fully paid, for example, if the last payment weren't on that output above, the Total Transaction Amount would instead be 103.46.

Here's an not yet fully paid example showing an initial amount debited to invoice, and 2 payments, showing a balance as the Total Transaction Amount.

So your Amount Paid formula could be [Total Credit Amount] if your widget or dashboard is filtered to Payment Method Type = Invoice, or if you need to do a filtered value formula, then ...

( [Total Credit Amount] , [Payment Method Type=Invoice] )

And for reference then, the Total Invoice Amount is [Total Debit Amount], and the Balance is [Total Transaction Amount].

Extending that into a widget filtered only to a few Order ID and not filtered on Payment Method Type:

• Ticket+Fee Paid Amount is a formula of [Total Ticket Paid Amount] + [Total Fee Paid Amount]
• Paid Upon Order is formula of ( [Total Ticket Paid Amount] , [Payment Method Type excluding (none) and Invoice] ) + ( [Total Fee Paid Amount] , [Payment Method Type excluding (none) and Invoice] )
• Invoiced Amount as ([Total Debit Amount] , [Payment Method Type = Invoice])
• Invoice Paid Amount as ([Total Credit Amount] , [Payment Method Type = Invoice])
• Invoice Due Amount as ( [Total Transaction Amount] , [Payment Method Type = Invoice] )

If you break that by performance and fee, then you'll find the Invoice Paid Amount alone, on a row by itself (at the top in this case), with performance -999999 and fee -999999.

What I would not expect is an Order ID of -999999 on any of the related transactions and if you're seeing that, it may be worth a support ticket for us to dig into that.

Best,
Chris