Skip to main content

SYSPRO Customization

Overview​

This document details the customization aspects of the AR Payment Reversal dashboard's SYSPRO integration, including custom forms integration, custom field handling, validation implementation, custom table management, SQL script deployment patterns, and configuration for different SYSPRO environments. These customizations extend SYSPRO's standard functionality while maintaining compatibility and upgradability.

Key Concepts​

  • Custom Tables (CG_ prefix): Dashboard-specific data storage
  • Custom Forms Integration: XML-based form definitions
  • Custom Field Management: Extending standard SYSPRO entities
  • Environment Configuration: Multi-environment support
  • SQL Deployment: Database object creation and maintenance
  • Testing Strategies: Validation of customizations

Implementation Details​

Custom Table Management​

CG_ Table Naming Convention​

public static class CustomTableConventions
{
    public const string Prefix = "CG_";
    public const string CompanyPrefix = "C"; // Optional company-specific prefix
    
    public static string GetTableName(string baseName, string companyId = null)
    {
        if (!string.IsNullOrEmpty(companyId))
        {
            return $"{Prefix}{companyId}_{baseName}";
        }
        return $"{Prefix}{baseName}";
    }
    
    public static bool IsCustomTable(string tableName)
    {
        return tableName.StartsWith(Prefix, StringComparison.OrdinalIgnoreCase);
    }
    
    public static class StandardTables
    {
        public const string PaymentQueue = "CG_ArReversePaymentQueueHeader";
        public const string PostHistory = "CG_ArReversePaymentPostCompletionHistory";
        public const string AuditLog = "CG_ArReversePaymentAuditLog";
        public const string Configuration = "CG_ArReversePaymentConfig";
    }
}

Custom Table Creation and Maintenance​

-- MepApps.Dash.Ar.Maint.PaymentReversal/Resources/SQL/CREATE TABLE CG_ArReversePaymentQueueHeader.sql
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CG_ArReversePaymentQueueHeader]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[CG_ArReversePaymentQueueHeader](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Customer] [varchar](20) NOT NULL,
        [CheckNumber] [varchar](20) NOT NULL,
        [CheckValue] [decimal](18, 2) NULL,
        [PaymentDate] [datetime] NULL,
        [Bank] [varchar](10) NULL,
        [TrnYear] [decimal](4, 0) NULL,
        [TrnMonth] [decimal](2, 0) NULL,
        [Journal] [decimal](10, 0) NULL,
        
        -- Audit fields
        [CreatedDate] [datetime] NOT NULL DEFAULT GETDATE(),
        [CreatedBy] [varchar](20) NULL DEFAULT SYSTEM_USER,
        [ModifiedDate] [datetime] NULL,
        [ModifiedBy] [varchar](20) NULL,
        
        -- Status tracking
        [QueueStatus] [varchar](50) NULL DEFAULT 'Pending',
        [ProcessAttempts] [int] NOT NULL DEFAULT 0,
        [LastError] [varchar](500) NULL,
        
        -- Custom fields
        [CustomField1] [varchar](50) NULL,
        [CustomField2] [varchar](50) NULL,
        [CustomField3] [decimal](18, 2) NULL,
        [CustomDate1] [datetime] NULL,
        [CustomFlag1] [char](1) NULL,
        
     CONSTRAINT [PK_CG_ArReversePaymentQueueHeader] PRIMARY KEY CLUSTERED ([Id] ASC),
     CONSTRAINT [UQ_CG_ArReversePaymentQueue_Payment] UNIQUE NONCLUSTERED 
        ([Customer], [CheckNumber], [TrnYear], [TrnMonth], [Journal])
    )
    
    -- Performance indexes
    CREATE NONCLUSTERED INDEX [IX_CG_ArReversePaymentQueue_Customer] 
        ON [dbo].[CG_ArReversePaymentQueueHeader] ([Customer]) 
        INCLUDE ([CheckNumber], [CheckValue], [QueueStatus])
    
    CREATE NONCLUSTERED INDEX [IX_CG_ArReversePaymentQueue_Status] 
        ON [dbo].[CG_ArReversePaymentQueueHeader] ([QueueStatus], [CreatedDate])
    
    CREATE NONCLUSTERED INDEX [IX_CG_ArReversePaymentQueue_Date] 
        ON [dbo].[CG_ArReversePaymentQueueHeader] ([PaymentDate] DESC)
    
    -- Add extended properties for documentation
    EXEC sys.sp_addextendedproperty 
        @name=N'MS_Description', 
        @value=N'Queue table for AR payment reversals pending processing', 
        @level0type=N'SCHEMA',@level0name=N'dbo', 
        @level1type=N'TABLE',@level1name=N'CG_ArReversePaymentQueueHeader'
