Defaulting user-defined fields for records created before the customization

Question

I have a DAC extension that has a default attribute set for it(see definition below). I have noticed that for any records that existed prior to the DAC extensions existence will still hold a null value. Is there a conventional way that we can get all the records initialized?

If not I will likely perform a check within an event handler before its used. Thanks in advance!

    public abstract class usrCustOptInOut : IBqlField
    {
    }
    protected string _UsrCustOptInOut;
    [PXDBString(1, IsUnicode = true)]
    [PXDefault(OptInOut.Default, PersistingCheck = PXPersistingCheck.Nothing)]
    [PXUIField(DisplayName = "Click To Pay Opt In/Out")]
    [PXStringList(
        new[]
        {
            OptInOut.OptOut,
            OptInOut.OptIn,
            OptInOut.Default
        },
        new[]
        {
            "Customer Opt Out",
            "Customer Opt In",
            "Default"
        })]
    public virtual string UsrCustOptInOut
    {
        get
        {
            return _UsrCustOptInOut;
        }
        set
        {
            _UsrCustOptInOut = value;
        }
    }

Answer 1

You will need to run an update manually. Existing records will not be updated automatically when adding new fields and records exist.

I see two options:

1. Add a a customization plugin to your project (preferred)
2. Add a custom SQL script to your project

Option 1

You can use a customization plugin to make sure all null values have a default. The customization plugin will run at the end of each publish within the site.

You can create a code file in your customization project to this:

The UpdateDatabase() method in a CustomizationPlugin runs after the customization was published and the website was restarted (the Usr field will be applied).

Then you can use PXDatabase.Update to run the update without needing a graph or any validation/events to run. You just need a bulk SQL update and PXDatabase.Update will do the job. The example below will only set a default if there is a null value. The same concept can be applied to any table/field added not just user fields. For example if you have a custom table and you need to add a new field and have existing records contain a default.

A down side (when compared to Option 2) is PXDatabase.Update will run for the current tenant (company) as it appends the current CompanyID to the SQL statement (which is a good thing). If you have multiple tenants (companies) you will need to run the publish for multiple tenants. Alternativly you can write your code so that it will loop each company and run your statement using PXLoginScope (see references).

The end result using a Customization Plugin would look something like this:

public class UsrFieldDefaults : CustomizationPlugin
{
    //This method executed after customization was published and website was restarted.  
    public override void UpdateDatabase()
    {
        PXDatabase.Update<MyDac>(
            new PXDataFieldAssign<MyDacExt.usrCustOptInOut>(PXDbType.NChar, 1, OptInOut.Default),
            new PXDataFieldRestrict<MyDacExt.usrCustOptInOut>(PXDbType.NChar, 1, null, PXComp.ISNULL)
            );
    }
}

Option 2 You can write a custom SQL script to do the same and put it in your customization project. Keep in mind which DBMS you are publishing to (MSSQL/MYSQL). There is also a way to use a shared SQL syntax so it should work for any DBMS. Also be aware that you would most likely run the script for all tenants (companies) which would include snapshots.

Additional References:

Custom Processes During Publication of a Customization

To Add a Customization Plug-In to a Project

Save data to different company

To Publish a Customization for a Multitenant Site

Creating a Custom SQL Script

Writing Custom SQL Scripts for Interpretation