I'm currently working with an add on application that monitors a Sage Mas90 providex multi keyed database. I'm monitoring 2 database tables, the accounts receivable masterfile which contains customer number, name, address etc., and the open invoice file where all open invoices, dates, balances are stored. The add on app can generate an email alert based on criteria set within the query. I'm trying to send one email to each customer listing all open invoices, invoice date and invoice balance amount. What's being generated by the add on alert is one email for each invoice. As the alert is defined the app will create it's own SQL script based on selections and criteria defined. I do have an option of creating my own SQL statement but that would then eliminate some of the design features of the add on. I would be willing to do this if I could be sure that the manually created statement would work. Here's a sample of what's currently being defined automatically. I've tried equal and left outer joins between the two tables I'm monitoring but they both come up with the same result. Any suggestions would be appreciated.
SELECT "AR1_CustomerMaster".Division, "AR1_CustomerMaster".CustomerNumber, "AR1_CustomerMaster".CustomerName, "AR1_CustomerMaster".PhoneNumber, "AR1_CustomerMaster".Extension, "AR1_CustomerMaster".FaxNumber, "AR1_CustomerMaster".LastPaymentAmount, "AR1_CustomerMaster".CreditLimit, "AR1_CustomerMaster".HighStmntBalance, "AR1_CustomerMaster".DateLastPayment, "AR1_CustomerMaster".AveDaysPaymentInvoice, "AR1_CustomerMaster".AveDaysOverDue, "AR1_CustomerMaster".Over30Days, "AR1_CustomerMaster".Over60Days, "AR1_CustomerMaster".Over90Days, "AR1_CustomerMaster".Over120Days, "AR1_CustomerMaster".EmailAddress, "AR4_OpenInvoice".InvoiceNumber, "AR4_OpenInvoice".InvoiceDate, "AR4_OpenInvoice".Balance FROM {oj "AR4_OpenInvoice" LEFT OUTER JOIN "AR1_CustomerMaster" ON "AR1_CustomerMaster".Division = "AR4_OpenInvoice".Division} WHERE ("AR1_CustomerMaster".CustomerNumber = "AR4_OpenInvoice".CustomerNumber) AND (("AR4_OpenInvoice".Balance > 0.00))
SELECT "AR1_CustomerMaster".Division, "AR1_CustomerMaster".CustomerNumber, "AR1_CustomerMaster".CustomerName, "AR1_CustomerMaster".PhoneNumber, "AR1_CustomerMaster".Extension, "AR1_CustomerMaster".FaxNumber, "AR1_CustomerMaster".LastPaymentAmount, "AR1_CustomerMaster".CreditLimit, "AR1_CustomerMaster".HighStmntBalance, "AR1_CustomerMaster".DateLastPayment, "AR1_CustomerMaster".AveDaysPaymentInvoice, "AR1_CustomerMaster".AveDaysOverDue, "AR1_CustomerMaster".Over30Days, "AR1_CustomerMaster".Over60Days, "AR1_CustomerMaster".Over90Days, "AR1_CustomerMaster".Over120Days, "AR1_CustomerMaster".EmailAddress, "AR4_OpenInvoice".InvoiceNumber, "AR4_OpenInvoice".InvoiceDate, "AR4_OpenInvoice".Balance FROM {oj "AR4_OpenInvoice" LEFT OUTER JOIN "AR1_CustomerMaster" ON "AR1_CustomerMaster".Division = "AR4_OpenInvoice".Division} WHERE ("AR1_CustomerMaster".CustomerNumber = "AR4_OpenInvoice".CustomerNumber) AND (("AR4_OpenInvoice".Balance > 0.00))
