InfoPath Dev

Hi, I am new to the forum and have a question about date and time filtering querying SharePoint lists. I am putting together a room management application where the user submits a form that captures the meeting room details, date/time and locations. The details I am having an issue with is the date and time which can be a day or range. I am trying to figure out how I can query the SharePoint list for dates where meetings have already been approved so that the locations that have availability will be shown for the user to select from. This is so I do not get double bookings. I have several rooms they can choose from so what I have tried so far is to use conditions on the sections for each location that would show the location available if the date ranges do not match for an approved meeting already. I have tested using the "calendar name" which is the name of the field in the calendar which specifies the meeting room location. I have successfully tested this works but when I add date meetingstartdate greater than or equal to startdate and meetingenddate is less than or enddate I see the section disappear once the startdate is selected that meets a date that I have in my list but the range between startdate and enddate is not working to set the range as if I select a day out side of the startdate or enddate the location in the section that should be hidden as a selection appears. This then gives me as a user the opportunity to schedule a meeting one day before the startdate and one day after the enddate for the same meeting location. Any way to fix this?

Any chance I could see a copy of your form? You can attach it under the Options tab in a reply. In general: SharePoint data includes time - you may want to make sure you can see the raw data somewhere on your form while you are working on this so that you can see what you are comparing. Drag the repeating data fields from your secondary data source onto the form. You may need to add calculated values set to show the XML Value for the date time so you can see what the data actually is. When I'm having trouble with conditional formatting, I break all my conditions out (temporarily) and set the section color rather than hide it - this usually helps me see what's going on....

I can't send the copy of the form. Working in a secured environment but I did recreate the form to test it how you asked. I created a start date and end date and added 2 locations on the form view. I then clicked on the section for two different locations and added formatting condition for name of the location first so it will query the name, then start date and finally the end date. When I open the form and do a test of the page and I update the start date and end date with the dates from an item I know that is in my SharePoint list and the color formatting comes up on that location. One of the locations comes up in my repeating table as well when I open the form. There is however second location that should come up also because it is a collaborative location based on the same meeting that does not come up in my repeating table. I am only receiving one item in my repeating table how do it get it to query and show all data from the list based on the dates selected? The next question would be after I get all data to query how to I set a range and keep those dates frozen even though I am adding a day ahead or a day after? There should be no way for me to create a meeting from January 4 - 9 if January 5-8 is booked. Any help on this would be great. I apologize I cannot send any screen shots.

You mention querying the name, then start date, then end date - can you tell me more about this? Query fields for SharePoint lists only are for equivalence, so that could be part of the issue here. You can set the Name query field and run the query, that way all the data returned is just for the specific location. Then you should be able to date filter the returned items on the dates from the form.

I query the name of the location, start date and end date to get the exact dates to make sure if they are available or not available. I get a red mark across each section for the specific location that are not available based on those dates. This is done from the fields start date and end date on top of the screen. This will make the location unavailable based on the dates input by the user. But if I change the date one day ahead or one day later than the end date it will let me schedule a meeting for those dates which then includes the days I want restricted. I will test using the query only on the name and see what returns.

I got the query working and it does bring back all data in the list. I can query dates and it filters by date through the query with no issues. I just cannot get it to validate or find dates between two dates like January 5th through January 10th as not being valid for scheduling so its one range of dates and not just two days start and end date. Anything I can do here to make this work in InfoPath?

You need to query for all the data for the location. Then, in the form, use logic, like rules, to determine whether there is any entry in the returned data that matches or overlaps the date range in the form. If you use a REST query for your list data, you would be able to query for a range, but a built-in InfoPath List data connection will only query on equivalence, not a range. That is, you could query for every thing for a location that stars on Jan 1 and ends Jan 9, but would miss anything inside those dates that didn't match exactly. If you are using SharePoint Online, I think a REST query will fail - that approach will only work if you are using on-prem, I think.

Yes, I believe you are correct. I have been trying to get my customer to see that InfoPath is just not the tool for this and this information definitely helps me get that message across. We need to use Power Apps and I have told them this a few times over the course of the development of this form. Thanks for all the help!