cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Ali_Nawaz
Level: Powered On

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
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
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

Ali_Nawaz
Level: Powered On

Re: Data validation in collection

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

 

Best Regards,
Ali Nawaz

Helpful resources

Announcements
thirdimage

Power Platform 24

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors
Users online (6,040)