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.
© Stonefield Software Inc., 2023 • Updated: 06/06/16
Comment or report problem with topic