Conditional Value Increase With Sql? Follow


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" 
   ("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*


Domingo Rodriguez
Comment actions Permalink

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

Legacy Poster
Comment actions Permalink

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?

Legacy Poster
Comment actions Permalink

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!

Is there any way for this VB to be applied to a currency field?
Domingo Rodriguez
Comment actions Permalink

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 Function


Returns an expression formatted as a currency value using the currency symbol
defined in the system control panel.


[,IncludeLeadingDigit [,UseParensForNegativeNumbers






Required. Expression to be formatted.




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.




Optional. Tristate constant that indicates whether or not a
leading zero is displayed for fractional values. See Settings section for




Optional. Tristate constant that indicates whether or not to
place negative values within parentheses. See Settings section for values.




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.




The 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




When 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


Note   All settings information comes from the
Regional Settings Currency tab, except leading zero, which comes from the Number


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.

Please sign in to leave a comment.