Working with Virtual Fields and Custom Base Views

Virtual fields allow you to extend entities with computed or related data without modifying the underlying table structure. MemberJunction supports two primary approaches for implementing virtual fields through custom base views.

Overview

Virtual fields are calculated or derived fields that appear alongside regular entity fields in views, forms, and filters. They don't exist as physical columns in your database tables but are computed dynamically through SQL views or business logic.

Common use cases include:

• Displaying related field values from linked entities
• Performing calculations across related records
• Aggregating data from child records
• Computing derived values based on business rules

Types of Virtual Fields

1. Related Field Values

These fields pull values from related entities through foreign key relationships.

Example: Adding an IsSubscription field to ContractTermLineItem that displays the subscription status from the linked Product record.

-- In your custom base view
SELECT 
    ctli.*,
    p.IsSubscription
FROM ContractTermLineItem ctli
LEFT JOIN Product p ON ctli.ProductID = p.ID

2. Calculated Fields

These fields perform computations, often involving aggregations from related records.

Examples:

• AnnualContractValue: SUM of all Amount values from related ContractTermLineItem records
• AnnualRecurringRevenue: SUM of Amount values where IsSubscription is true
• CurrentTermStart/CurrentTermEnd: Dates from the active contract term

-- Example aggregation in custom base view
SELECT 
    c.*,
    ISNULL(line_items.AnnualContractValue, 0) as AnnualContractValue,
    ISNULL(subscription_items.AnnualRecurringRevenue, 0) as AnnualRecurringRevenue
FROM Contract c
LEFT JOIN (
    SELECT 
        ContractID,
        SUM(Amount) as AnnualContractValue
    FROM ContractTermLineItem 
    GROUP BY ContractID
) line_items ON c.ID = line_items.ContractID
LEFT JOIN (
    SELECT 
        ctli.ContractID,
        SUM(ctli.Amount) as AnnualRecurringRevenue
    FROM ContractTermLineItem ctli
    INNER JOIN Product p ON ctli.ProductID = p.ID
    WHERE p.IsSubscription = 1
    GROUP BY ctli.ContractID
) subscription_items ON c.ID = subscription_items.ContractID

Implementation Steps

Step 1: Create or Modify the Base View

You have two options for implementing custom base views:

Option A: Modify Existing Generated View

1. Locate the existing base view (e.g., finance.vwContracts)
2. Modify the view definition to include your virtual fields
3. Update the Entity record in MemberJunction

Option B: Create New Custom View (Recommended)

1. Create a new view with a descriptive name (e.g., finance.vwContracts_Custom)
2. Include all original fields plus your virtual fields
3. Update the Entity record to reference the new view

Recommended approach: Option B provides better maintainability and preserves the original generated view.

Step 2: Update Entity Configuration

1. Navigate to the Entity record for your target entity
2. Update the BaseView field to reference your custom view name
3. Set BaseViewGenerated = 0 to prevent MemberJunction from overwriting your custom view
4. Save the changes

Step 3: Run CodeGen

Execute the CodeGen process to regenerate the entity classes and metadata:

# Run CodeGen to update entity definitions
npm run codegen

This will:

• Generate new TypeScript/JavaScript classes with your virtual fields
• Update GraphQL schema definitions
• Refresh metadata for the MemberJunction Explorer

Step 4: Verify Implementation

After running CodeGen:

1. Check that virtual fields appear in entity forms
2. Verify fields are available as columns in list views
3. Confirm fields can be used in filters and searches
4. Test that calculated values display correctly

Best Practices

Naming Conventions

• Use descriptive names that clearly indicate the field's purpose
• Consider prefixing calculated fields (e.g., Calc_AnnualValue)
• Maintain consistency with existing field naming patterns

Performance Considerations

• Index Related Tables: Ensure foreign key columns used in JOINs are properly indexed
• Limit Aggregations: Complex calculations can impact view performance
• Consider Materialized Views: For frequently accessed calculated data
• Test with Production Data Volumes: Verify performance with realistic data sizes

View Structure

-- Template for custom base view
CREATE OR ALTER VIEW [schema].[vwEntityName_Custom] AS
SELECT 
    -- Original entity fields
    base.*,
    
    -- Related field values
    related.FieldName as VirtualFieldName,
    
    -- Calculated fields
    calculations.CalculatedValue,
    
    -- Conditional logic
    CASE 
        WHEN condition THEN value1 
        ELSE value2 
    END as ConditionalField
    
FROM OriginalTable base
LEFT JOIN RelatedTable related ON base.RelatedID = related.ID
LEFT JOIN (
    -- Subquery for calculations
    SELECT 
        ParentID,
        SUM(Amount) as CalculatedValue
    FROM ChildTable
    GROUP BY ParentID
) calculations ON base.ID = calculations.ParentID

Error Handling

• Use ISNULL() or COALESCE() to handle null values in calculations
• Test edge cases where related records might not exist
• Consider default values for calculated fields

Troubleshooting

Common Issues

Virtual fields not appearing in UI

• Verify CodeGen completed successfully
• Check that BaseViewGenerated = 0 in the Entity record
• Confirm the custom view exists and is accessible

Performance problems

• Review execution plans for complex calculations
• Consider adding appropriate indexes
• Evaluate whether calculations should be moved to business logic

Incorrect calculations

• Test SQL queries independently before implementing
• Verify JOIN conditions and GROUP BY clauses
• Check for data type compatibility issues

Validation Steps

1. Query the custom view directly to verify results
2. Check entity metadata in MemberJunction Explorer
3. Test field behavior in various UI contexts
4. Validate calculated values against expected results

Advanced Scenarios

Dynamic Calculations

For calculations that change frequently or require complex business logic, consider implementing them in custom business logic classes rather than SQL views.

Cross-Entity References

Virtual fields can reference data from multiple related entities, but be mindful of performance implications with complex JOIN operations.

Conditional Display

Use CASE statements to create virtual fields that display different values based on business rules or entity states.

Virtual fields provide powerful capabilities for extending your MemberJunction entities without modifying database schema. By following these guidelines and best practices, you can implement robust calculated and related fields that integrate seamlessly with the MemberJunction platform.