A Love Poem to Lava Shortcodes

Shared by Ben Murphy, Simple
3 years ago

Web, Administration / Finance, Communications, General Beginner

Roses are red,
Violets are blue,
Lava Shortcodes are like the swiss army knife of RockRMS
and you should use them too!

Click on the topics below to jump to that section:

Intro
TLDR
Documentation
Use Cases
Examples & Code
Final Thoughts

INTRO

Lava Shortcodes are amazing! Have you ever used them? Was it mostly just to save some coding lines on a CMS page? That seems to be the most common use of them. But did you know that they can be used anywhere that lava or HTML is available? Email templates since you can’t turn on SQL in an email template or complex report columns for non-trivial lava needs, HTML based merge documents, single line text fields like subject lines to name a few?!?!?!

TLDR

Lava Shortcodes let you cram oodles and oodles of code into a teeny tiny key word. You can use that keyword, nearly anywhere in RockRMS.

DOCUMENTATION

Before we dive into some practical uses, let’s familiarize ourselves with their basic use by checking out the already great RockRMS documentation on Shortcodes:

Lava Shortcodes RockU video overview: https://community.rockrms.com/rocku/lava/lava-shortcodes
Here’s the official Rock documentation on adding lava shortcodes: https://community.rockrms.com/Rock/BookContent/33#addinglavashortcodesinrock
Authoring Shortcodes: https://community.rockrms.com/Rock/BookContent/33#authoringshortcodes
CMS Shortcodes: https://community.rockrms.com/Rock/BookContent/14#lavashortcodes

USE CASES

The number of ways for which lava shortcodes can be utilized is nearly endless. But the most common I've found is for needs within email templates, merge template documents, and other fields such as workflow email send subject lines and the Inline Shortcode tag type makes the most sense to use. They are also typically easier to develop.

The great thing about shortcodes is that you can enable lava commands that are applicable for your needs. In the First Time Givers email communication and merge template letter example, getting some of the information like Gift Amount and First Time Gift Date were non-trivial to pull with basic lava, so we enabled the SQL Lava Command in the shortcode to quickly pull this data.

When using a shortcode in an email template (you can’t turn on SQL runs in an email template), workflow, or merge template document there is typically an entity available at the time the New Communication or Create Merge Template button is selected from a grid. A lot of times Person Id or Group Id (or anything else) is passed over. So writing a simple lava

{% sql %}{% endsql %}

query allows you to quickly tie complex tables together and smash this all into a single line lava shortcode which will pull that person’s or group member’s results from the query at time of rendering the email or merge document.

EXAMPLES and CODE

Here are just a few examples how useful lava shortcodes can be to get complex things into areas of RockRMS you've likely struggled with before:

Email wizard template:

Merge template (Word doc):

Email wizard template with full sql table embedded:

Tag Name: FirstGiftAmount

Tag Type: Inline

Tag Description: Used for inserting SQL into the First Time Gift Email Communication Template.

Documentation: Add

{[ FirstGiftAmount ]}

to an email so that SQL can return First Gift Amount in $. This SQL assumes the communication or report column has a Person Id field.

Enabled Lava Commands: SQL

Shortcode Markup:

