Maximum Last Sold Date for Stale Inventory Report

[ehk]

Crystal Version: 8.5
Datasource: Advantage SQL

I’m writing a Stale Inventory report that is using a parameter for Days Stale based upon a string field {Sales.DateSold}

@NoSale – if no Sales history, calculates Days Stale based upon Received Date
DateDiff ("d",{@RecToDate} ,CurrentDate )

@SoldtoDate – converts string to date
CDate(Val(Left({SALES.DATE},4)) , Val(Mid({SALES.DATE},5,2)), Val(Right({SALES.DATE},2)))

@DaysOld – when there is a Sales record, calculates Days Stale
DateDiff ("d",{@SoldtoDate} ,CurrentDate )

@Stale – show Days Stale with our without Sales History
If {@DaysOld}>0
then {@DaysOld}
else
{@NoSale}

Select Expert: {@Stale}>={?DaysOld}

Problem: When an inventory item has multiple sales records, based upon the value entered in my ?DaysOld parameter, my report is producing different data. I want only the most recent sales record considered but my report is looking at Days Stale and finding the sales record preceding that point.

Example: SKU 123456
Sold 09/24/2004 – run for DaysOld 30 used as last sold
Sold 08/20/2004 – run for DaysOld 60 used as last sold
Sold 06/19/2002 – run for DaysOld 90 used as last sold

I have attempted to incorporate a Maximum expression in both my formulas and Select Expert but previewing the report returns “Formula cannot be used because it must be evaluated later”. Next tried “whilereadingrecords” and “whileprintingrecords” but must not be using these correctly.

Thanks in advance for any suggestions.

 

[lbass]

Don't limit the dates in your record selection formula. First group on {table.SKUno} and then go to report->edit selection formula->GROUP and enter:

minimum({@stale},{table.SKUno}) >= {?days old} and
maximum({@SoldToDate},{table.SKUno}) = {@SoldToDate} and
maximum({@RecToDate},{table.SKUno}) = {@RecToDate}

This will return the most recent dates for your two date formulas and the group selection will display only those groups (SKUs) which whose smallest datediff is greater than your criterion.

-LB

 

[ehk]

Thank you for your post, LB.

In Group Select Expert:
minimum({@Stale},{ORDERS.INV_ID})>{?DaysOld}and
maximum ({@SoldtoDate},{ORDERS.INV_ID})={@SoldtoDate}and
maximum ({@RecToDate},{ORDERS.INV_ID})={@RecToDate}

In Record Select Expert:
{INVENT.LOCATION} like {?Location} and
Trim({INVENT.VENDOR}) like Trim({?Vendor}) and
Trim({INVENT.MFG}) like Trim({?Mfg})and
Trim({INVENT.CATEGORY}) like Trim({?Category})and
{INVENT.TYPE}='F' and
{INVENT.QTY}>0 and
{ORDERS.RECEIVED}<>'' and
{@Stale}>={?DaysOld} *first I left this in*

Report produced same results
at ?DaysOld 30 Last Sale 09/24/2004 Stale 32
at ?DaysOld 60 Last Sale 08/20/2004 Stale 67
at ?DaysOld 90 Last Sale 06/19/2002 Stale 860

I then removed and {@Stale}>={?DaysOld} from Record Selection. Results are:
at ?DaysOld 30 Last Sale 09/24/2004 Stale 32
at ?DaysOld 60 No Sales Stale 176
at ?DaysOld 90 No Sales Stale 176

Stale 176 represent last received date - it's not finding the previous sales and my parameter must be filtering out the maximum sale. Maybe what I'm trying to accomplish isn't logical?

 

[dgillz]

{ORDERS.RECEIVED}<>'' Is I believe your problem. Put this at the beginning of your record selection formula, and use the following syntax:

Not IsNull({ORDERS.RECEIVED})

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[ehk]

{Orders.Received} is a string - does this instruction still apply?

 

[lbass]

I'm confused. My understanding is that you want to see one record for each SKU--if there is something sold, then the most recent sale date, otherwise, the most received date. So a report should show a series of SKUs which meet the {?days old} criteria that you choose, as in, the following if {?days old} is set to 60:

SKU {@stale}
1356 62
2456 65
3589 78

Why are you displaying different parameter values? Do these reflect multiple refreshes, changing the parameter each time?

You should have a left join from the Inventory table to the sales table, and I guess an equal join from inventory to orders. Your record selection formula should not use the parameter value or any criteria on a field or formula referencing a field from the sales table. Your {@DaysOld} should probably be written:

if not (
isnull({@SoldToDate}) or
{@SoldToDate} = date(0,0,0)
) then
DateDiff ("d",{@SoldtoDate} ,CurrentDate) else 0

-LB

 

[ehk]

Thank you again for your time.

Yes, the different parameters I'm displaying represent multiple refreshes. When I run the report for DaysOld 30 I get the first result. When I run for 60 or 90 DaysOld, I get different results. My ?DaysOld parameter is to allow me to control the aging - a filter for how Stale the items are.

Part of my problem is conceptualizing the report. I have not taken into account a FIFO situation where I am checking the Quantity on Hand in my Invent.dbf against the Quantity Received in the Orders.dbf and Quantity Sold in the Sales.dbf. In the examples I've been providing, the same Qty on Hand of "2" is reported regardless of my Days Old parameter. I think I need to go back to the drawing board and think this through again.

Right now, my primary table is Orders, then Invent, then Sales - all linked by a unique Inventory ID. I have an equal join between Orders & Inventory and a left join between Invent and Sales. Have made the assumption that an item must have an Order/Received history before it's considered Stale and that the item may or may not have Sales history. Where I'm running into problems is situations where there is >1 sales record for the same Inventory ID. Must be related to the fact I'm not considering when the Quantity On Hand was created.

Received 06/01/04 1 Sold 06/24/04 1 on/hand 0
Received 08/01/04 3 Sold 08/24/04 2 on/hand 1
Received 09/01/04 2 Sold 09/21/04 1 on/hand 2

Quantity on Hand=2 but different Last Sold for each piece.

Appreciate the feedback from you both. If you can point me to some samples of inventory reporting, maybe I can improve my design...ehk

 

[lbass]

Your primary table should be inventory, I think, with a left join from inventory to sales, and an equal join from inventory to orders. I think your group should be on {Inventory.Inventory ID}, not on {Orders.Inventory ID}.

Also, do you have any other groups in your report besides on Inventory ID? Please outline the report structure.

-LB

 

[ehk]

I will try moving table order.

Group 1 = Invent.Location (Office Location ID)
Group 2 = Invent.Vendor
Group 3 = Invent.Manufacturer
Group 4 = Orders.Inv_ID (unique inventory identifier)

Looked at an old version of this report. The ?DaysOld parameter was actually based upon the last Orders.Received date. This might be easier. There will always be an Order record associated with the Inv_ID. May or may not be a Sales record.

Thinking that if I used the parameter this way, I would have the report look for inventory items that were last received on or before the ?DaysOld paramter - e.g. if the DateDifference between the maximum ({Orders.Received}, CurrentDate) is >= to ?DaysOld, then report the Inventory item, otherwise bypass it. Display Last Sold equal to the maximum {Sales.Date} if one exists or print "No Sales". Calculate Stale Days from maximum {Sales.Date} to CurrentDate or from {Orders.Received} if no sales history.

Please advise if this sounds like a logical approach. Thanks.