Lightning Web Component Reactive Properties

Lightning Web Components have quite a lot going on behind the scenes. One of the newer changes is the introduction of reactive properties. Reactive properties are variables you declare in components that fire page rerenders when changed. These variables can be specific to a single component, or they can be passed into children components, which creates a framework for sophisticated app design.

Reactive properties have changed since LWC’s inception, so this post will cover the new developments!

Natively Reactive Properties

Let’s start with a simple example to show LWC reactivity. I’m making a component that displays a user input on a page:

LWC Javascript

import { LightningElement } from 'lwc';

export default class Reactive extends LightningElement {
    reactiveText = '';

    assign(e){
        this.reactiveText = e.target.value;
    }
}

LWC HTML

<template>
    <div>
        <input type="text" name="ReactivePropertyValue" onkeyup={assign}/>
        <br/>
        {reactiveText}
    </div>
</template>

This is as simple as it gets. This component has a single input, ReactivePropertyValue, whose value gets assigned to the reactive property ‘reactiveText.’ reactiveText is displayed under the input:

lwc input

When you type a value into this input field, it will display under in and update in real time! This is reactivity in action!

lwc reactive input

This may seem simple, but this is what makes sophisticated web apps. Any primitive data type declared in a Lightning Web Component is reactive. But, what is happening behind the scenes? Salesforce is rerendering the entire template when a reactive property changes. This also fires any renderedCallback() methods. renderedCallback() is a lifecycle hook that fires after the page as been rendered. It can be used to make some really fancy apps.

To demonstrate what is happening, if I add a renderedCallback() to my component that does a console log everytime it is fired:

    renderedCallback(){
        console.log('I rendered');
    }
lwc renderedCallback

The component initially fired a renderedCallback when it finished loading and then one event everytime I typed, updating the value of the reactive property reactiveText. Use this to your advantage.

Declaring Reactive Properties

Primitive data types are reactive by default, but arrays and objects are not. I’ll adjust my component to use an object:

LWC Javascript

import { LightningElement} from 'lwc';

export default class Reactive extends LightningElement {
    reactiveText = {text1:'', text2:''};

    renderedCallback(){
        console.log('I rendered');
    }

    assign(e){
        this.reactiveText.text1 = e.target.value;
    }
}

LWC HTML

<template>
    <div>
        <input type="text" name="ReactivePropertyValue" onkeyup={assign}/>
        <br/>
        {reactiveText.text1}
    </div>
</template>
lwc reactive object

In this case, the object did not rerender even though we are targeting a key-value pair inside it. To get an object (and array or list) to also be reactive, tag it with the ‘@track’ property. Make sure to import ‘track’ from ‘lwc’:

import { LightningElement, track } from 'lwc';

export default class Reactive extends LightningElement {
    @track reactiveText = {text1:'', text2:''};

    renderedCallback(){
        console.log('I rendered');
    }

    assign(e){
        this.reactiveText.text1 = e.target.value;
    }
}
lwc reactive object display

Cross-Component Reactive Properties

With Lightning Web Components, you can pass property values from parent to child. Properties that are configured this way have the reactive decorator ‘@api.’ This also means they follow the same reactive behavior as natively reactive or ‘@track’ decorated properties.

Say we pass in the value of reactiveText.text1 to another component that renders it, instead of rendering it in our component:

reactive LWC

<template>
    <div>
        <input type="text" name="ReactivePropertyValue" onkeyup={assign}/>
        <br/>
        <c-reactive-display display-text={reactiveText.text1}></c-reactive-display>
    </div>
</template>

import { LightningElement, track } from 'lwc';

export default class Reactive extends LightningElement {
    @track reactiveText = {text1:'', text2:''};

    renderedCallback(){
        console.log('I rendered');
    }

    assign(e){
        this.reactiveText.text1 = e.target.value;
    }
}

reactiveDisplay LWC

<template>
    <div>
        {displayText}
    </div>
</template>

import { LightningElement, api } from 'lwc';

export default class ReactiveDisplay extends LightningElement {
    @api displayText = '';

    renderedCallback(){
        console.log('Child component rendered');
    }
}

Now, reactive is simply an input that sets the value for reactiveText.text1. reactiveDisplay is a child component of reactive that displays the value of displayText. displayText is tagged with the ‘@api’ decorator and it is assigned the value of reactiveText1.text1 in reactive’s HTML.

What happens when you start typing?

lwc api property

reactiveDisplay displays the text! I added a renderedCallback() in both components so you can also see that the renderedCallback(), and thus the rerendering behavior, or each component.

Apex Errors: 9 Common Errors And How To Resolve Them

Apex errors happen to anyone developing code on Salesforce. You can be an admin trying to troubleshoot something, a junior developer tackling a new task for the first time, or a seasoned developer trying to figure out some legacy code. Regardless, you will run into Apex errors from time to time. Here are some common errors and their root causes:

1. Attempt to de-reference a null object

This Apex error is a big one that comes up in all sorts of scenarios. It simply something is returning null! This could be an sObject, a variable, a data class, or a set but something is not being defined or returned properly. For example:

String nullString;
nullString = nullString.capitalize();

Will return this error. The issue here is the nullString is declared as a variable, but never initialized. When calling the capitalize() method, there is nothing for the method to perform its operation on, returning the error.

Because Apex will not allow you to use an undefined variable, one of your existing variables is either not being initialized properly or it is the return value of a method that is not returning what you expect. For example:

DOM.Document doc = new DOM.Document();
dom.XmlNode rootNode = doc.createRootElement('Envelope', 'namespace', 'soapenv');
String message = rootNode.getChildElement('Body', 'diffnamespace').getText();

Will also return the error. The part here that is null is the getChildEleent() return value, so when you call getText() on it, it is trying to preform it on a null value. More complex or custom methods may not have robust handling of null return and parameter values, so they need to be scrutinized when you encounter this error.

2. List has no rows for assignment to sObject

You will see this Apex error when a SOQL query assigning the value of a single sObject is not returning a value. Double-check what your SOQL criteria is and make sure it is logical. If it is and you are still seeing this error, try running the query in real-time using the developer console. If this is not returning records, you’ll have to edit your criteria or figure out why a record you expect to be there is not.

To prevent this error from happening in the first place, it is possible to always assign your SOQL query results to a list of the sObject you are expecting to get back. A list can accept query results that are empty. Then, simply check if your list is not empty and you can then use the value in the list for whatever you needed it for:

List<Account> emptyList = [SELECT Id FROM Account WHERE Type = 'Non-Account'];
if(emptyList.size() > 0){
	//condition if true
}

3. List index out of bounds: [n]

You will see this Apex error if you are trying to access a list index that is outside the indices of that list. For example:

List<Account> accountList = [Select Id FROM Account LIMIT 1];
Account outOfBounds = accountList[1];

In this example, the list accountList only has one value, the value at index 0. By then trying to access the value at index 1, we get the error that this is out of bounds.

In cases where this error is not as obvious, double-check your variables that are accessing indices and make sure they are not accessing something that does not exist. Similarly, if you are accessing indices you do expect to exist, check out the lists themselves. They may be empty or they may not have the correct number of elements in them, for various reasons. You can also use the list method list.size() to determine how many indices are in the list itself.

Also, the [n] in the error is the index you are trying to reference that does not exist. This is a handy indicator of the error’s location. If you are using a variable to access an index, you will know what value it throws the error. If it is being called discretely, you will know what line to look at in your code.

4. Invalid index at id [n]: null

I was working on debugging some code once and I ran into the above Apex error. It was stumping me for a while because I was checking to make sure records existed, could be queried, etc., but the culprit was something I had simply overlooked. I had no ID to work with! Take a look at the following code:

Opportunity oppNotInserted = new Opportunity();
delete oppNotInserted;

The code I was looking at was essentially adding an object to a list that was being deleted, but the object had not been inserted yet. There’s nothing to delete using that command in the database if it was never inserted in the first place. If I similarly tried to update it without inserting:

Opportunity oppNotInserted = new Opportunity();
update oppNotInserted;

The error ‘Id not specified in update call‘ is returned. Either of these errors mean you are trying to make a DML statement on something that is not yet a record.

5. SObject row was retrieved via SOQL without querying the requested field: [field]

This is a simple Apex error to resolve. The issue here is I made a query to get an sObject and it referenced a field that was not explicitly called in the query. For example:

Account queriedAccount = [SELECT Id FROM Account LIMIT 1];
String accountName = queriedAccount.Name;

Will result in this error. The issue here is the queriedAccount sObject was retrieved without including the field ‘Name.’ If you see this error, simply add the missing field to the query in question. Also, only include fields you need in the query! You are retrieving this record for a reason, so make sure you only get the information you need.

6. Method does not exist or incorrect signature: [method]

This Apex error occurs when you reference a method incorrectly. It can be a little frustrating at first, but once you understand how to troubleshoot it, it is simple to solve.

Look at the reference in the error and ask yourself the following:

  1. Did I include the correct number of parameters?
  2. Are the parameters the right type?
  3. Does the object/type method I am calling exist?
  4. Does the method I am calling exist in the class in a static context?

The error has to lie in one of these areas. If you are calling an error you see in documentation, like Salesforce standard methods, double-check that you are calling the method on the right class/type/object. If this is an error you are seeing with a custom method, confirm you are calling the right class and your types are correct.

Also, if you update existing code, you will need to update all the areas (including test classes) that reference the code you changed. This may seem obvious, but it helps to think simply when troubleshooting.

7. Recursive Trigger Error

You will encounter this Apex error when you have a recursive trigger situation. Recursive triggers occur when a trigger on Object A makes an update to Object B. Object B then has its own trigger that makes an update to Object A. This will trigger Object A’s trigger again and repeat into an infinite loop. Salesforce can detect these situations and it will throw an error if this happens.

There is a decent amount of content out there on how to get around this issue. I have seen a lot of people recommend using a flag called runOnce, or something similar, and check its context when the triggers fire. The idea is you only run the triggers while the value of runOnce = false, then set it to true. This works, but it is not really a solution!

I recommend taking a step back at what your triggers are actually doing instead. Your code is analogous to a business process. If triggers are firing repeatedly, something is wrong with the process. Look at what is really happening and add some conditions to your triggers so they don’t fire wholesale. There is no business process out there that results in an infinite loop, or else work wouldn’t get done!

8. MIXED_DML_OPERATION, DML operation on setup object is not permitted after you have updated a non-setup object (or vice versa): [sObjects]

This Apex error can show up in test and non-test contexts. Salesforce does not allow you to insert objects before or after certain objects due to how those changes would propagate in the database. The list of objects can be found here.

This error occurs when two separate DML transaction are being made on objects that do not allow insertions in the same transaction. In a non-test scenario, you will have to wrap one of the transactions in a @future context. This means that the transaction will be processed asynchronously (which just means it will be done separately).