END

Custom Forms Integration​

CustomForms.xml Configuration​

<!-- MepApps.Dash.Ar.Maint.PaymentReversal/Resources/CustomForms.xml -->
<CustomForms>
    <Form name="PaymentReversalQueue" version="1.0">
        <Description>AR Payment Reversal Queue Entry Form</Description>
        
        <!-- Form Fields -->
        <Fields>
            <Field name="Customer" type="lookup" required="true">
                <Label>Customer Code</Label>
                <MaxLength>15</MaxLength>
                <LookupTable>ArCustomer</LookupTable>
                <LookupKey>Customer</LookupKey>
                <LookupDisplay>Name</LookupDisplay>
                <Validation>
                    <Rule type="exists" table="ArCustomer" field="Customer"/>
                    <Rule type="custom" method="ValidateCustomerStatus"/>
                </Validation>
            </Field>
            
            <Field name="CheckNumber" type="text" required="true">
                <Label>Check/Reference Number</Label>
                <MaxLength>20</MaxLength>
                <Validation>
                    <Rule type="pattern" pattern="^[A-Z0-9\-]+$"/>
                    <Rule type="custom" method="ValidateDuplicateCheck"/>
                </Validation>
            </Field>
            
            <Field name="CheckValue" type="decimal" required="true">
                <Label>Payment Amount</Label>
                <Precision>18</Precision>
                <Scale>2</Scale>
                <MinValue>0.01</MinValue>
                <MaxValue>9999999.99</MaxValue>
                <Format>currency</Format>
            </Field>
            
            <Field name="PaymentDate" type="date" required="true">
                <Label>Payment Date</Label>
                <DefaultValue>today</DefaultValue>
                <MinDate>-365</MinDate>
                <MaxDate>0</MaxDate>
            </Field>
            
            <Field name="Bank" type="dropdown" required="true">
                <Label>Bank Account</Label>
                <DataSource>
                    <Table>ApBank</Table>
                    <ValueField>Bank</ValueField>
                    <DisplayField>Description</DisplayField>
                    <Filter>BankOnHold = 'N'</Filter>
                </DataSource>
            </Field>
            
            <Field name="ReversalReason" type="dropdown" required="false">
                <Label>Reversal Reason</Label>
                <Options>
                    <Option value="NSF">Non-Sufficient Funds</Option>
                    <Option value="ERROR">Entry Error</Option>
                    <Option value="DISPUTE">Customer Dispute</Option>
                    <Option value="DUPLICATE">Duplicate Payment</Option>
                    <Option value="OTHER">Other</Option>
                </Options>
            </Field>
            
            <Field name="Notes" type="memo" required="false">
                <Label>Additional Notes</Label>
                <MaxLength>500</MaxLength>
                <Rows>4</Rows>
            </Field>
        </Fields>
        
        <!-- Form Actions -->
        <Actions>
            <Action name="Save" type="submit">
                <Validation>all</Validation>
                <Method>SaveToQueue</Method>
            </Action>
            
            <Action name="SaveAndClose" type="submit">
                <Validation>all</Validation>
                <Method>SaveToQueue</Method>
                <CloseForm>true</CloseForm>
            </Action>
            
            <Action name="Cancel" type="cancel">
                <ConfirmMessage>Discard changes?</ConfirmMessage>
            </Action>
        </Actions>
        
        <!-- Form Events -->
        <Events>
            <Event name="OnLoad" method="InitializeForm"/>
            <Event name="OnCustomerChange" method="LoadCustomerDefaults"/>
            <Event name="OnBankChange" method="ValidateBankAccount"/>
        </Events>
    </Form>
</CustomForms>

Custom Form Service Implementation​

// MepApps.Dash.Ar.Maint.PaymentReversal/Services/CustomFormService.cs
public class CustomFormService : ICustomFormService
{
    private readonly ILogger<CustomFormService> _logger;
    private XDocument _formsDocument;
    
    public async Task<CustomForm> LoadFormDefinition(string formName)
    {
        var formElement = _formsDocument.Root
            .Elements("Form")
            .FirstOrDefault(f => f.Attribute("name")?.Value == formName);
        
        if (formElement == null)
        {
            throw new FormNotFoundException($"Form '{formName}' not found");
        }
        
        return new CustomForm
        {
            Name = formName,
            Version = formElement.Attribute("version")?.Value,
            Description = formElement.Element("Description")?.Value,
            Fields = ParseFields(formElement.Element("Fields")),
            Actions = ParseActions(formElement.Element("Actions")),
            Events = ParseEvents(formElement.Element("Events"))
        };
    }
    
