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

[ramanan89]

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

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

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 🙂

[eka24]

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

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.

[ramanan89]

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

[eka24]

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.

[ramanan89]

The full Sharepoint list contains this data,

ID	Customer Name	Date	Seats	Status	Labels
01	abc	01 July 2020 01:00 PM	40	Completed	Temp
02	sads	04 July 2020 01:00 PM	20	Completed	Temp
03	fxf	05 July 2020 01:00 PM	30	Requested	Temp
04	abdc	05 July 2020 01:00 PM	40	Requested	Temp
05	df	06 July 2020 01:00 PM	50	Requested	Temp
06	abdfdfc	07 July 2020 01:00 PM	60	Completed	Temp
07	dfdf	07 July 2020 01:00 PM	30	Requested	Temp

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,

Date	Total Seats
05 July 2020	70
06 July 2020	50
07 July 2020	90

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

Hope this clears 🙂

Thanks 🙂

[eka24]

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.

[ramanan89]

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

[eka24]

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.

[ramanan89]

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

Getting red-underlined error on DateValue(Date),

[eka24]

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.