How to filter complex logics in database views?

[Jonas Schuba]

Hi,

2 issues with filters of a view in a confluence database.

1. I need a filter for date fields, which includes all entries 6 days back from today. Right now we have to set "is on or after" and "is on or before" manually every time we won't to go through this view in a weekly meeting. Is their a way to reference "today" dynamically?

2. we also need to look out for another date field following the same logic. How to do that. I just figured out to set any or all for all or no filter entries but not between single entries, which would necessary to say either date field 1 is in this range or date field 2 is in this range.

Example What I tried to achieve but couldn't

("creation date" on or after 1 week ago AND on or before today)

OR

("review date" on or after 1 week ago AND on or before today)

Thanks for assistance in advance.

[Sam Beavis]

Is there a way to have this without installing paid for add ons I wonder?

[Humashankar VJ]

Hi @Sam Beavis 

I'd say the filtering capabilities for this particular requirement are quite limited.

Regards

[Sam Beavis]

Atlassian have a lot to do to improve upon it thats for sure

[Humashankar VJ]

Hi @Jonas Schuba 

To leverage SQL queries in Confluence, installation of the third-party app, SQL for Confluence (Pro Edition) or similar one is required.

Regards

[Jonas Schuba]

In that case thanks for trying to help me. That was not my intention. I was looking for an approach using native confluence features.

[Humashankar VJ]

Hi @Jonas Schuba 

For the problem statement-2:

To create a filter that includes entries based on two different date fields, each within the last 7 days, you can use a combination of OR conditions in your filter syntax. Here's how you can achieve this in Confluence:

Using ConfiForms

For ConfiForms, you can use the following filter syntax:

Filter

(creationDate:>[today]-7 AND creationDate:<=[today]) OR (reviewDate:>[today]-7 AND reviewDate:<=[today])

Replace creationDate and reviewDate with your actual field names.

Using Table Transformer Macro

If you're using a Table Transformer macro, you can use an SQL-like query:

sql

SELECT * FROM YourTable

WHERE (DATEDIFF(day, creationDate, "today") <= 7 AND DATEDIFF(day, creationDate, "today") >= 0)

   OR (DATEDIFF(day, reviewDate, "today") <= 7 AND DATEDIFF(day, reviewDate, "today") >= 0)

Again, replace creationDate and reviewDate with your actual field names, and YourTable with your table name.

This filter displays entries where either the creation date or review date falls within the last 7 days. To adjust the time frame, simply modify the "7" in the formulas. The dynamic [today] function in ConfiForms and "today" reference in Table Transformer ensure automatic updates to the current date.

 For more complex filtering, you can add additional OR conditions to incorporate multiple date fields. This approach effectively translates to: "Show me entries where either the creation date or review date (or both) is within the last week," aligning with your desired logic.

Hope this helps - Happy to help further!!
Thank you very much and have a great one!
Warm regards

[Humashankar VJ]

Hi @Jonas Schuba 

Please find some tips for the problem statement-1:

Try to use a combination of date functions and relative date references.

Using Table Transformer Macro

If you're using a Table Transformer macro to display your data, you can use the following SQL-like query:

sql

SELECT * FROM YourTable

WHERE DATEDIFF(day, YourDateField, "today") <= 6

 AND DATEDIFF(day, YourDateField, "today") >= 0

Replace YourTable with the name of your table and YourDateField with the name of the date field you want to filter on.

Using ConfiForms

If you're using ConfiForms to manage your data, you can use the following filter syntax:

Filter

YourDateField:>[today]-6 AND YourDateField:<=[today]

Replace YourDateField with the name of your date field.

When using date-based filters, keep the following key points in consideration:

The [today] function automatically updates to reflect the current date, ensuring your filter remains current. To adjust the time frame, simply modify the "6" in the formula to the desired number of days.

For recurring weekly meetings, consider replacing [today] with [startOfWeek] to maintain consistency throughout the week.

This approach guarantees accurate and dynamic filtering, tailored to your specific needs.

To apply the filter and automate your weekly meeting updates:

In the Confluence page featuring the database view, Edit the Table Transformer or ConfiForms macro then paste the relevant formula from above into the filter or query section.

Hope this helps - Happy to help further!!
Thank you very much and have a great one!
Warm regards

[Jonas Schuba]

I'm used to work with sql queries. This option would be very handy. But where to activate the macro? Here is a fake example of what I'm looking at to insure we're on the same page

[Jonas Schuba]

I also don't find the macro within confluence pages, if this is the place where it should be. 

[Humashankar VJ]

Hi @Jonas Schuba 

Install, then configure data source profiles in app settings. Next, run queries using the SQL macro on your pages.

Regards