Working with fields

Changing lowercase to uppercase

You can convert lowercase to uppercase using this expression:

XYZ=Name.GivenName using "%1S"

Return first three characters of a field

Create a derived user field to return the first three characters of a field:

Derived Fields
	ZZZThreeChar is a DerivedField
		type is Alpha size 3
		default label is untranslatable:”Display first three characters”
		return Reference1[0:3]

Return substring of a field

Create a derived user field to return a substring of a field. This example returns the first four characters of a field..

Derived Fields
	ZZZThreeChar is a DerivedField
		type is Alpha size 50
		return Reference1[1:4]

Or, this example returns the third through sixth characters.

Derived Fields
	ZZZThreeChar is a DerivedField
		type is Alpha size 50
		return Reference1[3:6]

If statement to derive fiscal quarter

First, create a derived date field as the current date.

    Derived Fields
        ZZZMyDate is a DerivedField
            type is Date
            return current date

Then create another derived date field to extract the quarter number.

    Derived Fields

        ZZZQuarter is a DerivedField
            type is Numeric size 1
            if (ZZZMyDate month >= 1
            and   ZZZMyDate month <= 3)
                ZZZQuarter = 1
            else
                if (ZZZMyDate month >= 4
                and   ZZZMyDate month <= 6)
                    ZZZQuarter = 2
                else
                    if (ZZZMyDate month >= 7
                    and   ZZZMyDate month <= 9)
                        ZZZQuarter = 3
                    else
                        if (ZZZMyDate month >= 10
                        and   ZZZMyDate month <= 12)
                            ZZZQuarter = 4

Named Types

Named Type is an alternative to using Field Type. Use this if you want to mimic the behavior of a field with the type and size already defined.

Return two digit month

Use this example to extract the single digit month elements from today’s date and then build a string datatype to display a two digit month the different date elements.

First, create a new derived user field set to the current date:

    Derived Fields

        ZZZCurrentDate is a DerivedField
            type is Date
            return current date

Next, create a derived user field to extract the month. This will return a single digit.

    Derived Fields

        ZZZCurrentMonth is a DerivedField
            type is Date
            return (ZZZCurrentDate month)

Then create another derived user field to determine if the month is double digit or single digit. If it is a single digit, add a zero (0) to the front.

ZZZMonthTwoChar is a DerivedField
            type is Alpha size 2
            if (ZZZMonth size <  2)
                return ("0" + ZZZMonth)
            else
                return (ZZZMonth)