If the context is a test method, you have two options:

  1. Initially create some of the objects in the @testSetup method Salesforce allows for test classes. This method runs first and creates test data that all test methods in the class can access.
  2. Use the System.runAs([User]) method to run a DML transaction as a different user. Salesforce will allow test classes to treat this as a different transaction.

I have used both solutions, but I found using the @testSetup method is cleaner and much easier to understand.

9. Too many SOQL queries: 101

This is an Apex error that is very common, especially for developers who are getting used to Saleforce governor limits for the first time. Salesforce will not allow you to make more than 100 SOQL queries in a single transaction. A transaction is defined as the start of an operation seen through to end of itself and any other operations it could have triggered. For example:

Save a record --> custom validation code fires --> sObject trigger fires --> configuration rules fire --> sObject trigger fires --> record is saved

In the above sequence, there cannot be more than 100 queries from the operation of the record being saved until the record is finally saved in the database. If this is the case, some of the configuration may need to be consolidated into code. This helps because a developer can bulkify operations.

You may also be seeing this error because you are introducing code into the system that is not bulkified. For example:

List<Account> accountList = [Select Id FROM Account];
for(Account a : accountList){
    List<Opportunity> oppList = [SELECT Id FROM Opportunity WHERE AccountId = :a.Id];
	//use opp data here
}

A common case of not bulkifying is when you are using an object to get some information about another, related object. In the above case, the limit will trigger because I have more than 100 accounts in that org. I need the opportunities related to the accounts, but this is not the correct way to do it. To bulkify, I will use a map instead.

A Salesforce map is a collection of key-value pairs. You can declare any sObject or datatype as either a key or value. Also, you can have values be maps or lists, which can make for some interesting data structures. To bulkify the above example:

List<Account> accountList = [Select Id FROM Account];
List<Opportunity> oppList = [SELECT Id, AccountId FROM Opportunity];
Map<Id, Opportunity> accountToOppMap = new Map<Id, Opportunity>();
for(Opportunity o : oppList){
    accountToOppMap.put(o.AccountId, o);
}

for(Account a : accountList){
    if(accountToOppMap.containsKey(a.Id)){
        Opportunity oppToUse = accountToOppMap.get(a.Id);
		//use opp value here
    }
}

Now, this is a bit more complicated, but we went from 100+ SOQL queries to 2! I made a map of Id –> Opportunity and I made the keys the related AccountIds. In the loop itself, I used the map method containsKey() to check if the account I am on exists in the map. If it does, I grab the Opportunity using the get method and the ID of the account. I can then use it as I please.

If you’re still running into SOQL query errors, or any of the errors above, leave a comment or contact me for advice.

How To Create A Discord Bot Using Node.js

Discord is a popular messaging app primarily used for video game communities. It has grown and evolved into a platform where users can still chat, message each other, create servers and channels, and congregate, but also create full fledged apps! I am going to walk through how to make a Discord bot in this article, a popular type of app.

To make a Discord App, we are going to use Node.js. Node.js is a Javascript runtime driven by events. It has become very popular over the years, so any experience you have with it can help. To get this bot up and running, we will need to do the following:

  • Download and install Node.js on our local machines
  • Create a Discord account and create a server and a bot application
  • Write a Node.js file to define what the bot does
  • Run the bot in the command line!

Before we get into the steps, let me clarify that this bot will listen to a channel in Discord and respond to messages automatically as they are posted. Once we get the bot up and running, I will show you how you would wire it up to fire on many events, not just messages coming into a channel.

Step 1 – Install Node.js

Head over to download and install Node.js. Pick the installer for your operating system and install it as you would any application.

Step 2 – Create A Discord Account, Server, and Bot Application

Before we do any coding, let’s head over to Discord and get the infrastructure we need up and running. Head over to Discord and create an account if you don’t have one. You will also have to verify your email to be able to create apps and bots. Once you login, you will see a screen like this:

Discord Home Screen

Click on the ‘+’ button on the left sidebar to create a server:

Discord Create A Server

Select ‘Create A Server:’

Discord Create A Server Prompt

And I will call mine ‘Bot Test Server.’ Great! Now that we have a server and channel setup, head over to the Discord developer portal. Login with your Discord account and you will see the following screen:

Discord Developer Home

Create an application by clicking ‘New Application’ in the upper-right corner;

Discord Create New Application

Let’s call it ‘My First Bot.’ After you create it, you’ll see you have extra options on the left-hand sidebar:

Discord Application

Click on the ‘Bot’ tab and create a bot by clicking ‘Add Bot’:

Discord Bot Home

Accept the prompt:

Discord Bot Prompt

And you’ll have your first bot!

Discord Bot Home

Your bot will now need to be added to your channel. The way Discord allows this to happen is through the OAuth2 standard. You will pick what type of authorization you are seeking and what permissions you want to have access to. You’ll see an ‘OAuth2’ tab on the left-hand bar. Click on it to configure your bot:

Discord OAuth2 Permissions

There is a section called ‘scopes’ where you will select ‘bot.’ Under ‘Bot Permissions,’ select ‘Send Messages’ and ‘Read Message History.’ This generates a URL in the middle of the page that you will paste in your browser. It will then take you to a screen where you can add this bot to channels you have permissions to add bots to. In this case, select the ‘Bot Test Server’ channel and your bot will be added!

Discord Add Bot To Channel
Discord Bot In Channel

