Re: 1.3 - Inject part directly to the parts table

Subject: Re: 1.3 - Inject part directly to the parts table

From: Pongrácz István <..hidden..>

Date: Sun, 2 Feb 2014 16:01:11 +0100

Hi,

Thanks guys, working well :)

I think it is worth to put them into a transaction, including the setup of the taxes.

To setup the taxes, I should use currval to get the actual value of the product and setup partstax based on that value? I assume it will not change if I setup the taxes of the product right after I inserted into the database (the whole process is a one-by-one: one product -> its taxes -> next product -> its taxes etc.).

Cheers,

István

----------------eredeti üzenet-----------------
Feladó: "Chris Travers" <..hidden..>
Címzett: "Development discussion for LedgerSMB" <..hidden..>
Dátum: Sun, 2 Feb 2014 03:27:29 -0800
----------------------------------------------------------

On Sun, Feb 2, 2014 at 2:58 AM, Pongrácz István <..hidden..> wrote:

Hi,

I am working on a method to inject parts into the database directly.

I figured out the parts id, and partnumber auto increment, which basically was the key point from my point of view.

So, to be able to add a new record to the parts table, it needs to build a proper record.

The sequences needed to get the id and partnumber correctly, I can use the following two sql commands:

select nextval('parts_id_seq'); -> gives a new id for the parts table
select value from sequence__increment('Monitors'); -> build up the new partnumber (using sequences addon, otherwise it needs an other increment)

So, the first part is ok.

I have a sample record, which is the basis to build up the correct record, which includes all default settings, like accounts, units etc.

So, my question, how to build up the right sql syntax, preferably in one-liner?

Obviously, the following sql command does not work, check the red fields: id, partnumber.

insert into parts (id,partnumber,description,unit,listprice,sellprice,lastcost,priceupdate,
weight,onhand,notes,makemodel,assembly,alternate,rop,inventory_accno_id,income_accno_id,
expense_accno_id,bin,obsolote,bom,image,drawing,microfiche,partsgroup_id,project_id,avgcost)
values
(select nextval('parts_id_seq'),
select value sequence__increment('Monitors'),
'Test part',
'db',0,0,0,'2014-02-01',2,0,'',t,f,f,0,32,261,227,'8471',f,f,'','','',1,,);

If you want to do subqueries you need to surround them with ().

You can also just call the functions directly. So either:

((select nextval('parts_id_seq')), (select value from sequence__increment('Monitors')), ...)

Or you could:

(nextval('parts_id_seq'), (sequence__increment('Monitors')).value, ....)

In the latter example, the parentheses serve to identify a tuple from which the value attribute is extracted.

Best Wishes,

Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.

http://www.efficito.com/learn_more.shtml

------------------------------------------------------------------------------
WatchGuard Dimension instantly turns raw network data into actionable
security intelligence. It gives you real-time visual feedback on key
security issues and trends.  Skip the complicated setup - simply import
a virtual appliance and go from zero to informed in seconds.
http://pubads.g.doubleclick.net/gampad/clk?id=123612991&iu=/4140/ostg.clktrk

_______________________________________________
Ledger-smb-devel mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel