Using Sharepoint Calculated Columns To Restrict Data Entry

What is a Calculated Column?

A calculated column in SharePoint is a column that displays a value based on a formula. The formula can reference other columns, perform operations like concatenation or rounding, call SharePoint functions, and more. Calculated columns allow displaying information derived from other data in the list or library without affecting the underlying data.

Key capabilities of SharePoint calculated columns include:

Performing calculations using values from other columns
Manipulating and transforming data
Validating data entered by users
Restricting the types of data entered into other columns
Dynamically updating based on changes to referenced columns
Formatting data for display with formatting functions
Referencing information from related lists with lookup functions

Why Restrict Data Entry?

Restricting data entry in SharePoint has several important benefits:

Table of Contents

Data quality – Restricting inputs to specific data types or values prevents invalid or inconsistent data from being entered.
Data integrity – Validation rules prevent incorrect or incomplete data that could lead to errors and corruption.
Accuracy of calculations – Data type restrictions ensure calculations are accurate by operating on consistent data types.
Easier reporting – Standardized, validated data allows for more accurate searching, aggregation, and reporting.
Form validation – Immediate feedback can prompt users to correct invalid entries.

Using Calculated Columns to Set Data Type

Calculated columns can dynamically restrict inputs to many standard data types like numbers, currencies, dates, and more. The column validation formula simply specifies the desired return type. Some examples:

Number

=INT([Column1])

Restricts inputs to whole numbers by converting values entered to integers.

Percentage

  
=TEXT(INT([Column2])/100,"0%")

Converts inputs to a percentage value ranging from 0% to 100%. The percentage is stored as an integer value.

Currency

=TEXT([Column3],"$#,##0.00")

Formats inputs as currency with 2 decimal places, using locale currency symbols like $ and £.

Date Only

  
=TEXT(INT([Column4]),"Short Date")

Accepts date values but stores them as integers representing serial date numbers, formatted as date strings.

Validation Message

=IF(ISERROR(INT([Column5])),"Enter a number",INT([Column5]))

Validates that inputs can be converted to integers, displaying a user-friendly message if not.

Validating Data with Calculated Columns

In addition to data types, custom validation logic can be built into calculated columns using IF statements, logical operators, and text functions.

Validate Email

=IF(OR(ISBLANK([Email]),AND(ISERROR(FIND("@",[Email])),ISERROR(FIND(".",[Email])))),"Invalid email",[Email])

Checks for presence of ‘@’ and ‘.’ to confirm a valid email structure before allowing the input.

Validate Phone Number

=IF(OR(ISBLANK([Phone]),NOT(ISNUMBER(FIND("-",SUBSTITUTE([Phone]," ",""))))),"Format as ###-###-####",[Phone])

Uses nested logic to check for proper phone number format of ###-###-#### before allowing the input.

Limit Text Length

=IF(OR(ISBLANK([Comments]),LEN([Comments])>250),"Maximum 250 characters",[Comments])

Restricts text inputs to 250 characters maximum.

Require Value

=IF(OR(ISBLANK([AccountNum]),[AccountNum]=0),"Account number required",[AccountNum])

Makes the column a required field by not allowing blank or 0 entries.

Cascading Data Restriction

Calculated columns also enable cascading or hierarchical validation logic where inputs are restricted based on values in another column.

Show Fields Based on Selection

=IF([RequestType]="Billing Question","Billing Email",IF([RequestType]="Technical Issue","Tech Email",""))

Shows additional email fields only if relevant based on selection in another RequireType column.

Restrict Choices Based on Status

 
=IF([Status]="Active", "In Progress", "Not Started")

Limits selections in a Status column to only certain values based on current value in another Status column.

Require Follow-up Date

=IF(AND([Status]="Closed",ISBLANK([ClosedDate])),"Follow-up date required",[ClosedDate])

Requires entry of a ClosedDate if Status is set to Closed.

Troubleshooting and Considerations

When working with calculated columns, keep these troubleshooting tips and considerations in mind:

Check for syntax errors in complex calculated column formulas
Ensure proper data types are used in calculations to avoid type conversion errors
Calculated columns may impact form load times as more columns are added
Extensive use of calculations can reduce overall list performance
Test validation logic to confirm desired behavior before list deployment
Set columns to display error messages rather than reject invalid entries not caught in validation
Consider indexed columns in choice lists to optimize filter performance