If done right, you will see your bot on the right-hand side of the channel, offline. To get the bot online, we will write our Node.js code!

Step 3 – Write Our Bot

To begin, pick a folder for your files to be stored in. Node.js applications are made up of a Javascript file, a package.json file, and whatever libraries are pulled in by the code. The Javascript file is what we will write and it acts as the main logic behind the bot. The package.json is a standard file for Node.js files that describes the architecture of the app. It will be created automatically in this tutorial.

Once you have picked a folder to work out of, navigate to that folder using the Command Prompt. You can get to the Command Prompt by searching in the search bar on Windows Machines:

Windows Command Prompt

My bot files will live in the folder shown below:

Windows Node.js File Directory

Open the Command Prompt and navigate to whatever folder you are using. Use ‘cd’ and then type in the file path to get to your folder:

Windows Command Prompt Navigation

Then, run the following command:

npm init

npm is the Node Package Manager. Since you previously installed Node.js, this command is available and it is a robust package initializer. You will be asked some questions, and you can enter answers by typing and pressing enter. When you get to specify the ‘entry point,’ call it whatever you want your Javascript file to be. In this case, I entered ‘discordbot.js.’

Node package.json

If you check your directory, you will see the package.json file:

Windows File Directory Node.js
Node.js Package.json Discord Bot

Now, create a file called ‘discordbot.js’ in the same folder using your preferred text editor (I prefer Sublime). Then, paste in the following code and save the file:

var Discord = require('discord.io');
var logger = require('winston');

logger.remove(logger.transports.Console);
logger.add(new logger.transports.Console, {
    colorize: true
});
logger.level = 'debug';

var bot = new Discord.Client({
   token: [YOUR BOT TOKEN],
   autorun: true
});

bot.on('ready', function (evt) {
    logger.info('Connected');
    logger.info('Logged in as: ');
    logger.info(bot.username + ' - (' + bot.id + ')');
});
bot.on('message', function (user, userID, channelID, message, evt) {
    logger.info('msg');	
    bot.sendMessage({
        to: channelID,
        message: 'I am a bot'
    });     
});

The above code will create a Discord object from the discord.io library. This will be the major library you will use to preform most of the bots functions. winston is a popular logger for Node.js, so we are using it here to debug out statements to help us figure out what is going on. The token here is the bot token that is on your bot’s page in Discord. Go back to it, copy that token, and paste it within double quotes. Finally, the two events are events that are defined in discord.io. They fire when the bot comes online (which happens when the Javascript is run) and when a message appears in the channel.

Now, we are almost ready to run the bot. Before we do, we have to import the files we require at the top: discord.io and winston. To do this, open the Command Prompt and navigate to the folder our files reside in. Then, run the following commands:

npm install discord.io winston --save
npm install https://github.com/woor/discord.io/tarball/gateway_v6
Command Prompt Node.js Install Requirements

Now, if you look at your file directory, you will see the libraries are in a folder called node_modules:

Node.js File Directory Files Installed

Step 4 – Run The Bot!

Now, simply run the following command in the Command Prompt, while still navigated to the folder your files are in:

node discordbot.js

You should now see you bot come online in your channel:

Discord Node.js Run Bot File
Discord Bot In Channel Online

Now, if you type a message, you will see the bot respond and an event log in your console:

Discord Bot In Channel Working
Discord Bot Node.js Logging

And there you go! You can now read the discord.io documentation and see what else you can do. You are not limited to just triggering on messages. The bot can do much more. You can fire on all sorts of events and give the bot permissions to respond to events, create channels, and manage your discord server!

Setup Google Analytics For WordPress: The Only Guide You Need (No Plugin!)

Google Analytics is a powerful marketing analytics platform provided by Google for free. Marketing teams use GA to measure their traffic, where it’s coming from, what’s converting, and even what times throughout the day are most active! It’s a great source of information that drives decision making around website management and design. You can also setup Google Analytics for WordPress sites.

I was having trouble configuring GA on my own blog without the use of a plugin. After scouring through a few articles online, I was finally able to piece everything together and get GA on my blog. I am going to describe what I did to hopefully save you the hours I spent.

To configure GA, I completed the following steps:

  • Upgraded my WordPress account to a Business Plan – this is necessary because you need to be able to install themes in this guide
  • Create a Google Analytics account and generate your tracking code
  • Use an FTP client to access your site’s files
  • Edit your theme’s header file and add your tracking code to it

To get GA on your blog, consider if upgrading is worth it to you. I think for the cost of the plan and the potential to have powerful insights into your content strategy, it is definitely worth it if you are serious about creating a successful blog. To create a Google Analytics account, head over to the GA page and select ‘Start For Free.’

Google Analytics Home Page

You will need to then name your account, specify your site’s URL, what your using GA for, and what industry you’re in. After the initial steps, you will be brought to the Admin Dashboard, where you will see your GA tracking code! This is what you need to place in the head of your site’s HTML/php.

Google Analytics Tracking Code

Once you have your tracking code, you’ll need to edit the file’s theme. I am assuming if you are using WordPress, you are using many of the great themes the community, or WordPress themselves, have created. If you are using your own theme, you already know how to access and edit your files, so simply place the tag in your head and you are all set!

If you are using a theme, then you’ll need to get an FTP client to access your site’s files. FTP stands for File Transfer Protocol. The client will essentially allow you to explore your site’s file structure as if you were exploring it using your operating system’s file browser. Within your WordPress site files, you will see the files that makeup the theme you are using. You will need to edit these to add the GA tracking code in the head and get GA integrated to your site.

