Simplify formulas with the With function

Welcome the With function to Canvas apps!  You can now simplify large formulas by dividing them into named sub-formulas, eliminate redundant sub-formulas, and easily work with functions that return records.  All while staying declarative and automatically responding to dependency changes.

That’s a mouthful, it’s easier to show you.  Let’s create a simple mortgage calculator to calculate monthly loan payments:

Today, the payment calculation in this app would be a bit complex:

(RateSlider/8/100/12)*(AmountSlider*10000) / (1-(1+(RateSlider/8/100/12))^(-(YearsSlider*12)))

Hmm, I wonder if that is even correct?  It’s hard to tell, there is a lot going in here.  Let’s start to unpack this with With.  The core of the calculation is the well known interest formula c = rP / (1 − (1+r)⁻ⁿ) where r is interest rate, P the loan amount, n the number of payments, and c the resulting payment amount.  Let’s use that formula verbatim with named values that pull apart the formula into small sub-formulas:

With( { r: RateSlider/8/100/12,     // interest rate
        P: AmountSlider*10000,      // loan amount
        n: YearsSlider*12 },        // number of payments
      r*P / (1 − (1+r)^-n) )        // standard interest calculation

Great, that’s a lot clearer.   We just created named values that we could plug in to our standard formula.  Besides making it simpler, we can reuse the value for r in two places, eliminating the redundant sub-formulas and the chance that the two get out of sync.  These names values are scoped to this With.

But those slider calculations are still a bit complicated.  It’s because, although you can set the min and max values on a slider control, the increment is always 1.  In the case of the Rate, we want fractional interest rates, so we divide the slider value by 8 to work with 1/8^th of a percentage point.  In the case of the Amount, it is easier to position the slider if we snap to increments of 10,000 by multiplying by that factor.  Finally, the interest rate and number of payments need to be in monthly vs. yearly values, requiring the division and multiplication by 12 months respectively.  We can pull all these adjustements out into another level of With:

With( { AnnualRate: RateSlider/8/100,           // slider moves in 1/8th increments and convert to percentage
        Amount: AmountSlider*10000,             // slider moves by 10,000 increment
        Years: YearsSlider,                     // slider moves in single year increments, no adjustment required
        AnnualPayments: 12 },                   // number of payments per year
      With( { r: AnnualRate/AnnualPayments,     // interest rate
              P: Amount,                        // loan amount
              n: Years*AnnualPayments },        // number of payments
            r*P / (1 - (1+r)^-n)                // standard interest calculation
      )
)

Way clearer and easier to verify as correct.   As long as the names don’t collide, we have access to all of the values in both With records.

We have now seen three formulas that do exactly the same thing, no more and no less.  And that’s one of the advantages of With: it can be used anywhere, in any context, to break up a complex formula with no other impact.   It can be used in any declarative context as shown here, where the app is recalculating the formula automatically as the sliders change value.   There are no OnChange event handlers used in this app, there are no state variables to manage with Set or UpdateContext.  It is our hope that With will reduce the need for some state variables and thus reduce the complexity of apps.

The curly braces in the first argument are no accident.  We are actually defining a record, similar to how UpdateContext is used.   With is a function for creating your own record scopes, enjoyed by lots of our functions such as Filter and Sum.  This also means we can pass a record in as the first argument.

Imagine you are working with SQL Server and want to create a record in the Orders table and then add records to the OrderDetails table that have a foreign key to it.   When creating records, the Patch function will return the newly create record including the server generated primary key.  We need to hold on to this value just long enough to use it to Patch in the OrderDetails records (held in the NewOrderDetails collection).  With can also be used in behavior formulas to do imperative work:

With( Patch( Orders, Defaults( Orders ), { OrderStatus: "New" } ),
      ForAll( NewOrderDetails, 
              Patch( OrderDetails, Defaults( OrderDetails ), 
                     { Order: OrderID,          // from With's first argument, primary key of Patch result
                       Quantity: Quantity,      // from ForAll's NewOrderDetails table
                       ProductID: ProductID }   // from ForAll's NewOrderDetails table
              )
      )
)

We’d like to do something similar for Common Data Service but we don’t yet have a way to reference the records from the first arguments of With and ForAll as a whole.  We are actively working on it.

If you depend on capturing error results from Patch, there is currently a bug that prevents that from being properly sent through With.  The bug has already been fixed and is making its way through our deployment process and should be in your hands in a few weeks.

And we can now finally complete our time parsing journey.   We started last November with 39 lines and in January trimmed that to 6 lines.  We can now take it to down to 3.  The Match function returns a record of the named matches within a regular expression and With makes it easy to use those values with the Time function.

With( Match( "PT2H1M39S", "PT(?:(?<hours>\d+)H)?(?:(?<minutes>\d+)M)?(?:(?<seconds>\d+)S)?" ),
      Time( Value( hours ), Value( minutes ), Value( seconds ) )
)

With might look familiar if you know Visual Basic’s With … End With syntax, from which our function got its name.