dynamic outdated inventory list 1

[VAhorsevet]

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.

 

[JeanW]

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

 

[JeanW]

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

HTH

 

[VAhorsevet]

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

 

[JeanW]

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.