cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Data validation in collection

Hi PowerUsers, 

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

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)

Screenshot_1.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Data validation in collection

@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."

View solution in original post

2 REPLIES 2
Highlighted
Super User
Super User

Re: Data validation in collection

@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."

View solution in original post

Highlighted
Helper III
Helper III

Re: Data validation in collection

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

 

Best Regards,
Ali Nawaz

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,438)