×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

dynamic outdated inventory list

dynamic outdated inventory list

dynamic outdated inventory list

(OP)
I need to find outdated items in my inventory list, but I want to do it dynamically. I want to perform a find in a script and have the date field that is searched have a calculated result inserted. I need to find items older than 12 months with respect to today's date. I have the stocking date listed with each item. Any help would be appreciated.

RE: dynamic outdated inventory list

Chances are that later you will need to find outdated items in another timeframe.

Make a calculation field(findDate)result text, indexed, along these lines:

Case(
GetAsNumber(yourItemDate) = GetAsNumber(Get(CurrentDate));"Today";
GetAsNumber(yourItemDate) = Get(CurrentDate) - 1;"Yesterday";
GetAsNumber(yourItemDate) = Get(CurrentDate) - 2;"Last " & DayName(yourItemDate);" "
)
 & "¶" &
Case(
WeekOfYear(yourItemDate) = WeekOfYear(Get(CurrentDate)) and Year(yourItemDate) = Year(Get(CurrentDate)); "This week";
WeekOfYear(yourItemDate) = WeekOfYear(Get(CurrentDate) - 7) and Year(yourItemDate) = Year(Get(CurrentDate));"Week before";" "
)
 & "¶" &
Case(
Month(yourItemDate) = Month(Get(CurrentDate)) and Year(yourItemDate) = Year(Get(CurrentDate));"Actual month";
Month(yourItemDate) = Month(Get(CurrentDate) - 7) and Year(yourItemDate) = Year(Get(CurrentDate));"Previous month";" "
)
 & "¶" &
Case(
Year(yourItemDate) = Year(Get(CurrentDate));"Current year";
Year(yourItemDate) = (Year(Get(CurrentDate)) - 1);"Year before";
"Prior to last year"
)

You have to fidle a little bit with the Month section to have it - 12 months.

Make a script (find Date) along these lines:

Set Error Capture (On)
Allow User Abort (Off)
Freeze Window
Enter Find Mode ()
Go to Layout (yourLayout)
Insert From Index (Select;yourLayout::findDate)
If
(IsEmpty(yourLayout::findDate)
Enter Browse Mode
Go to layout (Original Layout)
Halt Script
End If
Perform Find()

Tweak your script with some messages tothe user for no records found, with Get(LastError) function etc, and redirect thesystem to an appropriate layout for 1 record, more than 1 record etc.

Now you can find all the records in a timeframe;Today, Yesterday, Last(dayName), This Week, Month etc.

By activating the script, it will show you a value list with choices....

Maybe the calc is not 100 %, I didn't test it, just typed it, but you get the idea.

HTH

RE: dynamic outdated inventory list

I took several items in one Case() statement.
Maybe it's better for you to put each item in an own Case() statement.

HTH

RE: dynamic outdated inventory list

(OP)
Thank you for the response. I have tried it and it works to a point, maybe its in my calculation field? The following is my entry for that field but I am now looking forward instead of backward in dates...

Case(
GetAsNumber(Coggins_NextDate) = GetAsNumber(Get(CurrentDate));"Today";
GetAsNumber(Coggins_NextDate) = Get(CurrentDate) + 1;"Tomorrow";
GetAsNumber(Coggins_NextDate) = Get(CurrentDate) + 7;"Next Week" & DayName(Coggins_NextDate);" "
)
 & "¶" &
Case(
WeekOfYear(Coggins_NextDate) = WeekOfYear(Get(CurrentDate)) and Year(Coggins_NextDate) = Year(Get(CurrentDate)); "This Week";
WeekOfYear(Coggins_NextDate) = WeekOfYear(Get(CurrentDate) + 7) and Year(Coggins_NextDate) = Year(Get(CurrentDate));"Next Week";" "
)
 & "¶" &
Case(
Month(Coggins_NextDate) = Month(Get(CurrentDate)) and Year(Coggins_NextDate) = Year(Get(CurrentDate));"Current month";
Month(Coggins_NextDate) = Month(Get(CurrentDate) +1) and Year(Coggins_NextDate) = Year(Get(CurrentDate));"Next month";" "
)
 & "¶" &
Case(
Year(Coggins_NextDate) = Year(Get(CurrentDate));"Current Year";
Year(Coggins_NextDate) = (Year(Get(CurrentDate)) + 1);"Next Year";
" "
)

I get current month, current year and next year as selection options when the script is activated... but not today, tomorrow, next week, this month or next month...?

am I supposed to be getting them?

wbl

RE: dynamic outdated inventory list

See my second post.

The formula is tweaked to 'my' needs, hence my second post.

A Case() statement will stop evaluating when it hits the first match.

It's best for you to put each requirement in a single Case () statement and concatenate them (each line) with & "¶" &.

It will make your calc field longer, but you have to do it just once or, if you have the Advanced version, make a CF out of it.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close