{% sql %}
    -- Find 2nd Time Givers, Grouped by Household
    -- Reason we are Grouped by Household is to avoid identifying the spouse on their second gift, when it's the households 20th gift.
    
    -- Query in english
    -- > Searching the past 18 months, Get me
    -- > the 2nd gift made per family. Pull the 1st adult male from the household,
    -- > or any adult female if no male. Only show
    -- > me the seconds gifts that have occurred in the past month
    -- > You can change t.GivingGroupId to t.AuthorizedFamilyId if you want it to work based on families rather than giving groups - effectively disregarding whether or not they have combined giving turned on
    
    -- You can find 3rd time givers by changing RowNumber = 2 to RowNumber = 3
    
    
    DECLARE @AdultRoleId int = ( SELECT TOP 1 CAST([Id] as varchar)  FROM [GroupTypeRole] WHERE [Guid] = '2639f9a5-2aae-4e48-a8c3-4ffe86681e42' )
    
    
    SELECT TOP 1
        --Giver.Id [GiverId],
        --Person.Id [Id],
        --Giver.FirstName,
        --Giver.LastName,
        --Giver.Email,
        b.Amount [FirstGiftAmount]
        --b.PublicName [AccountName],
        --CAST (b.TransactionDateTime AS DATE) [TransactionDate]
        --av.Value [Merge Template Run Date]
    FROM (
      SELECT
      a.*,
      Head.Id [HeadPersonId]
        FROM (
        SELECT
        t.*,
        fa.PublicName,
        ROW_NUMBER() OVER (PARTITION BY t.AuthorizedFamilyId ORDER BY t.TransactionDateTime) AS RowNumber
          FROM AnalyticsFactFinancialTransaction t
        INNER JOIN FinancialAccount fa
          ON fa.Id = t.AccountId
        -- remove children
          INNER JOIN PersonAlias pa
            ON pa.Id = t.AuthorizedPersonAliasId
          INNER JOIN GroupMember
            ON GroupMember.PersonId = pa.PersonId AND GroupMember.GroupRoleId = @AdultRoleId
          INNER JOIN [Group] ON [Group].Id = GroupMember.GroupId AND [Group].GroupTypeId = 10
        WHERE --t.TransactionDateTime > DATEADD(month, -18, GETDATE())
            t.EntityTypeName != 'Registration'
            -- AND fa.CampusId = 4 -- used for manually restricting to a specific campus
            -- AND fa.ParentAccountId = 5 -- used for manually restricting to a specific top-level account
      ) [a]
      OUTER APPLY (
          SELECT TOP 1 p.[Id]
            FROM [Person] p
            INNER JOIN [GroupMember] gm ON gm.[PersonId] = p.[Id]
          WHERE
            gm.GroupRoleId = @AdultRoleId
            AND gm.[GroupId] = a.AuthorizedFamilyId
          ORDER BY p.[Gender]
      ) [Head]
      WHERE a.RowNumber = 1
      --AND a.TransactionDateTime >= CAST (@StartDate AS DATE)
      --AND a.TransactionDateTime < DATEADD (day, 1,CAST(CONVERT(datetimeoffset,@EndDate,127) AS DATE))
    ) b
    JOIN Person
      ON Person.Id = b.HeadPersonId
    JOIN PersonAlias [GiverAlias]
      ON b.AuthorizedPersonAliasId = GiverAlias.Id
    JOIN Person [Giver]
      ON Giver.Id = GiverAlias.PersonId
    LEFT JOIN AttributeValue av
      ON av.EntityId = Person.Id
      AND av.AttributeId = 6000 --First Time Gift Letter Sent Date
    WHERE av.Value is null
    AND {{ Person.Id }} = Person.Id
        -- ignores potential First Time Givers that already have had a letter sent to them
        -- AND Address.Street1 is not null
    --ORDER BY b.TransactionDateTime desc -- Person.LastName
{% endsql %}

