Collecting and reporting on items that have a certain value in a field?


Badge +8

I have a list of items that have a due date and a completed date.  I already have logic that does the calculation, and determines whether a 10 day SLA has been met, and populates an "SLA met?"  field with a Yes/No  and an "SLA +/- Days" field (gives me a zero or negative number if the SLA was met or exceeded, and a positive number if the SLA was missed).

Now what I would like to do is schedule a workflow to run weekly and report out on the items that were completed in the previous 7 days, and whether the SLA was met for each or not and by how much.

I figure I need to use a site workflow so that I can schedule it.  I also figure I need to Query List, filter for show items when column SLA Met = Yes, and store it in a collection variable.  I can get these items into a notification, but I don't know how to limit the collection to just items that were completed in the past 7 days.


5 replies

Badge +10

you can calculate the date to get date before 7 days using below setting

203130_pastedImage_1.png

use this in query CompletionDate > dtB4SevenDays

Hope this works

Badge +4

Chris

Try the below please and see if you can filter them and put them in collection.

Calculate the date first to get the date which is 7 days less than the current date and store in a variable and use that in the filter option.

Tested this and it seems to be working. give this a try if you have not done and see how it goes.

Userlevel 5
Badge +14

CAML schema directly supports <Today> element, which has OffsetDays attribute

so something like this should return items modified within last 7 days

<Gt>
  <FieldRef Name="Modified" />
  <Value Type="DateTime">
    <Today OffsetDays='-7' />
  </Value>
</Gt>

see these examples as well

https://community.nintex.com/message/51718-re-comparing-date-value-to-todays-date-does-not-work-in-query-list 

https://community.nintex.com/message/52284-re-query-list-for-not-modified-items

Badge +8

Thanks to Sojan and Shaju, this worked well, I don't know why I didn't think of putting in a negative number there.

Userlevel 3
Badge +9

Did not know that.  I need to beef up my CAML skills.

Reply