Help with SQL to create a price check button

V5 Question
84

The script has incorrect syntax for the SQL query as @sukasem has mentioned.

You are failing to 'single-quote' the inputBarcode for the SQL …

This is correct:

function checkPrice(inputBarcode) {
	qry =  "SELECT p.[Price] FROM [MenuItemPrices] p LEFT JOIN [MenuItemPortions] s ON s.[Id] = p.[MenuItemPortionId] LEFT JOIN [MenuItems] m ON m.[Id] = s.[MenuItemId] WHERE m.[Barcode]='"+inputBarcode+"'AND s.[Name]='Normal'";
	var salesTicketCount = sql.Query(qry).First;
	return salesTicketCount;
}

The bad part:

[Barcode]="+inputBarcode+"AND

modified to be good:

[Barcode]='"+inputBarcode+"'AND
3 Likes
85

It was working because barcode is number but i think it will fail if it is string.

1 Like
86

The [MenuItems].[Barcode] field in the DB is datatype nvarchar(max). It will accept a raw unquoted number in a WHERE clause, but it will not accept any characters unless you 'quote' the value. So always best to quote it.

1 Like
87

Q any idea how to include tax in the price?

88

Using SQL, tax templates/mappings are likely to be tricky.
What type of taxes do you have setup.
Myself and Rick will both have tax included in prices so is not an issue as in UK retail prices are generally handled inc vat as customer sees them.

89

I don’t really quite understand why have to do all this. It seem this will happen in ticket anyway. why just add the item to ticket and just read the price for that item and display it on ask question, you can get product name portion tax bla blah blah. If accept, do nothing and not cancel last item.

1 Like
90

But how could you read the item price for that item from a ticket?
Next thing when we press price check the rule for price check have to wait till barcode scanned.

91

As you said before :wink: interesting turn of events hey, too complicated to lets make it more complicated LOL

92

He means, scan barcode to add the product to ticket and see price and cancel order if not wanted.

93

We have a vat too here and thats 12.5%.
Can’t we multiply 12.5% manually in sql by group code?
I have only one group code for non vat items and rest are the vat items.

94

No actually I mean, popup ask Question like the way you want.
So step out of my head right now:

  1. click price check popup to scan like you normally do.
  2. set program setting to price check mode
  3. in order add to ticket event check if this is check price mode then popup ask question. this is event we still in order level so, {XXX} for order level will be available
  4. after ask question set price check mode off.
95

Nah nah nah actually cashier is not allowed to cancel transaction, they need admin confirmation and if i added price check this way they gonna rob the grocery :smile:

96

The original purpose of the prompt and ask question was to avoid creating ticket unescerserally.
But the simplest solution would be just to scan barcode as normal so it adds to ticket and cancel if not needed.

This process as you cay would be automated, add to ticket using barcode with ask question but if adding to ticket unless cancel has been removed for whatever reason and you want to allow only in price check flow.

97

Yeah you are right sql is the best way to do price check.
Unless @QMcKay help with the tax template.

98

This is not cancel transaction. Cancel last order add to ticket.

1 Like
99

So you mean to say in price check it would only add one item, show the price by ask Que and remove it?

100

No, ticket will not close if you dont set action to close it.

101

Well offcourse I have to add cloce ticket.
But i wanted to know how could we read barcode from numpad, add item to ticket and display its total price with tax?

102

I can store barcode by set numpad value but how can i feed barcode into add order action and then display its name and total?

103

I don’t think you are understand what we saying and I am not sure you ask the right question.

So, this thing happen at cashier? Do you have customer Display?

BTW I never see price check (popup screen like that) at cashier so, they do have order screen (customer display to show orders). Customer already see the price when order scan.

And tax thing isn’t customer should expect to pay already. It is not like we can’t show it. but from my experience at big box grocery store in order line normally is price before tax. You know you can’t do anything about tax.

1 Like