Filter on gallery with a collection and Table

[Chrislgr59]

Hey,

I try to make appear on a Gallery a collection (With the list of the months) as well as the year (Not in a collection) according to if in another table it does not exist:

either no record,

or the status of the record related to the month and year in my Gallery is not "ok". (In case someone start and don't finish inventory of actual month)

Knowing that :

-In my collection I only have the Months with their IDs

-The Years are not in my collection but directly generated on my gallery

-In my Sharepoint Table I have on 3 different columns Month, Year, Status

I tested many different solutions found on the internet and in the end I ended up with the following code (which of course does not work and not complete).

Filter(

Controle;

Filter(Inventaire;

LookUp(Inventaire;Controle.Mois=Mois And Value(Annee)=Annee;Statut) != "ok")

)

I thank you in advance,

Wishing you a good day,

Sincerely,

[BCBuizer]

Hi @Chrislgr59 ,

The below should do the trick as 

Filter(
	Controle, 
	Not(
		Mois in Filter(
			Inventaire;
			Controle.Mois=Mois And
			Value(Annee)=Annee And
			Statut = "ok"
		).Mois
	)
)

In the nested filter clause it generates a table of valid inventory items. Then we use a filter with some operators to Filter() which items in the Controle list are Not() in the .Mois column of those filtered valid items.

Hope this helps.

[Chrislgr59]

Thanks for your fast reply,

After trying it, it say Incompatible Type for comparison: Table, Text (In Controle.Mois= Mois) and

at the end they say that .Mois is not recognized

[BCBuizer · Accepted solution]

Hi @Chrislgr59 ,

I copied the conditions without thorough checking, but actually you don't need any for Mois:

Filter(
	Controle, 
	Not(
		Mois in Filter(
			Inventaire;
			Value(Annee)=Annee And
			Statut = "ok"
		).Mois
	)
)