The With function in Power Apps evaluates a formula for a single record, allowing you to calculate values and perform actions. It is especially useful for improving the readability of complex formulas by breaking them into smaller, named sub-formulas.
Sintax
With( Record, Formula )
Examples
1. Simple arithmetic calculations: In this example, Result is calculated as 50 * 10, and then 50 is added to get 550.
With( { Result: 50 * 10 }, Result + 50 )
2. Date and Time Manipulation: This example extracts and manipulates date and time components.
With( { Today: Now() }, UpdateContext( { YYYY: Year(Today), MM: Month(Today), DD: Day(Today) } ) )
Advanced example of using with
Here it is used to calculate monthly dates between two estimated start and end dates of a project, and then filter a set of data based on those dates in order to show the monthly progress for each activity.
We have the following Custom Page, where the objective is to be able to show the monthly progress of each activity of a project.
This graphical representation is composed of two galleries:
The interesting part of this development is how to obtain the dates and progress for each month.
To do this we use the following formula in the Items property of the gallery:
With(
{
sequenceYear: ForAll(
Sequence(DateDiff(varProjectSelected.EstimatedStartDate, varProjectSelected.EstimatedEndDate, TimeUnit.Months) + 1),
{
CalculateDate: DateAdd(Date(Year(varProjectSelected.EstimatedStartDate), Month(varProjectSelected.EstimatedStartDate), 1), Value - 1, TimeUnit.Months)
}
)
},
ForAll(
sequenceYear,
First(
Filter(
colProgressPlan,
ProjectID = GUID(varProjectSelected.ProjectID),
ActivityID = ThisItem.'PM Activity',
LocalPlanDate = CalculateDate
)
)
)
)
Let’s explain the formula in detail:
1. Generate the monthly dates: we must obtain the months of duration of each of the activities.
A. Sequence: This function generates a sequence of numbers from 1 to the number of months between the start and end dates of the project. It calculates the difference in months between the two dates and adds 1 to include the start month.
DateDiff(varProjectSelected.EstimatedStartDate, varProjectSelected.EstimatedEndDate, TimeUnit.Months) + 1
B. DateDiff: Calculates the difference in months between the estimated start and end dates of the project.
C. ForAll: Iterates over each number in the generated sequence. generada.
D. DateAdd: Adds the number of months corresponding to each value in the sequence to the project start date to calculate the monthly dates.
With(
{
sequenceYear: ForAll(
Sequence(DateDiff(varProjectSelected.EstimatedStartDate, varProjectSelected.EstimatedEndDate, TimeUnit.Months) + 1),
{
CalculateDate: DateAdd(Date(Year(varProjectSelected.EstimatedStartDate), Month(varProjectSelected.EstimatedStartDate), 1), Value - 1, TimeUnit.Months)
}
)
},
2. Filter the data to obtain the activity progress value for the current activity of the first gallery and by the corresponding date.
A. ForAll: Iterate over each date calculated in sequenceYear.
B. ColProgressPlan: It is a local collection where we have our progress by activity, project, month and progress.
C. Filter: Filters the colProgressPlan collection to find records that match the ProjectID, ActivityID and the calculated date (CalculateDate).
D. First: Selects the first record that meets the filter criteria. This is useful if you expect only one record per combination of ProjectID, ActivityID and LocalPlanDate.
Achivement
This custom page is part of a project management application. With this development we allow users to track the progress of project activity on a monthly basis. This way we are making sure that the activities are being completed as planned.
Conclusion
The With function is a powerful tool in Power Apps that can help you create more efficient and easier to maintain applications. By breaking complex formulas into more manageable parts, you can improve both the readability and performance of your apps.