Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: IF isnull then maximum by order number Post Reply Post New Topic
Page  of 2 Next >>
Author Message
ajp42
Newbie
Newbie


Joined: 26 Mar 2013
Online Status: Offline
Posts: 28
Quote ajp42 Replybullet Topic: IF isnull then maximum by order number
    Posted: 27 Feb 2015 at 5:48am
Hello

I have an issue with a report bringing back blanks - I need the blanks to be replaced with the data ( transaction date ) of the rest of the order

So below is what I have tried to do - but its not recognising it

I need it to say if the (MITTRA.MTTRDT) is Null then replace with the (MITTRA.MTTRDT) of the rest of the order( where its not blank) by order number(OOHEAD.OACUOR)

I tried the below
If isnull({MITTRA.MTTRDT}) then (maximum({MITTRA.MTTRDT},{OOHEAD.OACUOR})
    Else {MITTRA.MTTRDT}


Below is the results - as you can see - some blanks on the end column

OOHEAD.OACUOR     Machine     Order Payer     MITTRA.MTTRDT
1000268910     CXVINTAGE-3GP     Y400001     20150226
1000268910     CXMAJORDEPA     Y400001     20150226
1000268910     COSTABRITAFILT     Y400001     20150226
1000268910     XX-6SPECIAL     Y400001     
1000268910     1007734     Y400001     20150226
1000268910     XX-6BRITAEXCHG     Y400001     
1000268910     XX-6TRANSPORT     Y400001     
1000268910     XX-6GRINDERPACK     Y400001     
1000268910     XX-6MACHINEPACK     Y400001     
1000268910     SERVKIT23     Y400001     20150226
1000268910     CXVINTAGE-2GP     Y400001     20150226
1000268910     CXMAJORDEPA     Y400001     20150226
1000268910     COSTABRITAFILT     Y400001     20150226


IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Feb 2015 at 10:09am
are you using your formula field or the original MITTRA.MTTRDT field?
A Crystal formula does not alter existig data fields to insert data into them.
You have to use your new formula field in the report canvas to display the result of the formula. Also note this is only displaying so if you are running other calculations off this desired output it won't alter those either.
IP IP Logged
ajp42
Newbie
Newbie


Joined: 26 Mar 2013
Online Status: Offline
Posts: 28
Quote ajp42 Replybullet Posted: 01 Mar 2015 at 5:22am
hi
Maybe I haven't explained it too well
the above formula are what I am trying to do - the formula do not work currently
Basically one order with multiple items - not all items have a date next to them as they are just charges.
So where the blanks are - I need it to replace a blank with the same date as the rest od the order
Does that make more sense
So if null then put the overall date of the order type thing
IP IP Logged
adavis
Senior Member
Senior Member


Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
Quote adavis Replybullet Posted: 02 Mar 2015 at 3:43am
I think DBlank was asking if you put your formula field in the report details, or the original MITTRA.MTTRDT field, because you use the field name in the page header.

The formula fields create an entirely new field that you need to place in the report in lieu of the database field.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 02 Mar 2015 at 4:10am
adavis is correct.
The way you are asking your question leads me to believe that you think the formula is actually doing an UPDATE to the MITTRA.MTTRDT field.
It does not. It simply creates a new field (a formula field) that gives you results based on the formula.
 
When you say your formula is not working you don't explain how.
 
The syntax and logic of it looks correct and should work. Is it resulting in an error? Odd data?
Are you placing it in the select expert? If so that is not correct- it needs to be a formula field created in the field explorer.
Drag and drop it from the field explorer next to the MITTRA.MTTRDT field and then explain what is not working about it.
IP IP Logged
ajp42
Newbie
Newbie


Joined: 26 Mar 2013
Online Status: Offline
Posts: 28
Quote ajp42 Replybullet Posted: 02 Mar 2015 at 4:18am

Hi

As per below the lines with "XX-" - do not come back the MITTRA.MTTRDT field is the last one below as a date field (backwards)
I am trying to replace the blank fields with the same date as the others - so it looks at the whole order by order number

Order     Machine     Qty     Confirmed Date
1000268910     CXVINTAGE-3GP     1.00     20150226
1000268910     CXMAJORDEPA     1.00     20150226
1000268910     COSTABRITAFILT     1.00     20150226
1000268910     XX-6SPECIAL     1.00     
1000268910     1007734     1.00     20150226
1000268910     XX-6BRITAEXCHG     1.00     
1000268910     XX-6TRANSPORT     1.00     
1000268910     XX-6GRINDERPACK     1.00     
1000268910     XX-6MACHINEPACK     1.00     
1000268910     SERVKIT23     1.00     20150226
1000268910     CXVINTAGE-2GP     1.00     20150226
1000268910     CXMAJORDEPA     1.00     20150226
1000268910     COSTABRITAFILT     1.00     20150226
1000268910     XX-6SPECIAL     1.00     
1000268910     1007734     1.00     20150226
1000268910     XX-6BRITAEXCHG     1.00     
1000268910     XX-6TRANSPORT     1.00     
1000268910     XX-6GRINDERPACK     1.00     
1000268910     XX-6MACHINEPACK     1.00     
1000268910     SERVKIT23     1.00     20150226
1000268910     MARCOECOBT10     1.00     20150226
1000268910     PURITYC500SKIT     1.00     20150226
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 02 Mar 2015 at 4:22am
is the field NULL or just an empty string?
create a test formula to debug and avoid assumptions
//Test1
isnull({MITTRA.MTTRDT})
 
place this next on the detail row
shold be a True for your "NULL" rows and False for all the rows with data...
Is this correct results?


Edited by DBlank - 02 Mar 2015 at 4:23am
IP IP Logged
ajp42
Newbie
Newbie


Joined: 26 Mar 2013
Online Status: Offline
Posts: 28
Quote ajp42 Replybullet Posted: 02 Mar 2015 at 4:26am
YEs - True for Null - False has data in the rows
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 02 Mar 2015 at 4:30am
is the Order number the OOHEAD.OACUOR field?
IP IP Logged
ajp42
Newbie
Newbie


Joined: 26 Mar 2013
Online Status: Offline
Posts: 28
Quote ajp42 Replybullet Posted: 02 Mar 2015 at 4:31am
YEs
IP IP Logged
Page  of 2 Next >>
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.035 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.