Hi,
I need some help on setting up a MySQL report.
I have set up a view that pulls data from multiple tables so I can print an invoice.
The invoice header is linked to a customer table, management company table, detail table, payments table and credits table. (Payment and credits are 2 different things) A sample of the sql data string is shown in one of the atached images.
An invoice can have 1 or more payments applied to it and can have 1 or more credits applied to it. The invoice form is multi-use in that it is obviously used for initial billing where no payments and/or credits are attached, but also for reprints and rebilling when payments and/or credits have been applied.
The problem is how to show the payments and credits on the invoice when they apply. See the bottom part of the invoice image I have attached. When I built this invoice originally it was built against .dbf tables, so it was easy to show the payments/credits by using a subreport. I cannot figure out to do that now with a sql data string.
I can get the payments and/or credits by doing this in the calculation values part of the report by doing this:
credit = if(SQLDATASOURCE->credits<>0,inv_credit(SQLDATASOURCE->invno),"")
payments = if(SQLDATASOURCE->amountpaid<>0,inv_payment(SQLDATASOURCE->invno),"")
This meant I had to build 2 functions inv_credit() and inv_payment() to get the data, 2 more hits to the server to get the info. But that is not a good way to do it obviously.
Any ideas on how I can make this work using the sql data string instead of having to use these functions?
Thanks,
mike
Part of the data string with invoice amount, info from credits table and info from payments table.
sql_data.jpg
How part of the invoice looks using the functions listed above
invoice.jpg
I need some help on setting up a MySQL report.
I have set up a view that pulls data from multiple tables so I can print an invoice.
The invoice header is linked to a customer table, management company table, detail table, payments table and credits table. (Payment and credits are 2 different things) A sample of the sql data string is shown in one of the atached images.
An invoice can have 1 or more payments applied to it and can have 1 or more credits applied to it. The invoice form is multi-use in that it is obviously used for initial billing where no payments and/or credits are attached, but also for reprints and rebilling when payments and/or credits have been applied.
The problem is how to show the payments and credits on the invoice when they apply. See the bottom part of the invoice image I have attached. When I built this invoice originally it was built against .dbf tables, so it was easy to show the payments/credits by using a subreport. I cannot figure out to do that now with a sql data string.
I can get the payments and/or credits by doing this in the calculation values part of the report by doing this:
credit = if(SQLDATASOURCE->credits<>0,inv_credit(SQLDATASOURCE->invno),"")
payments = if(SQLDATASOURCE->amountpaid<>0,inv_payment(SQLDATASOURCE->invno),"")
This meant I had to build 2 functions inv_credit() and inv_payment() to get the data, 2 more hits to the server to get the info. But that is not a good way to do it obviously.
Any ideas on how I can make this work using the sql data string instead of having to use these functions?
Thanks,
mike
Part of the data string with invoice amount, info from credits table and info from payments table.
sql_data.jpg
How part of the invoice looks using the functions listed above
invoice.jpg
Comment