Conditional Value Increase With Sql?
Hi All!
I have created a label template that pulls information from two databases to create a shelf label that includes a price, description and VAT/No VAT. This has been easy enough to achieve.
However, now I am attempting to set the price displayed to gross, by adding 20% to a label price if it is VAT applicable (i.e. if it has a tax ID of 2583).
The information for tax is stored in one database table called StockItem.TaxCodeID, and the price is stored in another called StockItemPrice.Price.
Is there a way to make this happen? I'm very new to SQL and have gotten by so far using the query and join tools included with Bartender, and has generated the following code:
SELECT "dbo"."StockItem"."Code", "dbo"."StockItem"."Name", "dbo"."StockItem"."TaxCodeID", "dbo"."StockItem"."PartNumber", "StockItemPrice"."Price" FROM "dbo"."StockItem" , "StockItemPrice"
WHERE "dbo"."StockItem"."ItemID" = "StockItemPrice"."ItemID"
AND
("StockItemPrice"."PriceBandID" = 1001
AND "dbo"."StockItem"."Code" LIKE '6%'
AND "dbo"."StockItem"."PartNumber" LIKE '%?BarcodeSearch1%'
AND "dbo"."StockItem"."Name" LIKE '%?NameSearch%'
AND "dbo"."StockItem"."TaxCodeID" = 258
I have tried appending the following code to this:
SELECT *, CASE "dbo"."StockItem"."TaxCodeID"
WHEN "dbo"."TaxCodeID"=2583 THEN "dbo"."StockItemPrice"."Price" *1.20
ELSE "dbo"."StockItemPrice.Price"
END as newcolumn
FROM "dbo"."StockItem"
But no matter how I work it, I always get the generic error code from Bartender.
I have very little experience with SQL - is there something I'm missing or doing wrong?
Thanks anyone for any help!
*edited for unknown formatting cock-up*
-
You should be applying this instead by using VBScript over the text object's data source which will display the price on your BarTender document, something like:
If Field("StockItem.TaxCodeID") = "2583" Then
value = value * 1.20
End If
0 -
Hi! Thank you for stepping me in the right direction with the VB - I've applied the code to the Price field, however I get the message:
"This script did not read from the 'Value' property, which means that the currently specified data source was completely ignored", and no calculations are done.
However if I just use Value = Value * 1.20 (as a test) the priced changes accordingly.
Any ideas?
0 -
got it! I just had to change the parenthesis:
If Field ("StockItem.TaxCodeID" = "2583") Then
value = value * 1.20
End If
This got it working, and now tax is applied =D Thanks Domingo!
---ADDITION:---
Is there any way for this VB to be applied to a currency field?0 -
When you choose "Currency" as the data type, you could still set the Source under the "Data Source" tab to be "VBScript", for example an "Event Control Script" one. The data types might be conflicting though (String vs numeric operations) so you might first need to do some data type conversions.
What you could do is to use the FormatCurrency() VBScript function instead and then convert your numeric value to a currency:
FormatCurrency FunctionReturns an expression formatted as a currency value using the currency symbol
defined in the system control panel.FormatCurrency(Expression[,NumDigitsAfterDecimal
[,IncludeLeadingDigit [,UseParensForNegativeNumbers
[,GroupDigits]]]])
ArgumentsExpression
Required. Expression to be formatted.
NumDigitsAfterDecimal
Optional. Numeric value indicating how many places to the right
of the decimal are displayed. Default value is -1, which indicates that the
computer's regional settings are used.IncludeLeadingDigit
Optional. Tristate constant that indicates whether or not a
leading zero is displayed for fractional values. See Settings section for
values.UseParensForNegativeNumbers
Optional. Tristate constant that indicates whether or not to
place negative values within parentheses. See Settings section for values.GroupDigits
Optional. Tristate constant that indicates whether or not
numbers are grouped using the group delimiter specified in the computer's
regional settings. See Settings section for values.
SettingsThe IncludeLeadingDigit, UseParensForNegativeNumbers, and GroupDigits
arguments have the following settings:
Constant Value Description TristateTrue -1 True TristateFalse 0 False TristateUseDefault -2 Use the setting from the computer's regional
settings.
RemarksWhen one or more optional arguments are omitted, values for omitted arguments
are provided by the computer's regional settings. The position of the currency
symbol relative to the currency value is determined by the system's regional
settings.Note All settings information comes from the
Regional Settings Currency tab, except leading zero, which comes from the Number
tab.The following example uses the FormatCurrency function to format the
expression as a currency and assign it to MyCurrency:Dim MyCurrency MyCurrency = FormatCurrency(1000) ' MyCurrency contains $1000.00.
0
Please sign in to leave a comment.
Comments
4 comments