Loop through lines in excel with values from a collection

[LucB]

I have an excel file with a column containing dates. The user can select a from date and a to date with dropdown boxes in the app, which create a collection containing all the dates from until to. Subsequently I want to check if from this list of dates there are already some in the excel file (to check for double entries).

I have tried filtering the excel list and using this filtered set as basis for a ForAll loop which then check the values in the collection. 

I do not seem to figure out the correct way of nesting the Filter in the ForAll loop. 

Are there any examples codes I could draw from?

[v-yutliu-msft]

Hi @LucB ,

Do you want to check for double columns of two tables?

If so, I do not think you need to use ForAll fucntion.

I suggest you just use Filter function.

If you want to filter based on two conditions at the same time, you just need to use "," or ";" to connect these two conditions.

Try this formula:

Filter(tablename,field1 in collection.fromtime,field2 in collection.totime)

Then, the formula will results the items that meet two conditicons at the same time.

What's more, usually we use ForAll function to update multiple records.

Here's a doc about it for your reference:

https://powerapps.microsoft.com/en-us/blog/bulk-update-using-forall-and-patch/

Best regards,

[LucB]

I think my question was not very clear, my appologies. Let me rephrase:

In my app users can select with dropdownboxes a from date and a to date to request leave time. Once they click on the for approval button, the dates from until to are parsed into a collection. Subsequently the app should check in a linked excel file if for this user there are already one or multiple dates with approved status in the file.

Herunder some code of how I have tried to solve the issue:

verlof is the excel table, DateRange is the collection, the dates have been split up in day/month/year to avoid intercontinental date issues

If(!IsBlank(ForAll(Filter(verlof,Initials = DropdownAanvrager.Selected.Initials,Status <> "Rejected"),
ForAll(DateRange,Value(Day(Date)) = Value(Day) && Value(Month(Date)) = Value(Month) && Value(Year(Date)) = Value(Year)))),
"yes","no")

Resulting yes/no will be replaced by further coding to direct the app.