cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andypi
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
GarethPrisk
Resident Rockstar
Resident Rockstar

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
GarethPrisk
Resident Rockstar
Resident Rockstar

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,027)