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 Apps Super User Class of 2020

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

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,520)