I've got the following SuiteAnalytics Connect query that returns the billing schedule for a sales order without tax (the UI shows the amounts WITH tax). I can't seem to replicate this query with SuiteQL as the

billingschedule

table doesn't have the same amount fields. Can this be done with SuiteQL or a workbook?

SELECT SUM(BS.BILL_AMOUNT_FOREIGN),
       BS.BILL_DATE
FROM BILLING_SCHEDULE BS
         INNER JOIN TRANSACTION_LINES TL
                    ON BS.TRANSACTION_ID = TL.TRANSACTION_ID
                        AND BS.TRANSACTION_LINE_ID = TL.TRANSACTION_LINE_ID
         INNER JOIN ITEMS I
                    ON TL.ITEM_ID = I.ITEM_ID
WHERE BS.TRANSACTION_ID = 495276
GROUP BY BS.BILL_DATE
ORDER BY BS.BILL_DATE

For what it's worth, if you sum the AMOUNT_TAXED in TRANSACTION_LINES for that TRANSACTION_ID do you get your expected result?

Ultimately what we ended up doing was using this SuiteQL query, which only works for billing schedules with a recurrence type of 'CUSTOM', and combining it with our own calculations for other billing schedules using a combination of

frequency

,

recurrencecount

,

repeatevery

, and the line

amount

.