cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Update multiple records in an excel table only if multiple conditions are true

Hello I hope you are well, I am developing an app based on the PowerApps "Leave Request" template, unfortunately I am stuck. My problem is the following: I have a record of employees in an excel table, this record contains the day in which they started working in the company and the vacation days have been assigned the problem is that annually from the date in which they started working the days assigned vacations must be restarted with the possibility of saving only 5 days, for example: The employee Robert was assigned 10 days a year of which he only spent 3, which would result in a balance of 7 days but since he has completed his year in the company he can only save 5 days and he is assigned 10 more having a new result 15 days total in the new year. How can I develop this to be done automatically according to the current day and month for all employees at the same time?
Can i get some help with this??
Thank you very much.

4 REPLIES 4
Super User II
Super User II

I have a sample app attached, that I built from inspiration of your request. I feel like there may be common needs to calculate running totals, and conditional minimums, so I figured I'd make something that sort of meets your needs, but will hopefully show you a few ways to possible do what you're looking to do. Please let me know if you have any questions about how this applies to your exact scenario.

 

For my app, I ended up creating a reference Collection of 12 rows. This is a useful approach if you ever need to repeat an action X times, as it gives you a simple list to execute a ForAll function against.

ClearCollect(colPTOChances,[1,2,3,4,5,6,7,8,9,10,11,12])

Then I created a Button which would let me generate records for this example. You will have data, so only parts of this function are useful - notably, conditional filtering, using a Max or Min function, etc.

// Set local variable to add a new Year, one greater than current max
UpdateContext(
    {
        locYear: Max(
            colYear,
            yearID
        ) + 1
    }
);
// Collect the new Year
Collect(
    colYear,
    {
        yearID: locYear,
        yearValue: Year(
            DateAdd(
                Now(),
                locYear * -1,
                Years
            )
        ),
        yearDaysRemaining: 0,
        yearDaysRunningTotal: 0
    }
);
// For all 12 opportunities in a year, 50% chance of taking a day off, with random date in that year
ForAll(
    colPTOChances,
    Collect(
        colPTO,
        {
            ptoTaken: Rand() > .5,
            ptoDate: Date(
                Year(
                    DateAdd(
                        Now(),
                        locYear * -1,
                        Years
                    )
                ),
                Round(
                    Rand() * 12,
                    0
                ),
                Round(
                    Rand() * 28,
                    0
                )
            )
        }
    )
);
// Count how many leftover Days from the year, after determining how many days were PTO days
UpdateIf(
    colYear,
    yearID = locYear,
    {
        yearDaysRemaining: Min(
            5,
            10 - CountRows(
                Filter(
                    colPTO,
                    Year(ptoDate) = yearValue,
                    ptoTaken
                )
            )
        )
    }
);
// Trigger Recalculation
Select(btnYearRecalculate)

I also have a Button which cleans up the two Collections which had added to them when 'Add a Year' was selected.

// Remove PTO Rows
RemoveIf(
    colPTO,
    Year(ptoDate) = LookUp(
        colYear,
        yearID = Max(
            colYear,
            yearID
        ),
        yearValue
    )
);
// Remove Year
RemoveIf(
    colYear,
    yearID = Max(
        colYear,
        yearID
    )
);
// Trigger Recalculation
Select(btnYearRecalculate)

Both of the blocks above will trigger a recalculation of the running totals. This is a function on a separate Button.

// Create a temporary loop collection, based on colYear
ClearCollect(
    colYearLoop,
    RenameColumns(
        ShowColumns(
            colYear,
            "yearID",
            "yearValue"
        ),
        "yearID",
        "loopNo",
        "yearValue",
        "loopValue"
    )
);
// Use that loop, to update each record in colYear, with the Remaining days from prior years and current year
ForAll(
    colYearLoop,
    UpdateIf(
        colYear,
        loopNo = yearID,
        {
            yearDaysRunningTotal: Sum(
                Filter(
                    colYear,
                    yearValue <= loopValue
                ),
                yearDaysRemaining
            )
        }
    )
)

As we add Years, we are emulating what it would be like to have multiple Years, with varying PTO taken. This way, you can see how several concepts, including conditional filtering, collecting, and recalculating are implemented.

PowerUser - Tracker 1.png

PowerUser - Tracker 2.png

PowerUser - Tracker 3.png

Above are some screenshots of the app in action, and I have attached it for reference.

Fun concepts here @PowerAddict 

@Roy let me know if you want any help.

Hi Gareth,
First of all thank you very much for your valuable help, I will be trying to understand your code to later prove it, if I am stuck I will let you know.

Although I think that "ForAll" function will be the solution.

Thank you again.

Helper V
Helper V

Then, select the column to filter. Open the menu > data > filter > advanced. Select the criteria range, the other column with the same name, and the Excel advanced filter multiple criteria one column will be applied on the first column, filtered with the multiple criteria.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (1,730)