Rounding and issuing change in multiple currencies

[QMcKay]

I give up. I cannot make this work the way I want.

It also occurred to me that I don’t really want to alter the Ticket Total anyway, I would rather it stay as-is and just have rounding calculations placed into a separate Account without altering the Ticket Total with Calculation Types.

Here are a some examples of what I am looking to achieve. I built this with a cool little function that allows me to send in a number, the Rounding factor, and the Exchange Rate if required:

function nearest(num,rnd,xr) {
  // num : the number to round
  // rnd : a value to round to
  // xr  : exchange rate
  num = num * xr;
  rnd = 1 / (rnd);
  return Math.round(num * rnd) / rnd;
}

These shots show the rnd rounding factor set to 10, so the function was called like this:

{CALL:round.nearest('{TICKET TOTAL}','{:CALC RND}','{:CALC XR}')}

---

Examples of what it produces:

---

I have a Payment Processor for Cash HNL set to Execute Automation Command that produces those dialogs. Right now, it just shows information without doing anything else, but my intent would be to create an Account Transaction on-the-fly and apply the DIFF value to that Account.

I might want to build my own Custom Payment Screen LOL

[emre]

@QMcKay sorry I’m in a business trip to Istanbul so couldn’t answer that quickly. Basically you need to setup an auto round calculation as explained here.

SambaPOS Rounding Features

Hello. In this tutorial I’ll show you some rounding features of SambaPOS. I’ll demonstrate different rounding methods here so you can choose that fits fine to your business needs. ##Method 1: Automatic Rounding to Nearest Value Automatic rounding to nearest value means it can both increase or decrease ticket amount. So we’ll need two account transactions and ticket calculations here. By default SambaPOS has Discount and Rounding accounts. We’ll create two additional account transactions to dif…

You need to have 2 calculations for rounding up and down as both they reflect to accounting differently. Instead of applying automatically it should apply via payment processor so you need to assign both templates (up / down) to payment type.

I fixed a small related issue and uploaded a new update so it will be better to redownload latest .61 setup.

[QMcKay]

@emre

Basically you need to setup an auto round calculation as explained here. You need to have 2 calculations for rounding up and down as both they reflect to accounting differently. Instead of applying automatically it should apply via payment processor so you need to assign both templates (up / down) to payment type.

I tried that too. But as seen in my last screenshot, while it shows Tendered Amount properly Rounded, the Change Amount is not coming out how I want it. It may well be technically mathematically correct (though I don’t think it is), but it is still not how I want this to work.

This item is 276.00, rounded to nearest 5 which becomes 275.00, with 500.00 tendered, the Change Amount should be 225.00, not 225.02. It is the ‘.02’ part that is undesirable. It’s almost as if we need a Rounding parameter for Change Payment Types? Green parts are good, Red parts are bad

[emre]

We can’t vanish amount because of rounding so we should convert all cents to base currency and decrease them from ticket.

I tested some similar configuration and it appears like that for me. (PS. I don’t have execute script processor).

I switched to Foreign Currency and about to settle 500.

Gives no decimal

Rounds 0.04 USD cents to round ticket amount by 5.

Did you downloaded recent upload? Maybe I couldn’t upload it because my connection was really bad.

PS: I downloaded it and it seems like uploaded fine. I’m behind 3G now. I’ll test better tomorrow.

[QMcKay]

@emre

I tested some similar configuration and it appears like that for me. (PS. I don’t have execute script processor).

How would the the Script have any effect on this at all? All it really does is Tag Ticket - it does not alter values. P.S. I removed the ExecScript and the result is the same. There is 2 Calculations for Round+ and Round-

@emre

Rounds 0.04 USD cents to round ticket amount by 5.

Same here. Good.

@emre

Gives no decimal

Your screenshots look exactly the same as mine, except for the Change Amount, which is exactly the thing that has me perplexed. Mine shows 225.02 for some reason.

@emre

Did you downloaded recent upload?

Yes …

[QMcKay]

@emre, I just had a thought. How is your Foreign Currency set? Here is mine … maybe I don’t have the correct syntax?

---

Hmm … I just changed mine from above to below, and now the .02 decimals are GONE!

This is GOOD!

[mjb2000]

Hi guys

I am getting really close to finishing the last few things for my Dive Center POS system, but I am having a couple of small options related to ticket printing…

##Inverse exchange rate not available
My customers pay in MXN using a card, but using the standard printer tags it seems I only have access to {PAYMENT AMOUNT} and {EXCHANGE RATE:MXN}

I have my default currency as USD with MXN a foreign currency stored with an inverse rate of 21

The problem seems to be that {EXCHANGE RATE:MXN} returns a value of 0.05 (a very rounded, non-inverse version of my exchange rate).

– So - How can I get direct access the echange rate as I store it (21) not the value 0.05

