How to check for date in a collection in another collection?

[Anonymous]

I have an excel with dates and i want to check if the dates in the excel matches with the dates i have stored in a collection. If there are dates that matches i need to display "Matches"

[KennethNicasens]

Is this what you are looking for (see attachment)?

If(!IsBlank(LookUp(colExcel,ThisItem.collectionDate = collectionExcelDate)),"Match")

[mdevaney]

deleted... I misunderstood the question.

[Anonymous]

like this but i cant use a gallery. 

[KennethNicasens]

Hello @Anonymous ,

Can you give a bit more context or/and a screenshot of your app and how you want to see this work?

[Anonymous]

@KennethNicasens 

I have this HTML table i've created. First row is day number, I and S doesnt matter, 3rd row represents total hours and last row represents starting and ending hour. 

My focus is on 3rd and 4th row.

If its weekend display and empty space, if its a holiday write Liber and leave empty space on 4th row and for everything else write 8 and 10 to 18. Now if someone has a holiday during that day i want to write O.H in that specific day cell on 3rd row and leave empty space on 4th row.

Here is the code where all the action happens. 

Concat(tabelConcedii.AllItems, "<tr> <th rowspan='2'>" & EmployeeName & "</th>" & Concat(ZileLuna, "<td colspan='2'>" & If(Value in WeekDaysCollection, " ",If(IsBlank(LookUp          (HolidaysCollection, Text(Value, "[$-en-US]mm/dd") = Text (StartDate, "[$-en-US]mm/dd") )), "8" , "Liber" ) ) &  "</td>"  ) &  "</tr> <tr>" & Concat(ZileLuna, "<td>" & If(Value        in WeekDaysCollection, " ", If(IsBlank(LookUp(HolidaysCollection, Text(Value, "[$-en-US]mm/dd") = Text (StartDate, "[$-en-US]mm/dd") )), "10" )) &"</td>" &"<td>" & If(Value in        WeekDaysCollection, " ", If(IsBlank(LookUp(HolidaysCollection, Text(Value, "[$-en-US]mm/dd") = Text (StartDate, "[$-en-US]mm/dd") )), "18" )) & "</td>"  ) & "</tr>"

 tabelConcedii is a gallery with all the people that had a holiday during that month. ZileLuna is a collection with all the days in the current month. WeekDaysCollection is a gallery with all the weekends in that month. HolidaysCollection is a collection with all the holidays.

I have an excel that stores all the dates intervals when someone has an accepted leave and i need to check if the date there matches the date in my ZileLuna(the one with all the date). If it does display OH else display 8. But i dont know how to do that check

[Anonymous]

@KennethNicasens 

I have an HTML table that i need to populate. (Check attachments)

First row represents day number, 2nd row is irrelevant, 3rd row tells total worked hours and 4th row tells hour started and hour ended.

My focus is on 3rd and 4th row. If its weekend leave empty space on 3rd and 4th row. If its holiday that day write Liber on 3rd row and leave empty space on 4th row. So far so good.

Now if someone has an accepted leave request then write O.H on 3rd row and leave empty space on 4th row.

Here is the code where all the action happens 

Concat(tabelConcedii.AllItems, "<tr> <th rowspan='2'>" & EmployeeName & "</th>" & Concat(ZileLuna, "<td colspan='2'>" & If(Value in WeekDaysCollection, " ",If(IsBlank(LookUp          (HolidaysCollection, Text(Value, "[$-en-US]mm/dd") = Text (StartDate, "[$-en-US]mm/dd") )), "8" , "Liber" ) ) &  "</td>"  ) &  "</tr> <tr>" & Concat(ZileLuna, "<td>" & If(Value        in WeekDaysCollection, " ", If(IsBlank(LookUp(HolidaysCollection, Text(Value, "[$-en-US]mm/dd") = Text (StartDate, "[$-en-US]mm/dd") )), "10" )) &"</td>" &"<td>" & If(Value in        WeekDaysCollection, " ", If(IsBlank(LookUp(HolidaysCollection, Text(Value, "[$-en-US]mm/dd") = Text (StartDate, "[$-en-US]mm/dd") )), "18" )) & "</td>"  ) & "</tr>"
         

tabelConcedii is a gallery with all the people that had a holiday during that month(thats why there are 2 rows full of 8's and 10/18's).

ZileLuna is a collection with all the days in the current month

WeekDaysCollection is a collection with all the weekend days in the current month.

HolidaysCollection is a collection with all the holidays.

Now i have the excel with the accepted leave request date intervals but i dont know how to check if the days in the excel match with the days in current month(zileLuna)

[GarethPrisk]

You can check if a value exists in another collection/table. You will need to refactor what I have below to meet your collection names, and date formats, but this approach should work. In my example, I am not using Excel, but instead generating two separate Collections, and comparing them.

Below will generate the two collections.

ClearCollect(colDateTableX,{xID:1,xDate:Date(2020,01,06)},{xID:2,xDate:Date(2020,01,07)},{xID:3,xDate:Date(2020,01,08)});ClearCollect(colDateTableY,{yID:1,yDate:Date(2020,01,07)},{yID:2,yDate:Date(2020,01,08)},{yID:3,yDate:Date(2020,01,09)})

I then created two galleries. One with colDateTableX as its source, and another with colDateTableY as its source.

Then, I added a Toggle control to show the outcome of a check in each gallery.

• For the Toggle in GalleryX, set the Default to ThisItem.xDate in colDateTableY.yDate
• For the Toggle in GalleryY, set the Default to ThisItem.yDate in colDateTableX.xDate

[KennethNicasens]

You could also join the collection with the excel (sql like inner-join).

ClearCollect(colNewCollection,AddColumns(colCollection,"LEAVE",If(!IsBlank(LookUp(colExcel,collectionDate = collectionExcelDate)),"O.H")))

Now you have a collection with your data in and a new column "LEAVE". This will be blank if the value is not found in the excel, otherwice you can fill this in with "O.H"

[Anonymous]

I must get the variables wrong because i get "Invalid argument type"