Hi folks, I’m trying to get transactions with amortization schedules and their amounts via the suiteql restapi I can get everything else apart from the TotalAmortized amount. Can’t figure out how I can get this

SELECT
            AmortizationSchedule.ID AS ScheduleID,
            BUILTIN.DF(AmortizationSchedule.Name) AS ScheduleName,
            AmortizationSchedule.Status AS ScheduleStatus,
            AmortizationSchedule.StartDate,
            AmortizationSchedule.TotalAmount AS ScheduleTotalAmount,
            AmortizationSchedule.TotalAmortized AS ScheduleTotalAmortized,
            TransactionLine.LineSequenceNumber,
            TransactionLine.Memo,
            TransactionLine.Amount,
            Transaction.TranID,
            Transaction.Type AS TransactionType,
            BUILTIN.DF(TransactionAccountingLine.Account) AS GLAccount,
            TransactionAccountingLine.Debit,
            TransactionAccountingLine.Credit
        FROM AmortizationSchedule
        INNER JOIN TransactionLine ON 
            (TransactionLine.AmortizationSched = AmortizationSchedule.ID)
        INNER JOIN Transaction ON 
            (Transaction.ID = TransactionLine.Transaction)
        INNER JOIN TransactionAccountingLine ON 
            (TransactionAccountingLine.Transaction = Transaction.ID)
        WHERE Transaction.Trandate >= ADD_MONTHS(CURRENT_DATE, -1)
        ORDER BY Transaction.Trandate DESC, AmortizationSchedule.ID, TransactionLine.LineSequenceNumber

Field 'TotalAmortized' for record 'AmortizationSchedule' was not found. Reason: NOT_EXPOSED - Not available for channel SEARCH

Hi Rahul! You need to calculate it from journals like that: SELECT AmortizationSchedule.ID AS ScheduleID, BUILTIN.DF(AmortizationSchedule.Name) AS ScheduleName, AmortizationSchedule.Status AS ScheduleStatus, AmortizationSchedule.StartDate, AmortizationSchedule.TotalAmount AS ScheduleTotalAmount, ( SELECT SUM(COALESCE(TAL.Debit, 0) - COALESCE(TAL.Credit, 0)) FROM TransactionLine TL INNER JOIN Transaction T ON T.ID = TL.Transaction INNER JOIN TransactionAccountingLine TAL ON TAL.TransactionLine = TL.ID WHERE TL.AmortizationSched = AmortizationSchedule.ID AND T.Type = 'Journal' AND T.Posting = 'T' ) AS CalculatedTotalAmortized, TransactionLine.LineSequenceNumber, TransactionLine.Memo, Transaction.TranID, Transaction.Type AS TransactionType, BUILTIN.DF(TransactionAccountingLine.Account) AS GLAccount, TransactionAccountingLine.Debit, TransactionAccountingLine.Credit, (COALESCE(TransactionAccountingLine.Debit, 0) - COALESCE(TransactionAccountingLine.Credit, 0)) AS LineAmount FROM AmortizationSchedule INNER JOIN TransactionLine ON (TransactionLine.AmortizationSched = AmortizationSchedule.ID) INNER JOIN Transaction ON (Transaction.ID = TransactionLine.Transaction) INNER JOIN TransactionAccountingLine ON (TransactionAccountingLine.TransactionLine = TransactionLine.ID) WHERE Transaction.Trandate >= ADD_MONTHS(CURRENT_DATE, -1) ORDER BY Transaction.Trandate DESC, AmortizationSchedule.ID, TransactionLine.LineSequenceNumber

Hi @Dmitry Masanov, Thanks for your response! I work with @Rahul Bhatnagar and just tried your suggested query. however I am getting a timeout even with a limit of 10. Do you have any suggestions here?

Looks like there are too many JEs in your account (I don't have any in mine so I only tested that syntax was correct). Don't know what will be the solution here. Did you try a saved search instead?