I used FileZilla, a free FTP client that will fulfill our requirement here. Download and install the FileZilla client to your local machine and boot it up. You will see the following screen:

FileZilla FTP Client

You will need to specify the host, username, password, and port for your site. Where do you find these? Good question! Go to your WordPress Site and click on ‘My Site’ and go to ‘Hosting Configuration,’ under ‘Manage:’

WordPress SFTP Credentials

Click ‘Create SFTP Crendentials’ and you will be given the information you need. Use the URL, username, password, and port you get in FileZilla and you will now be connected to your site’s files:

FileZilla FTP Connected

Go into your wp-content folder, and then to your themes folder. Here is where you will have to edit your theme’s header to get GA up and running. If you see the following icon next to your theme:

WordPress Themes In FTP Client

You have not installed the theme! This also means you cannot edit it. Head over to your admin view and install your theme from that page ([your site URL]/wp-admin/themes.php), and then activate it. Then, your icon will simply look like a folder without the arrow. When it is just a plain folder, you can go into the folder and find the ‘header.php’ file:

WordPress Theme header.php In FTP Client

This will open a text editor where you can finally paste your GA tracking code:

Add Google Analytics Tracking Code To header.php

Place it within the head! Save your file, and head over to your page! If everything was done correctly, you should see a site visit in your GA dashboard! You have now setup Google Analytics for WordPress!

Google Analytics Dashboard

Once you get the hang of the FTP client, you can also edit your themes using the ‘Theme Editor’ in your admin dashboard. Go to [your site URL]/wp-admin/theme-editor.php and you will see your themes and a similar file structure to the FTP client. Sometime’s it is not possible to edit theme’s in this view due to permissions, which the FTP client allow you to overwrite.

The Right Way To Do Last Modified Date In Excel (Without Using TODAY)

Excel still holds an important place in a lot of the business software world. Underneath its simple spreadsheet is a powerful tool with its own robust coding language (Visual Basic) and frameworks. I recently ran into an interesting problem when I was working on an Excel project:

How do I store the last modified date of a cell in another cell?

This seems simple enough in concept, but the solutions I was coming across after doing some research were variations of using the TODAY function or having NULL checks and formulas. Neither of these options were viable because TODAY updates everytime the sheet is recalculated, losing my last saved date. Formula fields got too messy and would often need a default date initially, which looked cluttered.

After doing some more reading, I came across the answer:

VBA Events!

Excel captures certain events and allows certain events to have parameters that can be used for extra processing. The event we will use to implement a Last Modified Date in Excel is the Change Event.

Change fires whenever a cell is changed by a user or an external link and when it fires it passes the Range of what triggered it as a parameter. Plainly, this means that you know what cell triggered the change. You can use the cell’s properties to build complex logic that will then only run code when a certain cell is fired.

Let’s start illustrating this by opening Excel and creating a simple spreadsheet. It will have one column with a field for user input and another column that will hold the Last Modified Date of the cell adjacent to it:

Excel Sheet For Change Event

To use the Change event, right-click on the bottom of the tab where it says ‘Sheet1’ ad click ‘View Code’:

This opens up the VBA panel, which is where you write VBA code to have Excel compute more sophisticated operations. Paste the following snippet in the panel for ‘Sheet1’:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Row = 2 Then
        ActiveSheet.Cells(2, 2).Value = Format(Date, "mm/dd/yyyy")
    End If
End Sub
VBA Code For Last Modified Date

Excel routines are specified by the keyword Sub. Worksheet_Change(ByVal Target As Range) is how the Change event is called. This code will run every time a Change event happens in our worksheet. Target is name of the variable being passed in, and it is of type Range. Range is a data object VBA uses that represents a range of cells.

VBA supports If statements like any coding language. The syntax is as follows:

If [TRUE / FALSE condition] Then
    (your code here)
ElseIf [TRUE / FALSE condition] Then
    (your code here)
Else
    (your code here)
End If

In our case, we only have one condition to check, so our if statement is really simple. Range objects have the properties ‘Column’ and ‘Row,’ which return the long values of their respective columns and rows. For this example, we only want to record the Last Modified Date cell A2. It’s row and column are 2 and 1, respectively, so we place that condition in our If statement.

If this condition is met, we want to set the value of the cell next to it, B2. To set a cell’s value, use the syntax:

ActiveSheet.Cells([Row Value], [Column Value]).Value = [Value]

We are going to set our cell to today’s date, so we use the Date object and the Format method, passing in a month, day, and year format to display our date in.

That’s it!

Now let’s see our code in action:

Excel Last Modified Date VBA
Excel Last Modified Date VBA

Perfect! Since this method does not really on TODAY, the value will not change. Also, there is no formula needed so the cell will not have a default value or anything messy in it if the cell it is tracking has not been modified.

To learn more about Excel VBA concepts, check out some of my other posts about looping and conditionals. Find this post helpful? Reach out to me using the Contact button at the top of this post if you want a consultation for your tech-related issues!

Batch Apex Example And Explanation

Batch Apex

Batch Apex – When would you need to use it?

You may find yourself in a situation where you need to process a lot of data on an object in your Salesforce instance or even simply data from an API endpoint. Salesforce has governor limits that make processing very large data sets or running complex operations on those data sets tricky. You can only have your process run for so many seconds or take up so much memory.

So, when you need to process a lot of data in Salesforce, use Batch Apex!

When Do You Need To Use Batch Apex?

