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
Solved! Go to 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.
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.
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:
------------
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,
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 |
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