File List: 20170320_My FileName.pdf 20170320_Another FileName.pdf 20170313_My FileName.pdf 20170313_Another FileName.pdf 20170306_My FileName.pdf 20170306_Another FileName.pdf 20170228_My FileName.pdf 20170227_Another FileName.pdf

I need to create a view that will return distinct file names and only return the file from the prior month end or the current month file closest to the prior month end

Given the file list above the view would display two files:

20170228_My FileName.pdf        **Month End file** 
20170306_Another FileName.pdf   **Current month file closest to month end**

To do so I will need to trim the first 9 characters from the file name.


Do those dates equal the actual created date? This will be difficult in a standard view, since you can't access the name of the file in a calculated field. But if you could use the created date you could try this:

This would get you the document that was created on the last day of the previous month using a standard view. Add 4 calculated fields:

EOM:             =DATE(YEAR(Created),MONTH(Created)+1,0)
CreatedOnEOM:    =TEXT(Created,"yyyymmdd")=TEXT(EOM,"yyyymmdd")
ShowDuringStart: =DATE(YEAR(Created),MONTH(Created)+1,1)
ShowDuringEnd:   =DATE(YEAR(Created),MONTH(Created)+2,0)  

Those calculated fields are returned as date, yes/no, date, date

The filter to see those created on the end of last month would be

CreatedOnEOM is equal to Yes
ShowDuringStart is less than or equal to [Today]
ShowDuringEnd is greater than or equal to [Today]

The first 2 fields help you decide if the file was created on the last day of the month. And to figure out last month, you have to twist things around because you can't use [Today] in a calculated field. From the files perspective, those fields give you the 'next' month, which is when you'll want to start displaying them. Clear as mud?

But that's as close as I can figure out with a standard view. I don't think you'll be able to get the first document of the current month in the same view. You could do it in another view by limiting the items returned to 1 and test for the current month with some more calculated fields.

For something like this, I'd create a wiki page, add a content editor, and use JQuery and SPServices to read the library properly, with more flexibility. Still not simple, but easier in code.

Maybe some of this date math will spark some creative ideas.

  • Is there anyway to use a sql query to filter for a veiw? – Tony Danza Mar 27 '17 at 19:24
  • Not sure, I've never done anything like that. I stick to calculated fields and CAML queries with SPServices. Sorry.... – Robin in Or Mar 27 '17 at 20:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.