##Calculating foreign currency paid historically
Assuming we can find a solution to the above, because my customers often pay their debts over several days it would be realy important to me to track exactly what a customer paid over several days, but the tag discussed above only seems to return the current exchange rate.

Is there any way we can store and return the exact amount of foreign currency that was paid or issued as change, rather than a value calculated by the current rate. (Although I guess it would also work it we tracked changes to the exchange rate so we knew which rate was active at the time of the transaction).

This would also be very important for tracking credit card payment slips and the money going in to my bank account, without knowing the exact amount that was processed it would be difficult track back from a particular transaction in our bank statement.

Any thoughts on this being possible?

[mjb2000]

I think I may have missed something trying to get this great work…

I have set-up USD and MXN rounding transactions (both up and down). I am trying to round USD to the nearest 1 USD and MXN to the nearest 10 MXN.

Imagine the situation illustrated in the GIF below
Add a 70.00 product and apply a 9% discount
This is now 63.70 - so it makes sense that IF this ticket was going to be settled in USD then we should add 0.30 and settle at 64 USD cash.
BUT the customer only pays 60 USD cash. I still need to press the cash button, but doing so adds the 0.30. This means that instead of owing 3.70 to finalize their bill (perhaps on card or account), they now owe 4.00, which is incorrect.

Later, when issueing change everything doesn’t work too smoothly, and there is a balance left over - I’m not sure if this leftover balance is related?

EDIT: Would one possible solution be to ignore the rounding if the amount entered is less than the amount that would be due?

[QMcKay]

I see what you’re saying and what you are trying to achieve.

However, at least to me, the behavior is as it should be (as designed), considering you are using a Payment Processor to do the rounding… which means it Rounds as per the Currency when you click the Cash button.

Maybe you need to look at possibly rounding the Discount? I don’t know.

It is a bit difficult to judge what to do in such a scenario. Because theoretically, without the Rounding done by the Payment Processor, the amount owing would be 3.70 … which I suppose could then be switched back to MXN and paid that way (or by account or CC or whatever).

I agree the balance seems to be a strange thing, but when you think about it, just because of the sequence of operations, you are performing rounding of different currencies every time you switch back and forth from one payment method to another. So first you Round the USD, then you switch to MXN and it rounds again.

Look at the table on the left as you go through the sequence.

First there is a USD+0.30
Then you switch to MXN and pay…
Now there is a MXN+0.11 and the USD+ has been changed from +0.30 to +0.19

That last part (the change) I don’t quite understand. But there is your Balance. Weird.

Need more help from @emre, or it might be worth considering execution of a custom JScript to figure this stuff out and skip the Calculations altogether? I just don’t know if we have access to enough data via the API to be able to process it all, considering the way people pay for things in 2 currencies at once.

[mjb2000]

@QMcKay

That last part (the change) I don’t quite understand. But there is your Balance. Weird.

Yes! - That is something I definitely thought was weird - offering change that would lead to a balance still due?!

[mjb2000]

OK, I think I have cracked it…

My rounding calculations were all set-up with a “rate/amount” of 1.00 (because no matter what I put here, the number that I entered as the amount on the payment processor seemed to be the one that was used).

Now, changing the calculation and payment processor to both be 10.00 for the MXN rounding, it seems to work:

Ignore the ‘round’ buttons at the bottom, that was part of an experiment with manual rounding.

So now, the USD+ is cancelled out by the MXN- and this removes the 0.19 balance. It’s still probably more messy than it would be, but unless there is an easy solution do you think it should just be left as it is?

[QMcKay]

I have the virtually the same setup and exchange rate. But I still don’t like the way it works. As I previously mentioned, I want to avoid coins in both currencies and small bills in HNL.

So my Rounding is set to 1 for USD and 10 for HNL.

I think the problem is because the XR is 23 so it is never a whole number.

It is almost as though we need Rounding for Change Amount and/or Rounding for Remaining Amount.

In this video you can see that the resultant Change Amount is fractional, which I want to avoid.

---

[QMcKay]

Dumping this here for future reference (making notes for myself ;)) …

SELECT
tkt.[Id]
--,[LastUpdateTime]
--,[TicketNumber]
--,[Date]
--,[LastOrderDate]
--,[LastPaymentDate]
--,[PreOrder]
--,[IsClosed]
--,[IsLocked]
,tkt.[RemainingAmount]
,tkt.[TotalAmount]
--,[DepartmentId]
--,[TerminalId]
--,[TicketTypeId]
--,[Note]
--,[LastModifiedUserName]
--,[TicketTags]
--,[TicketStates]
--,[TicketLogs]
,tkt.[ExchangeRate]
,tkt.[TaxIncluded]
--,tkt.[Name]
--,tkt.[TransactionDocument_Id]
--,[TicketVersion]
--,[LineSeparators]
--,[TicketUid]

