Validation of two lists on filling a form

[ramanan89]

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

Date	Some Data	Seating
July 1	Text1	30
July 1	Text2	40
Sum		70
July 2	Text3	20
Sum		20

List 2

Date	Seating Availibility
July 1	100
July 2	20

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

[eka24 · Accepted solution]

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.

[ramanan89]

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.

[eka24]

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

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

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.

[ramanan89]

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. 

[eka24]

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.

[ramanan89]

Sorry, forgot to accept the solution. Thanks

Output I am looking for is,

Date	Total Seats
05 July 2020	40
06 July 2020	50
07 July 2020	50

All Requests table data

Date	Seats
05 July 2020	10
05 July 2020	30
06 July 2020	20
06 July 2020	25
06 July 2020	5
07 July 2020	50

[eka24]

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.

[ramanan89]

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

[eka24]

@ramanan89 

AddColumns(GroupBy('All Requests',"Date","AllSeats"),

"TotalSeats", Sum(AllSeats,Seats))

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

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.