Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: formula for checking records Post Reply Post New Topic
Author Message
Memoli28
Groupie
Groupie
Avatar

Joined: 10 Apr 2009
Online Status: Offline
Posts: 58
Quote Memoli28 Replybullet Topic: formula for checking records
    Posted: 05 Oct 2009 at 4:51am
Hi All,
 
I need some help for the following problem:
 
i have the following data:
 
Order-Line      Sequence     TO number    Status TO number
AA-01               1                00001          picked
AA-01               2                00002          picked
AA-02               1                00003          picked
AA-02               2                00004          unpicked
AA-03               1                00005          unpicked
AA-03               2                00006          unpicked
 
As you can see,the records are per TO number,so 1 Order-line can have more than 1 record.
 
What kind of formula do i need to display the status of an ORDER-LINE in a new column?
 
So,
AA-01 should get status 'totally picked',because both TO numbers are picked, 
AA-02 should get 'partially picked',because 1 record of the 2 dont have the same TO-status ,
AA-03 should get 'unpicked, because both TO are unpicked.
 
I want to insert this in a new column,because then i can use it in a cross tab and count distinct records etc.
I have no groupings,this info is just from the details section.
 
Any help/advice is appreciated.
 
regards,
Memoli
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 05 Oct 2009 at 6:53am
I haven't done cross tabs, so I don't know how this will work, but this is what I would do.
I would add a grouping on Order-Line, and use shared variables.
in the GH a reset:
shared numbervar pickStatus :=0// neutral, nothing
""//hide the reset
 
GF, display
shared numbervar pickStatus;
 
if pickStatus = 1 then
  "Unpicked"
else
 if pickStatus = 2 then
  "Paritally Picked"
 else
  "Totally Picked"
 
Details, this is the hard one:
shared numbervar pickStatus;
local booleanvar thisPick := false; //unpicked
 
if {table.field}="picked" then thisPick := true;
 
if pickStatus = 0 then (//first record for group
  if thisPick then
    pickStatus := 3
  else
    pickStatus := 1 ;
)
 
if thisPick AND pickStatus = 1 then pickStatus :=2;    //partial
if not thisPick and pickStatus = 3 then pickStatus := 2; //partial
 
""//hide the result
 
it should work, since if the all items are picked or unpicked, they never change, only if there is a mix do they change.
 
HTH
IP IP Logged
Memoli28
Groupie
Groupie
Avatar

Joined: 10 Apr 2009
Online Status: Offline
Posts: 58
Quote Memoli28 Replybullet Posted: 05 Oct 2009 at 11:23pm
Hi Lockwelle,
 
thanks for your help.
 
But i am getting error messages in the following parts during creating the formula:
 
 
GH reset:
""//hide the reset===> dont part of the formula
 
and in the details also the same for the following part:
 
if thisPick AND pickStatus = 1 then pickStatus :=2;    //partial
if not thisPick and pickStatus = 3 then pickStatus := 2; //partial
 
""//hide the result            
 
Since i am not at work,i just simply tried it with an excelsheet source.
 
regards,
Memoli
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 06 Oct 2009 at 6:03am
for the reset, end the prior line with a semicolon. same for the detail, add a semicolon after the )
IP IP Logged
Memoli28
Groupie
Groupie
Avatar

Joined: 10 Apr 2009
Online Status: Offline
Posts: 58
Quote Memoli28 Replybullet Posted: 06 Oct 2009 at 6:57am
Hi Lockwelle,
 
i did what you said and no more errors in the formula's:
 
When i place below formula in the detail section,i dont see any results.
 
shared numbervar pickStatus;
local booleanvar thisPick := false; //unpicked
 
if {Blad1_.Status TO number}="picked" then thisPick := true;
 
if pickStatus = 0 then (//first record for group
  if thisPick then
    pickStatus := 3
  else
    pickStatus := 1 ;
);

if thisPick AND pickStatus = 1 then pickStatus :=2;    //partial
if not thisPick and pickStatus = 3 then pickStatus :=2; //partial
 
""//hide the result

Has it something to do with pickstatus 3? because i dont see pickstatus 3 defined in the group footer formula?
Also tried with removing the ""//hide the result and get some numbers,but no texts like in the group footer.
 
 
The status per Order-line is now defined in the group footer formula,
the only thing i need now is to put this status in the detail section.
 
 
Order-Line      Sequence     TO number    Status TO    Ord.-L status
AA-01               1                00001          picked             Totally picked
AA-01               2                00002          picked             Totally picked 
AA-02               1                00003          picked             Partially picked
AA-02               2                00004          unpicked         Partially picked
AA-03               1                00005          unpicked         Unpicked
AA-03               2                00006          unpicked         Unpicked
 
regards,
Memoli
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 06 Oct 2009 at 1:54pm

3 is not defined in the footer as if it is not 1 or 2, it must be 3.  I don't know if you can place the status in the detail line, as until the entire ticket has been 'read' you don't know the status, unless you want to use the Status TO field.  You could use a subreport, if you need to the know the final status of a detail row before all rows are proccessed or 'read' by CR.

The detail formula determines the number, as I think numbers are easier than words, the footer translates the number to a string.
 
HTH
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.014 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.