Validate the combination of two columns in the record are unique - c#

david.j.meyer2@boeing.com

Description of problem: I have a table in our Oracle DB that has two columns requirement and diameter. I would like to validate the combination of these two fields is unique when inserting or editing the table. I want them to be able to edit the fields individually just validate the combination is unique when saving. If its not unique, i want to return a custom message.

        using (var db = new Database("oracle", connectionString))
        {
            var response = new Editor(db, "TABLE", "TABLE.THEKEY")
                .Model<PDS_BASE_HPF_DIAMETER>()
                .Field(new Field("REQUIREMENT")
                    .Validator(Validation.NotEmpty())
                ).Field(new Field("DIAMETER")
                    .Validator(Validation.NotEmpty())
                ).Field(new Field("THEKEY"))
                .Process(request)
                .Data();

            return Json(response);
        }

david.j.meyer2@boeing.com

I have modified the code to create a combined field. It displays the combined field correctly in the table but it does not validate the uniqueness. I think I'm close

        using (var db = new Database("oracle", connectionString))
        {
            var response = new Editor(db, "TABLE", "TABLE.THEKEY")
                .Model<PDS_BASE_HPF_DIAMETER>()
                .Field(new Field("REQUIREMENT || DIAMETER")
                .Validator(Validation.Unique(new ValidationOpts { Message = "The combination of REQUIREMENT and DIAMETER can not be empty and must be unque!" }, "REQUIREMENT || DIAMETER", "TABLE", db))
                ).Field(new Field("REQUIREMENT")
                    .Validator(Validation.NotEmpty())
                ).Field(new Field("DIAMETER")
                    .Validator(Validation.NotEmpty())
                ).Field(new Field("THEKEY"))
                .Process(request)
                .Data();

            return Json(response);
        }

I have an oracle constraint preventing it and that message displays fine in the dialog. However, I don't want it to exception out at the database and i want a more user friendly message other than the oracle exception number and message.

allan

Our unique validator doesn't currently handle multiple values. You could either create a custom validator that would do this, or since you already have the DB constraint, you could use postSubmit to check for the error message from the DB, and if present replace it with something more user friendly. Not as nice a solution, but definitely fast!

Allan

david.j.meyer2@boeing.com

I ended up using presubmit to do the check. I set an error for both items with a message saying the combination most be unique. I needed to check this constraint for both create and update. It works fine and exists the search if it finds something that violates uniqueness.

Not sure i like this solution either but it works.

Could you show me an example on how i would create a custom validator for my scenario?

david.j.meyer2@boeing.com

I provided the preSubmit solution code below encase anyone else runs into this problem and wants to validate on the client side.

editorDiameter.on('preSubmit', function (e, o, action) {
    var REQ_DIAM = REQUIREMENT + DIAMETER;
    // ... 
    if (action == 'create' || action == 'edit') {
        var REQUIREMENT = this.field('REQUIREMENT');
        var DIAMETER = this.field('DIAMETER');
        var THEKEY = this.field('THEKEY').val();
        var REQ_DIAM = REQUIREMENT.val() + DIAMETER.val();
        // ... Only validate user input values - different values indicate that
        // ... the end user has not entered a value
        if (!REQUIREMENT.isMultiValue()) {
            if (!REQUIREMENT.val()) {
                REQUIREMENT.error('A REQUIREMENT must be given');
            }
            // ... 
            if (REQUIREMENT.val().length > 100) {
                REQUIREMENT.error('The REQUIREMENT can be a max length of 100 characters');
            }
        }
        // ... 
        if (!DIAMETER.isMultiValue()) {
            if (!DIAMETER.val()) {
                DIAMETER.error('A DIAMETER must be given');
            }

            if (DIAMETER.val().length > 100) {
                DIAMETER.error('The DIAMETER can be a max length of 100 characters');
            }
        }
        // ... 
        // ... additional validation rules
        if (!this.inError()) {
            var rows = diameterTable.rows().data();
            for (let index in rows) {
                //var data = rows[index].data();
                var data = rows[index];
                var val = data.REQUIREMENT + data.DIAMETER;
                var tmpKey = data.THEKEY;
                if (tmpKey != THEKEY && val == REQ_DIAM) {
                    DIAMETER.error('Uniqueness Issue: The Combination of REQUIREMENT and DIAMETER must be unique!');
                    REQUIREMENT.error('Uniqueness Issue: The Combination of REQUIREMENT and DIAMETER must be unique!');
                    break;
                }
            }
        }
        // ... If any error was reported, cancel the submission so it can be corrected
        if (this.inError()) {
            return false;
        }
    } 
});

allan

With a single field you can use a custom validators like this (querying the database to check for duplicates), or if you have two or more fields that interact a global validation function would be the way to do it.

Regards,
Allan

david.j.meyer2@boeing.com

Hi Allan, I have looked at those links a number of times and have not been able to successfully get it to work for my specific use case. I was hoping for an example that targeted my problem. If you could provide one, it would be greatly appreciated.

In the meantime i was having issues with the postSubmit event. When i made the error message modification worked fine. However, when the data was corrected to submit without the unique issue. The dialog would not close and the data was not updating.

I decided to catch the error in C# response object. It works fine but I would really like an example of how to do this with a custom validator for my situation.

Here is the solution i'm using now. Thanks, Dave

        using (var db = new Database("oracle", connectionString))
        {
            var response = new Editor(db, "PDS_BASE_HPF_DIAMETER", "PDS_BASE_HPF_DIAMETER.THEKEY")
                .Model<PDS_BASE_HPF_DIAMETER>()
                .Field(new Field("DIA_FROM")
                    .Validator(Validation.NotEmpty()).Validator(Validation.Numeric())
                ).Field(new Field("DIA_THRU")
                    .Validator(Validation.NotEmpty()).Validator(Validation.Numeric())
                ).Field(new Field("REQUIREMENT")
                    .Validator(Validation.NotEmpty())
                ).Field(new Field("DIAMETER")
                    .Validator(Validation.NotEmpty())
                ).Field(new Field("THEKEY"))
                .Process(request)
                .Data();

            if (!String.IsNullOrEmpty(response.error) && response.error.Contains("ORA-00001: unique constraint"))
            {
                response.error = "Uniqueness violation: REQUIREMENT and DIA_FROM and DIA_THRU must be a unique combination!";
            }

            return Json(response);
        }