🧮 Day 4: Formula Fields & Calculations

What I Built Today

Today I focused on automating calculations by implementing 8 formula fields across my Housing Operations system. Formula fields are powerful because they automatically calculate values based on other fields, eliminating manual data entry and ensuring accuracy.

The 8 Formula Fields:

1. Property Age – Calculates how old a property is

2. Tenancy Duration Days – Calculates how long a tenancy has lasted

3. Tenancy Status Indicator – Visual emoji indicator for tenancy status

4. Repair SLA Status – Checks if repair was completed within SLA

5. Days to Repair Deadline – Calculates days remaining to meet SLA

6. Rent Arrears Risk Level – Categorizes arrears (Low/Medium/High)

7. Vulnerability Review Overdue – Flags overdue reviews (checkbox)

8. Property Full Address – Concatenates address fields into one

---

Key Learnings

1. What Are Formula Fields?

Formula fields are read-only fields that automatically calculate their value based on other fields, expressions, or values. They update in real-time whenever the underlying data changes.

Key characteristics:

• Automatic – No manual data entry required

• Dynamic – Updates when source fields change

• Read-only – Users cannot edit them directly

• Efficient – No storage space used (calculated on-the-fly)

---

2. Formula Return Types

Formulas can return different data types:

Number – For calculations (e.g., Property Age, Days to Deadline)

YEAR(TODAY()) - Year_Built__c

Text – For concatenation or conditional text (e.g., SLA Status, Full Address)

IF(Actual_Completion_Date__c <= Target_Completion_Date__c, "✅ Met SLA", "❌ Missed SLA")

Checkbox – For TRUE/FALSE conditions (e.g., Review Overdue)

TODAY() - Review_Date__c > 90

