David, You are a genius. Thank you, that did work. As per your first mail to me, I took two backups first, though ;)
> > The remaining issue of importance is finding the extra tax fields. > > There are over 700 customers in the database, it would be preferred if > > we did not have to visit each customer on the edit page to enable the > > tax. > Bob, > > Have some info for you. > > If you run this SQL > SELECT > c.id as "Customer ID", > c.customernumber as "Customer Number", > c.name as "Customer Name", > ct.chart_id as "Tax ID", > ch.description as "Tax Name", > ch.accno as "Chart Number" > FROM customer c > LEFT JOIN customertax ct on ct.customer_id=c.id > LEFT JOIN chart ch on ch.id = ct.chart_id; > > It will return all customers and the Tax settings for them. > The key to all of this is the customertax table > it has two fields > customer_id which is = customer.id > chart_id which is = chart.id > > Hopefully this enough info for you. > > I "THINK" the following SQL will set both fields for you, but test > carefully. > DELETE FROM ONLY customertax; -- Start by emptying > customertax > > INSERT INTO customertax ( -- Add customer entries for GST > SELECT id AS customer_id, > (SELECT id FROM chart WHERE accno = '2310') AS chart_id > FROM customer > ); > > INSERT INTO customertax ( -- Add customer entries for PST > SELECT id AS customer_id, > (SELECT id FROM chart WHERE accno = '2320') AS chart_id > FROM customer > ); > > SELECT * FROM customertax; -- show you the results > > I have tested this on a demo database here and it seems to work fine. > Instead of the final "SELECT * FROM customertax;" > you could use the first SQL block, which will show you much more > detail. > > This obviously removes all tax assignments for customers and creates > the same assignments for all customers currently in the database. > Of course if any new customers are added you will either need to do > this again (loosing any changes that have been made in the meantime) > or manually set the tax for the new customer. > It should be possible to create a TRIGGER that automatically sets this > for a new customer. > > Hope this solves your problems. > > Regards > David > > > > Bob Miller 334-7117/633-3760 http://computerisms.ca [email protected] Network, Internet, Server, and Open Source Solutions ------------------------------------------------------------------------------ Throughout its 18-year history, RSA Conference consistently attracts the world's best and brightest in the field, creating opportunities for Conference attendees to learn about information security's most important issues through interactions with peers, luminaries and emerging and established companies. http://p.sf.net/sfu/rsaconf-dev2dev _______________________________________________ Ledger-smb-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
