When a Qty equals zero, display that date and time

[Former Member]

Post Author: Aman

CA Forum: Crystal Reports

Hello everyone,

I have table called INVENTORY table which contains the following tables; ItemNUM, ItemDescrip, QTY OH (qty on-hand), DateItemLastUsed, etc...

Now, I need to setup a field which displays a date when a QTY went to zero or equaled zero.

I tried many formulas but nothing seems to work properly.

Can someone please help?

Thank you in advance.

[Former Member]

Post Author: Aman

CA Forum: Crystal Reports

Everyone thank you for your support and help. I appreciate the all of the reponses and suggestions I recieved.

However, below is the formula what I tried and I believe it does work because it's displaying date and time. As of right now, I believe it is correct; however, I am running tests with each item number to be sure. If any thing changes I will post it.

If {INVENTORY.QTYOH} = 0 then

ToText({HISTORY.TRANSDATE}) else ""

Here is the formula if someone else needs it.

Thank you yangster and JKitz

[Former Member]

Post Author: JKitz

CA Forum: Crystal Reports

There are many ways we can go about this but it this is a transactional table with a CurrentQOH field then you can do this: Create 3 Formulas: 1) Place in Report or Group Header (If grouped by Item Number)whileprintingrecords;DateVar ZeroDate := Date(1900,01,01) 2) Place in Detailswhileprintingrecords;DateVar ZeroDate;If CurrentQOH <= 0 then ZeroDate := TransDate 3) Place in FooterwhileprintingrecordsDateVar ZeroDate Good Luck. E-mail me at jkitzerow@tornier.com if you need anymore assistance or clarification on this

[Former Member]

Post Author: yangster

CA Forum: Crystal Reports

No you definately need the transdate. You are essentially counting down your supply and are trying to narrow down when you ended up at 0.So let me give you a basic scenirio which should be similiar to your situation.On Jan 1 you have 10 toys.On Jan 2 you sell 5.On Jan 3 you sell 3.On Jan 4 you sell 2.so your transaction would show you on each day you have a declining inventory and by creating a running total you will see how it the total on hand goes down by x amount. And by using a variable you can set the date to be equal if/when the runningtotal reaches 0.Is this how your transaction table is setup or is this information not available?If it isn't then you are going to have a heck of a time trying to figure out when your on hand quantity reaches 0.

[Former Member]

Post Author: Aman

CA Forum: Crystal Reports

Thank you for your response Yangster.

I'm using CR 9

for example:

Partnumber QTY O/H DateQtyWentToZero

1111-121212 0 (What date this qty went to zero)

1212-111111 0 (What date this qty went to zero)

1111-131313 0 (What date this qty went to zero)

We do have TransDate in this table to display TransactionDate for each transaction. I tried using it but it displays a date for every transaction and really does not have much to do with when the inventory level reaches zero. Is there a way I can have the system pull the date from the database a date when each item qty goes to zero?

Thanks in advance for your help.

[Former Member]

Post Author: yangster

CA Forum: Crystal Reports

sounds like you need to create a variable based on a running totalhard to tell for sure unless you supply some sample data and what version of the program you are using