,pmt.[TicketId]
--,[PaymentTypeId]
--,[DepartmentId]
,pmt.[Name]
,pmt.[Date]
--,[AccountTransactionId]
,pmt.[Amount]
,pmt.[TenderedAmount]
--,[UserId]
--,[TerminalId]
--,pmt.[AccountTransaction_Id]
--,pmt.[AccountTransaction_AccountTransactionDocumentId]
,[Description]

--,txdoc.[DocumentTypeId]
,txdoc.[Name]

,tx.[Amount]
,tx.[ExchangeRate]
,tx.[Name]

,c.[Name]
,c.[Order]
,c.[DecreaseAmount]
,c.[Amount]
,c.[CalculationAmount]
,c.[Rounding]

FROM [Tickets] tkt
join [Payments] pmt on pmt.[TicketId]=tkt.[Id]
join [AccountTransactionDocuments] txdoc on txdoc.[Id]=tkt.[TransactionDocument_Id]
join [AccountTransactions] tx on tx.[Id]=pmt.[AccountTransaction_Id]
left join [Calculations] c on c.[TicketId]=tkt.[Id]

where tkt.[Date]>'2016-12-04 11:00'

function nearest(num,rnd,xr) {
  // num : the number to round
  // rnd : a value to round to
  // xr  : exchange rate
  num = num * xr;
  rnd = 1 / (rnd);
  return Math.round(num * rnd) / rnd;
}

[mjb2000]

A big part of the problem with the multi-currency payments seems to be that the rounding is happening on the Ticket Total and not the balance remaining. Although it’s called Ticket Total, it is actually the Ticket Total, after discounts - which is sensible

Take this example…

55 USD was paid, leaving a balance of 8.70 or 182.70 MXN

When I manually arround Round MXN it changes the total to 63.81 or 185 MXN - So why didn’t it round to 10… Well, it kind of did…
The 0.11 USD would have made it a round total IF we had not accepted the 55 USD

Without the 55 USD the total would have been 63.81 or 1340 pesos

@emre - The the rounding is correct, but applied at the wrong time. Is it possible have it applied to the REMAINING TOTAL rather than the ticket total?

[mjb2000]

Just thinking about it, applying this rounding to the REMAINING AMOUNT would also be helpful for Credit Card surcharges - we wouldn’t want to add a % to the whole bill if actually only the small balance was paid on the card. I thnk at the moment a service calculation payment processor would add the service charge to the whole ticket, correct? @QMcKay, would that be helpful to you as well?

[QMcKay]

@mjb2000

The the rounding is correct, but applied at the wrong time. Is it possible have it applied to the REMAINING TOTAL rather than the ticket total?

Looks like you realized the same thing as I did:

@QMcKay

It is almost as though we need Rounding for Change Amount and/or Rounding for Remaining Amount.

… which is what I was trying to experiment with …

For testing, this is what I did:

• removed the Calculations from the Payment Processors.
• changed the Calculation Types to Custom so that I can apply any value that I want.
• created an Action for Update Ticket Calculation
• created a Rule for Payment Processed to fire the Action when [:RemainingAmount] != 0
• calculate the Rounding Amount using a JS function as:

[=TN('{CALL:round.nearest([:RemainingAmount],1,1)}') - [:RemainingAmount]]

But my experiment is not working. By that I mean, no Calculation is being applied for some reason - at least, not that I can tell…

[mjb2000]

It all seems way over my head, I’m just think if multi-currency and rounding support is going to be part of the SambaPOS offering then it probably should be handled a little more ‘out of the box’ and maybe this would better be a change made by @emre (maybe offering a rounding type of “Round remaining balance”) as a calculation option, instead of having to get users to add these custom scripts?

[QMcKay]

@mjb2000

offering a rounding type of “Round remaining balance”) as a calculation option

Yes I agree @mjb2000, eventually that ^ is exactly what we want from @emre, but I was just creating a way (or so I thought) to do it through Scripts/Automation to see if my theory worked as planned.

[mjb2000]

@QMcKay

eventually that ^ is exact

I hope you find a way that I can piggy-back on. For now I’m still battling through customer accounts stuff. Struggling to work out what part of your Customer Accounts screen tutorial is still valid and what bits are now being done in other ways.

Once I get that sorted I’ll check back in and see how we’re doing with rounding.

All of my friends and employees keep telling me “this can’t be this complicated can it?” - Then when I tell them the list of requirements they then realise… “Oh yeah, it’s pretty complicated”

PS - Only just realised you’re in Honduras - Might see you if I come diving down there some time

[QMcKay]

@mjb2000

“Oh yeah, it’s pretty complicated”

It is complicated. And a BIG PITA. If the XR was 20 or 25 then most of this is moot, but anything in-between and it gets complex.

@emre, I can’t figure how to apply a Calculation using an Action… I mean, even this does not work… why?