Exclude Gift Certificates from sales totals

[silentbob]

Continuing the discussion from Gift Certificate Sales & Redemption:

I have noticed that a gift certificate sale shows up under sales in reports on day of purchase, and then again when it is redeemed. So a 50 GC will total to 100 in sales.

Is it possible to apply a state to GC orders and exclude these from Report Ticket Detail?

Or even to do it the opposite way around, and make it that when a gift certificate is “tendered” that it just applies a discount to the ticket rather than having it as a payment.

This could also be useful for my loyalty system where users get credit. At the minute it is showing as a payment, where in reality it is a discount as they are not physically paying me this money. From reading the forum some people suggest that we should pay tax regardless of discount applied but in Ireland we only pay tax on actual money received.

[JTRTech]

That’s because you are using products to sell I guess.
In theory you should use an entity account to hold the origional payment as credit and then only use products for sales which also go to account leaving a payable balance.

[gsreddy]

I also raised this before the gift certificate is a product and when you sold its showing in product. Than when redeeming few other products selling as this is also a sale. what have to do manually calculate sale-GC = Real Sale . What is a ideal setup, may be GC is like advance payment? or credit a entity when some one purchase so they can redeem. finding a way simpler . What my idea is Use manual GC Entities (dummy GC Numbers )like gift cards than credit ; But we already have samba card same as. what about using samba as gift card?

[silentbob]

Yes i follwed the v4 tutorial. I am going to try modify it to eliminate products, but im sure if it was easy to do, then QMcKay would have done it this way. Its a long tutorial and took me more than one go to get it working, so I’m sure that I will need some help.

What about the loyalty account Credit.
Is it possible to use this credit and apply is as a discount rather than a tender?

[QMcKay]

There is likely a way to filter the Report to remove GC Sales, however, the default Work Period Report is not configured to do so.

I use a SQL Script for my Z Reports which filters out the GC Sales and only shows Product Sales. I don’t use the default Work Period Report because I don’t like the data it shows, and GC Sales is one such thing.

How you want to slice it is up to you or your local laws. To me, a GC Sale is not a Product, and it has no Tax as such. It is simply a trade of Cash for a Paper Certificate. So the Cash is reported as a Payment or a sort of Income, but only for the purpose of balancing the Cashout Report at the end of the day to reconcile Cash in Drawer. It’s like having a Customer give you money to put on their Account as a Credit to be used at a later time. We don’t know which Product(s) the Credit (or GC) will be used for, and whether or not they are Taxable Products.

So at a later time, when an actual Product is purchased, Sales figures are reported for that Product (Base price plus applicable Taxes). Any method of Payment (or combination of methods) can be used, be it Cash, CC, GC, Customer Account, etc.

---

@silentbob

use this credit and apply is as a discount

Probably, but that doesn’t sound right to me. I would guess you would need to use a Fixed Amount Ticket Calculation for that instead of a Payment Type.

In any case, the way GC’s are currently set up as per the Tutorial, they are both a “Product” and an Entity with an associated Account. It could be reconfigured so that the Product does not exist at all, but at the time the Tutorial was written, using a Product was a simple way to get a button on the Menu. Now we can have Custom buttons on the Menu which can do anything we want, effectively invoking the same sort of Automation that we currently use for GCs, but in a way that eliminates the Product itself. That might be worth looking into.

---

All that said, if you want to continue to use the default Work Period Report in the interim, I would probably look for a way to alter it to filter out certain Transaction Types. For example, the GC explicitly uses a different Tx Type rather than the regular Sales Tx Type. That was exactly the intended purpose - to exclude the GC Purchase from actual Product Sales …

[silentbob]

Currently the only part of the default workperiod I use is the Report Tickets. But I have been avoiding using sql to report tax when samba does it for me.

Filtering via sql is an option but if staff were to go to tickets it would display a different total than the reported sales.

So i think eliminating the product is the xorrect thing to do.

Using the credit and apply it as a discount was the next thing I need to solve for Loyalty Credit since it is effectively a discount and tendering gives an incorrect sales total.
I just thought if I had to I could use this method for the gift certs and pay tax at time of purchase (as its working now) rather than when its redeemed (as I think it should be). Not ideal but at least tax is being paid and only once for the sale.

[QMcKay]

I’m not suggesting you use SQL; rather, change the Work Period Report in some way to filter out the GC Purchase Transaction Type.

I agree, eliminating the Product is probably a cleaner way to do this. I will have a go at it.

I just noticed though, that the Account Screen is mis-reporting the GC Purchase as a Sale Transaction - not sure why this is, because it used to work properly, but this is my Dev machine, so it might be broken somehow. The figure circled in Red is incorrect - it is including the GC Purchase and the Product Sale, when it should only report the Product Sale …

… and here is the Sales Account Detail, clearly showing the $25 GC Purchase as a Sale Transaction - this should not be the case - it should be a GC Purchase Transaction instead …

[gsreddy]

Yes that correct , that was my concern too. it doubles sales figures , we can calculate manually and consider as paper work but , we need to explain this to accountants? and show the GC product sales. Hmm I reaaly wanted to get a solution may be @emre might do on next development.

Same thing like GC I have promotion with customer where they get 5% in account . when they redeem I wanted to consider the redeems as a discount . i altered the wp report sales-redeems but still confuse my staff. hope i get idea from this discussion.

[emre]

Sorry I couldn’t catch what I’m expected to develop.

[gsreddy]

Sorry hats about the above discussion Gift certificate sales . May be pre numbered cards top up and redeemed or GC funds as a discount? as I tried to setup my loyalty redeems as a discount but I cannot add a more than one transaction as a discount? Ex: I wanted a promotion as a discount, GC redeem as discount, Change price also as a discount? may not possible but just a query sorry

