Updating data fields across the database
I have been involved with setting up a small system from scratch and as often happens now discovered that some of my initial settings could have been done in a much better way.
Specifically my challenges are :
o I did not use templates to setup common costs like injection fees but rather assigned a fixed cost to a number of products and as a consequence now have to manually update any generic charge increases.
o I did not establish ACCOUNT TYPES which could be tied to customer accounts and as a consequence can not now filter account details. This environment will only have one account type so updating this would be a generic change across all customers.
My question is - is there a way of updating these sorts of fields across the database using SQL scripts or is the only way to do it by doing the data entry? (I appreciate that manipulating the database from outside is dangerous but thought that given this looks like a generic across the board change it might be able to be done?)
Thanks for any help or advice
Des
Updating data fields across
Hi Des,
As a start here is some advice regarding establishing links between customers an a specific account type for those customer that do not currently have one.
Firstly add the account Type you want as default in Administration -> Lookups i.e New then select Customer Account Type. Set the default flag on this Account Type so all subsequent new customers will be linked to it.
Secondly run this query to get the lookup_id's of your current account types
Note the lookup_id of the account type you wish to link to customers not currently linked.
Next run this script replacing <lookup_id> with the id you noted above.
drop table if exists no_account_types; create table no_account_types (entity_id bigint(20) not null); insert into no_account_types (entity_id) select entity_id from entities where arch_short_name = "party.customerperson" and entity_id not in (select e.entity_id from entities e, entity_classifications ec, lookups l where e.arch_short_name = "party.customerperson" and e.entity_id = ec.entity_id and ec.lookup_id = l.lookup_id and l.arch_short_name = "lookup.customerAccountType"); insert into entity_classifications (entity_id, lookup_id) select entity_id, <lookup_id> from no_account_types; drop table no_account_types;It goes without saying please test this on a test database/system first and always do a full production database backup before running the script. :-)
I will have a think about dispensing fee changes from fixed to linked fees. Definitely possible but will have to have some clear way to identify those fixed fees that need to be changed either by name or by value or combination of both. As a start you may want to analyse the output of the following query to see the range of names and values currently used:
Hope this helps.
Cheers
Tony
Re: [OpenVPMS Developers] Updating data fields across
I will do some experimenting and see how it pans out .... only on the demo version.
Best regards Des
-----Original Message----- From: developers-bounces@lists.openvpms.org [mailto:developers-bounces@lists.openvpms.org] On Behalf Of tony@openvpms.org Sent: Thursday, 1 July 2010 7:49 AM To: developers@lists.openvpms.org Subject: Re: [OpenVPMS Developers] Updating data fields across
Hi Des, As a start here is some advice regarding establishing links between customers an a specific account type for those customer that do not currently have one. Firstly add the account Type you want as default in Administration -> Lookups i.e New then select Customer Account Type. Set the default flag on this Account Type so all subsequent new customers will be linked to it. Secondly run this query to get the lookup_id's of your current account types
select loookup_id, name from lookups where arch_short_name = "lookup.customerAccountType"
Note the lookup_id of the account type you wish to link to customers not currently linked. Next run this script replacing with the id you noted above.
drop table if exists no_account_types; create table no_account_types (entity_id bigint(20) not null);
insert into no_account_types (entity_id) select entity_id from entities where arch_short_name = "party.customerperson" and entity_id not in (select e.entity_id from entities e, entity_classifications ec, lookups l where e.arch_short_name = "party.customerperson" and e.entity_id = ec.entity_id and ec.lookup_id = l.lookup_id and l.arch_short_name = "lookup.customerAccountType");
insert into entity_classifications (entity_id, lookup_id) select entity_id, from no_account_types; drop table no_account_types;
It goes without saying please test this on a test database/system first and always do a full production database backup before running the script. :-) I will have a think about dispensing fee changes from fixed to linked fees. Definitely possible but will have to have some clear way to identify those fixed fees that need to be changed either by name or by value or combination of both. As a start you may want to analyse the output of the following query to see the range of names and values currently used:
select name, price, count(*) from product_prices where arch_short_name = "productPrice.fixedPrice" group by name, price;
Hope this helps. Cheers Tony _______________________________________________ OpenVPMS Developers Mailing List developers@lists.openvpms.org To unsubscribe or change your subscription visit: http://lists.openvpms.org/listinfo/developers Posts from this mailing list can be viewed online and replied to in the OpenVPMS Developer's forum- http://tinyurl.com/openvdf
_______________________________________________ OpenVPMS Developers Mailing List developers@lists.openvpms.org To unsubscribe or change your subscription visit: http://lists.openvpms.org/listinfo/developers Posts from this mailing list can be viewed online and replied to in the OpenVPMS Developer's forum- http://tinyurl.com/openvdf