Make collection item unavailable in form if already chosen in other collection and month match

[yanni99]

There is 2 collections, one for my form, one for the collection of item that is not going to change.

Collection1

Title

P1

P2

P3

P4

Collection2

Title	Name	Month
P2	Item1	March 2020
P3	Item2	March 2020
P3	Item3	April 2020

So, in this scenario, P2 and P3 would not be available in my form if the month chosen is March 2020. And P3 would not be available if month chosen is April 2020. But they would be available every other months.

Any help on how to achieve this?

[mdevaney]

@yanni99 

I will make an assumption you have a drop-down containing values from Collection1 that is being used to FILTER a gallery loaded with values from Collection2.

Put this code in the Items property for your drop-down

Collection1.Title

Then put this code in the Items property of your gallery.

Filter(Collection2, Month = DropDown_Month.Selected.Value)

Note: I am assuming your month/year is written as a Text value.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[yanni99]

Thank you for replying, yes , months are in plain text. 

I will try to explain better what I want to achieve. 

In this scenario, I should not be able to see P3 in Dropdown1 because P3 is already selected in Collection2 for the month of  March 2020.

In this scenario, I should not be able to see P3 in Dropdown1 because P3 is already selected in Collection2 for the month of April 2020

 

In this scenario, I am able to see P3 in Dropdown1 because P3 is not selected in Collection2 for the month of May 2020

I hope it clears things up. Thank you.

[v-yutliu-msft · Accepted solution]

Hi @yanni99 ,

Do you want to Filter collection1 based on the selection of Month in collection2？

If so, I've made a similar test for your reference:

Step1\add a dropdown control and Set the control’s items property to:

Distinct(collection2,Month)

Step2\add a gallery and Set the gallery control’s items property to:

Filter(

    collection1,

    Not(

        Title in Filter(

            collection2,

            Month = Dropdown1.Selected.Result

        ).Title

    )

    )

Then the gallery will only display the unavailable Title based on the selection of Month.

If you still have any question please don’t hesitate to let us know

Best regards,

[yanni99]

Thank you that is exactly what I needed.