FAQ 879: Data Checks - How to write custom formulas

Problem: I want to write a data check to find participants who terminated in a prior year, have no prior or current account balance, and no prior or current compensation. How can I do this in ASC? I've reviewed the current list under Utilities-Data Checks - Employee Data, but there isn't one that does exactly this.

Solution: You can create a custom data checks formula file that you can then select in the Data Checks set up screen when you run the standard system data checks. To set up a custom formula file: 1. Go to the EMPLOYEE window and select View - Wizards - Formulas and Create New 2. Enter a name for the formula - e.g. ABCDataChecks. Then click on the magnifying glass to select a directory to store it (usually ASC\User) and click SAVE. This saves the formula file. We will create one formula in the file, but you may add more if you like. 3. You will be in the Formula box with a sample formula format at the top of the left white box. Click ADD at the bottom and you will move to the right side. 4. Enter a formula name - e.g. DelOldTerm 5. Enter a Description 6. Enter Evaluation type - 0-Normal 7. Enter the formula - these follow similar conventions to programming or Excel, same as in the Grid or Reporter. For a listing of the pre-set functions in the system (e.g. for ending balances), go to the back of the Reporter manual (p. 155). (You can find the manual in the ASC window under Help-Manuals.) For the items that are just fields, not functions (e.g. Compensation) - you can just go to that field in the Employee screen and the Item Name balloon should pop up with the proper field name. To compare a field to a Plan specification, you have to start it with S:. In this example, the formula would be: ($BEGFUND=0)AND($ENDFUND=0)AND(COMP=0)AND(PRCOMP=0)AND(TERMIN<S:PLNYRBEG) 8. Select the Grid Properties, i.e. formats at the bottom 9. Click Post 10. Click Execute to see if it is a valid formula. If you get any error, something is wrong with the format. ASC Support is happy to help you with the format so contact us if necessary. 11. Click OK to exit 12. Run Data Checks from ASC window - Utilties-DataChecks-Employee Data. In the setup window in the lower right, browse to the formula file you just created. Select it and run the report. You should then see the data check and the person you set up. 13. If you want to edit this formula or add others later, you can go to View-Wizards-Formulas, select the formula file name you created in Step 2, pick Read/Write and Add a formula following the basic procedure above. If you want to learn more, you can review the Grid or Reporter Manuals for more details.