Need to identify payments / invoices related to a subscription

API Q&A
1

Hi all

I have a requirement to locate any successfully paid invoices that relate to a subscription - something I thought would be quite easy but I’m going round in circles. Getting the subscriptions themselves, Im fine with - I can pick up whether its active, when it started etc. My problem is then linking the invoices to the subscription so that I can check there is a paid invoice in the list. The customer has situations where people sign up for a subscription, so an invoice is raised but then the card payment fails so whilst there is a subscription and an invoice, its not been a subscription that’s worth counting in the figures so i need to identify if the invoice has been paid before counting the subscription in some stats.

I’m initially selecting all subscriptions for a specific contact (this will be extended later to filter for a specific product id) as below

    $subscriptions = $is->getConnection()
            ->data()
            ->findByField("RecurringOrder",1000,0,"ContactId",$user->contact_id,
                ["Id",
                    "ContactId",
                    "OriginatingOrderId",
                    "AutoCharge",
                    "BillingAmt",
                    "BillingCycle",
                    "CC1",
                    "CC2",
                    "EndDate",
                    "Frequency",
                    "NextBillDate",
                    "StartDate",
                    "Status",
                    "SubscriptionPlanId",
                    "ProductId",
                    "ReasonStopped"]);

To get any subscriptions that relate to a contact and then looking at the Job table to get any jobs relating to that subscription.

$jobs = $is->getConnection()
        ->data()->query("Job",1000,0,
            ["JobRecurringId"=>$subscription_id],
            ["Id"],"Id",false);

Once I’ve got the job id, I believe thats the invoice number and so I then look at the Invoice payments based on the invoiceId to see if there is a successful payment against it.

My problem is that for some subscriptions, I can find jobs in the Job table and for others I can’t, even though in Infusionsoft I can see an invoice having been raised and a payment having been processed

Any one got any hints or suggestions on how I can do this?

Thanks in advance

Tony

2

The xml-rpc has methods for reading from tables. You can use this to identify payments based on the order id for the payments by reading the Payments table.