Before we get into how to use Batch Apex, let’s talk about when you would use it. Batch Apex is commonly used to:

  • Process a large set of data, namely Salesforce records
  • Communicate with an API and process a large payload
  • Complete a long running operation

Batch Apex allows you to do this because the governor limits are expanded a bit and the code is processed in chunks, or batches. As long as no individual batch hits governor limits, the operation will complete.

Now, let’s get started!

Batch Apex Class Structure

To create a Batch Apex class, you need to implement the Database.Batchable interface on your class. This interface requires three methods: start, execute, and finish. Your methods also need to have the proper return type and parameters:

  • Start method: This method takes in a Database.BatchableContext Object and returns a list of sObjects or a Database.QueryLocator object. The start method initializes the list of sObjects being processed by the class.
  • Execute method: This method takes in the list of sObjects from the start method and a Database.BatchableContext object. This method is where the majority of the processing is done.
  • Finish method: This method takes in a Database.BatchableContext object.

To look at some code, the batchable class looks like this:

global class MyBatchClass implements Database.Batchable<sObject>{

  global (Database.QueryLocator | Iterable<sObject>) start(Database.BatchableContext bc) {}

  global void execute(Database.BatchableContext BC, list<sObject>){}

  global void finish(Database.BatchableContext BC){}
}

A Practical Example

This looks great, but it’s not helpful if you’re looking to follow a practical example. To show you a more helpful example, let’s make a batch class for the Account object:

global class MyBatchClass implements Database.Batchable<Account>{

  global List<Account> start(Database.BatchableContext BC){
      
  }

  global void execute(Database.BatchableContext BC, List<Account> scope){
      
  }

  global void finish(Database.BatchableContext BC){
      
  }
}

Now, this looks a little more readable. You can see that I am going to return a list of Accounts in my start method, the execute method will take in the same list of Accounts, and the finish method will do some post processing. A few things to notice:

  • The Database.BatchableContext BC object is for Salesforce to recognize it as a batchable class. You do not declare it anywhere. It is created when you call the class to run, which will be shown at the end
  • The execute method is called with whatever the start methods returns automatically. You do not have to manually write MyBatchClass.execute(List<Account>)

Say we want to process all our Accounts and add ‘123’ to their names. We would use the above class and do the following:

global class MyBatchClass implements Database.Batchable<Account>{

  global List<Account> start(Database.BatchableContext BC){
      List<Account> appendNames = [SELECT Id, Name FROM Account];
      return appendNames;
  }

  global void execute(Database.BatchableContext BC, List<Account> scope){
      for(Account append : scope){
          append.Name = append.Name + ' 123';
      }
      
      update scope;
  }

  global void finish(Database.BatchableContext BC){
      system.debug('Batch finished!');
  }
}

Now that the class has been created, let’s run it! The class can be scheduled or it can be run manually. To run it manually, go to the developer console and create an instance of the class and pass it to the Database.executeBatch method. This method takes in the class, or the class and an integer variable called the scope. The scope specifies how many records to process in each batch.

For example, if my class returns 100 records and I want to process them 5 at a time, I would pass 5 as the scope for the executeBatch method. This will create 1 run of the start method, 20 runs of the execute method, and 1 run of the finish method:

Executing Batch Apex In Developer Console
Batch Apex Execution Results

If you count it, there are 22 executions of Batch Apex now, just like we thought there would be:

Batch Apex Results On Records
Batch Apex Finish Method

And there you have it! A practical example of Batch Apex.

Other Considerations For Batch Apex

Batch Apex has a few more nuances to it that I will list out here:

  • If you want to retain values of variables across batches, specify Database.Stateful in the class definition. This will allow a variable declared within the class to keep its value during processing. This is useful if you are maintaining a counter or some other value that is affected by all the records.
  • A constructor can be created to pass in additional values to the batch class.

To show the second point, take our existing batch class. Say I wanted to specify what string I wanted to add to all the accounts when I execute the class. I would modify my class to be:

global class MyBatchClass implements Database.Batchable<Account>{

  global String className;
    
  global MyBatchClass(String namePassedIn){
  	className = namePassedIn;     
  }
    
  global List<Account> start(Database.BatchableContext BC){
      List<Account> appendNames = [SELECT Id, Name FROM Account];
      return appendNames;
  }

  global void execute(Database.BatchableContext BC, List<Account> scope){
      for(Account append : scope){
          append.Name = append.Name + ' ' + className;
      }
      
      update scope;
  }

  global void finish(Database.BatchableContext BC){
      system.debug('Batch finished!');
  }
}

className is the class variable that is being set when the class is executed. It will allow me to specify something to append to the Account names. The constructor, in this case, is simply assigning the parameter passed into it to the class variable:

Executing Batch Apex With A Constructor
Batch Apex Results On Records

To learn more about Apex, try your hand at some Trailheads. If you are having issues with Apex, my article on 9 common Apex errors may help!

Initializing Third-Party JavaScript Components in LWC

