cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Variable within ForAll

Hello,

 

I have seen a few posts on this but nothing that answers my query so hopefully someone can help.

 

On the Select of a button, I want to be able to look up a SP list and grab all of the items within there and create items for them in my other data source. I can successfully use the ForAll function to do this, however I am trying to set a Due Date on the tasks which in my app, skip by holidays (sitting in another list) and weekends. I have the code to successfully skip by these, but they are Set which isn't allowed in my ForAll. Please see code below:

 

Skipping by holidays and Weekends. The very first number in the first Set would be where I would want to grab the variable from the SP list.

 

Set(
                varDaysToAdd,
                1
            );
Set(varResult, DateAdd(Today(),
Value(varDaysToAdd) + 
RoundDown(Value(varDaysToAdd) / 5, 0)*2+
Switch(Weekday(Today()),5,If(Mod( Value(varDaysToAdd) , 5)>0,2,0),
                                         4,If(Mod( Value(varDaysToAdd) , 5)>1,2,0),
                                         3,If(Mod( Value(varDaysToAdd) , 5)>2,2,0),
                                         2,If(Mod( Value(varDaysToAdd) , 5)>3,2,0),
                                         1,If(Mod( Value(varDaysToAdd) , 5)>4,2,0))
)
);
If(varResult in Holidays.Date, 

Set(varDateDiff, RoundDown(DateDiff(Today(), varDayOne, Days) / 7, 0) * 5 +
Mod(5 + Weekday(varDayOne) - Weekday(Today()), 5));

Set(varResult, DateAdd(varFinalDate,
Value(varDaysToAdd - varDateDiff) +
RoundDown(Value(varDaysToAdd - varDateDiff) / 5, 0)*2+
Switch(Weekday(Today()),5,If(Mod( Value(varDaysToAdd - varDateDiff) , 5)>0,2,0),
                                         4,If(Mod( Value(varDaysToAdd - varDateDiff) , 5)>1,2,0),
                                         3,If(Mod( Value(varDaysToAdd - varDateDiff) , 5)>2,2,0),
                                         2,If(Mod( Value(varDaysToAdd - varDateDiff) , 5)>3,2,0),
                                         1,If(Mod( Value(varDaysToAdd - varDateDiff) , 5)>4,2,0))
)
),

If(Today() < varDayOne And varResult > varFinalDate, 

Set(varDateDiff, RoundDown(DateDiff(Today(), varDayOneMinus, Days) / 7, 0) * 5 +
Mod(5 + Weekday(varDayOneMinus) - Weekday(Today()), 5));

Set(varResult, DateAdd(varFinalDate,
Value(varDaysToAdd - varDateDiff) +
RoundDown(Value(varDaysToAdd - varDateDiff) / 5, 0)*2+
Switch(Weekday(Today()),
5,If(Mod( Value(varDaysToAdd - varDateDiff) , 5)>0,2,0),
4,If(Mod( Value(varDaysToAdd - varDateDiff) , 5)>1,2,0),
3,If(Mod( Value(varDaysToAdd - varDateDiff ) , 5)>2,2,0),
2,If(Mod( Value(varDaysToAdd - varDateDiff ) , 5)>3,2,0),
1,If(Mod( Value(varDaysToAdd - varDateDiff ) , 5)>4,2,0))
))))

 

 

My current ForAll which works for everything but where it says "DueDate_Column: SLA" I would want to replace SLA with my variable above, "varResult".

 

ForAll('NPI Tasks Items',

If(Complexity = "1",

If(
            CountRows(
                Filter(
                    taskscol,
                    cr80a_id = idvar And Subject = Task
                )
            ) = 0,
            
            Collect(
                taskcol,
                {
                    ID_Column: LookUp(
                        'Product Briefs',
                        ID = idvar,
                        ID
                    ),
                    Subject_Column: Task,
                    DueDate_Column: SLA,
                    Gate_Column: Gate
                }
            )
 )));



        ForAll(
    taskcol,
    Patch(
        'NPI Tasks',
        Defaults('NPI Tasks'),
        {
            cr80a_subject: Subject_Column,
            cr80a_id: ID_Column,
            cr80a_duedate: DueDate_Column,
            cr80a_gate: Gate_Column
        }
    )
)

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Variable within ForAll

@sammybridge ,

Do you want to set a variable within your ForAll function using Set function?

 

If you want to set a variable within a ForAll function using Set function, I afraid that there is no way to achieve your needs. Currently, Set function could not be used within a ForAll function.

 

As an alternative solution, I think the collection could achieve your needs. On your side, you could set up a collection to store the Due Date value instead of using global variable. Then you could update the Due date value via updating the collection data.

 

On your side, please consider modify your formula as below:

Clear(varResult); // Add this formula
ForAll(
        'NPI Tasks Items',
       If(Complexity = "1",
           If(
            CountRows(
                Filter(
                    taskscol,
                    cr80a_id = idvar And Subject = Task
                )
            ) = 0,
            If(                        // Add this formula. Store the Due date value within the varResult as a single one row
               IsEmpty(varResult),
               Collect(varResult, ...), // Replace '...' with the actual date value
               Patch(varResult, First(varResult), {Value: ....})
            );
            Collect(
                taskcol,
                {
                    ID_Column: LookUp(
                        'Product Briefs',
                        ID = idvar,
                        ID
                    ),
                    Subject_Column: Task,
                    DueDate_Column: SLA,
                    Gate_Column: Gate
                }
            )
 )));



        ForAll(
    taskcol,
    Patch(
        'NPI Tasks',
        Defaults('NPI Tasks'),
        {
            cr80a_subject: Subject_Column,
            cr80a_id: ID_Column,
            cr80a_duedate: DueDate_Column,
            cr80a_gate: Gate_Column
        }
    )
)

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Super User
Super User

Re: Variable within ForAll

@sammybridge 

You can't put SET inside of FORALL.  But consider the following: you can change the values within COLLECTION inside FORALL using PATCH.

 

Instead of using a SET variable your approach should be to store these values in a COLLECTION and PATCH them as needed.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Community Support
Community Support

Re: Variable within ForAll

@sammybridge ,

Do you want to set a variable within your ForAll function using Set function?

 

If you want to set a variable within a ForAll function using Set function, I afraid that there is no way to achieve your needs. Currently, Set function could not be used within a ForAll function.

 

As an alternative solution, I think the collection could achieve your needs. On your side, you could set up a collection to store the Due Date value instead of using global variable. Then you could update the Due date value via updating the collection data.

 

On your side, please consider modify your formula as below:

Clear(varResult); // Add this formula
ForAll(
        'NPI Tasks Items',
       If(Complexity = "1",
           If(
            CountRows(
                Filter(
                    taskscol,
                    cr80a_id = idvar And Subject = Task
                )
            ) = 0,
            If(                        // Add this formula. Store the Due date value within the varResult as a single one row
               IsEmpty(varResult),
               Collect(varResult, ...), // Replace '...' with the actual date value
               Patch(varResult, First(varResult), {Value: ....})
            );
            Collect(
                taskcol,
                {
                    ID_Column: LookUp(
                        'Product Briefs',
                        ID = idvar,
                        ID
                    ),
                    Subject_Column: Task,
                    DueDate_Column: SLA,
                    Gate_Column: Gate
                }
            )
 )));



        ForAll(
    taskcol,
    Patch(
        'NPI Tasks',
        Defaults('NPI Tasks'),
        {
            cr80a_subject: Subject_Column,
            cr80a_id: ID_Column,
            cr80a_duedate: DueDate_Column,
            cr80a_gate: Gate_Column
        }
    )
)

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,510)