Data validation in collection

[Ali_Nawaz]

Hi PowerUsers, 

I have this record set in SQL data base (See Image)

I am collecting this data on AppStart in a collection. 

Now the purpose is to set a validation : When I enter a new record set in the powerApp a logic should check the new record set should not have any over lapping value in FromHours or ToHours  (See Image)

Like, If I am entering 10 in FromHours and 14 in ToHours the validation should not let me enter this record. 
Because it is overlapping with the Set 3 Entry FromHours 9.01 - ToHours 12

It should only let me enter 12.01+ Onwards record now

Or if I delete any of these existing record, still it should always check the new record I enter it should not overlap with the existing record.

The idea is to avoid any over lapping record set. 

I am assuming it would require me to loop though these records but I am unable to transform this logic into code. 


Looking forward to swift and helpful response. 


Best Regards, 
Ali Nawaz

[mdevaney · Accepted solution]

@Ali_Nawaz 

You should be able to do this without using any looping logic.  My approach would be to create a collection with a FILTER on the SQL Table to find any items which violate the validation conditions.  Then we COUNTROWS on the new collection: 0 rows means validation passed whereas 1 or more rows means validation failed.  I am storing the result in a SET variable called validation check.  True means pass and False means fail.

You'd want to put this code in the OnSelect property of a button to submit the new record.  I assume there are inputs called TimeStart and TimeEnd to take time entries from the user

Set(
    validationCheck,
    If(
        CountRows(
            Filter(
                your_datasource_name,
                Or(
                    And(Value(TimeStart.Text) >= FromHours && Value(TimeStart.Text) <= ToHours)
                    And(Value(TimeEnd.Text) >= FromHours && Value(TimeEnd.Text) <= ToHours)
                )
            )
        )=0,
    true,
    false
)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Ali_Nawaz]

@mdevaney Thank you Brother for your assistance. 
It worked like a charm 👍

Best Regards,
Ali Nawaz