• Registered: 2020-05-17
  • Posts: 38

Topic: How to handle prices and taxes changes?

Hi all,
I,m wondering what's best way to handle situations when db contains prices and taxes which will occasionally change. When price/taxes change, results of all calculated field will change but "old" records should somehow keep old prices.
Vladimir

borisavljevic's Website

  • From: Gotham City
  • Registered: 2017-12-31
  • Posts: 406

Re: How to handle prices and taxes changes?

You have to use prices in each transaction... for example you are selling some items then you have to add price column and taxes column in sale table.

  • Registered: 2014-11-13
  • Posts: 371

Re: How to handle prices and taxes changes?

duplicate the item and change the price to the new item

Domebil

domebil's Website

4 Reply by borisavljevic (edited by borisavljevic 2021-08-31 15:46:38)

  • Registered: 2020-05-17
  • Posts: 38

Re: How to handle prices and taxes changes?

blackpearl8534 wrote:

You have to use prices in each transaction... for example you are selling some items then you have to add price column and taxes column in sale table.

Thanks for answer, one more question. I have problem with part of SQLExecute statment. What I don't know is how to add condition after WHERE? Condition should be: sales.date = DateTimePicker1 date. Part of code as example: 

(SELECT id from orders WHERE orders.date = DateTimePickerTest)

Basically what I dont't know is syntax to compare if date from database DATE field is same as date in datetimepicker commponent.

Thanks for help.

borisavljevic's Website

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,682

Re: How to handle prices and taxes changes?

Hi,
If your prices or tax rates are changeable but you don't want those changes to affect old transactions, then you could try it like this (using the attachment as an example).
1.  if the transaction is a 'New Record' then
1.1  retrieve the price from the 'product' table into an edit field on the form. 
1.2  use a calculated field to calculate the value (price * quantity). 
1.3  when you save the record, write the price to the invoice table.
2.  if the transaction is a 'Show Record' then
2.1  the price is now retrieved from the 'invoice' table (not the 'product' table) and therefore not affected by any subsequent change to the price held on the 'product' table.
This way, you also have the option to over-ride the price manually (for whatever reason), so it's quite flexible.
Hope this helps,

Post's attachments

Attachment icon price updates.zip 766.07 kb, 224 downloads since 2021-08-31 

  • Registered: 2020-05-17
  • Posts: 38

Re: How to handle prices and taxes changes?

derek wrote:

Hi,
If your prices or tax rates are changeable but you don't want those changes to affect old transactions, then you could try it like this (using the attachment as an example).
1.  if the transaction is a 'New Record' then
1.1  retrieve the price from the 'product' table into an edit field on the form. 
1.2  use a calculated field to calculate the value (price * quantity). 
1.3  when you save the record, write the price to the invoice table.
2.  if the transaction is a 'Show Record' then
2.1  the price is now retrieved from the 'invoice' table (not the 'product' table) and therefore not affected by any subsequent change to the price held on the 'product' table.
This way, you also have the option to over-ride the price manually (for whatever reason), so it's quite flexible.
Hope this helps,

Man, this is so simple and elegeant solution....
Tnak you very much.
Cheers,
Vladimir

borisavljevic's Website

  • From: Gotham City
  • Registered: 2017-12-31
  • Posts: 406

Re: How to handle prices and taxes changes?

I have some suggestions for you....
If you are developing like pos software or invoicing software then you have to add 3 different tables
1 itemes 2 sale and 3 invoices