cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Using a variable in Forall

How could I reduce the code execution in a ForAll?  I thought I could use 'in' but looks like I need to run the Weekday function twice and do a
> 1 AND < 7 method 
I am trying to exclude weekends in building a collection of dates:

 

    ForAll(
        Sequence(10),
        If(Weekday(DateAdd(
                    inpDay_1.SelectedDate,
                    Value
                )) in [2,3,4,5,6],
        Collect(
            RequestDates,
            {
                Day: DateAdd(
                    inpDay_1.SelectedDate,
                    Value
                )
            }
        ))

 

 but needs

 

If(
    Weekday(DateAdd(inpDay_1.SelectedDate,Value)) > 1 AND
    Weekday(DateAdd(inpDay_1.SelectedDate,Value)) < 7
  )

 

which seems to unnecessarily double the calculations ? 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Depending on how you write it, you can use formula-level variables (i.e. With). However, usually UpdateContext/Set/etc. are somewhat limited given the conflict possibilities. Since you're collecting (another way to track runs in a loop), this is a straight-forward formula.

It's not clear above if that's an Items property or an OnSelect to build a collection. Either way, I recommend the latter - just decide when to build the collection.

Then use this code, or something like it.

 

Clear(colDates);
// Reset Collection
With(
    {
        varDaysToAdd: 20, // Your business days to add
        varDateStart: Today() // Your initial date
    },
    ForAll(
        Sequence(varDaysToAdd) As Loop,
        If(
            IsEmpty(colDates),// Is Collection Empty?
            Collect(
                colDates,
                // Initial Collection
                {
                    tblRef: Loop.Value,
                    tblDate: varDateStart
                }
            ),
            With(
                // Subsequent Collections
                {
                    varLastDate: Last(
                        Sort(
                            colDates,
                            tblRef,
                            Ascending
                        )
                    ).tblDate
                },
                Collect(
                    colDates,
                    {
                        tblRef: Loop.Value,
                        tblDate: DateAdd(
                            varLastDate,
                            Switch(
                                Weekday(varLastDate),
                                7,// Saturday
                                2,// Add 2 Days
                                6,// Friday
                                3,// Add 3 Days
                                1// Else Add 1 Day
                            ),
                            Days
                        )
                    }
                )
            )
        )
    )
)

 

View solution in original post

1 REPLY 1
Super User II
Super User II

Depending on how you write it, you can use formula-level variables (i.e. With). However, usually UpdateContext/Set/etc. are somewhat limited given the conflict possibilities. Since you're collecting (another way to track runs in a loop), this is a straight-forward formula.

It's not clear above if that's an Items property or an OnSelect to build a collection. Either way, I recommend the latter - just decide when to build the collection.

Then use this code, or something like it.

 

Clear(colDates);
// Reset Collection
With(
    {
        varDaysToAdd: 20, // Your business days to add
        varDateStart: Today() // Your initial date
    },
    ForAll(
        Sequence(varDaysToAdd) As Loop,
        If(
            IsEmpty(colDates),// Is Collection Empty?
            Collect(
                colDates,
                // Initial Collection
                {
                    tblRef: Loop.Value,
                    tblDate: varDateStart
                }
            ),
            With(
                // Subsequent Collections
                {
                    varLastDate: Last(
                        Sort(
                            colDates,
                            tblRef,
                            Ascending
                        )
                    ).tblDate
                },
                Collect(
                    colDates,
                    {
                        tblRef: Loop.Value,
                        tblDate: DateAdd(
                            varLastDate,
                            Switch(
                                Weekday(varLastDate),
                                7,// Saturday
                                2,// Add 2 Days
                                6,// Friday
                                3,// Add 3 Days
                                1// Else Add 1 Day
                            ),
                            Days
                        )
                    }
                )
            )
        )
    )
)

 

View solution in original post

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Top Solution Authors
Top Kudoed Authors
Users online (30,237)