Although the Output Expression property for a calculated field can be a simple expression, often you need to perform more complex calculations. For this, you have to call a script.

This tutorial shows how to do this. It assumes you completed the "creating a project" tutorial for either Access or SQL Server and that project is open in Stonefield Query Studio.

We want a formatted address field in the Customers table that shows the city, region, and postal code in one field so it appears conveniently formatted for reports or labels. While we could enter a simple expression that concatenates these three fields together, a problem we have is that different countries format their addresses differently. For example, in North America, addresses are formatted as city, comma, space, state or province, comma, space, zip or postal code. However, in Germany, addresses are formatted as postal code, space, city. So, we can't create a simple expression that gives a country-sensitive formatted address line. Instead, call a script.

Start by selecting the Customers table, and choose Create Field from the Objects menu. Specify "FormattedAddress" as the field name, set the width to 40, and enter "Formatted Address" for the caption. Then, click the Calc button and enter the following for Output Expression:

GetFormattedAddress(City, Region, PostalCode, Country)

This tells Stonefield Query to call the GetFormattedAddress script, passing it the current value of the City, Region, PostalCode, and Country fields, when this field is used in a report.

Next, click the Scripts panel bar and click the Create button to create a new script. Enter "GetFormattedAddress" for the script name, choose the desired script language, and enter one of the following for the code:

Visual FoxPro

lparameters tcCity, tcRegion, tcPostalCode, tcCountry
local lcAddress
do case
  case tcCountry = 'Canada' or tcCountry = 'U.S.'
    lcAddress = trim(tcCity) + ', ' + trim(tcRegion) + ;
      ' ' + tcPostalCode
  case tcCountry = 'Germany'
    lcAddress = trim(tcPostalCode) + ' ' + tcCity
  otherwise
    lcAddress = trim(tcCity) + ' ' + trim(tcRegion) + ;
      ' ' + tcPostalCode
endcase
return lcAddress

VBScript

function Main(Application, City, Region, PostalCode, _
  Country)
select case Country
  case "Canada"
    Main = rtrim(City) + ", " + rtrim(Region) + " " + _
      PostalCode
  case "U.S."
    Main = rtrim(City) + ", " + rtrim(Region) + " " + _
      PostalCode
  case "Germany"
    Main = rtrim(PostalCode) + " " + City
  case else
    Main = rtrim(City) + " " + rtrim(Region) + " " + _
      PostalCode
end select
end function

JavaScript

function Main(Application, City, Region, PostalCode,
  Country) {
var Address ;
switch (Country) {
  case 'Canada' :
    Address = City + ', ' + Region + ' ' + 
      PostalCode ;
    break ;
  case 'U.S.' :
    Address = City + ', ' + Region + ' ' + 
      PostalCode ;
    break ;
  case 'Germany' :
    Address = PostalCode + ' ' + City ;
    break ;
  default :
    Address = City + ' ' + Region + ' ' + 
      PostalCode ;
    break ;
}
return Address ;
}

C#

public static object GetFormattedAddress(string city, string region, 
  string postalCode, string country)
{	
  string address = String.Empty;
  switch (country.ToUpper()) 
  {
    case "CANADA" :
      address = city + ", " + region + " " + postalCode;
      break;

    case "U.S." :
      address = city + ", " + region + " " + postalCode;
      break;

    case "GERMANY" :
      address = postalCode + " "  + city;
      break;

    default :
      address = city + " " + region + " " + postalCode;
      break;
    }
    
    return address;
}

VB.NET

public shared function GetFormattedAddress(city as String, region as String,
  postalCode as String, country as String) as object
  Dim address As String = String.Empty
  Select Case country.ToUpper()
      Case "CANADA"
        address = city + ", " + region + " " + postalCode

      Case "U.S."
        address = city + ", " + region + " " + postalCode

      Case "GERMANY"
        address = postalCode + " " + city

      Case Else
        address = city + " " + region + " " + postalCode
  End Select
  Return address
End Function

Run Stonefield Query and create a report showing the CompanyName and FormattedAddress fields from the Customers table. Notice the address is formatted properly for each country.