Skip to Content
author's profile photo Former Member
Former Member

When a Qty equals zero, display that date and time

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2007 at 02:01 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2007 at 02:30 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2007 at 05:22 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2007 at 08:21 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 05, 2007 at 05:25 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.