Check Date Availability for a Sharepoint item and then change Edit Lookup to show items available

[Alerts_OM_USA]

I have 2 lists in Sharepoint.

One list with all the Apartments.

A second (main) list with all the guests that are registered (including their arrival and departure date, and what apartment they are staying in.) The guest can choose an apartment to stay in, and I want the dropdown list in the PowerApps app to show only apartments that are not already booked during that time (apartments that don't have arrival or departure dates that overlap with the new applicants arrival and departure dates).
Does anyone have any guidance on how I could do this?

Thanks.

[Alerts_OM_USA]

@Meneghino

I named it Guest Registration Form and not Registrations. It's working fine when I make it patch outside of editing a form. So I think this means I will have to leave the form system and use Patching. I do have a question sort of related to Patching though. How do I make a list of items for a dropdown box. I want the dropdown to have the choices of 1, 2, 3, 4, or 5, and I'm not sure how to add that. Using the form system it would just sync to Sharepoint and see what I had selected for the Column in Sharepoint.

I know I could make a Sharepoint List with an item for each number, but I am guessing there is an easier way?

[Meneghino]

Hi @Alerts_OM_USA

If I understand your requirements correctly, all you need is to set the dropdown box's Items property to:

[1,2,3,4,5]

If you want to show a text but save a value, it is just slightly more complex:

Table({ID: 1, Description: "My first choice"}, {ID: 2, Description: "My second choice"}, etc. etc.)

See the Table function reference for details.

If you want to show the text and save the value, that is also easily doable.  Let me know if you need guidance on this.

[Alerts_OM_USA]

I feel like I tried using the brackets earlier, but it works now for some reason. Anyways thanks.

Also it seems >, <, in, not are not supported/delegated by Sharepoint. Is there a way to work around this?

Also I am getting this blue recommendation  which I'm not sure what it means:

[Meneghino]

Hi @Alerts_OM_USA

Yes, you are correct in regards to delegation.  This may however be changing soon (by end of July?), as I have posted elsewhere.

My current workaround is to store dates as yyyymmdd.

Hope this helps.

[Alerts_OM_USA]

@MeneghinoDo you think it would be a safe bet to just stick with what I have now and not try to work around it and hope it gets updated soon?

[Meneghino]

Hi @Alerts_OM_USA, difficult question.  Bug fix and new feature timing is highly unpredictable...

It really depends on how critical the functionality is, but if you need assistance in implementing the work-around, please let me know.

[Alerts_OM_USA]

@MeneghinoAlright, I guess for now I will leave it be, and I may come back later and fix it.

If I were wanting to replace the form system though, would I use the Collect and Patch features, or is there a way to only use the Patch feature so I don't have to make 2 different systems for editing and creating new forms?

[Meneghino]

Hi @Alerts_OM_USA

Yes there is.  I keep track of whether I am creating a new record or editing an existing one by using context variables.

For example let's say you use IsNew to tell you whether to create a new record or not and the current record is stored in CurrentRecord.

Then the save button OnSelect would just be something like:

Patch(MyDataSource, If(IsNew, Defaults(MyDataSource), CurrentRecord), MyNewOrChangedRecord)

Please be aware that CurrentRecord and MyNewOrChangedRecord are also context variable storing a single record.

You can use (and I do) collections as well, but then you have to wrap them in First() or Last()

[Alerts_OM_USA]

Okay so the code I have is 

Patch('Guest Registration Form', If(IsTrue, Defaults('Guest Registration Form'), First(CurrentRecord)), First(MyNewOrChangedRecord))

But I am getting this error when I mouse over "First(MyNewOrChangedRecord)"

Maybe I'm missing something. 

OnChange of the "Name of Guest" text entry, I have this code:

Collect(MyNewOrChangedRecord,'Name of Guest'.Text)

And when you select the record you want to view in the edit screen I have this happen 

Navigate(FormScreen, ScreenTransition.None,{IsTrue: false}); Collect(CurrentRecord,ThisItem.Name_x0020_of_x0020_Guest_x0028_)

I'm just testing the Name of Guest text for now trying to get Patching working for my form.

Thank you so much for all of your help.

[Meneghino]

Wow, you are doing well.

Just change the collection of the change record to this:

Collect(MyNewOrChangedRecord, {Name_x0020_of_x0020_Guest_x0028_: "The new name goes here"})

Where you get the new name could be a text input or a dropdown, so instead of a hard coded string you could have TextInput1.Text or DropDown1.Selected.Value or someting.

PS This is because the change record column names have to match the data source which it is trying to change or add records to

PPS Avoiding spaces in table/list/column/field names makes your life much easier