[QMcKay]

@emre

Sorry I couldn’t catch what I’m expected to develop.

Not sure, but what @gsreddy is proposing is probably already doable with what we have. We can apply multiple discounts already.

But there seems to be a problem with the GC Setup and I cannot figure out how it got broken. The GC Purchase is being logged as a Sale Transaction instead of a GC Purchase Transaction. I know this used to work just fine, but now it is not. If I look back at the Tutorial, it is clear that it was working by looking at the Accounts screenshots.

@emre, you have my DB. There are Rules and Actions prefixed with “GC” that handle this. The GC Purchase Transaction Type is not being applied properly any more. Not sure how long this has been broken.

I am going to switch to my other Prod DB Backup and see if it is broken there too.

[JTRTech]

You would be better off handeling properly with a credit account.
Creating gift voucher as entity and payment goes to account putting it in credit.
So a payment without sales.
Then when redeeming you ring in orders and use the voucher account payment type with a account credit balance max limit so if it’s £50 voucher and a £75 ticket it will be £75 sales, £50 paid using account putting account to £0 and leaving £25 paid by cash or card.
Then adjust work period report to include account payments for voucher purchases and some form of vociherledger balance to track your credit ‘liability’ for paid and unused voucher payments.

[QMcKay]

@QMcKay

@emre, you have my DB. There are Rules and Actions prefixed with “GC” that handle this. The GC Purchase Transaction Type is not being applied properly any more. Not sure how long this has been broken.

I am going to switch to my other Prod DB Backup and see if it is broken there too.

Switched to my other Prod DB backup and it is working just fine … GC Purchase for 250 shows Cash but not Sale Transaction …

[QMcKay]

@QMcKay

Switched to my other Prod DB backup and it is working just fine … GC Purchase for 250 shows Cash but not Sale Transaction …

@emre I want you to know that I found the problem. It has to do with an unrelated Rule used for Staff Purchases. I added the Constraint outlined in Red and the GC problem is fixed. I also removed the 2nd Action since it is no longer required with the Rule Constraint in place. GC Purchases no longer show as a Sale Transaction, so everything is good, and working as intended.

Back to Reporting …

[QMcKay]

Here is something that can be used in the Workperiod Report so that it only reports on actual Sales … “Sales Old” is the default supplied in the report, while “Sales New” is another way to calculate Sales based on Transaction Types.

NOTE: I show T1 and T2 Transactions to include Taxes in the Report. You will need to change the names of the Tax Tx Types and Tax Accounts to coincide with your setup.

[Sales Old:1, 1]
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0}

[Sales New:1, 1]
Sales|[=F({ACCOUNT TRANSACTION TOTAL:Sale Transaction:Sales})]
Tax T1|[=F({ACCOUNT TRANSACTION TOTAL:T1 Transaction:Sales Tax T1})]
Tax T2|[=F({ACCOUNT TRANSACTION TOTAL:T2 Transaction:Sales Tax T2})]
>Tax Total|[=F({ACCOUNT TRANSACTION TOTAL:T1 Transaction:Sales Tax T1} + {ACCOUNT TRANSACTION TOTAL:T2 Transaction:Sales Tax T2})]
>>TOTAL|[=F({ACCOUNT TRANSACTION TOTAL:Sale Transaction:Sales} + {ACCOUNT TRANSACTION TOTAL:T1 Transaction:Sales Tax T1} + {ACCOUNT TRANSACTION TOTAL:T2 Transaction:Sales Tax T2})]

Now on to creating a GC Setup that does not use a Product; something that only uses Entities and their Accounts using a Custom Menu Button and some Automation … theoretically, we won’t even need a Ticket to do this, so even the “old” Sales Report should work fine. The only “problem” that I can see is that we won’t be able to use the Payment Screen to Settle the purchase of the GC, because there is no Ticket to Settle.

[QMcKay]

@QMcKay

Now on to creating a GC Setup that does not use a Product

I started to play with this a bit, and suffice it to say, I will not continue down this path. It is simply more work than it is worth with no “real” benefit.

Lack of a Product means no Ticket, and thus no Payment Screen. Because of this, we need to create Tx Docs (and associated Tx Types) for all Payment Methods, and it will not allow for making a GC Purchase with multiple Payment Types in one go like the Payment Screen allows. Having no Product also removes the ability to track GC Product Sales in the form of Reporting in a native fashion, which I don’t think is a good idea either.

Using a Product as we currently do keeps all the native functionality that we are used to having without the need to create Automation for Payments via manual methods like Tx Docs and Ask Question Actions.

[silentbob]

Yes the lack of the payment screen was something that concerned me.

@QMcKay

{ACCOUNT TRANSACTION TOTAL:Sale Transaction:Sales}

This does exactly what is needed. The only issue for me is that I filter out the different ticket types, specifically a Layaway and this does not get included in the sales until the final payment has been made and it is converted back to a ticket.

Maybe I should rethink my Layaway setup and use an entity for each ticket, top it up and when Enitity credit = Ticket Total have it auto settle.
But i am hesitant to touch it as it is currently working exactly how I need it to.

[emre]

I added new filter types and selectors for reporting.

{REPORT TICKET DETAILS} report will have TA.<Transaction Type Name> to get ticket amount filtered by orders assigned to the transaction type.

Also {REPORT ORDER DETAILS} tag will have (OTT=<Transaction Type Name>) expression to filter orders by transaction type.

You can test them on next update.

[silentbob]

@emre

{REPORT TICKET DETAILS} report will have TA.<Transaction Type Name>

I am just working on this but I noticed TA.23% Vat does not work. Is it because of the % symbol

[QMcKay]

@silentbob

Is it because of the % symbol

Probably. I would avoid special characters like that in naming conventions.