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

22 REPLIES 22

Thank you so much for your efforts. But I am unable to figure it out even after spending a day on this. Not getting the output 🙂

Sorry for the trouble,

I am trying to create a New Collection with Dates and Seat Columns, and it will be of only 3 rows as I mentioned in the table above

DateTotal Seats
05 July 202040
06 July 202050
07 July 202050

 

The main list is 'All requests' list, from where I am pulling the Total seats count based on 3 dates.

Also this new collection I will be using it to create a New chart and also to validate the form submission, which is sorted once I am able to create this new collection on OnVisible property of the form.

I have tried using the formulas given and also tried googling but no luck.

 

Thanks again 🙂

 

That is what i did on a Gallery. You can use the formula on a chart and it will work.

Chart4.JPG

 

 

If you want a collection, Onvisible create a collection:

ClearCollect(MyRequest,AddColumns(GroupBy('All Requests',"Date","AllSeats"),
"TotalSeats", Sum(AllSeats,Seats)))
 
Then use the Collection Name: MyRequest on the Chart ITEMS
------------

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 it shows all the bars.

I am trying to show the bars for next 3 days only, and also count is wrong Date 29/06 is repeating instead of showing total

Screenshot 2020-07-05 at 5.46.13 PM.png

 

I don know the table you are using, if different from what you showed, then kindly show the full table since you are new.

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

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.

 

The full Sharepoint list contains this data,

 

IDCustomer NameDateSeatsStatusLabels
01abc01 July 2020 01:00 PM40CompletedTemp
02sads04 July 2020 01:00 PM20CompletedTemp
03fxf05 July 2020 01:00 PM30RequestedTemp
04abdc05 July 2020 01:00 PM40RequestedTemp
05df06 July 2020 01:00 PM50RequestedTemp
06abdfdfc07 July 2020 01:00 PM60CompletedTemp
07dfdf07 July 2020 01:00 PM30RequestedTemp

 

Now the collection table I am trying to generate should have values with Dates - Today, Tomorrow, and Day After that. So here the dates will be 05, 06 and 07 July and Sum of Seats of each date from the above table. So the collection list will be,

 

DateTotal Seats
05 July 202070
06 July 202050
07 July 202090

 

and then with the collection I will be Populating the Chart and also validate the form on Submit.

 

Hope this clears 🙂

 

Thanks 🙂

Create the Collection:

ClearCollect(MyRequest,Filter(AddColumns(GroupBy('All Requests',"Date","AllSeats"),
"TotalSeats", Sum(AllSeats,Seats)),DateValue(Date)>=Today()))
 
Then use: MyRequest
------------

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.

Chart is not generating, when I use the updated code.

Show a screenshot of the formula and the chart

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

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.

 

ClearCollect(MyRequest,Filter(AddColumns(GroupBy('All Requests',"Date","AllSeats"),"TotalSlides",Sum(AllSlides,'Seats')),DateValue(Date)>=Today()));

 

Getting red-underlined error on DateValue(Date),

Screenshot 2020-07-05 at 10.27.17 PM.png

 

Try;
ClearCollect(MyRequest,Filter(AddColumns(GroupBy('All Requests',"Date","AllSeats"),
"TotalSeats"Sum(AllSeats,Seats)),Date>=Today()))
 
If still errors give screenshot
------------

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 (23,542)