Transaction Total Price changes after saving

Version 4 V4 Question
21

I think that will make sql reporting a real headache… Maybe we should store only total price instead of price. Calculate unit price and display rounded value.

However that shouldn’t cause an issue for cost calculation because while calculating cost we should use non-rounded unit prices. It will be more accurate when we base total prices but if someone take a calculator and make some calculations with unit price he’ll probably find different values. Does not sounds like a big issue…

@QMcKay what do you think about that change? How it changes your current implementation? I’m planing to delete Price column and create TotalPrice column instead. Of course I’ll update old TotalPrice values before deleting Price column.

2 Likes
22

@emre, I’m not sure how it affects my implementation, if at all, if you’re simply deleting the display column, and not the DB column. Deletion of the DB column would almost certainly mess things up in my implementation. Though I wouldn’t want the display column deleted either because…

I use the Inventory Screens to confirm my PHP transactions and calculations are correct, but I don’t use them for entering actual Inventory Purchase Transactions.

Certainly, we should not store rounded values in the DB … the precision there is a good thing. If you want to display them rounded, so be it.

Maybe I don’t understand the issue completely, but I’d rather not see anything deleted or rounded. I need to read the thread again.

23

That actually sounds better!

24 
Item    Quantity    Price    Total Price
xxx            5     2.00          10.00
yyy            3     3.33          10.00

YYY item generates an issue here. Normally if price is 3.33 total price should be 9.99 but user bought 3 items for $10.

As we store 3.33 in database and calculate total price on the fly total price changes from 10.00 to 9.99.

So I’ll store total price in database and calculate unit price on the fly. So instead of 3.33 you’ll read 10.00 from database and you’ll divide it with quantity to find item price.

25

Ok, I think I understand. You currently store Price and calculate Total on-the-fly for display purposes. Now instead you want to store Total so it doesn’t look like it is “out”, and calculate Price on-the-fly (rounded).

In the DB, you are talking about replacing the [Price] column with a [Total] column instead, true? If so then…

Yes, this will break my implementation with PHP… how much I can’t say right now, but I’m sure it is manageable… it’s all for the better good :wink:

EDIT: it doesn’t look too bad, certainly manageable (9 changes required)

295f5409298cdb66.png709×251 33.2 KB

Maybe if you give me at least 1 more Unit and a Quantity Field for all 3 Units, I should be able to forgive you… in time. :stuck_out_tongue_winking_eye:

EDIT: continue reading…

How about this (nothing will break):

  • keep [Price] in the DB - it has enough precision (16,2) or change it if you wish to (16,3)
  • add [Total] to the DB - give it the same precision as [Price]
  • change code to calculate [Price] on-the-fly from [Total]
  • store both values in the DB
  • round both values in the display as you currently do
1 Like
26

I think you should also change your implementation to align your solution with future SambaPOS features. I’ll always reference total price and there will be small differences in calculations if you reference price. I’m replacing it…

27

Certainly, I will do so - I just wanted to present an option that doesn’t break anything. For example, how many Reports will this break (built-in or Custom) if you replace the field? A few for sure.

28

I see… Hmm. should we break them so we’ll understand we need to fix it or should we keep them running… Hard decision.

29

I don’t think I mind one way or the other, personally.

That said, it’s easier to add something than replace it - whether or not that is good practice is debatable. I’m not sure how many code changes v.s. code additions you will encounter yourself.

I’m not sure it will have a huge impact on the user base, but I don’t have the visibility on the matter like you may have.

I can understand why you want to replace rather than add: it’s the correct thing to do. But is it worth it? Hard to say…

It is a hard decision. I know you will make the correct one, and we will all adapt, one way or the other.

30

I’ll break it. It will cause some issues but that won’t be bigger than possible future issues.

Item    Quantity    Price    Total Price
yyy            3     3.33          10.00

We shouldn’t have something like that in database. We can slowly change custom reports … @gerlandog? :stuck_out_tongue:

@QMcKay thank you very much for your help.

2 Likes
31

How will it work? I see your example but do not know how it works… if both fields will remember user input the I think that this is the best solution (more complicated tho)…

My initial issue was that the value for total that I had entered changed when I reopen the transaction, and that was something strange to me… something that did not happen before…

G.

32

@gerlandog, it sounds like @emre is going to make a change where the Price column is calculated on-the-fly, and not remembered (not stored).

Currently, the database table [InventoryTransactions] has fields for [Quantity] and [Price], but not [Total].

The Total is being calculated on-the-fly and is being displayed, but it is not being stored in the DB. This results in a display of Total being 9.99 when you enter 3 items with a Price of 3.33.

The same thing happens when you enter 3 items with a Total of 10.00… when you leave and come back, the Total changes to 9.99 (because it is not stored). Presumably, this is not desirable, thus the change @emre is making.

@emre is going to reverse the process, and therefore he is changing what is stored.

He is making a change to the DB to replace the [Price] field with a [Total] field. So when the next version comes out, the Total will be stored, and not the Price.

In practice, this means that when you make an Inventory Purchase, you should enter the Total rather than the Price. I assume results will still be calculated on-the-fly for either field during entry, but only the Total will be stored.

So if you enter 10.00 for the Total, it will be stored that way and it won’t change when you exit and re-enter. The Price will be calculated and displayed as 3.33.

If you enter 3.33 for the Price, the Total should be calculated for you, but it will be stored as 9.99, so when you exit and re-enter, it will still be 9.99. This is probably not desirable, so you should enter the Total instead of the Price so that it stores the correct amount in the DB under [Total]

2 Likes
33

THAT IS GREAT FOR ME!!!

Hope this works for everybody else!!!

Thanks!!!

G.

34

I’ve uploaded 4.1.70 for your review. This is not a public download.

You can download it from here and let me know if it works fine for your setup.
http://sambapos.com/?wpfb_dl=79

35

Tested and seems to be ok. In practice, the behavior hasn’t changed (it still calculates automatically in all directions), though it now saves the Total.

Made a few edits to my PHP to accommodate the change from [Price] to [TotalPrice] … nothing major, very simple.

I want to suggest though, regarding the behavior of the Unit Price column, when it comes to having it populate with Default Cost. If I change the Item, the Unit Price does not change - it keeps the previously selected Item Price in place. This does not work well when you start typing the Item in the selector, because as soon as you hit a single character, it pulls the default cost from the first Item it finds, and doesn’t update after that…