Conditional Value Increase With Sql?

Legacy Poster

• 31. Oktober 2014 11:42

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*

0

• 

  Domingo Rodriguez

  Moderator

  ★ BarTender Hero ★

  • 31. Oktober 2014 16:13

  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
• 

  Legacy Poster

  ★ BarTender Hero ★

  • 3. November 2014 14:41

  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
• 

  Legacy Poster

  ★ BarTender Hero ★

  • 3. November 2014 14:53
  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
• 

  Domingo Rodriguez

  Moderator

  ★ BarTender Hero ★

  • 4. November 2014 12:52

  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.

   

  FormatCurrency(Expression[,NumDigitsAfterDecimal
  [,IncludeLeadingDigit [,UseParensForNegativeNumbers
  [,GroupDigits]]]])

   

  Arguments

   

  Expression

   

  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.

   

  Settings

   

  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
  settings.

   

  Remarks

   

  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
  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

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.