| Author |
Message |
jkwrpc
Senior Member
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
|
   Posted: 27 Jun 2007 at 5:20am |
Rick
You formula looks like this
------------------------------
NumberVar Supplier;
If not IsNull ({Table.FieldX})
Then Supplier := {Table.FieldX};
Else Supplier := Supplier;
Supplier
-----------------------------
It should look like this
----------------------------------------
NumberVar Supplier;
If not IsNull ({Table.FieldX})
Then Supplier := {Table.FieldX}
Else Supplier := Supplier;
Supplier
----------------------------------
The difference if the semi colon at the end of then part of THEN statement line. The ';' marks the end of a line of code or process so it is seeing the IF-THEN as valid but picks up the ELSE as not being a part of the entire IF-THEN-ELSE statement.
Hope it helps
John W.
|
|
Regards,
John W.
www.CustomReportWriters.net
|
 IP Logged |
|
Rick
Newbie
Joined: 14 Mar 2007
Online Status: Offline
Posts: 22
|
Posted: 27 Jun 2007 at 6:50am |
Thanks John,
The formula gives me no more errors, but it also doesn't give me any results! I.e., it results in a null-value.
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 27 Jun 2007 at 7:32am |
Taking a step back and taking into account your need to have only the 1600 records that have a 4622 record, I think there's an easier, but completely different way to do this......
1. In the Database Expert, add a second copy of the Bookings table - Crystal will ask you to give it an "Alias" so it can distinguish it from the first copy of the table. I'm going to call it "Bookings_4622". Link from the Bookings table to the Bookings_4622 table on the booking# field. Do NOT change the link options - this needs to be an inner join.
2. In the Select Expert, set a filter on {Bookings.account} = 1600 and another one on {Bookings_4622.account} = 4622.
3. Set a group on {Bookings.account}. In the group header put the invoice and supplier information. Set this to "Underlay Following".
4. In a single details section put the information from the 4622 record, leaving space for the invoice and supplier info from the group header.
-Dell
|
|
|
|
IP Logged |
|
jkwrpc
Senior Member
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
|
Posted: 27 Jun 2007 at 7:46am |
As I look at the formula it sets only a value to Supplier if there is a value other than null.
It does not seem to set a value if the field isNull, I would suggest setting a default value to Supplier for example 0 then see what happens
ie.
----------------------------
numbervar Supplier := 0;
then the rest of the formula
----------------------------
John W.
|
|
IP Logged |
|
Rick
Newbie
Joined: 14 Mar 2007
Online Status: Offline
Posts: 22
|
Posted: 28 Jun 2007 at 12:05am |
|
Still doesn't work, unfortunately. I'll have to give the report some more thought, maybe another approach is necessary. Thanks a lot for your help guys, I certainly learned some new things!
|
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 28 Jun 2007 at 11:10am |
|
I haven't read all this thread, I'm just scanning through today's questions to see the activity and caught the tail end of this one. I agree with John. The formula you listed effectively says "If Supplier is Null then return Supplier". That doesn't make any sense because it will return Null. I would remove the 'Not' from in front of IsNull() otherwise Null will return Null.
Again, I haven't read the thread so I might be totally off base. But that formula just doesn't make sense with the Not in it.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
Rick
Newbie
Joined: 14 Mar 2007
Online Status: Offline
Posts: 22
|
Posted: 29 Jun 2007 at 3:02am |
The formula with "not IsNull" results in the supplier# in the detail row for account# 1600 (where supplier# was already filled in the table in the first place) and null for account# 4622 (where the value for supplier# was null). So, no change from what was already in the table.
When using the formula without "not", it results in null for both detail rows.
Although this topic is already exceeding my novice knowlegde of CR, I am trying to catch up. It helps me a lot, I am still discovering everything...
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 29 Jun 2007 at 9:09am |
Have you tried it this way?
If not IsNull ({Table.FieldX})
Then Supplier := {Table.FieldX};
Supplier
The other thing is that you can get around using this formula at all if you follow my last suggestion to bring in a second copy of the Booking table, linked by booking# to the first copy of Booking and filtering Booking_1 on account# = 1600 and Booking_2 on account# = 4622. (There are other instructions in that post for grouping an layout.)
-Dell
|
|
|
|
IP Logged |
|
Rick
Newbie
Joined: 14 Mar 2007
Online Status: Offline
Posts: 22
|
Posted: 04 Jul 2007 at 12:27am |
I have tried the idea of using a second table, I have to check what possibilities it gives me and what the limitations are.
Anyway, I have tried to solve the matter in a more drastic way: the database has been changed so that future bookings for account# 4622 come with a supplier#. But your ideas are helpful to get the same information on past bookings. Thanks for your help!
|
|
IP Logged |
|
|
|
