I'm stuck with a join table problem.

Classic Servoy
1

Dear All…

I have three tables, invoices, invoice_credit_join and line_items.

I have to record via the invoice_credit_join table a history of items that were on invoices, even if those items move to other invoices (or credits). There is an invoice_join record for each time a line_item is on an invoice. This works well.

relations are

I need to be able to load all the lines for any given invoice, which i do by doing a sql join query across the join and the line_items table. This works well too and looks like this:

	var sql_query = "SELECT DISTINCT line_items.line_itemsid, invoice_credit_join.invoice_id "
            + "FROM line_items, invoice_credit_join "
            + "WHERE line_items.line_itemsid = invoice_credit_join.line_items_id AND invoice_credit_join.invoice_id = " + forms.invoice_master.invoice_number

I load this data, into my invoice print form which is based on line_items. It then prints the lines from this invoice. All good…

The trouble is that on my invoice print form, I also have data about the invoice (invoice number, totals, date, etc.). My problem is that this data is shown as related to the lines that are loaded into the form (based on line items). This means that the form loads data about the invoice that the line is CURRENTLY on (through a relation from line_items to invoices). At this point, the foundset is unaware of the join table, and has no way to find out where it came from.

I can’t use a tabpanel or portal of any sort and show my line items because it is a record view form with all sorts of leading and trailing subsummaries. I therefore HAVE to print from a form based on the line_items table. I need to be able to show data from the invoice that I’ve come from through the join table…

Any thoughts or ideas on how to do this. I had a thought to set a temp invoice field in line items via a sql update when printing, and then load my related invoice number / date etc. using that as a pk on the line items, but it seems like a workaround and clunky (and is likely to leave junk data lying around (which I suppose I could clear up after the print))

Thanks for any help.

Bevil

2

If I were to be faced with that scenario I would probably have a globals.invoice_id and set that prior to printing the report. This way the report could use a global relation to get to the proper invoice data, as opposed to relying on the join from the line.