Record selection - dual qualifiers when or while

[Luego]

I'm using Crystal Reports 9 Pro.

I have a series of ticket numbers which track units of 100 pieces.
We have a series of "bins" in our warehouse which holds the units after production and before shipping. The kicker is some units will never see a bin and be shipped directly from the line, or we will farm out the work and the unit will go directly from recieving to a bin....or from recieving to shipping.

I'm trying to track only the units moving from production (Staging) to a Bin#.

Our software tracks unit movement by adjusting up when a unit enters the bin and adjusting down when it exits a bin (or stage).

My current statement is:

( QTY > 0 and BIN like ["1*", "2*", "3*"] )

- that mostly works but some units don't seem to appear.

I tried the following statement but it came back blank:

( QTY > 0 and BIN like ["1*", "2*", "3*"] ) and
( QTY < 0 and BIN = "Staging" )

In common english I'd like to have a statement that:

Show Ticket # when Quantity is positive and current Bin is a number and when Quantity is negative and Bin is Staging.

Meaning that the report will display ticket 1023 if Staging is adjusted down by 100 qty (-100)and #Bin is adjusted up by 100 qty (+100).
But ticket 1024 will not display when Staging is adjusted down by 100 qty and Shipping is adjusted up by 100 qty.

Any ideas on how to make that work?


Note: the different fields are all in the same table so I can't do anything funky with relationships, and I'm doubtful of a subreport's effectiveness.

 

[lbass]

Your record selection formula should be:

(
QTY > 0 and
BIN like ["1*", "2*", "3*"]
) or
(
QTY < 0 and
BIN = "Staging"
)

Beyond that, I'm not sure I follow.

-LB

 

[Luego]

I apologize for not being clearer.
That does give me tickets on both results, however...

My parameter date is 3/1/06 to 3/31/06

Ticket 96074 went to bin 530 from Staging on 1/1/06
It moved to bin 540 on 3/6/06.
- It appears going +200 in bin 540

Ticket 131402 went from Staging on 3/30/06
It was Shipped on 3/30/06
- It appears leaving -125 Staging

Ticket 134720 went to bin 114 from Staging on 3/30/06
- It appears leaving -508 Staging
- It appears going +508 in bin 114


I am trying to get only ticket 134720 to appear.
First Solution: I can get it to appear with both +508 and -508 ("dual records") and perform a distinct count.
Second Solution: I suppress the display of the -508 record, showing only the +508 record.

 

[lbass]

Instead of giving a narrative description, please show sample detail records for the above scenario.

-LB

 

[Luego]

My bad.

Ticket#  Bin       Transaction Date  Qty 

134428   437E      3/30/06           300
134428   Staging   3/30/06          (300)
135064   Staging   3/30/06          (521)
136044   330W      3/30/06           300

 

[Luego]

Sorry about these two posts - I thought I could edit after posting.



Using
with date range 3/1/06 to 3/31/06
sorted by ticket#

( ( {INVTRAN.QTY} > 0 and 
    {INVTRAN.BIN} like ["1*", "2*", "3*", "4*", "5*"] ) or
(   {INVTRAN.BIN} < 0 and 
    {INVTRAN.BIN} = "Staging" ) ) and
    {INVTRAN.DATE} in {?Date}

Results in (the above post reposted here)

Ticket#  Bin       Transaction Date  Qty

134428   437E      3/30/06           300
134428   Staging   3/30/06          (300)
135064   Staging   3/30/06          (521)
136044   330W      3/30/06           300

At worst, I am trying to just get

Ticket#  Bin       Transaction Date  Qty

134428   437E      3/30/06           300
134428   Staging   3/30/06          (300)

At best, I am trying to get

Ticket#  Bin       Transaction Date  Qty

134428   437E      3/30/06           300

If I use

( {INVTRAN.QTY} > 0 and 
  {INVTRAN.BIN} like ["1*", "2*", "3*"] )

I will get

Ticket#  Bin       Transaction Date  Qty

134428   437E      3/30/06           300
136044   330W      3/30/06           300

But 136044 is a bogus result for what I'm trying to display.

 

[lbass]

First, use the record selection formula that I suggested earlier, that has the two clauses connected by an "or".

Am I correct that the displayed ticket must always have a corresponding staging record? Create two formulas:

//{@bin} - this ensures a record of each type, staging and number:
if val({table.bin}) <> 0 then 1 else
if {table.bin} = "Staging" then 1000 else 0

//{@qty} - this will be used to ensure that the two records total 0:

