What is the logic used by Netsuite to properly show the payment date of an installment, since there isn't a Payment Date field on the table InvoiceInstallment? How can I use it to write a query that returns me an invoice, its installments and the payment date of each installment?

AI generated answer (using internal Slack threads):

NetSuite does not directly provide a Payment Date field on the InvoiceInstallment table. However, you can infer the payment date of an installment by looking at the related Payment record. When a payment is applied to an invoice, it is recorded in the Applied To sublist of the Payment record. The installment reference number is displayed in this sublist. To write a query that returns an invoice, its installments, and the payment date of each installment, you would need to start from the Invoice record, join to the Installment record, and then join to the Payment record. Unfortunately, this cannot be done directly using a Saved Search due to the limitations of the Saved Search join functionality. However, you can achieve this using SuiteQL or SuiteScript. Here is a conceptual example of how you might structure your SuiteQL query:

sql
SELECT 
    Invoice.internalid AS InvoiceID, 
    Installment.internalid AS InstallmentID, 
    Payment.trandate AS PaymentDate
FROM 
    Transaction AS Invoice
JOIN 
    Installment ON Invoice.internalid = Installment.invoice
JOIN 
    Transaction AS Payment ON Installment.internalid = Payment.installment
WHERE 
    Invoice.type = 'Invoice'

Please note that this is a conceptual example and the actual field names and join conditions may vary based on your specific NetSuite setup. You would need to adjust this query to match your actual field names and relationships. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******

There is no Installment field on the Transaction table, how can I adjust that query?

Thank you for your feedback