Supercharge your Patch Function

[JR-BejeweledOne]

Bogged down with long if statement patch functions because a control might or might not contain information, you have a ComboBox with multiple selections or you need to patch a value based on another value?

I am going to show you some things you can do to ease the pain and take your Patch game up a notch.

Coalesce

This is a handy function that evaluates the arguments, provided by you, in order and returns the first value that isn’t blank or an empty string.  If all the arguments are blank or empty strings it returns blank.  It is perfect for an edit scenario where you only want to change a value if the input is not blank or an empty string but leave the existing value otherwise.

In the example below #1 is the value of a ComboBox which might or might not be empty and #2 is the existing value in the data source that is being updated.  Coalesce will look at both ComboBoxMondays and cr9f7_name.   If ComboBoxMondays has a value it will replace the existing value (cr9f7_name) with the new value from the ComboBox and if the ComboBox is empty, the existing value will be used, thus leaving the existing value unchanged.

With

With is another one of those functions that can make your life ‘oh so much’ easier.  There are many situations where you need to access items that you can’t access directly in the Patch statement.  

One of my goals when creating apps is making it as easy as possible for the person using it.  In many scenarios it’s easier to allow someone to make multiple selections in a ComboBox or listbox than to require them to make a bunch of selections in individual fields.  This can create quite a challenge on the back end depending on how you are accessing those selections and what you are doing with them. 

‘With’ allows you to access those selections individually and use them.   If you have a situation where your ComboBox will only ever have one or two choices and you need to access data associated with each choice consider using ‘With’.

This formula creates a record where the entries for the 2^nd selection in the ComboBox are initially set to blank strings.   If there is a 2^nd value selected in the ComboBox it then updates the newly created record with the information from the 2^nd selection.

Notice in the Collect and the UpdateIf, the ComboBox selection is referred to by it's number, 1 and 2 referencing the first and second selections.   This allows you to tell your formula exactly which selection you want to use for that item.

If( !IsBlank(ComboBoxFridays),
    With({_sel: Last(FirstN(ComboBoxPrimaryContact.SelectedItems, 1))}, 
        Collect(
        colTempSC,
        {
            cr9f7_name: Concat(ComboBoxMondays.SelectedItems.DateDisplay, DateDisplay & "; "),
            cr9f7_primarycontact: _sel.Email,
            cr9f7_pcname: _sel.Title,
            cr9f7_primarycontact2: "",
            cr9f7_pc2name: "",                              
            }
        )
   );

  If(
      CountRows(ComboBoxPrimaryContact.SelectedItems) = 2,
        With({_sel: Last(FirstN(ComboBoxPrimaryContact.SelectedItems, 2))}, 
          UpdateIf(colTempSC, cr9f7_name = cr9f7_name && cr9f7_primarycontact = LabelPrimaryContactValue.Text,
           {
             cr9f7_primarycontact2: _sel.'{Name}',
             cr9f7_pc2name: _sel.Title                             
           }
        )
     )   
 )
)

Switch

Did you know you can nest a switch statement inside of a Patch function?    You can and it’s a real game changer.

In the formula below, I am doing some date math to set an end date for a schedule rotation depending on the number of days in the rotation and using the selected start date.   I don’t have to build a collection or a variable with this information before I can patch it, I can do it right in the patch statement.

Collect(
            colSupportSchedule,
            {
                cr9f7_teamname: SC.cr9f7_teamname,
                cr9f7_teamrotation: SC.cr9f7_teamrotation,
                cr9f7_rotationend: 
                    Switch(SC.cr9f7_teamrotation,
                    "7",
                    Text(DateAdd(DateValue(Result), 6, Days)),
                    "5",
                    If(Weekday(DateValue(Result)) = 6,
                        Text(DateAdd(DateValue(Result), 2, Days)),
                        Text(DateAdd(DateValue(Result), 4, Days))
                        ),
                    "3",
                    Text(DateAdd(DateValue(Result), 2, Days))
                    )                
            }
         )

Using With, Coalesce and Switch in your Patch function allows you to avoid lengthy If statements, minimize use of collections and variables and make patching a lot easier and cleaner.