nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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)

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

Thanks for assistance in advance.

Accepted answers

All comments

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

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

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

Bildschirmfoto 2024-10-10 um 13.30.11.png

Jonas Schuba

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

Bildschirmfoto 2024-10-10 um 13.48.27.png

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

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

Regards

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

cloud

confluence-cloud

database