Trying to total quotation part weights for shipping costs.

[Terry Porter <..hidden..>]

Hi,
Thanks for the excellent resource of Ledger-Smb, I hope to contribute is some small way as the opportunity arises. I have minimal skills in Perl, Sql and documentation.

This post is somewhat lengthy, but I think it's interesting, my apologies in advance to anyone who reads on.

I'm still using Sql-Ledger-2.6.19 as I haven't had the time to move over to Ledger-Smb, but will have a go soon. I think the Database and table layouts are similar enough, at least for what I want to do, after examining the tables of both smb and sql-ledger.

I've searched the (smb user) mailing list without success regarding a problem I'm having, namely I'm attempting to obtain a total of item weights after creation of a quotation, in order to automatically calculate shipping costs to add back into the quotation before emailing to the customer.

I'm running the query via a separate application, and will make a small Gtk2-Perl Gui to run the query based on entering a quote number *when* I have a query designed that actually does what I want. For me this is easier than attempting to modify the Perl code for the Quotation form, and it's a separate app that won't need updating with each new version of Smb-Ledger.
The application (when complete) will automatically total the quotation weights and query the shippers on-line freight costings in conjunction with the customers postcode. This will provide a fast and hopefully error free (at least free of *my* errors) amount for insertion into the final quote.

The ability to query the database separately is one thing I love about Ledger.

I have produced a working query, which *almost*does what I want but only works after the quote becomes an invoice, due to the fact I haven't been able to link the parts table to the invoice table prior to ar.quonumber being allocated a value, and the trigger for this doesn't seem 
to occur until the quote *becomes* an invoice.

Here is the query I'm using, it works fine *after* the query becomes a quote :-

select ar.quonumber AS QUOTE, customer.name AS CUST, parts.partnumber AS PART, parts.weight AS WEIGHT, invoice.qty AS QTY, (parts.weight * invoice.qty) AS TOT
	from acc_trans,ar,invoice,customer,parts
		where acc_trans.trans_id = ar.id and acc_trans.invoice_id = invoice.id and ar.customer_id = customer.id and invoice.parts_id = parts.id and (parts.weight * invoice.qty > 0) and ar.quonumber ='q895'

Anyone know if I am totally off the track (it wouldn't be the first time it's happened), or just confused ?

I admit I'm a bit lost in the database schema, but it is fantastic to have the smb-ledger documentation to refer to, my thanks again.

Any words of wisdom, criticism, or pointers to URL's etc gratefully received, and of course my code will be available (GPLd) to anyone that wants it when it's working (Linux only).

Thanks for your time reading this far.



-- 
Best Regards
Terry