Help

Automating a report; splitting the rich text checked boxes by time checked.

Solved
Jump to Solution
1004 10
cancel
Showing results for 
Search instead for 
Did you mean: 
LindsayRidpath
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello, 

I'm not sure which category this would fall under. I'm working on generating an automated report based on our data. I don't want it to be too long, so under one filtered category, I only want the report to pull information tied to a checked-off box that was only checked off in the last 7-14 days. (I'd like to be able to change the date as needed) The checkboxes live in our subtasks field and are rich text. 

Is there a way to sort when these boxes where checked based on date? For example, in the screenshot below, I only want to pull the checked boxes with green, not the ones with red: 

LindsayRidpath_0-1698788806191.png

Is this possible? Thanks a bunch! 

Thank you,

Lindsay 

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

good!

To get to two weeks, just extend the formula longer and longer.

IF(
  REGEX_MATCH(
    {Subtasks},
    "(^(.*\n)*.* \\((" &
    DATETIME_FORMAT(TODAY(), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -1, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -2, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -3, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -4, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -5, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -6, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -7, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -8, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -9, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -10, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -11, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -12, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -13, "days"), "YYYY/MM/DD") &
    ")\\)" & ")"
  ),
  REGEX_EXTRACT(
    {Subtasks},
    "(^(.*\n)*.* \\((" &
    DATETIME_FORMAT(TODAY(), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -1, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -2, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -3, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -4, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -5, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -6, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -7, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -8, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -9, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -10, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -11, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -12, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -13, "days"), "YYYY/MM/DD") &
    ")\\)" & ")"
  )
)

 

See Solution in Thread

10 Replies 10
stevendasilva
6 - Interface Innovator
6 - Interface Innovator

I would make a field called "checkbox last updated". Use the last updated field and only apply it to the checkbox. 

You can then use the "checkbox last updated" to filter out the specific timeframe you are looking for. 

Filter for: Checkbox checked, date is less than or greater than X. 

See the attachment for an example. 

 

Sho
11 - Venus
11 - Venus

Hi @LindsayRidpath ,

For example, in this case, if there is a date at the end of the list, filtering is possible.
However, the format needs to be consistent and ordered by date.2023-11-01 105740.png

REGEX_EXTRACT(
  {Notes},
  "(^(.*\n)*.* \\((" &
  DATETIME_FORMAT(TODAY(), "YYYY/MM/DD") & "|" &
  DATETIME_FORMAT(DATEADD(TODAY(), -1, "days"), "YYYY/MM/DD") & "|" &
  DATETIME_FORMAT(DATEADD(TODAY(), -2, "days"), "YYYY/MM/DD") & "|" &
  DATETIME_FORMAT(DATEADD(TODAY(), -3, "days"), "YYYY/MM/DD") & "|" &
  DATETIME_FORMAT(DATEADD(TODAY(), -4, "days"), "YYYY/MM/DD") & "|" &
  DATETIME_FORMAT(DATEADD(TODAY(), -5, "days"), "YYYY/MM/DD") & "|" &
  DATETIME_FORMAT(DATEADD(TODAY(), -6, "days"), "YYYY/MM/DD") &
  ")\\)" & ")"
)

stevendasilva
6 - Interface Innovator
6 - Interface Innovator

@LindsayRidpath

Can you provide the format of the rich text dates? 

Hi, @stevendasilva,

Thank you for your earlier suggestion. I'm looking for something that will extract the checked boxes after so many days. So up to this point, I haven't added dates to the rich text, only checked boxes. Is that what you're asking in your later message? 

@Sho , thank you for the formula, this might be more like what I'm looking for. Since I haven't added dates, I retroactively input a few to test the formula. Do you see why I might be getting the error? 

LindsayRidpath_0-1698807575503.png

LindsayRidpath_1-1698807710691.png

Thank you both for your help and insight! 

Sho
11 - Venus
11 - Venus

Ah, if the date was not found, there was an error.
In case of a workaround, it will look like this.

IF(
  REGEX_MATCH(
    {Notes},
    "(^(.*\n)*.* \\((" &
    DATETIME_FORMAT(TODAY(), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -1, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -2, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -3, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -4, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -5, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -6, "days"), "YYYY/MM/DD") &
    ")\\)" & ")"
  ),
  REGEX_EXTRACT(
    {Notes},
    "(^(.*\n)*.* \\((" &
    DATETIME_FORMAT(TODAY(), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -1, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -2, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -3, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -4, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -5, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -6, "days"), "YYYY/MM/DD") &
    ")\\)" & ")"
  )
)

 

Thank you Sho,

What if I put in the date? I just used the formula you provided and it turned back blank. 

LindsayRidpath_2-1698810577472.png

I'm not sure what I'm missing. Thank you for your time,

Lindsay 

Sho
11 - Venus
11 - Venus

This formula extracts up to one week before today. That is, until 10/26.
If you want to include unchecked rows, it needs to be reconsidered🤔

Ah! Ok, got it--almost! 🙂 

Sorry for the confusion, so I've updated the dates, and removed the date from the unchecked box. The unchecked box is showing up in the formula field, which I don't want. I only want the checked boxes from the last week or two. Do you know why this is? 

LindsayRidpath_0-1698811365642.png

Thanks again,

Lindsay 

Also, is there a way to expand the dates extract? One week to two weeks? 

Sho
11 - Venus
11 - Venus

good!

To get to two weeks, just extend the formula longer and longer.

IF(
  REGEX_MATCH(
    {Subtasks},
    "(^(.*\n)*.* \\((" &
    DATETIME_FORMAT(TODAY(), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -1, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -2, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -3, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -4, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -5, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -6, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -7, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -8, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -9, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -10, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -11, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -12, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -13, "days"), "YYYY/MM/DD") &
    ")\\)" & ")"
  ),
  REGEX_EXTRACT(
    {Subtasks},
    "(^(.*\n)*.* \\((" &
    DATETIME_FORMAT(TODAY(), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -1, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -2, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -3, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -4, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -5, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -6, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -7, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -8, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -9, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -10, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -11, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -12, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -13, "days"), "YYYY/MM/DD") &
    ")\\)" & ")"
  )
)