Validation of dependent fields

Validation of dependent fields

d-podd-pod Posts: 7Questions: 3Answers: 0

Hello,

I am trying to do the following but have not found a solution yet.

I have 7 fields in my table. For example, if field 1 is filled, field 2 must not be filled. In the frontend, field 2 is hidden when field 1 is set. However, field 2 can be filled before field 1 and then it will be saved anyway. I think I have to validate with PHP. Is there a way to validate the fields depending on each other?

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 54,181Questions: 1Answers: 8,456 Site admin
    Answer ✓

    Hi,

    Yes indeed, a global validator is probably the best option.

    With a custom field validator you do actually have the ability to access the data from the rest of the row (second parameter passed in $data), so you could do it there - it depends a little bit on if you want the error to appear next to a specific field or in the global error area.

    Allan

  • d-podd-pod Posts: 7Questions: 3Answers: 0

    Hi Allan,

    thank you very much for the quick reply. It worked!

    Now I have another question. I have to make sure that a combination of 2 fields can occur only once in the database. Is there a solution for this as well? I know that for single fields it can be solved with ::unique. Is there also something for combinations?

  • allanallan Posts: 54,181Questions: 1Answers: 8,456 Site admin

    There isn't a built in option for that I'm afraid, it just works with a single column.

    You'd need to use a custom validator for this as well, which you could possibly based on our unique validator for a single column.

    The other option, if you are okay with an SQL being shown in the error box (depends what your application is being used for) is to just use an SQL constraint and make the two columns unique together.

    Allan

  • d-podd-pod Posts: 7Questions: 3Answers: 0

    Hello Allan,

    thank you very much for your help. Unfortunately, the problem cannot be solved with SQL, because the combination should only be unique if Field1 has the value 1.

    Example: Field1 = 1; Field2 = 2; Field3 = 3;

    If there is this case: "Field1 = 2; Field2 = 2; Field3 = 3;" this record may occur more than once.

    I have to solve the problem in my PHP file where the validation is located with the following code:

    Solution 1:

    <?php
    
     // DataTables PHP library
    include( "../../../assets/plugins/custom/editor/lib/DataTables.php" );
    
     // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;
    
        $result = $db->sql( "SELECT CASE WHEN count(distinct [Field1])= count([Field1]) AND count(distinct [Field2])= count([Field1]) AND [Field3] = 1
        THEN 0 ELSE 1 END FROM Table1" );
        $response = $result->fetchAll();
    
         if($response === 0) {
            $editor = Editor::inst( $db, 'Table1', 'ID' )
                ->fields(
                    Field::inst( 'Field1' )
                        ->validator(Validate::notEmpty( ValidateOptions::inst()
                            ->message( 'Field1 is required!' ))),
                    Field::inst( 'Field2' ),
                    Field::inst( 'Field3' )
                        ->validator( Validate::numeric( '.', ValidateOptions::inst()
                            ->message( 'Decimal expected' )))
                )
                ->process( $_POST )
                ->json();
        }
    

    Solution 2:

    <?php
    
     // DataTables PHP library
    include( "../../../assets/plugins/custom/editor/lib/DataTables.php" );
    
     // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;
    
        $where = "Field1 = "."'".$_POST['data']['Field1']."'";
        $where .= " AND Field2 = "."'".$_POST['data']['Field2']."'";
        $where .= " AND Field3 = 1";
        $result = $db->sql( "SELECT * FROM Table1 WHERE " . $where);
        $response = $result->fetchAll();
    
        if(count($response) === 0) {
            $editor = Editor::inst( $db, 'Table1', 'ID' )
                ->fields(
                    Field::inst( 'Field1' )
                        ->validator(Validate::notEmpty( ValidateOptions::inst()
                            ->message( 'Field1 is required!' ))),
                    Field::inst( 'Field2' ),
                    Field::inst( 'Field3' )
                        ->validator( Validate::numeric( '.', ValidateOptions::inst()
                            ->message( 'Decimal expected' )))
                )
                ->process( $_POST )
                ->json();
        }
    
    

    Unfortunately, I noticed that an error is thrown if any code is added outside of the $editor variable. Is there any way around this?

    Do you have any other suggestion for this case?

    Translated with www.DeepL.com/Translator (free version)

  • allanallan Posts: 54,181Questions: 1Answers: 8,456 Site admin

    I'm not sure I understand what you mean I'm afraid. What is the error message you are being given?

    For validation of a single field, use a custom field validator. I'm not sure why you are doing a conditional query before the Editor instance?

    Allan

  • d-podd-pod Posts: 7Questions: 3Answers: 0

    When I add my code, i get " Invalid JSON response" out in the browser.

    Validating individual fields is no problem. It's about validating a combination of multiple fields. If the combination of field1, field2 and field3 is already in the database, it must not occur again. So I wanted to check for that before doing the rest of the validation.

    The peculiarity is that a combination of field1 and field2 may occur more than once. I hope now you can understand it better.

  • allanallan Posts: 54,181Questions: 1Answers: 8,456 Site admin

    When I add my code, i get " Invalid JSON response" out in the browser.

    What is the response from the server? Most likely it will contain an error message which will let us resolve the problem. It might be a syntax error for example.

    Allan

Sign In or Register to comment.