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

Validation of two lists on filling a form

Hi all,

I am having two lists,

One contains the list with all details and is grouped based on date and have separate totals columns for each date,

I am trying to validate that total column data with a date with value in another list while filling the form.

 

For Example,

 

List 1

DateSome DataSeating 
July 1Text130
July 1Text240
Sum 70
July 2Text320
Sum 20

 

List 2

DateSeating Availibility
July 1100
July 220

 

In the form validation,

If (List1.Sum of July 1) <= (List. Seating Availability of July 1)

 then (Submit the form)

else

(Invalid entry)

 

Any way to achieve this.

Also, I am using List 2 to generate a chart next to the form to make the user aware of the seating availability.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

1. Create a Collection

ClearCollect(JoinedT,AddColumns(Lis1,"SeatingAvailabilty",LookUp(List2,Date=List2[@Date], 'Seating Availabilty')))

 

2. Insert a Textbox2 that will hold the sum for the Date:

Sum(Filter(List1,Date=DatePicker1.SelectedDate), ‘Seating Availabilty’)

 

3. On the Form Validation:

If(Value(TextboxSeatRequest) <= Value(Textbox2.Text),

    SubmitForm(FormName),
    Notify("Please No Available Seats"))

 

4. On the Chart use the Collection: JoinedT

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

View solution in original post

22 REPLIES 22
Super User III
Super User III

1. Create a Collection

ClearCollect(JoinedT,AddColumns(Lis1,"SeatingAvailabilty",LookUp(List2,Date=List2[@Date], 'Seating Availabilty')))

 

2. Insert a Textbox2 that will hold the sum for the Date:

Sum(Filter(List1,Date=DatePicker1.SelectedDate), ‘Seating Availabilty’)

 

3. On the Form Validation:

If(Value(TextboxSeatRequest) <= Value(Textbox2.Text),

    SubmitForm(FormName),
    Notify("Please No Available Seats"))

 

4. On the Chart use the Collection: JoinedT

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

View solution in original post

Thanks. But I am not sure how the collection works, as I am new to PowerApps.

 

If you could help me with where the code goes for collection. It will be great.

Select the screen where the enteries woul be done and put the collection OnVisible of the Screen:

ONVISIBLE.JPG

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Thank you for the Collections.

 

I have removed the second list and did some work around with Collection. Now I am trying to add the column "Seat Count" from the List "All Requests" with the current date and next 2 days with the below code and generate a dynamic chart. But it gives some error,

 

Collect(JoinedT,{Date:Text(Today()),Seats:Sum(Filter('All Requests',Today(),'Seat Count'))},{Date:Text((Today()+1)),Seats:Sum(Filter('All Requests',Today(),'Seat Count'))},{Date:Text((Today()+2)),Seats:Sum(Filter('All Requests',Today(),'Seat Count'))});

 

I guess its the issue with Sum code which I am using here :(. Any help. 

 

Your original question were 2. Is any of the reply I provided work?

Also what is the purpose of the current Date you are adding. Can you show the output you want in table so is clearer

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Sorry, forgot to accept the solution. Thanks

 

Output I am looking for is,

DateTotal Seats
05 July 202040
06 July 202050
07 July 202050

 

All Requests table data

DateSeats
05 July 202010
05 July 202030
06 July 202020
06 July 202025
06 July 20205
07 July 202050

 

If you are using it on a Gallery, on the ITEMS of the Gallery:

AddColumns(

GroupBy(AllRequestTable,"Seats","AllSeats"),

"TotalSeats", Sum(AllSeats,Seats))

 

If there are issues, give feedback

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

nope... i am using it on a chart and it should filter based on date

 
AddColumns(GroupBy('All Requests',"Date","AllSeats"),
"TotalSeats", Sum(AllSeats,Seats))
 
Gallery9.JPG
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (62,649)