Skip to main content

Cloud

Cross Dimension Validation

Or…How do I assure my users pick the correct Entity / Cost Center combination?

The Situation:

  • Planning 11.1.2.1
  • Client has an Entity and a Cost Center dimension
  • End users primarily use Data Forms for data entry
  • The Cost Centers are setup such that they begin with the Entity and then have a 6 digit code.

Entity:  3600

Cost Center:  CC_3600123456

Requirement:

Remove the possibility that a user enters data at an invalid Entity / Cost Center combination

Solution:

I created a business rule which utilizes the @ISMBR, @MATCH, @CONCATENATE, @NAME and @RETURN function.

The business rule is setup to compare the selected Cost Center to all cost centers that begin with the first four digits of the Entity.  If the cost center is not a part of the list of cost centers then the @RETURN function is used to stop any further calculations and display an error message to the user.

Variables used in the Code below

varCostCenter = the selected Cost Center

varEntity = the selected Entity

Code:

 

FIX ({varCostCenter})

“HP_Account”(

IF (NOT(@ISMBR(@MATCH(“HP_CostCenter”,@CONCATENATE(@CONCATENATE(“CC_”,@NAME({varEntity})),”??????”)))))

@RETURN(“The Entity & Cost Center combination you have selected is not valid.  Clear the data. Save the form and select a correct Entity & Cost Center.”, ERROR);

ENDIF

)

ENDFIX

When we step through the rule we first build the @MATCH function to create the list of cost centers that begin with the selected Entity.

  • @NAME({varEntity}) = Returns the text name of the selected Entity (e.g. 3600)
  • @CONCATENATE = Adds the CC_ prefix to the selected Entity (e.g. CC_3600)
  • @CONCATENATE = Adds the “pattern” syntax for the @MATCH query (e.g. CC_3600??????)
  • @MATCH = HP_CostCenter,”CC_3600??????” returns all cost centers that are descendants of HP_CostCenter that begin with CC_3600 (e.g. CC_3600123456, CC_3600789012, CC_3600456789, etc.)
  • @ISMBR = Compares the varCostCenter in the FIX statement to the list of members returned by the @MATCH function

If the cost center is not in the list then the @RETURN function is called (note the NOT at the beginning of the TRUE test)

  • @RETURN = “Error Message”, ERROR

Result:

The above business rule is added to the data form before any other business rules also attached to the data form and set to run on save and uses members on form. When a user selects an incorrect Entity / Cost Center combination and clicks save the data is saved, but the message is displayed and not further business rules are run.

Note: The user will have to clear the data and resave the data form which will result in the message being displayed again but the invalid data has now been cleared.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Jason Kaniss

More from this Author

Categories
Follow Us
TwitterLinkedinFacebookYoutubeInstagram