    private List<CustomFormField> ParseFields(XElement fieldsElement)
    {
        return fieldsElement?.Elements("Field")
            .Select(field => new CustomFormField
            {
                Name = field.Attribute("name")?.Value,
                Type = field.Attribute("type")?.Value,
                Required = bool.Parse(field.Attribute("required")?.Value ?? "false"),
                Label = field.Element("Label")?.Value,
                MaxLength = ParseInt(field.Element("MaxLength")?.Value),
                ValidationRules = ParseValidationRules(field.Element("Validation")),
                DataSource = ParseDataSource(field.Element("DataSource")),
                Options = ParseOptions(field.Element("Options"))
            })
            .ToList() ?? new List<CustomFormField>();
    }
    
    public async Task<ValidationResult> ValidateFormData(
        CustomForm form,
        Dictionary<string, object> formData)
    {
        var errors = new List<ValidationError>();
        
        foreach (var field in form.Fields)
        {
            var fieldErrors = await ValidateField(field, formData);
            errors.AddRange(fieldErrors);
        }
        
        // Run custom validation methods
        foreach (var customValidation in form.CustomValidations)
        {
            var customErrors = await RunCustomValidation(customValidation, formData);
            errors.AddRange(customErrors);
        }
        
        return new ValidationResult
        {
            IsValid = !errors.Any(),
            Errors = errors
        };
    }
}

Custom Field Handling​

Extending SYSPRO Tables​

public class CustomFieldManager
{
    private readonly Dictionary<string, CustomFieldDefinition> _customFields;
    
    public CustomFieldManager()
    {
        _customFields = LoadCustomFieldDefinitions();
    }
    
    private Dictionary<string, CustomFieldDefinition> LoadCustomFieldDefinitions()
    {
        return new Dictionary<string, CustomFieldDefinition>
        {
            ["ArCustomer.CustomRating"] = new CustomFieldDefinition
            {
                TableName = "ArCustomer",
                FieldName = "CustomRating",
                DataType = "varchar(10)",
                Description = "Custom credit rating",
                ValidationRule = "IN ('AAA', 'AA', 'A', 'BBB', 'BB', 'B', 'CCC')"
            },
            ["ArInvoice.ReversalFlag"] = new CustomFieldDefinition
            {
                TableName = "ArInvoice",
                FieldName = "ReversalFlag",
                DataType = "char(1)",
                Description = "Payment reversal indicator",
                DefaultValue = "N",
                ValidationRule = "IN ('Y', 'N')"
            },
            ["ArInvoice.ReversalDate"] = new CustomFieldDefinition
            {
                TableName = "ArInvoice",
                FieldName = "ReversalDate",
                DataType = "datetime",
                Description = "Date of last payment reversal",
                AllowNull = true
            }
        };
    }
    
