One Page, Two Dynamic Data Blocks

Shared by Tim Seo, Valley Real Life
one year ago

14.2 Administration / Finance, General, Operations Intermediate

Prerequisites:

Be familiar with Page Parameters
Be familiar with how to configure a Dynamic Data Block
Be familiar with SQL

What

This recipe showcases the possibility of using two Dynamic Data Blocks in one page, rather than having two separate pages with one Dynamic Data Block each.

Why

I am not saying that we should apply this to all scenarios where currently there are two related pages with a Dynamic Data Block each.

I am sharing this recipe because, in some situations, it might be more helpful to have a single page with two Dynamic Data Blocks.

For example, situations in which a user might prefer to keep open the set of results from the first Dynamic Data Block, and then dynamically drill-down into various rows of this first Dynamic Data Block, without navigating to a child page.

How (general concept)

This is a 12-min 27-sec Youtube video, for those who might prefer a video format.

In a Page, configure three blocks:

Page Parameter Block

This Block will contain Page Parameter(s) necessary for the first Dynamic Data Block to run its SQL query

Dynamic Data Block

Any columns referenced in the WHERE clause of this Block's SQL query must also be included on the SELECT clause.
In the Selection URL configuration of this Dynamic Data Block, (1) re-build the URL of the current page, (2) include the PageParameters that were necessary for this Dynamic Data Block's SQL Query, and (3) include the PageParameters that will be necessary for the second Dynamic Data Block's SQL Query

Dynamic Data Block

How (specific example, step-by-step)

You can follow this example on the Rock Demo Site. (Just in case the demo config changes: As of 22-DEC-2023, the demo Site has a GroupType called 'Small Group' and this GroupType has a Group Attribute called 'Topic', which is a Defined Value). Also, if you're following along these steps, and you notice there's a setting I don't mention, it's safe to assume I kept the default settings for that unmentioned setting.

With all those disclaimers out of the way, let's say you want to show a filtered list of Groups on the first Dynamic Data Block, and you want to show a list of Group Members on the second Dynamic Data Block.

Let's also say that the first Dynamic Data Block must show a list of Groups (of GroupType = 'Small Group') filtered by a Group Attribute called 'Topic'.

Steps:

Configure a page where you want to do this. You could choose any Layout you want, but for me, I chose the 'Full Width' Layout.
In the Main Zone of this new page, add a Page Parameter Filter Block.
Let's open the Block Configurations for this Page Parameter Filter Block.
You can do whatever you see fit for the Block Title Settings and the Filter Settings, but in the Filters, we'll add whatever we want to parametrize in the SQL Query of the first Dynamic Data Block.
In the context of this example scenario, we will have one Filter for 'Small Group Topic'
I chose to name it "Small Group Topic" and i chose the key to be "topic".
When parametrizing things, I like choosing the 'Single-Select' Field Type and use SQL for the Values.
This is the SQL Query I used in this example:
```
SELECT dv.[Guid] AS 'Value', dv.[Value] AS 'Text' FROM [DefinedValue] dv WHERE dv.DefinedTypeId = 52;
```
In Section A of this page, add a Dynamic Data Block. I named it "Dynamic Data 1" because I know there will be two of these in this page.
Let's open the Block Configurations for this Dynamic Data Block.
Since we want to show a list of Groups filtered by Group Attribute, we will create a Lava Variable and store the Page Parameter, 'topic' (it's the key that we chose in Step#4, and it's case-sensitive), and we'll use this Lava Variable in the WHERE clause of our SQL Query afterwards.
```
 assign var_topic = PageParameter.topic | SanitizeSql 
```
Because our WHERE clause will have a variable, and because this variable begins as blank, the SQL query will err.
In order to prevent that error, we can wrap our whole SQL Query inside an IF statement, so that the SQL query will only run if our variable is not blank.
```
 if var_topic != empty 
    OUR-SQL-QUERY-WILL-BE-HERE
 endif
```
This is what the Lava and SQL look like, you can copy+paste this (or get adapt it to your needs) into the Query field:
If the code embedded below does not show line numbers, refresh this page and the formatting will be fixed.Notice that GroupId and var1 are included in the Query but they are also hidden from view.

Notice also that var1 is both in the WHERE clause and also the SELECT clause; (1) It's in the WHERE clause because that's how we filter our results, and (2) it's in the SELECT clause because that's how we will be able to pass this value into the Selection URL field later.

The Selection URL is the URL to which your user will be redirected when they click one of the rows in this Dynamic Data Block. Rather than using the Selection URL to navigate to a different page, we will navigate to the current page, and add some Page Parameter(s) that will enable the second Dynamic Data Block.

It's not included in my screenshots, but the current page was page/2760. So our Selection URL will begin with this page route:
```
 https://rock.rocksolidchurchdemo.com/page/2760
```
If we redirect to this page, and the PageParameter.topic is empty, the first Dynamic Data Block will not show anything. We can instead create the illusion that the first Dynamic Data Block remained open by simply including the PageParameter.topic in this Selection URL, like this:
```
 https://rock.rocksolidchurchdemo.com/page/2760?topic={var1}
```
We must also include the Page Parameter that will enable the second Dynamic Data Block. We haven't configured this yet, but the second Dynamic Data Block will show a list of Group Members, and in order to query a list of Group Members, we need GroupId; therefore we will include the GroupId in this Selection URL like this:
```
 https://rock.rocksolidchurchdemo.com/page/2760?topic={var1}&GroupId={GroupId}
```
This is what it looks like so far:
Before we configure the second Dynamic Data Block, let's also make a change in the Block Properties of this first Dynamic Data Block.
From the Block Properties, go to the 'Advanced Settings' tab.
Then add these snippets to the Pre-HTML and Post-HTML fields:
This is the Pre-HTML: And this is the Post-HTML: You might need to reload the page after saving, but the end-result is that your first Dynamic Data Block will look like a left sidebar like this:
In Section A of this page, let's add the second Dynamic Data Block. I named it "Dynamic Data 2" because I am very skilled at naming things.
In the Block Configurations of this second Dynamic Data Block, we will do a similar Query Logic; the only difference being that now we are using the GroupId from the Page Parameter, and querying Group Members.
This is the Query that I used:
If the code embedded below does not show line numbers, refresh this page and the formatting will be fixed.
And just like we did with the first Dynamic Data Block, we can add Pre-HTML and Post-HTML in the 'Advanced Settings' of the Block Properties:
This is the Pre-HTML: And this is the Post-HTML:

And that's all the steps! I didn't plan for it to be 10 steps, but I like the fact it's an even 10

Gotchas

The keen among you may have noticed that, if you followed my steps, the "Reset Filters" button in the Page Parameter Filter Block will clear the first Dynamic Data Block, but not the second one.

That's because, for some reason, the Page Parameter Filter Block's "Reset Filters" button can only clear the Filters that have been added under the Filters of its Block Configuration. Therefore, even if we are not expecting our users to use the Page Parameter Filter Block to add/remove GroupId values, we should add GroupId as one of the Filters in the Page Parameter Filter Block.