Date/DateTime – For date calculations (not used in today's examples, but possible)

---

3. Formula Functions I Mastered

IF() – Conditional Logic

IF(condition, value_if_true, value_if_false)

Example:

IF(ISBLANK(End_Date__c), TODAY() - Start_Date__c, End_Date__c - Start_Date__c)

If End Date is blank, calculate from today. Otherwise, calculate total duration.

CASE() – Multiple Conditions

CASE(expression, value1, result1, value2, result2, default_result)

Example:

CASE(TEXT(Tenancy_Status__c), "Active", "🟢 Active", "Ended", "🔴 Ended", "⚪ Unknown")

Cleaner than nested IFs for simple value matching.

ISBLANK() – Check for Empty Fields

ISBLANK(field_name)

Returns TRUE if field is empty, FALSE if it has a value.

TODAY() – Current Date

TODAY()

Returns today's date. Useful for age/duration calculations.

YEAR() – Extract Year from Date

YEAR(date_field)

Extracts the year from a date field.

& (Ampersand) – Text Concatenation

text1 & text2

Joins text strings together.

Example:

Address__c & ", " & City__c & ", " & Postcode__c

Result: "123 Main Street, Bradford, BD1 1AA"

---

4. The 8 Formula Fields Explained

Formula 1: Property Age

Object: HOA_Property__c Return Type: Number

Formula:

YEAR(TODAY()) - Year_Built__c

Why it matters:

• Helps prioritize maintenance for older properties

• Useful for portfolio age distribution reporting

• Supports strategic planning for property upgrades

Example: If Year Built = 2000 and current year = 2025, Property Age = 25

---

Formula 2: Tenancy Duration Days

Object: HOA_Tenancy__c Return Type: Number

Formula:

IF(ISBLANK(End_Date__c), TODAY() - Start_Date__c, End_Date__c - Start_Date__c)

Why it matters:

• Tracks tenancy length for reporting

• Identifies long-term vs. short-term tenancies

• Useful for turnover analysis

Example:

• Active tenancy (no end date): Days since start

• Ended tenancy: Total duration in days

---

Formula 3: Tenancy Status Indicator

Object: HOA_Tenancy__c Return Type: Text

Formula:

CASE(TEXT(Tenancy_Status__c), "Active", "🟢 Active", "Ended", "🔴 Ended", "Notice Given", "🟡 Notice Given", "⚪ Unknown")

Why it matters:

• Quick visual scanning of tenancy status

• Improves user experience on list views

• Makes dashboards more intuitive

---

Formula 4: Repair SLA Status

Object: HOA_Repair_Request__c Return Type: Text

Formula:

IF(ISBLANK(Actual_Completion_Date__c), "In Progress", IF(Actual_Completion_Date__c <= Target_Completion_Date__c, "✅ Met SLA", "❌ Missed SLA"))

Why it matters:

• Instant visibility into SLA performance

• Critical for board reporting

• Identifies contractors who miss deadlines

---

Formula 5: Days to Repair Deadline

Object: HOA_Repair_Request__c Return Type: Number

Formula:

IF(ISBLANK(Actual_Completion_Date__c), Target_Completion_Date__c - TODAY(), NULL)

Why it matters:

• Helps prioritize urgent repairs

• Negative values flag overdue items

• Supports proactive backlog management

Example:

• Target is 5 days away: Shows 5

• Target was 2 days ago: Shows -2 (overdue)

---

Formula 6: Rent Arrears Risk Level

Object: HOA_Tenancy__c Return Type: Text

Formula:

IF(Rent_Arrears__c = 0, "✅ No Arrears", IF(Rent_Arrears__c <= 500, "🟡 Low Risk", IF(Rent_Arrears__c <= 1500, "🟠 Medium Risk", "🔴 High Risk")))

Why it matters:

• Helps finance team prioritize collections

• Visual indicators make reports actionable

• Supports risk-based arrears management

Risk Levels:

• £0: No Arrears

• £1-£500: Low Risk

• £501-£1500: Medium Risk

• £1500+: High Risk

---

Formula 7: Vulnerability Review Overdue

Object: HOA_Vulnerability__c Return Type: Checkbox

Formula:

IF(ISBLANK(Review_Date__c), TODAY() - Identified_Date__c > 90, TODAY() - Review_Date__c > 90)

Why it matters:

• Critical for safeguarding compliance

• Easy to filter in reports (checked = overdue)

• Prevents regulatory violations

Logic: Flags as overdue if more than 90 days since last review (or identification if never reviewed)

---

Formula 8: Property Full Address

Object: HOA_Property__c Return Type: Text

Formula:

Address__c & ", " & City__c & ", " & Postcode__c

Why it matters:

• Simplifies address display in reports

• Reduces need for multiple address columns

• Improves readability

Example Output: "123 Main Street, Bradford, BD1 1AA"

---

Screenshots

1. Property Age Formula Setup

2. Tenancy Duration Days Formula Setup

3. Tenancy Status Indicator Formula Setup

4. SLA Status Formula Setup

5. Days to Deadline Formula Setup

6. Arrears Risk Level Formula Setup

7. Review Overdue Formula Setup

8. Full Address Formula Setup

9. Property Record with Formulas

10. Repair Request Record with Formulas

---

Challenges I Faced

1. Nested IF Statements

Getting the parentheses balanced in nested IF statements was tricky at first. I learned to:

• Work from the inside out

• Test each condition separately

• Use proper indentation (even though Salesforce doesn't require it)

2. Date Arithmetic

Understanding that subtracting dates in Salesforce returns the number of days was key. Also learned that:

• TODAY() returns a Date (no time component)

• Date fields can be subtracted directly

• Result is always in days

3. CASE vs. Nested IF

Learned when to use CASE (simple value matching) vs. nested IF (complex conditions). CASE is cleaner for:

• Checking a single field against multiple values

• Returning different results for each match

4. Formula Return Types

Had to think carefully about return types:

• Use Checkbox for TRUE/FALSE conditions (easier to filter)

• Use Text for visual indicators with emojis

• Use Number for calculations that might be used in other formulas

---

Real-World Impact for Incommunities

These 8 formula fields will:

Operational Efficiency

• Eliminate manual calculations – No more calculating property age or tenancy duration by hand

• Real-time insights – SLA status and days to deadline update automatically

• Reduce errors – Automated calculations are always accurate

Compliance & Risk Management

• Safeguarding compliance – Review Overdue checkbox ensures no vulnerability is forgotten

• Financial risk visibility – Arrears Risk Level helps prioritize collections

• SLA tracking – Instant visibility into repair performance

Reporting & Decision-Making

• Better dashboards – Visual indicators (emojis, colors) make data easier to scan

• Accurate metrics – Automated calculations ensure consistent reporting

• Proactive management – Negative "Days to Deadline" flags overdue repairs immediately

User Experience

• Cleaner interface – Full Address field reduces clutter

• Intuitive status indicators – Emojis provide instant understanding

• Less data entry – Users don't need to calculate or enter these values

---

What I Learned About the Salesforce Admin Role

Today's work directly aligns with the Salesforce Administrator role at Incommunities:

From the Job Spec:

"Configure Salesforce to meet business requirements"

How formula fields deliver this:

• Translate business logic into automated calculations

• Reduce manual work for end users

• Ensure data consistency across the platform

From the Job Spec:

"Create and maintain reports and dashboards"

How formula fields deliver this:

• Provide calculated fields for reporting (e.g., SLA Status, Arrears Risk Level)

• Enable visual indicators that make dashboards more effective

• Support KPI tracking (e.g., % of repairs meeting SLA)

From the Job Spec:

"Ensure data quality and integrity"

How formula fields deliver this:

• Eliminate manual calculation errors

• Ensure consistent logic across all records

• Provide real-time, accurate data for decision-making

---

Tomorrow: Day 5

I'll dive into Security & Access – understanding profiles, permission sets, role hierarchy, and sharing settings to control who sees and does what in Salesforce! 🔒

This is critical for ensuring data privacy and compliance in a housing association context.

---

Resources

• GitHub: [github.com/chidoziemanagwu/sfdc-housing-ops..]

• Hashnode: [](https://github.com/chidoziemanagwu/sfdc-housing-ops-admin-ba)doxzy.hashnode.dev/series/project-based-lea.[.]

---

Progress Tracker

✅ Day 1: Data Modeling & ERD

✅ Day 2: Custom Objects & Fields

✅ Day 3: Data Quality & Validation Rules

✅ Day 4: Formula Fields & Calculations

⬜ Day 5: Security & Access

⬜ Day 6: Automation (Flow)

⬜ Day 7: Reports & Dashboards

⬜ Day 8: Data Management

⬜ Day 9: Integration Basics

⬜ Day 10: Final Project & Review

---

Follow my journey:

• GitHub: [github.com/chidoziemanagwu/sfdc-housing-ops..]

• Hashnode: [](https://github.com/chidoziemanagwu/sfdc-housing-ops-admin-ba)doxzy.hashnode.dev/series/project-based-lea.[.]

#Salesforce #SalesforceAdmin #LearningInPublic #Day4 #FormulaFields #Automation #HousingAssociation