Pretty sure his tutorial was not using products on the tickets.
EDIT: I stand corrected he was using that but now he is not. PS Just enable the option to allow zero priced tickets. It should be in Department settings.
Thanks @Jesse
I already have that option checked for the account payment ticket type, but Iâll see if it fixes it by enabling that option for the standard ticket type.
With regards to using a product⊠So what is the new way of doing this, what am I missing? Is there another guide I should be following?
Simplest solution will beâŠ
Create an Account Transaction Document Action.
Create another Action to refresh Ticket Display Screen.
Create an automation command button on map it to ticket.
Create a rule to handle button click and make the account transaction.
Here you can see how it works.
You can customize Amount Query screen by defining a numeric mask and numeric keyboard.
[?Enter Transaction Amount;\d+\.\d{0,2};;OCN]
It should appear like that.
2 Likes
It is very difficult to tell what might be happening because the SQL executes in a âhiddenâ manner so we cannot see what criteria is being used. In this case, the only way to troubleshoot this is to put the SQL in SSMS and hard-code the values in questionâŠ
This is just guessingâŠ
Is the Entity Type for that Entity âCustomersâ ? Because that is hard-coded right at the top of the script in the very first line:
declare @entityType varchar(255) = 'Customers'
The main insert/select statement has, as part of if filter criteria, this part, where it will only return records having Entity Type of âCustomersâ:
LEFT JOIN [TicketEntities] te on te.Ticket_Id = tkt.[Id] and te.[EntityTypeId] IN (SELECT [Id] FROM [EntityTypes] WHERE [Name]=@entityType)
If you think this might be what is causing your issue, we need to make the @entityType variable dynamic (ie. pass it in to the SQL like we do with @entName), or hard-code it to another value.
1 Like
I abandoned making payments to Accounts via Payment Screen which uses a Product because there are too many ânigglesâ doing it that way. The method I use is very similar to what Emre demonstrated, using Transactions.
1 Like
Wow Wow Wow!!!
This is perfect for what I need. Especially with the multiple currencies and the list of customers with a âticket explorerâ.
One thing - Would it be possible to display just a subset of customers on the entitiy selction (only those customer who has an outstanding balance at present).
Have I missed some guide on how this type of thing could be achieved, because right now itâs all way above my head.
Account screens have that feature.
While entity selection I donât know how it can work as you wonât be able to assign a customer to a ticket if she have no outstanding balance.
Entity screen is easily filtered by state, so if using accounts you could maybe create automation as part of payment and charge flow to update a DEBTOR state to the customer entities?
Doing same thing with product sales is also possible and might be a better fit for some cases so Iâll also demonstrate this.
I created few products and added them to menu.
We need a new Account Transaction Type.
When a credit order is added Iâll change account transaction type for the order. Iâll create the action.
⊠and create the rule.
That also Cancels order by custom constraints which checks if a customer is selected on ticket so if you need such feature you also need to create Cancel Order action.
This is how it should work for the extreme case.
2 Likes
If the Account does not have an outstanding balance you cannot apply a Credit via the Payment Screen. At least not natively as Emre mentioned above.
At least I think that was a problem, maybe it isnât any longer.
In any case, the custom Credit solution gives much more control I think⊠maybe I need to revisit the Payment Screen idea, but Iâm not up for it at the moment since I already have something that works well for me.
i have actualyy ammended all âCustomerâ references to âBookingsâ as these are the entities I am using. These accounts are booking accounts relating to Hotel Guests booking into the Hotel. As such they are accounts time limited to the duration of the Guests stay at the Hotel. Transactions are then collected (and signed to the Booking account) and subsequently settled on the Guests departure. I have changed all the Customer referenecs in the scripts and automation to suit the entities I am using.
The whole process works perfectly until the account is credited in some way. Under normal circumstances the Guest makes only one payment on this account (at the time of check out) and the Account Operations and Statements work perfectly in this case.
The problem comes when the guest makes an interim payment. The payment process works fine and the correct amounts are credited to the account but after this transaction the automation will not display ANY transactions either histoical or subsequent. The transactions are still there as I can view them in the hard coded accounts module. Also any future transactions will not display on this automation once a payment has been made.
I do see some scripting relating to âlast date for zero balanceâ which seems to relate to only displaying current balances. Could this be causing my problem. I do not need this feature as I require my system to display ALL historical transactions for each account.
You wouldnât need the zero date if using booking accounts, expect like you say that might be some crossover from Qs solution
An update on the process so far.
I stripped out all the date related conditional script as I just need all transactions relating to a particular account. That worked fine.
I was still getting random accounts that were refusing to dispaly the account transactions and this I could not solve until I relaised that the script was generating a stack overflow error due to a numerical conversion. Basically because there are about 150 Sri Lankan Rupees to the dollar, some of the individual transactions go into 7 figures and the fields were being formatted as 6,2. Small change to 7,2 now all is working.
Took some finding but once seen it was a very simple fix.
1 Like
I am having another issue with my getStatement script relating to the Ticket Number.
The Account Statement report works fine with F&B tickets and transactions however I have a couple of different ticket types namely
Account Payment Ticket (from your automation)
Laundry Ticket (guests submitting and paying for Laundry through POS)
Half Board Tickets
For purposes of clarity and to prevent repeated ticket numbers I have prefixed the number generators for these tickets to make them more meaningful to my staff.
For example Booking Account Payments (from your automation above) are prefixed in the number generator as ACCPAY- and then a sequential number.
This unfortunately âbreaksâ the report as the value for the Ticket Number is a non integer. I have been trying for more than a few hours now to write some SQL script to strip the alpha characters and convert to integer but I realise that without more extensive SQL knowledge this could take me a while. If I could pull out the last 4 digits this could work. Tried CAST and now I think that PATINDEX is the way to get there but cannot get any syntax of this to work in the script.
Is there a simple way to convert âACCPAY- 1234â into â1234â which can be pasted into the GetStatement script. Everything I have tried has just created other syntax issues in the script.
I can of course revert to numerical only ticket numbers if there is no sensible way to do this. Something like 99991234 although this is not as easy for the staff to follow.
Any suggestions (or SQL script!) would be much appreciated.
Iâm not %100 sure but I donât think it does something specific with ticket numbers so you can safely try selecting them as string instead of an integer.
I canât post full SQL as Iâm not sure if it matches to the version you have but you can make these 2 simple changes to test it.
2 Likes
If you want to leave âACCPAY-â as part of the Ticket Number, you can do as Emre suggests, and I think the Ticket Number field in other tables in the DB is actually a character field rather than an integer anyway, so it shouldnât hurt to change that to a varchar in the table declaration as Emre shows.
If you want to strip out the characters, you can use SQL REPLACE, and even nest them if you have more than 1 prefix. Based on the screenshot shown by Emre, it would be this line:
,REPLACE(tkt.[TicketNumber],'ACCPAY- ','') as [TicketNo]
Multiple prefix replacements nested - it will start with the inner-most and work outward:
,REPLACE( REPLACE( REPLACE(tkt.[TicketNumber],'ACCPAY- ',''), 'AnotherPrefix',''), 'A3rdPrefix','') as [TicketNo]
I am trying to add a Balance brought forward row to the top of the account statement.
From what I can tell this is where the balance is calculated.
SELECT @txcount = count([Id]) FROM @tbl_tx
WHILE @i<=@txcount
BEGIN
SET @balance = @balance + (SELECT [Amount] FROM @tbl_tx WHERE [Id]=@i)
UPDATE @tbl_tx SET [Balance] = @balance WHERE [Id]=@i
IF @balance = 0 UPDATE [ProgramSettingValues] SET [Value]=(SELECT [Date] FROM @tbl_tx WHERE [Id]=@i) WHERE [Name]='AS Entity Date_ZeroBalance'
SET @i = @i + 1
END
Is it possible to modify this to set the balance on the first row to show the Balance brought forward ie. existing balance on the filtered date .
It can be a bit confusing if the Date filter is used, the balance does not add up correctly. It only calculates balance from the filtered date and ignores any previous balance.
Without Filter: Calculates correctly
With Filter: Balance column is wrong. I would like a row on the top of this report showing
Balance brought Forward âŹ1430
You wonât use date filter here. It will automatically filter date from last 0 balance.
Yes I understand that this is how it works but there is also a date filter included in this tutorial.
It helps us as most of our customers will have have a continuous balance with us. Ie. rarely reaching 0.
We want to be able to issue a statement at the end of every month, but by the time the balance is paid the customer may already have made more purchases, so the the balance never really reaches 0.
In a couple of months time this could mean that the account statement could be 10 pages long.