Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 252

# Creating an Aging View ??

I need ot create a view that displays documents by Status that are categorized by aging days. For instance a status of open for the 4 categories:  > 20 days, 11 to 20, 6 to 10 and 2 to 5.
I have created a view sorted by Status, I then created another column that I am looking to possible place a formula in but I'm stuck as to the code ?!?!?!
Thanks !
Paul
0
pratigan
• 6
• 5
• 3
• +2
1 Solution

Commented:
unfortunately, that will make the view "time sensitive" meaning it will always show that it needs a refresh.

Lets assume you have a field on the form called "OpenDate" which is a date field, created when the status is first set to "Open" and another field called "Status" that has the value "OPEN" or some other value.

There are lots of ways to code this, I do it this way because its easy to read, rather than using a realy complex @If() with multiple cases.  This escalates, each value overwriting the other until it not longer passes the test for time length, then keeps that value.

Finally, it tests to see if the document is still open, that way you can use this category within other categories and it won't produce wierd results.

Column formula:

N1 := @If( @Adjust(OpenDate; 0; 0; 2; 0; 0; 0) > @Date(@now) ; "Open 2+ Days" ; "Open 2 or fewer days");

N2 := @If( @Adjust(OpenDate; 0; 0; 5; 0; 0; 0) > @Date(@now) ; "Open 5+ Days" ; N1 );

N3 := @If( @Adjust(OpenDate; 0; 0; 10; 0; 0; 0) > @Date(@now) ; "Open 10+ Days" ; N2 );

N4 := @If( @Adjust(OpenDate; 0; 0; 20; 0; 0; 0) > @Date(@now) ; "Open 20+ Days" ; N4 );

@If( Status = "OPEN" ; N4 ; "No longer open");
0

Commented:
Cool Aj ! I have a different version as you said.  If required i could send him.
0

Commented:
Andrew, the refresh button won't show when you use
@TextToTime("Today")
@Now

;-)
0

Commented:
yea, but then the view isn't up to date unless he manually refreshes.  He does want an AGING view, so I presume that its date sensitive and he wants it to stay up to date.
0

Author Commented:
Will This show headings ??  The view should like something like this:
>20 days, then the list of documents, then the total number.
11 - 20 Days, then the list of documents, then total
6 - 10 Days, then list documents, then total
2 - 5 Days, then documents, then totals.
The Status would be categorized twistie, then sub Days listings >20,11-20, 6-10, 2-5, categorized twistie then the actual documents then a total. ???
Not sure how to set this up ??
Thanks !!
:)
0

Author Commented:
Hello Andrew,
In reviewing the formulas you have established, I think their backwards.
OpenDate is thesame as a field I have called creationDate.  Therefore to test for aging over 20 days the formula should be something like:
@If(@Adjust(@Date(@Now); 0; 0; -20; 0; 0; 0) > CreationDate) then the document is greater than 20 days old. Am I thinking this correctly ???
I'm wondering how to have all of the formulas in 1 view.  I can create 4 different view, one for each aging period, nut I wanted to have 1 view with all four different aging periods and have the periods as the twistie category ??!!
<:-|
0

Author Commented:
Ok... Here's what I have done:  I have created 4 views, 1 for each aging period.  The formula for the first 2 are:
>20 days the view selection formula is:
SELECT ((Form = "CSC Incident report") &
(@Adjust(@Date(@Now); 0; 0; -20; 0; 0; 0) > Creation_Date_1)).
The second between 11 - 20 I have the formula as:
SELECT ((Form = "CSC Incident report") &
((@Adjust(Creation_Date_1; 0; 0; 11; 0; 0) <= @Date(@Now)) &
(@Adjust(Creation_Date_1; 0; 0; 20; 0; 0) >= @Date(@Now))) .
Am I on the right track with this ???  Also, this will work, but I wanted to have them on 1 view.
?!!?!

0

Commented:
the concept is right, Pratigan.  No reason they can't be in the same view.  I'll look more closely when I'm home later tonight.  (I'm mobile right now in a hospital waiting room -- nothing critical).
0

Commented:
Not another car accident I hope!?
0

Commented:
Just killing time at the hospital.  Waiting, waiting, waiting.   My wife had an appendectomy.  I've got this cool new 3G cellular "express net" card in my laptop.  It has a tiny little antenae that nobody notices, and I have two batteries to give me nearly 6 hours of live time.  Now I'm TRULY mobile.

Pratigan, I'm sorry I haven't had time to put that into a single view.  I'll try to get to it by monday afternoon.  The info is all there, it just needs a little more polish on it to make it clear.
0

Commented:
btw: With Barb's surgery, I couldn't go as a member of the team for training, but I did take the kids to a training burn today.  Someone donated an old house that needed to be taken down, so we used it for practice:

http://www.thenorth.com/northern.nsf/html/wburn

--AP
0

Author Commented:
Great Pictures..... Looked like a nice house.
Thank you !
Paul
:-)
0

Author Commented:
Hello All,
i have resolved this question with my previous posting.  I will still be granting points however for your efforts.  I'm going to grant 50 points to Andrew for his input and time.  I hope this is acceptable.  Thank you !
Paul
:)
0

Commented:
And me ?
0

Commented:
Points reduced from 100 to 50 per request

SpideyMod
Community Support Moderator @Experts Exchange
0

Author Commented:
Arun... you are a point junky... I will be having points for you in my other posting with the 2nd notes error..
Thanks !!
:-)
0

Commented:
Thats cool....
0

## Featured Post

• 6
• 5
• 3
• +2
Tackle projects and never again get stuck behind a technical roadblock.