Filtering Weekends Rows from a Table

[MustH]

Hi,

I have a Dataverse Table (Claims) and I have StartDate and EndDate and I have to check the rows if StartDate and EndDate has a weekend day or in between and if any of the rows has a weekend I want to add those rows in a new table as WeekendClaims

Can anyone help building the flow

[LinnZawWin]

You can trigger your flow when a row is added or modified (filter by StartDate and EndDate) to the Claims table.

• Then, check if the dayOfWeek(«StartDate») and dayOfWeek(«EndDate») are 0 or 6 (to see if StartDate and EndDate has a weekend day)
• Or greater(float(split(dateDifference(«StartDate», «EndDate»), '.')[0]), 4) (to see if the difference between StartDate and EndDate is more than 4  to see if there is a weekend day in between)
• Then, add a new row to WeekendClaims table

[MustH]

@LinnZawWin 

Thanks for your answer, can you tell me how to apply those conditions? actually I am confused.. I am picking all rows  ( currently 6) 

2 of them dont have weekends and 4 have weekends
I used this Flow



I initialized a variable for WeekendArray, I want to append those rows to that array and then use that Array to Insert those rows into the Table WeekendClaims and rest of the Rows to another Table as NonWeekendClaims

but I am unable to put the condition, can you please re-work my flow what should I remove and add? 

Please help

[LinnZawWin]

You don't need a Filter Array step since you are already looping through an array to process.

You need to add a Condition step to check if the current item matches the criteria (whether one of the dates falls on the weekend or there is a weekend in between) and append it to the respective array variable.

I've added one more Compose step with the following expression to calculate the number of days between StartDate and EndDate (split with '.' character only to take the number of days and truncate the time difference)

split(dateDifference(items('Apply_to_each_Claim')?['cr510_startdate'], items('Apply_to_each_Claim')?['cr510_enddate']), '.')[0]

That string value is converted into an integer and checked in the Condition step. If the number of days between StartDate and EndDate is more than 5 days, there will be at least one weekend day in between.

int(outputs('DateDifferenceBetweenStartAndEnd'))

[MustH]

Hi @LinnZawWin 

I highly appreciate that and I will test it and let you know.. 

I have actually 5 different conditions to check for a Claim Request, would you help me? 

I am fairly new to flows

[LinnZawWin]

@MustH Let me know your conditions and I can help to show how to use those in the condition step.

[LinnZawWin]

@MustH 

If my answer solved your problem, can you please mark it as a solution to close this thread? That will be useful for others in the community who come across this thread to identify the solution easily.

[MustH]

Hi  

The Conditions will be applied when creating a Claim Request, those claim requests store in Claims Table and in which they get stored without any conditions and then I am thinking to run a flow when an item is created in Claims table take that record and then start applying those conditions.



and then we will do List rows in Travel Table and get that row


 

In this Record, the End Date is a Weekend Day
8 June to 10th June, There is one weekend day between it.

how to apply these conditions and then store that claim in ClaimVerification Table
something like this we need to store in this table, after every condition check

Conditions: 

1- Check if either From Date or To Date is a Weekend?

Check From Date and To Date should not be a Weekend

2- Check Departure Time (Should not be greater than
15:00:000 )

Check the Departure time ( should not be greater than 3:00 pm )

3-  Check if there is a Weekend falling between Start Date and End Date

Check in the dates if any of the date has a weekend.

4- Check Public Holidays

there should be no Public Holiday between Start and End Dates. 

( for this the user has to maintain a Public Holidays Table of each year, (UAE) )

@LinnZawWin

[LinnZawWin]

You can initialize Boolean variable for each condition.

Once you get the value from the Travel Table in List rows, populate the Boolean variables with the expressions.

1- Here are the sample ones for Is Start Date Weekend and Is End Date Weekend.

or(equals(dayOfWeek(items('Apply_to_each_Claim')?['cr510_startdate']), 0), equals(dayOfWeek(items('Apply_to_each_Claim')?['cr510_startdate']), 6))

or(equals(dayOfWeek(items('Apply_to_each_Claim')?['cr510_enddate']), 0), equals(dayOfWeek(items('Apply_to_each_Claim')?['cr510_enddate']), 6))

2- For Check Departure Time, you need to use formatDateTime function to get the 'HH' hour part and convert to int() to check if it's later than 15.

3- Weekend falling between Start Date and End Date can be checked by the number of days between those two dates as I mentioned above.

greater(int(split(dateDifference(items('Apply_to_each_Claim')?['cr510_startdate'], items('Apply_to_each_Claim')?['cr510_enddate']), '.')[0]), 7)

4- Checking Public Holidays can be really complex depending on where you store the public holidays. You have to list the Public Holidays which are on or between the start/end dates and set as True if there is any record.

Then, set the Overall Status variable and using and() function for all of the variables above.

and(variables('Is Start Date Weekend'),variables('Is End Date Weekend'))

Then, Create a new Claim Verification row and populate with all those variables.

Original screenshot of the sample flow: https://www.screencast.com/t/yy27IM9uywF