FORMEMBERS

Syntax

FORMEMBERS (Dimension, Direction, Expression)

Description

The FORMEMBERS function loops through all of the members of a dimension (unless you interrupt it with the BREAK function).

Parameters

Parameter	Description
Dimension	The dimension to use.
Direction	The direction to loop through the members (#FORWARD or #REVERSE).
Expression	The expression to evaluate for each iteration.

Example

Consider the following formula that uses DLOOKUP:

DLOOKUP(RANGES, COMMISSION_RATE, SALES >= SALES_LEVEL, #REVERSE)

You could achieve the same thing with the FORMEMBERS function:

FORMEMBERS(RANGES, #REVERSE,
	IF(SALES >= SALES_LEVEL, RETURN(COMMISSION_RATE))
);
RETURN(0)

Of course, in this case it is simpler just to use the DLOOKUP function, but the FORMEMBERS function makes it possible to perform more sophisticated lookups and tabulations. For example, the following formula returns the product that has the highest sales:

FORMEMBERS(PRODUCTS, #FORWARD,
	IF(SALES > &MaxSales,
		SET(&MaxSales, SALES);
		SET(&Product, MEMBER(PRODUCTS))
	)
);
&Product

Following is how you would have to do it without procedural logic:

DLOOKUP(PRODUCTS, MEMBER(PRODUCTS), SALES = DMAX(PRODUCTS, SALES))

The above version is shorter, but it is much less efficient than the version that uses procedural logic, because it calculates the DMAX repeatedly for every product.

You could eliminate some of the redundancy by using an expression block and a variable:

SET(&MaxSales, DMAX(PRODUCTS, SALES));
DLOOKUP(PRODUCTS, MEMBER(PRODUCTS), SALES = &MaxSales)

The previous version is more effective than the version that does not use procedural logic, but it is not as effective as the version that uses procedural logic. This is because in the version that does not use procedural logic, the FORMEMBERS function only loops through the products once. In the previous version, it loops through them twice—once for the DMAX and once for the DLOOKUP—although the DLOOKUP stops when it finds the right product.