SQL or Servoy query I cannot figure out...

Classic Servoy
1

Hi all.

I have a problem with a join table which I need some help figuring out.

I have three tables:
line_items has a line_itemsid and many fields for description of the line
invoice_credit_join has a line_itemsid, and an invoice_id
and invoice has an invoice_id

I want to be able to record a history of every invoice and credit that each line item has been put on (the line items move around, they are invoiced, then credited, then invoiced again on a new invoice etc.), and be able to re-print invoices at a later date, even if the line items have been credited, and re-invoiced elsewhere.

line_items can create invoice_credit_join records, which in turn create invoices if it is an invoice (or obviously credits if they are credits). The lines are then given an invoice_id which is the CURRENT invoice or credit that the line is on.

Since invoice_credit_join has a series of records of the same line_item_id with several different invoice numbers, I want to be able to go to a form based on the invoice table, and load a (tabpanel table) form with the line items which belong to THAT invoice number. Therefore, we need to find the line items through the invoice_credit_join table where the invoice_id matches the invoice we’re on.

I cannot do this for love nor money, nor 3 days of trying. I am sure it is easy, but I can’t get it to work…

There are two approaches I think. I either do it as a sql query, and I have made this:

var sql_query = "SELECT DISTINCT line_items.line_itemsid "
				+ "FROM line_items JOIN invoice_credit_join "
				+ "ON line_items.line_itemsid = invoice_credit_join.line_items_id JOIN invoices "
				+ "ON invoice_credit_join.invoice_id = " + forms.invoice.invoice_number

	var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), sql_query , null,-1)
	forms.line_items_invoice.controller.loadRecords(dataset)

This does load different line items for each invoice, but they are scrambled up (not the correct line items that I can see for that invoice number in the invoice_credit_join table).

The other approach is to do a search through from the invoice, through the join table, to the line items table. To do this, I have made this:

forms.line_items_invoice.controller.loadRecords(invoices_to_invoice_credit_join_with_invoice_number.invoice_credit_join_to_line_items)

This works, but only loads one of the line items every time. Although there may be 3 records in the invoice_credit_join table with invoice 999, only ONE line item is loaded in my form.

I would be very grateful if someone can please let me know how and where I’m going wrong, or how I should approach this…

Sorry to ask stupid questions…

Bevil

2

Hi Bevil,

the problem is that you don’t apply a constraint to the invoice table but instead, twice on the invoice_credit_join in two different joins.
But you don’t need the invoice table for this at all because you already have the invoice_id.

You could do this:

var sql_query = "SELECT DISTINCT line_items.line_itemsid "
            + "FROM line_items "
            + "JOIN invoice_credit_join ON line_items.line_itemsid = invoice_credit_join.line_items_id and invoice_credit_join.invoice_id = " + forms.invoice.invoice_number

or

var sql_query = "SELECT DISTINCT line_items.line_itemsid "
            + "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.invoice_number
3

Hi Jeroen

Thank you very much for this, I think it has solved it (I’m still getting erroneous results though, but I think that is something to do with not having the right data loaded to my tabpanel onRecordSelection)

:)

Bevil