if val({table.bin}) <> 0 then {table.qty} else
if {table.bin} = "Staging" then {table.qty} else 0

Insert a group on {table.ticket#} and then go to report->selection formula->GROUP and enter:

sum({@bin},{table.ticket#}) = 1001 and
sum({@qty},{table.ticket#}) = 0

Not sure I've totally followed your requirements...

-LB

 

[Luego]

Am I correct that the displayed ticket must always have a corresponding staging record?

Yes.


Thanks. I'll try those formulas out and let you know.

 

[Luego]

Thank you for the suggestions. It looks great and I'll definately remember this for future projects. There's one slight problem though.

Here's what I did.

I had to do this formula since the #-ed bins are both in triple digit format as well as triple digit plus compass initials (202, 541, 100N, 303W, etc).

BinNum
if {INVTRAN.BIN#} like ["1*", "2*", "3*", "4*", "5*", "6*", "7*", "*8", "*9"] then
cdbl(left({INVTRAN.BIN#}, 3))

BinCompare
if {@BinNum} <> 0 then 1 else
if {@BinNum} = 0 then 1000 else 0

BinQtyCompare
if {@BinNum} <> 0 then {INVTRAN.QTY} else
if {@BinNum} = 0 then {INVTRAN.QTY} else 0

Record Selection
{INVTRAN.DATE} in {?Date}

Group Selection
sum({@BinCompare},{INVTRAN.TICKET#}) = 1001 and 
sum({@BinQtyCompare},{INVTRAN.TICKET#}) = 0

The result looks great, I can hide the various sections to make it look good. The problem is my count on Ticket# and my distinct count on Ticket# are the same as original.

Original* Record Selection
( ( {INVTRAN.QTY} > 0 and
{INVTRAN.BIN#} <> ["RECEIVING", "SHIPPING", "STK SHEETS", "WIP", "STAGING"])
or
( ({INVTRAN.QTY} < 0  and
{INVTRAN.BIN#} = "STAGING") ) ) and

{INVTRAN.DATE} in {?Date}

Using the above formula earlier gave me 73 pages of single lined results with a Count=3,619 and a DistinctCount=2,450

The new method displays fine but gives me 60 pages with pairs of single lines separated by a blank line with the same counts.

Not knowing anything about group selection this makes sense as my records are only being pulled based off date, but I still need a way to count just what is displayed.

I've got two ideas:
1. Would there be a way to do a subreport linked by ticket number to contain the calculation formulas then display only the 1001 and 0 results?

2. Perhaps make a subreport pulling Staging and a subreport pulling #-ed bins then comparing them in a main report?

But both ideas appear to still run up against the fact you can't run a main select record formula based on something being calculated as the report progresses or at the end of the report.

Maybe there's a way to use sum formulas based off the Compare formulas. Give each displayed group a value of 1 and sum it at the report footer. I just need away to make any record with Bin# w/o corresponding Staging or Staging w/o Bin# to have a formula with a value of 0.

I'll post results in a bit.

 

[Luego]

I created

@GroupCount
if Sum ({@BinCompare}, {INTRAN.TICKET#}) = 1001 then
1
else 0

Unfortunately I can't summarize or count this field...

 

[Luego]

Sorry for the multiple posts. My record selection is not just the date as it may appear above. My record selection formula includes the OR statement (last code statement of the most recent longest post).

 

[Luego]

It looks like running total did it. Not yet sure how to get that into a footer so I can hide all the detail but I've at least got a number based on what is displayed.

Thanks for the formula help.

 

[lbass]

Sorry I wasn't able to respond earlier. You could use:

val({table.bin})

...with bins that have leading numbers but end in letters, which is what I thought you were working with. I'm not sure why you have so many pages and blank lines. Did you suppress unused sections and also check "suppress blank sections"?

Yes, you must use running totals with group selection--forgot to mention that, since the non-group selected records are still present in the report, just not displayed.

Glad you got it working.

-LB

 

[Luego]

Pages is normal, we are talking thousands of records anyways. The "blank line" is the footer of the ticket# group. The lines weren't mentioned as problems, merely as indicative of size and length. Once I fixed and applied the correct record selection the lengths and number of pages jived with the totals I was getting.

The report was going to replace hand written notes, and would be simpler all around if I could just open > run > print a single page w/ my total w/o the user having to make sure he just prints the last page. But, the running total is fine and doubles as a reference help to discover just where his hand written totals and my database totals are off. So it's all good. Thanks again for the help.