    public async Task<bool> CreateCustomField(CustomFieldDefinition definition)
    {
        try
        {
            var sql = GenerateAlterTableSql(definition);
            
            using (var context = _serviceProvider.GetService<PluginSysproDataContext>())
            {
                await context.Database.ExecuteSqlCommandAsync(sql);
            }
            
            // Add extended property for documentation
            await AddFieldDocumentation(definition);
            
            _logger.LogInformation("Created custom field {Table}.{Field}",
                definition.TableName, definition.FieldName);
            
            return true;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Failed to create custom field {Table}.{Field}",
                definition.TableName, definition.FieldName);
            return false;
        }
    }
    
    private string GenerateAlterTableSql(CustomFieldDefinition definition)
    {
        var sql = new StringBuilder();
        
        // Check if column exists
        sql.AppendLine($@"
            IF NOT EXISTS (
                SELECT * FROM sys.columns 
                WHERE object_id = OBJECT_ID('{definition.TableName}')
                AND name = '{definition.FieldName}'
            )
            BEGIN");
        
        // Add column
        sql.AppendLine($@"
                ALTER TABLE {definition.TableName}
                ADD {definition.FieldName} {definition.DataType}");
        
        // Add default if specified
        if (!string.IsNullOrEmpty(definition.DefaultValue))
        {
            sql.AppendLine($@"
                    DEFAULT {definition.DefaultValue}");
        }
        
        // Add constraint if specified
        if (!string.IsNullOrEmpty(definition.ValidationRule))
        {
            sql.AppendLine($@"
                    CONSTRAINT CHK_{definition.TableName}_{definition.FieldName}
                    CHECK ({definition.FieldName} {definition.ValidationRule})");
        }
        
        sql.AppendLine("            END");
        
        return sql.ToString();
    }
}

SQL Script Deployment​

Deployment Framework​

// MepApps.Dash.Ar.Maint.PaymentReversal/Services/DatabaseDeploymentService.cs
public class DatabaseDeploymentService
{
    private readonly ILogger<DatabaseDeploymentService> _logger;
    private readonly string _scriptsPath;
    
    public async Task<DeploymentResult> DeployDatabaseChanges()
    {
        var result = new DeploymentResult();
        
        try
        {
            // Get all SQL scripts
            var scripts = GetDeploymentScripts();
            
            // Sort by version/sequence
            scripts = scripts.OrderBy(s => s.Sequence).ToList();
            
            foreach (var script in scripts)
            {
                var scriptResult = await ExecuteScript(script);
                result.ScriptResults.Add(scriptResult);
                
                if (!scriptResult.Success && script.Required)
                {
                    result.Success = false;
                    break;
                }
            }
            
            // Record deployment history
            await RecordDeploymentHistory(result);
            
            return result;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Database deployment failed");
            result.Success = false;
            result.Error = ex.Message;
            return result;
        }
    }
    
    private List<DeploymentScript> GetDeploymentScripts()
    {
        var scriptsDirectory = Path.Combine(_scriptsPath, "SQL");
        var scriptFiles = Directory.GetFiles(scriptsDirectory, "*.sql");
        
        return scriptFiles.Select(file => new DeploymentScript
        {
            FileName = Path.GetFileName(file),
            FilePath = file,
            Sequence = ExtractSequence(file),
            Required = IsRequiredScript(file),
            Content = File.ReadAllText(file)
        }).ToList();
    }
    
    private async Task<ScriptResult> ExecuteScript(DeploymentScript script)
    {
        var result = new ScriptResult
        {
            ScriptName = script.FileName,
            StartTime = DateTime.Now
        };
        
        try
        {
            // Parse script into batches (split by GO)
            var batches = ParseScriptBatches(script.Content);
            
            using (var context = _serviceProvider.GetService<PluginSysproDataContext>())
            {
                foreach (var batch in batches)
                {
                    if (!string.IsNullOrWhiteSpace(batch))
                    {
                        await context.Database.ExecuteSqlCommandAsync(batch);
                    }
                }
            }
            
            result.Success = true;
            result.EndTime = DateTime.Now;
            
            _logger.LogInformation("Successfully executed script {Script}",
                script.FileName);
        }
        catch (Exception ex)
        {
            result.Success = false;
            result.Error = ex.Message;
            result.EndTime = DateTime.Now;
            
            _logger.LogError(ex, "Failed to execute script {Script}",
                script.FileName);
        }
        
        return result;
    }
    
    private List<string> ParseScriptBatches(string scriptContent)
    {
        // Split by GO statements
        var goRegex = new Regex(@"^\s*GO\s*$", 
            RegexOptions.Multiline | RegexOptions.IgnoreCase);
        
        return goRegex.Split(scriptContent)
            .Where(batch => !string.IsNullOrWhiteSpace(batch))
            .ToList();
    }
}

Environment Configuration​

Multi-Environment Support​

public class EnvironmentConfigurationService
{
    private readonly string _environment;
    private readonly Dictionary<string, EnvironmentConfig> _configurations;
    
    public EnvironmentConfigurationService()
    {
        _environment = DetectEnvironment();
        _configurations = LoadEnvironmentConfigurations();
    }
    
    private string DetectEnvironment()
    {
        // Check SYSPRO database name patterns
        var dbName = GetSysproDbName();
        
        if (dbName.Contains("TEST") || dbName.Contains("UAT"))
            return "Test";
        if (dbName.Contains("DEV"))
            return "Development";
        if (dbName.Contains("TRAIN"))
            return "Training";
        
        return "Production";
    }
    
    private Dictionary<string, EnvironmentConfig> LoadEnvironmentConfigurations()
    {
        return new Dictionary<string, EnvironmentConfig>
        {
            ["Development"] = new EnvironmentConfig
            {
                EnableDebugLogging = true,
                EnableTestFeatures = true,
                ValidationLevel = ValidationLevel.Minimal,
                MaxBatchSize = 10,
                RetryAttempts = 1,
                CustomTablePrefix = "CG_DEV_",
                AllowDataPurge = true
            },
            ["Test"] = new EnvironmentConfig
            {
                EnableDebugLogging = true,
                EnableTestFeatures = true,
                ValidationLevel = ValidationLevel.Standard,
                MaxBatchSize = 50,
                RetryAttempts = 2,
                CustomTablePrefix = "CG_TEST_",
                AllowDataPurge = true
            },
            ["Training"] = new EnvironmentConfig
            {
                EnableDebugLogging = false,
                EnableTestFeatures = false,
                ValidationLevel = ValidationLevel.Standard,
                MaxBatchSize = 100,
                RetryAttempts = 3,
                CustomTablePrefix = "CG_TRAIN_",
                AllowDataPurge = false
            },
            ["Production"] = new EnvironmentConfig
            {
                EnableDebugLogging = false,
                EnableTestFeatures = false,
                ValidationLevel = ValidationLevel.Strict,
                MaxBatchSize = 200,
                RetryAttempts = 3,
                CustomTablePrefix = "CG_",
                AllowDataPurge = false
            }
        };
    }
    
    public EnvironmentConfig GetCurrentConfiguration()
    {
        return _configurations[_environment];
    }
    
    public T GetSetting<T>(string settingName)
    {
        var config = GetCurrentConfiguration();
        var property = typeof(EnvironmentConfig).GetProperty(settingName);
        
        if (property != null)
        {
            return (T)property.GetValue(config);
        }
        
        throw new SettingNotFoundException($"Setting '{settingName}' not found");
    }
}

public class EnvironmentConfig
{
    public bool EnableDebugLogging { get; set; }
    public bool EnableTestFeatures { get; set; }
    public ValidationLevel ValidationLevel { get; set; }
    public int MaxBatchSize { get; set; }
    public int RetryAttempts { get; set; }
    public string CustomTablePrefix { get; set; }
    public bool AllowDataPurge { get; set; }
}

Testing Strategies​

Custom Table Testing​

[TestClass]
public class CustomTableTests
{
    private readonly ITestDatabase _testDb;
    
    [TestMethod]
    public async Task CustomTable_Should_Be_Created_With_Correct_Schema()
    {
        // Arrange
        var tableName = "CG_TestTable";
        var createScript = GetCreateTableScript(tableName);
        
        // Act
        await _testDb.ExecuteScript(createScript);
        
        // Assert
        var tableExists = await _testDb.TableExists(tableName);
        Assert.IsTrue(tableExists);
        
        var columns = await _testDb.GetTableColumns(tableName);
        Assert.IsTrue(columns.Any(c => c.Name == "Id"));
        Assert.IsTrue(columns.Any(c => c.Name == "CreatedDate"));
    }
    
    [TestMethod]
    public async Task CustomField_Validation_Should_Enforce_Constraints()
    {
        // Arrange
        var invalidData = new CG_ArReversePaymentQueueHeader
        {
            Customer = "INVALID_CUSTOMER_CODE_TOO_LONG_FOR_FIELD",
            CheckNumber = "TEST001"
        };
        
        // Act & Assert
        await Assert.ThrowsExceptionAsync<DbEntityValidationException>(
            async () => await _context.SaveChangesAsync()
        );
    }
}

Custom Form Testing​

[TestClass]
public class CustomFormTests
{
    [TestMethod]
    public async Task CustomForm_Should_Validate_Required_Fields()
    {
        // Arrange
        var formService = new CustomFormService(_logger);
        var form = await formService.LoadFormDefinition("PaymentReversalQueue");
        
        var formData = new Dictionary<string, object>
        {
            // Missing required Customer field
            ["CheckNumber"] = "TEST001",
            ["CheckValue"] = 1000.00m
        };
        
        // Act
        var result = await formService.ValidateFormData(form, formData);
        
        // Assert
        Assert.IsFalse(result.IsValid);
        Assert.IsTrue(result.Errors.Any(e => e.FieldName == "Customer"));
    }
}

Best Practices​

  1. Always use CG_ prefix for custom tables
  2. Document all customizations with extended properties
  3. Version control SQL scripts with proper sequencing
  4. Test in all environments before production
  5. Maintain backward compatibility with SYSPRO upgrades
  6. Use environment-specific configurations
  7. Implement rollback scripts for all changes

Common Pitfalls​

  1. Not checking for existing objects before creation
  2. Hardcoding environment-specific values
  3. Missing indexes on custom tables
  4. Ignoring SYSPRO naming conventions
  5. Not testing with different SYSPRO versions

Summary​

SYSPRO customizations in the AR Payment Reversal dashboard extend standard functionality while maintaining system integrity and upgradability. Through careful use of custom tables, forms, and fields, along with proper deployment and testing strategies, the dashboard provides enhanced capabilities that seamlessly integrate with SYSPRO's core systems.