Filter Dataverse View to not show completed items after a week

[tkshepley]

Is there a way to filter a dataverse view to not show items that have completed in column after a week?

Thank you,

[Jmanriquerios]

Hi @tkshepley 

There are several ways to do this, but you must be more specific to be able to tell you how to filter

[tkshepley]

I am trying to filter a gallery view with a Dataverse table that shows everything except the items that have a status that has changed to Completed after a week.   I hope this helps.

Thank you,

[whyonearth · Accepted solution]

A janky way would be to create a new column that holds a date - populate this when the status is changed to completed.

Filter the view based on the new date column like this (I just used Created on to demonstrate the filters)

Will show your Completed for the last 10 days, and all other records that are not complete.

[ivantohelpyou]

Hi @tkshepley, 

Building on the suggestion from @whyonearth to add a Completion Date column and use a filter, you can be flexible about what gets archived and when using the following approach.

In Dataverse:

• Add a Yes/No column for "Archived" (default No)
• Create a view that includes Filter by ... Archive is 'No'   // all active rows by default

In Power Automate:

• Create a flow to handle the business rule: Archive completed items after a week.

Trigger:

Recurrence at interview of 1 Day.

Actions:

• Initialize "Days until archive" = -7 as Integer // hard-coded value
• List rows from <your table>
  • Filter rows using OData: <column name> le <function> // <-- tricky part, see notes below
• Apply to Each
  • body/value from List Rows action
• Update a Row
  • Row ID = from the List Rows action, look for the unique identifier for entity instances under the name of your table. 
  • Advanced Parameters
    • Set Archive to "Yes"

Notes:

1. Your column name must be all lowercase. Find the column name in Dataverse in the Columns list for your table, under "Name." It'll be prefixed by your Publisher prefix or the default prefix for your environment. e.g. xyz123_datecompleted, even if it says "xyz124_DateCompleted" in Dataverse!) 

2. To compare a Dataverse date/time to the current date/time, first you'll need to get the current date/time using the utcNow() function. Wrap it in addDays(datetime, day offset) and then add a formatting string with formatDateTime, and you end up with this:

formatDateTime(addDays(utcNow(),variables('Days until archive')),'yyyy-MM-ddTHH:mm:ssZ')

With this approach, you'll not only get the view you want, but you can also manually remove rows from the Archive as needed.

Plus, you'll have figured out this nifty trick!  

--- 

Ivan Schneider

Model Citizen Developer (see my LinkedIn newsletter) 

[tkshepley]

@whyonearth Thank you for your help with this, that worked perfectly.  I do have one more questions maybe you can help me with.  How do I sort and filter that view in a gallery?  I have this in the gallery items, however I can not seem to get it to work.  What am I missing?   Thank you so much for your help!

SortByColumns(Filter('Support''s', Support''s (Views)'.'Active''s','Created On',SortOrder.Descending))

[whyonearth]

I have very limited experience with Canvas Apps. 

However, I don't think you need to Filter() the "Supports" table by the "Actives" view. You're already filtering your table with that View, and unless you need to further cherry-pick that table, you won't need to filter.

I conceptualize a View as a table. It's a pared down or beefed up table, depending on what you show or filter, but at the end of things it's just another... temporary sort of table. Kind of like a Query table. So, just pass that View as if it were a table. See below.

The documentation shows this format for SortByColumns():

SortByColumns( Table, ColumnName1 [, SortOrder1, ColumnName2, SortOrder2, ... ] )

I'd wager that you wish to write SortByColumns('Table (Views)'.'ViewName', "Created On", SortBy.Descending)

• Table = 'Table (Views)'.'ViewName'
• ColumnName1 = "Created On"
• SortOrder1 = SortBy.Descending

My syntax is likely wrong (not kidding, I use Canvas and PowerFX as little as possible). First thing I notice is that you might need to use single quotes around Created On instead of doubles.

Let me know how this goes