Empty /Blank Fields within Crystal

[dannyb29]

Hi All,
I am trying to bring back a value from a field when a different field is NULL, but I am struggling with syntax. Can anyone help. I will try to illustrate what I mean.

Stock count table

Date/Time ProdCode ProdDescrp NoItems UserID
11/01/10 00:00:00 0001 Salted Nuts 100
11/01/10 00:00:01 0001 Salted Nuts 80
11/01/10 01:00:00 0001 Salted Nuts 80 432

Where a UserId is present a physical stock check has taken place. I have to capture the last value of a genuine movement and the figure the member of staff has entred as past of the of stock check. In this senario I would want

ProdCode ProdDescrp System Vlaue Stock Check Value (0001 Salted Nuts 80 80) These values should be set out in tabular form like the above but I think there is a problem with this page...

I have managed to figure out how to retreive the system value but i am struggling with the Stock Check Value.

I tried something like this but to no avail. This is because the value is a NULL not a value. (

If{ServiceCentreStocks.UserId} < = 0 Then
{ServiceCentreStocks.ItemCount} else 0

Many Thanks

 

[lbass]

If you are sorting by datetime ascending, I think you can use a formula like this to capture the value for the row before the non-null User ID:

previous({table.NoItems})

Then suppress the detail section using a formula like this:

isnull({table.userID))

-LB

 

[dannyb29]

Hi IBass this would normally work, but if the user has done a double stock check on the same day I need a piece of code that will allow me to select the last genuine movement as well as the stock check figure. Ill attach a file to try and demonstrate a lot better.... I hope.....

 

[dannyb29]

Hiya,
I thought it would be easier to type it out!

44
Coffee Stock Check Calculated
Group 1 - 13,100 700

Detail
No. Items User_ID
11/12/2010 22:55 13,100 413
11/12/2010 22:49 13,100 413
11/12/2010 19:09 1,150 376
11/12/2010 18:55 700
11/12/2010 18:46 2,845
11/12/2010 18:06 450
The report should show the last stock movement figure = 700
and the last Stock check = 13100.

where ever the user_id exists this is where a stock check happens,
I hope this makes sense.

 

[lbass]

You have changed the sort to descending on datetime. Anyway, can the following ever happen (where there is a quantity and no user ID for the most recent datetime)? And if so, what would you expect the report to show?

No. Items User_ID
11/12/2010 22:59 9,100
11/12/2010 22:55 13,100 413
11/12/2010 22:49 13,100 413
11/12/2010 19:09 1,150 376
11/12/2010 18:55 700
11/12/2010 18:46 2,845
11/12/2010 18:06 450

-LB

 

[dannyb29]

Hi IBass,
Yes therecould be an instance where activity has happened after the stock check. For example if there was an adhoc stock check during the day instead of an evening. The only reason I sorted it to decsending date and time is that I thought it would be easier to read the information. I realise this may not be the correct way. (

I dont need to see the detail, but I just need to see the last actual movement before the stock check and dis-regard anything after the stock check in a group header.
I hope this makes sense. )

Group 1
Coffee Calc No. Stock Check No.
700 13,100


No. Items User_ID
11/12/2010 22:59 9,100
11/12/2010 22:55 13,100 413
11/12/2010 22:49 13,100 413
11/12/2010 19:09 1,150 376
11/12/2010 18:55 700
11/12/2010 18:46 2,845
11/12/2010 18:06 450

 

[lbass]

No. Items User_ID
11/12/2010 22:59 9,100
11/12/2010 22:55 13,100 413
11/12/2010 22:54 4,500
11/12/2010 22:52 3,000
11/12/2010 22:49 13,100 413
11/12/2010 19:09 1,150 376
11/12/2010 18:55 700
11/12/2010 18:46 2,845
11/12/2010 18:06 450

Okay, how about this scenario? I'm confused by your reference to "movement". It appears you mean the number of items just prior to a check. But what if there are multiple instances as above? It might help if you explained the concept behind this. What is the report trying to tell the user?

-LB

 

[dannyb29]

Hi,
Sorry for the confusion,
All the report needs to show is the last stock check and the last number of items prior to the stock check.

Thanks

 

[lbass]

Insert a group on prodcode and then create this formula:

//{@stockchkdt}:
if not isnull({table.User_ID}) then
{table.datetime}

Create another formula {@priordt}:

if isnull({table.User_ID}) and
{table.datetime} < {@stockchkdt} then
{table.datetime}

Place this formula in the detail section:

whileprintingrecords;
numbervar stkitems;
numbervar prioritems;
if {@stockchdt} = maximum({@stockchkdt},{table.prodcode}) then
stkitems := {table.noofitems};
if {table.datetime} = maximum({@priordt},{table.prodcode}) then
prioritems := {table.noofitems};

In the group footer, add these formulas:

//{@displaystk}:
whileprintingrecords;
numbervar stkitems;

//{@displayprior}:
whileprintingrecords;
numbervar prioritems;

Add a reset formula in the group header:
whileprintingrecords;
numbervar stkitems;
numbervar prioritems;
if not inrepeatedgroupheader then (
stkitems := 0;
prioritems := 0
);

-LB

 

[dannyb29]

Hi IBass,

Thank you for your help so far.
I have created all the formulas in the formula section, but how do i add them to the:
detail
group footer and
group header?

DB

 

[lbass]

You should have created them in the field explorer->formula->new and then you just drag them into the appropriate section. You would suppress all formulas except the two in the group footer.

-LB

 

[dannyb29]

Hi Its me again. All the resluts are displaying as 0.00. I will show you my code under what you have told me to enter so we can take it from there.

//{@stockchkdt}:
if not isnull({table.User_ID}) then
{table.datetime}

//{@stockchkdt}:
if not isnull({ServiceCentreStocks.UserId}) then
{ServiceCentreStocks.StockDatetime}

------------------------------------------------------------
Create another formula {@priordt}:
if isnull({table.User_ID}) and
{table.datetime} < {@stockchkdt} then
{table.datetime}


if isnull({ServiceCentreStocks.UserId}) and
{ServiceCentreStocks.StockDatetime} < {@stockchkdt} then
{ServiceCentreStocks.StockDatetime}

------------------------------------------------------------
Place this formula in the detail section:

whileprintingrecords;
numbervar stkitems;
numbervar prioritems;
if {@stockchdt} = maximum({@stockchkdt},{table.prodcode}) then
stkitems := {table.noofitems};
if {table.datetime} = maximum({@priordt},{table.prodcode}) then
prioritems := {table.noofitems};

whileprintingrecords;
numbervar stkitems;
numbervar prioritems;
if {@stockchkdt} = maximum({@stockchkdt},{ServiceCentreStocks.ProductCode}) then
stkitems := {ServiceCentreStocks.ItemCount};
if {ServiceCentreStocks.StockDatetime} = maximum({@priordt},{ServiceCentreStocks.ProductCode}) then
prioritems := {ServiceCentreStocks.ItemCount};

This formula I called New
------------------------------------------------------------
In the group footer, add these formulas:

//{@displaystk}:
whileprintingrecords;
numbervar stkitems;

//{@displaystk}:
whileprintingrecords;
numbervar stkitems;

------------------------------------------------------------
//{@displayprior}:
whileprintingrecords;
numbervar prioritems;

//{@displayprior}:
whileprintingrecords;
numbervar prioritems;

------------------------------------------------------------
Add a reset formula in the group header:
whileprintingrecords;
numbervar stkitems;
numbervar prioritems;
if not inrepeatedgroupheader then (
stkitems := 0;
prioritems := 0
);


whileprintingrecords;
numbervar stkitems;
numbervar prioritems;
if not inrepeatedgroupheader then (
stkitems := 0;
prioritems := 0
);

this formula I called Reset

 

[lbass]

Please check the value of the two initial formulas {@stkchkdt} and {@priordt} by placing in the detail section. Do they appear with values?

Is it possible that your values are blanks instead of nulls?

Also please identify your full group structure, identifying the fields you are grouping on.

-LB

 

[dannyb29]

{@stkchkdt} shows the current date for the stock movement and {@priordt} is blank. I am assuming it is supposed to pick up the previous date to the {@stkchkdt}?

The only group I have on the report at the moment is Product Code.

DB

 

[lbass]

Please clarify whether {@priordt} has no value anywhere in the report.

Also please show samples of the data where you getting this inaccurate result--across several product groups.

-LB

 

[dannyb29]

Hi! It now works! I changed the "<" to ">" in the priordt formaula and it worked!
Thank you!

Could you tell me how to work out the difference between @priordt and @stockchdt as I have tried it in the formula section @priordt-@stockchdt and it is throwing a wobbly!

DB x

 

[lbass]

Sorry, but changing the sign makes no sense. You wanted the last date BEFORE the stock check, not after. Please take a look at the value or {@priordt} in the detail section and see how it is behaving.

To evaluate the difference between the formulas, you would need to set the maximum value to a variable in the same formula {@New}:

datetimevar maxstkdt;
datetimevar maxpriordt;
if {@stockchdt} = maximum({@stockchkdt},{table.prodcode}) then
maxstkdt := maximum({@stockchkdt},{table.prodcode});
if {table.datetime} = maximum({@priordt},{table.prodcode}) then
maxpriordt := maximum({@priordt},{table.prodcode});

In the group footer, use a formula like this:

whileprintingrecords;
datetimevar maxstkdt;
datetimevar maxpriordt;
maxstkdt-priordt;

...assuming the priordt is BEFORE the maxstkdt.

-LB

 

[dannyb29]

Another question has been raised since making this report live and I wonder if you can help.

I have added date parameteres within the stock check report to limit the data extract. The only problem with this is that if an item count has not moved within the specified date range, the calculated figure always shows as the stock check figure.
Is there a way of ignoring the data parameter for the purpose of retriving the true calculated figure?

I hope this makes sense?

 

[lbass]

No, I'm not following. Maybe you should show some sample data that illustrates the issue.

-LB

 

[dannyb29]

Parameter
Start date: 01/02/2011 00:00:00
End date: 08/02/2011 23:59:59

Coffee Calc No. Stock Check No.
13,100 13,100


No. Items User_ID
01/01/2010 22:59 9,100
01/02/2010 22:55 13,100 413

I would like the report to show 9,100 as the calculated figure, but because it is outside the parameter date it excludes it.

Is this a little clearer?