{% for item in results %}
    {{ item.FirstGiftAmount | FormatAsCurrency | Trim %}.
{% endfor %}

IMPORTANT

When embedding the lava shortcode in a Wizard based Email Template you must wrap the lava shortcode in the raw tag in order to resolve correctly:

 {% raw %}{[ FirstGiftDate ]}{% endraw %}

Tag Name: FirstGiftDate

Tag Type: Inline

Tag Description: Used for inserting SQL into the First Time Gift Email Template.

Documentation: Add

{[ FirstGiftDate ]}

to an email so that SQL can return First Gift Date. This SQL assumes the communication or report column has a Person Id field.

Enabled Lava Commands: SQL

Shortcode Markup:

{% sql %}
    -- Find 2nd Time Givers, Grouped by Household
    -- Reason we are Grouped by Household is to avoid identifying the spouse on their second gift, when it's the households 20th gift.
    
    -- Query in english
    -- > Searching the past 18 months, Get me
    -- > the 2nd gift made per family. Pull the 1st adult male from the household,
    -- > or any adult female if no male. Only show
    -- > me the seconds gifts that have occurred in the past month
    -- > You can change t.GivingGroupId to t.AuthorizedFamilyId if you want it to work based on families rather than giving groups - effectively disregarding whether or not they have combined giving turned on
    
    -- You can find 3rd time givers by changing RowNumber = 2 to RowNumber = 3
    
    
    DECLARE @AdultRoleId int = ( SELECT TOP 1 CAST([Id] as varchar)  FROM [GroupTypeRole] WHERE [Guid] = '2639f9a5-2aae-4e48-a8c3-4ffe86681e42' )
    
    
    SELECT TOP 1
        CAST (b.TransactionDateTime AS DATE) [FirstGiftDate]
    FROM (
      SELECT
      a.*,
      Head.Id [HeadPersonId]
        FROM (
        SELECT
        t.*,
        fa.PublicName,
        ROW_NUMBER() OVER (PARTITION BY t.AuthorizedFamilyId ORDER BY t.TransactionDateTime) AS RowNumber
          FROM AnalyticsFactFinancialTransaction t
        INNER JOIN FinancialAccount fa
          ON fa.Id = t.AccountId
        -- remove children
          INNER JOIN PersonAlias pa
            ON pa.Id = t.AuthorizedPersonAliasId
          INNER JOIN GroupMember
            ON GroupMember.PersonId = pa.PersonId AND GroupMember.GroupRoleId = @AdultRoleId
          INNER JOIN [Group] ON [Group].Id = GroupMember.GroupId AND [Group].GroupTypeId = 10
        WHERE --t.TransactionDateTime > DATEADD(month, -18, GETDATE())
            t.EntityTypeName != 'Registration'
            -- AND fa.CampusId = 4 -- used for manually restricting to a specific campus
            -- AND fa.ParentAccountId = 5 -- used for manually restricting to a specific top-level account
      ) [a]
      OUTER APPLY (
          SELECT TOP 1 p.[Id]
            FROM [Person] p
            INNER JOIN [GroupMember] gm ON gm.[PersonId] = p.[Id]
          WHERE
            gm.GroupRoleId = @AdultRoleId
            AND gm.[GroupId] = a.AuthorizedFamilyId
          ORDER BY p.[Gender]
      ) [Head]
      WHERE a.RowNumber = 1
      --AND a.TransactionDateTime >= CAST (@StartDate AS DATE)
      --AND a.TransactionDateTime < DATEADD (day, 1,CAST(CONVERT(datetimeoffset,@EndDate,127) AS DATE))
    ) b
    JOIN Person
      ON Person.Id = b.HeadPersonId
    JOIN PersonAlias [GiverAlias]
      ON b.AuthorizedPersonAliasId = GiverAlias.Id
    JOIN Person [Giver]
      ON Giver.Id = GiverAlias.PersonId
    LEFT JOIN AttributeValue av
      ON av.EntityId = Person.Id
      --AND av.AttributeId = 6000 --First Time Gift Letter Sent Date
    WHERE av.Value is null
    AND {{ Person.Id }} = Person.Id
{% endsql %}

{% for item in results %}
    {{ item.FirstGiftDate | Date:'MMMM d, yyyy' %}
{% endfor %}

EMAIL TEMPLATE CODE

{{ 'Global' | Attribute:'EmailHeader-One' %}

 <body>

  <div class="wrapper">

   <div class="wrapper-inner">

    <table class="main-table-first" align="center">

     <tr>

      <td class="one-column">

        <table width="100%">

         <tr>

          <td class="inner-td content" width="300">

           <!--chuch logo image goes here -->

          </td>

         </tr>

        </table>

      </td>

     </tr>

    </table><!--- End Main Table First -->

    <table class="main-table" align="center">

     <tr>

      <td class="one-column">

       <table width="100%">

        <tr>

         <td class="inner-td">

          <div class="structure-dropzone">

              <div class="dropzone">

                  <div class="component component-text" data-state="component">

             <p>Dear {% raw %}{{ Person.FirstName }}{% endraw %},</p><br>

             <p>Greetings in the GREAT and GRACIOUS name of Jesus!</p><br>

             <p>Thanks to you and your gift to {{ 'Global' | Attribute:'OrganizationName' }}, lives of children, students, adults and families are being transformed by God’s Good News found in Jesus - Here, Near and Far.</p><br>

             <p>This email and attachment are an expression of gratitude for your recent and, - according to our records - first gift to {{ 'Global' | Attribute:'OrganizationName' }} on {% raw %}{[ FirstGiftDate ]}{% endraw %} for {% raw %}{[ FirstGiftAmount ]}{% endraw %}</p><br>

             <p>We just wanted you to know that every dollar you give to this church goes to change lives!</p><br>

             <p>We know you could give your resources anywhere, so we just personally wanted to thank you for being a part of {{ 'Global' | Attribute:'OrganizationName' }} family through giving.</p><br>

             <p>On behalf of our church leadership and entire church family,</p><br><br>

             <p>Name<br>

             <!--signature image goes here --><br>

             Pastor of Stewardship</p>

           </div>

             </div>

            </div>

            <div class="structure-dropzone">

                <div class="dropzone">

                    <div class="component component-text" data-state="component">

                    <a href="mailto:email@test.com" target="_blank">email.com</a>

            </div>

           </div>

          </div>

         </td><!--- All of Content lives in here -->

        </tr>

       </table>

      </td>

     </tr><!--- End Heading, Paragraph, and Button Section -->						

    </table><!--- End Main Table -->

    {{ 'Global' | Attribute:'EmailFooter' }}

   </div><!--- End of wrapper inner -->

  </div><!--- End of wrapper -->

  <!--- prevent Gmail on iOS font size manipulation -->

  <div style="display:none; white-space:nowrap; font:15px courier; line-height:0;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </div>

 </body>

Custom Report Columns from SQL Lava Shortcode example:

Tag Name: CombinedFamilyGifts

Tag Type: Inline

Tag Description: Used for inserting SQL into a report Lava column (links on Group Id so this column must exist in the report but can be hidden).

Documentation: Add

{[ CombinedFamilyGifts ]}

into a report column. This SQL assumes the communication or report column has a Group Id field.

Enabled Lava Commands: SQL

Shortcode Markup:

{% sql %}
DECLARE @startDate DATETIME, @endDate DATETIME --, @campus varchar(40)
SET @startDate = '2021-01-01' --'{{ 'Global' | PageParameter:'startDate' }}'
SET @endDate = '2021-12-31'--'{{ 'Global' | PageParameter:'endDate' }}'
-- SET @campus = '{{ 'Global' | PageParameter:'campus' }}'

SELECT
    FORMAT(SUM(td.Amount), '$#,###.##') as TotalGiving
FROM FinancialTransaction t
	INNER JOIN FinancialTransactionDetail td
	  ON t.Id = td.TransactionId
  INNER JOIN FinancialAccount a
  		ON a.Id=td.AccountId
  	INNER JOIN Campus c
  		ON c.Id=a.CampusId
  			-- AND c.Name= 'Your Campus'
	INNER JOIN PersonAlias PA
	  ON t.AuthorizedPersonAliasId = PA.Id
	INNER JOIN Person
	  ON PA.PersonId = Person.Id
	 INNER JOIN [GroupMember] GM
	  ON GM.PersonId = PA.PersonId
	INNER JOIN [Group] G
	  ON GM.GroupId = G.Id
	  AND G.GroupTypeId = 10
	WHERE G.Id = {{ Id }}
AND	t.TransactionDateTime >= CAST(CONVERT(datetimeoffset,@startDate,127) AS DATE)
AND 
	t.TransactionDateTime < DATEADD(day, 1, CAST(CONVERT(datetimeoffset,@endDate,127) AS DATE))
{% endsql %}

{% for item in results %}
{{ item.TotalGiving }}
{% endfor %}

FINAL THOUGHTS

The sky's the limit with lava shortcode usage. Just be sure to add a very good explanation of its use in the description field. Include links to the email templates, cms pages, merge docs, reports, etc where it is being used. This will help your future self, trust me!

IF YOU LIKED THIS RECIPE

Please vote for this idea in order to have the capability to import/export RockRMS pages with blocks and content similar to how the Workflow Import/Export Power Tool does today. Contact info@sparkdevnetwork.org if you are able to financially support this idea: https://community.rockrms.com/ideas/1645/export-and-import-web-pages-in-rockrms

Download File

Recipe

A Love Poem to Lava Shortcodes

A Love Poem to Lava Shortcodes

Table of Contents

Login to add a comment...