I was working on a project recently and I needed a third-party JavaScript component for it, the Owl Carousel (https://owlcarousel2.github.io/OwlCarousel2/). After reading the documentation on how to load third-party JavaScript libraries in LWC and the documentation on how use Owl Carousel, I still hit a few snags getting it to work in my project. After some tinkering and reading on Stack Overflow, I finally figured it out thank to this great post!

I’ll use Owl Carousel as an example here, but this will work for any JavaScript library that needs to be initialized. First, you need to create a Static Resource that has the JavaScript and CSS files in it and upload to Salesforce in a .zip file:

These are the files I need for Owl Carousel and I needed to download the latest jQuery, which can be found here. Once those files are in a zip file, upload them as a Static Resource to your org:

A Static Resource is a set of JavaScript, CSS, and/or image files that you can upload to your organization and reference in your code. LWC has a built in method to do this called loadStyle/loadScript, depending on what you are referencing. This is done in the renderedCallBack of your component. You then need jQuery just because most third-party components depend on it.

To initialize the Owl Carousel in a component and have it ready for use, I had to load the jQuery library and then load the Owl Carousel JavaScript and CSS files

 renderedCallback() {
        if(this.initialRender){return;}
        this.initialRender = true;
        loadScript(this, '/owlcarousel/jquery-3.4.1.min.js')
            .then(e => {
                loadStyle(this, '/owlcarousel/owl.carousel.min.css')
                loadStyle(this, '/owlcarousel/owl.theme.default.min.css')
                loadScript(this, '/owlcarousel/owl.carousel.min.js')
                .then(() => {
                    const carousel = this.template.querySelector('div[class="owl-carousel owl-theme"]');
                
                    window.$(carousel).owlCarousel({
                            loop:true,
                            nav:true,
                            responsive:{
                                0:{
                                    items:1
                                }
                            }
                        })
                }
          }
} 

A few things to note here:

  1. I am using the renderedCallBack method to load these script/styles in. Also, loadScript/Style returns a promise, meaning the operation needs to wait for the response. jQuery is loaded first and then the Owl Carousel is loaded within the jQuery promise.
  2. The second promise body holds the initialization of the Owl Carousel component itself. It can be referenced now that the previous dependencies have been loaded.
  3. The syntax for initializing the component is:
    window.$([name of queried element]).[initialization method from doc](parameters)

You can follow this structure for loading any JavaScript component you want! Try it for yourself after you create and LWC component!

Table Header Always Displayed – CSS Solution

A common problem I had and saw online was making a table on a web page have the top/header row always displayed while the body of the table scrolls. I found this solution on Stack Overflow and it worked well! I cannot remember who suggested this, but if you are the person who did this, email me and I will credit you.

This solution is done purely with CSS and applied to an HTML table. Take the following CSS code and put it in your CSS file/head:

            .table-scroll {
                display: table;
                min-width: 100%;
                color: black;
                background-color: white;
                font-family: Arial, "Helvetica Neue", Helvetica, sans-serif;
                font-size: 13px;
            }

            .table-scroll thead,
            .table-scroll tbody {
                width: 100%;
            }

            .table-scroll thead {
                overflow-y: scroll;
                display: table;
                table-layout: fixed;
                margin-left: -1px;
                background-color: #f5f4f2;
            }

            .table-scroll tbody {
                outline-style: solid;
                outline-color: black;
                outline-width: 1px;              
                overflow-y: auto;
                max-height: 85vh;
                display: block;
            }
            .table-scroll tr {
                width: 100%;
                text-align: left;
                display: table;
                table-layout: fixed;
            }
            .table-scroll  td{
                border-right: 1pt solid lightgrey;
                border-bottom: 1pt solid lightgrey;
            }
            ::-webkit-scrollbar {
                width: 5px;
            }
            /* Track */
            ::-webkit-scrollbar-track {
                background: white;
            }

            /* Handle */
            ::-webkit-scrollbar-thumb {
                background:#D8D8D8;
                border-bottom-left-radius: 2px;
                border-bottom-right-radius: 2px;
                border-top-left-radius: 2px;
                border-top-right-radius: 2px;    
            }

Then, create an HTML table and give it ‘table-scroll’ class. The webkit-scrollbar-thumb and track code affects the scrollbar associated with the table. In this case, you will get a nice, thin scrollbar on the side!

Table With Header Always Displayed

This table will now have a table header that is always displayed at the top, even if you scroll all over the body. This is a great solution to this problem that is simple to implement, lightweight, and easy to understand.

You can also play around with the styling, so you can have the table be slightly larger, smaller, or have the row be different heights. This solution is really only affecting the behavior of the table as you scroll, so you are free to make changes to the visuals of the tables without losing the header being at the top.

Try implementing this solution while following one of my other articles on tables!

LWC Table Search – With Example!

LWC Table Search Banner

Now that we have covered how to make a table in LWC and how to sort it based on columns, lets cover how to implement LWC table search. When you build a table of data without search options, it can quickly get unwieldy to pore over the data. If you have a search bar near the table that filters the data based on the value entered into the search bar, using the table to get data becomes much easier. This article will cover how to do that.

How Will The Search Work?

There are a few things to consider when creating a search bar to filter data:

  • Which piece of information are you applying the search to? One column or multiple?
  • How many characters do you kick the search off for?
  • How do you handle clearing the search bar after data has been retrieved?

In this example, we will sort our People table by Name. We will trigger the sort to run after the search bar has 3 or more characters entered in it. Finally, when there are less than three characters, we will return all the records back in the table. Also, we will actually be making another object to hold the sorted data! This way, the original data that we are querying from the database remains untouched. We need to do this so we can recall the information we originally query if the search filter value is removed.

HTML And JS for Search Bar

Let’s add the html to our sortedTable component to render the search bar. We will give it an onkeyup event, which triggers when a key press is released, and a max-width so it sizes properly:

<input onkeyup={filter} type="text" class="slds-input" style="max-width: 400px;"/>

Then, we’ll write our function. The People table needs to be stored in another array, which we will call ‘searchable.’ We will then initially populate searchable with the value of People in our constructor:

    constructor(){
        super();
        getPeopleApex().then(result => {        
                this.People = result;
                this.searchable = this.People;
            }
        )
    }

Then, we’ll write our search event that is tied to the search bar:

    search(e){
        this.searchTerm = e.target.value;
        this.searchTerm = this.searchTerm.toLowerCase();
        const regex = new RegExp('(^' + this.searchTerm + ')|(.' + this.searchTerm + ')|(' + this.searchTerm + '$)');
        if(this.searchTerm.length > 2){
            this.searchable = this.People.filter(item => {
             
                if(regex.test(item.Name__c.toLowerCase() + ' ' + item.Name__c.toLowerCase())){
                    return item;
                }
            
            })
        }
        else if(this.searchTerm.length <= 2){this.searchable = this.People}                
    }

Table Searches!

And that’s it! Now if you try to search in the bar, you will the table responsively updates the table if the search term matches the name of a person in the table. If the term is 3 characters or more, this event triggers; if it’s not, the table is then set equal to People again, so all the people are visible.

This table is searching based on the name field, but you can switch it to anything. Also, we are using a regex, which is a Regular Expression to do the search. A regex is it’s own topic, but essentially it is a coded string that represents a term that you are interested in matching against in a string. You can play around with one here: https://regexr.com/.

You can also implement LWC table search by having the search event make a backend call to a database, building a dynamic query and returning that result to the front-end. This is useful if you have a lot of records initially (upwards of 20k) but still want to have searching.

Now that you have a table that searched, try enhancing it with pagination!

LWC Table Search Results

Create LWC Table From Records – With Example!

We worked with tables in my last post and I wanted to expand on that concept in the future. In my previous post, I explained how to sort a table. The table in that example was hard-coded, which doesn’t have much practical use in the world of IT. In this post, I will show you how to create an LWC table from records. This will require an Apex class that makes a call to the database, a Javascript method to call that class and process the response, and a little HTML to wrap it all up!

Create A Record Of The Type You Want In Your Table

First, let’s pick the object we want to make a table of. In the previous post, I made a table of people and displayed their ages, names, occupations, and favorite foods. I will do the same in this example, except I will create a custom object People and create records for each person. A handy tip for creating objects is to insert a single record using the Developer Console Execute Anonymous Apex window. Run the below code after you have created an object:

[Your Object] new1 = new [Your Object]();
insert new1;

This will create one record of your object. Depending on if it has any required fields, you may need to initially populate a few of the fields by passing them in as a parameters:

[Your Object] new1 = new [Your Object](Required_Field__c = RequiredData);
insert new1;

Something like that would do the trick. Once you have a single record, query for it and get the object Id:

Select Id FROM [Your Object]

This will return some 15/18 digit number. Take only the first 3 characters and paste them after the .com/ in your org’s URL to go to the List View for that page. From there, it is much easier to visualize and manage the data:

List View For Records

There’s my first Person! The URL changed because I created a List View to see the record, but putting ‘a00’ after the .com/ in my org brought me to this page.

Create Apex Methods To Get Data

Once my records are created, I am going to create an Apex Class that will call them and return them as a List. You could use any iterable object to do this, but List is going to make the most sense in this example. Just like with LWC components, you can use Visual Studio’s Command Palette to create an Apex class. Follow the instructions in the linked post, but use the command ‘SFDX: Create Apex Class’ instead of creating an LWC. Here is my class to get the records I just created:

public with sharing class GetPeople {

    @AuraEnabled
    public static List<People__c> GetPeople() {
        List<People__c> peopleRecords = [Select Id, Name__c, Age__c, Occupation__c, Favorite_Food__c FROM People__c];
        return peopleRecords;
    }

}

This is pretty simple! Notice the @AuraEnabled tag above the method. This allows our LWC component to call this method.

Create LWC Component

In the Javascript file for our sortedTable component, let’s call this method instead of hard-coding the data. First, import the Apex method by putting this at the top of your JS file:

import getPeopleApex from "@salesforce/apex/GetPeople.GetPeople";
(in your specific example)
import [your function name] from "@salesforce/apex/[Apex Class].[Apex Method]";

Then, create a constructor in your JS and call the method you imported. Assign the result of the call to the variable People:

   @track People;

    constructor(){
        super();
        getPeopleApex().then(result => {this.People = result})
    }

Notice the .then syntax for this method. This is a concept known as a Promise in Javascript. What it means is the result is only available after the call to the Apex method resolves. So you do not have to manage when that is, wrapping up your assignment in the paranthesis after .then tells the Javascript to wait until that call is finished, then do something with the result. result => is defining something to do with the result returned, which is why the actual assignment, this.People = result is in curly brackets. And that’s it!

Let’s change the HTML file to look at these new fields:

                <template for:each={People} for:item="person">
                    <tr key={person.Id}>                
                        <td key={person.Id}>                                
                            {person.Name__c}
                        </td>
                        <td key={person.Id}>
                            {person.Age__c}
                        </td>
                        <td key={person.Id}>
                            {person.Occupation__c}
                        </td>
                        <td key={person.Id}>
                            {person.Favorite_Food__c}
                        </td> 
                    </tr>                                                                     
                </template>

And our table is now based on records:

Lightning Web Component Table Made From Salesforce Records

Now that we covered how to create an LWC table from records, why not try refining our table?