no information when nothing is sold

henky · Jul 24, 2000

Hello All Firstly I have to tell that I am from Holland and that English isn't my main language, so you know why some sentences or words aren't clear to you. (all apologies for that

I am using CR v8.0 (for 2 months now.) I have to make a report about what we sold over the last 30 days. In the report 4 different tables are being used.(all dbf format which are linked right). When there is something sold #Table1 and #Table2 will be used. #Table1 will be used for showing some information regarding that title, next to this #Table2 is used to give the total price and sum of how many products are being sold of that title. If nothing is sold of a title a problem appears because the information in Nr.2 (see attachment) of the title will not be shown when nothing has been sold! I wish I could see that if there isn't sold anything this would result at a title: The name of the title from #Table1 and "0" in the row "how many products are sold of this title" and a $ 0.00 in the row "what is the profit of this title". It seems like this Nr.1 #Table1 #Table1 301098 PV 7.1a 18.0% 301123 PV 7.1b 7.0% 301176 PV 7.1c 10.0% 301177 PV 7.1d 6.0% 301178 PV 7.5a 10.0% 301179 PV 7.5b 8.0% The 6 lines with information (T1) is only about #Table1. These 6 Titles must be shown in T2 but if there is nothing sold of 301176, 301177 and 301178 these titles will not be shown. How can I get the T2 the same like T3 (T3 is handmade now) Nr.2 #Table1 #Table1 #Table2 #Table2 301098 PV 7.1a 18.0% 13 $ 1,072.50 301123 PV 7.1b 7.0% 19 $ 1,057.12 301179 PV 7.5b 8.0% 11 $ 775.28 It must be like this way (T3 is handmade now) (T3 is handmade now) Nr.3 301098 PV 7.1a 18.0% 13 $ 1,072.50 301123 PV 7.1b 7.0% 19 $ 1,057.12 301176 PV 7.1c 10.0% 0 $ 0.00 301177 PV 7.1d 6.0% 0 $ 0.00 301178 PV 7.5a 10.0% 0 $ 0.00 301179 PV 7.5b 8.0% 11 $ 775.28 Any help would be greatly appreciated. Henky

Ina · Jul 24, 2000

One of the main reasons why this could be happening is the logic in linking.  If you linked using Left Outer Join, it will display rows from Table 1 only if it matches the rows from Table 2.  You could make a copy of the report, change the link to equi join and see if it brings back the rows with 0 quantity and price.  Of course, this may cause some other side effects which you will have to test for. If you can't change the join, one way to remedy this is create 2 formulas, "substitute0Quantity" and "substitute0Price", with the logic to evaluate the row to gage if table2.quantity and table2.price are null or zero.  If both are 0, assign the field value of zero, otherwise, do nothing.  This will enable the field to be displayed on your report.  Place this field instead of displaying the database field directly on the report.   e.g. to substitue quantity //if the quantity and price are null If (table2.quantity = ' ' or isnull(table2.quantity)) and (table2.price = ' ' or isnull(table2.price) //then assign zero value to quantity then table2.quantity := 0 //else, do nothing else table2.quantity := table2.quantity

Ina · Jul 24, 2000

Oops!  Error in the formula.  The correct example is: e.g. to substitue quantity //if the quantity and price are null If (table2.quantity = ' ' or isnull(table2.quantity)) and (table2.price = ' ' or isnull(table2.price) //then display zero then 0 //else, do nothing else table2.quantity ;

henky · Jul 24, 2000

Hi Ina, Thanx for the helping hand (formula) I have only one problem when I check the formula on fouls I have two fouls (both the same). The cursor is standing before the two quotes (' ') and the error says A number is required here. I use Crystal Reports for quit a short time, so I don't know everything about it

but I think the format or the field is the problem Can you help me again? Henky

LindaC · Jul 24, 2000

Hi Henky,    If 'table2.quanity' is declared as a number try putting a zero instead of ' '. LindaC <a href=mailto:lcastner@co.orange.ny.us>lcastner@co.orange.ny.us</a> <a href= > </a>

henky · Jul 24, 2000

Hi LindaC, Thanks for the information. But

I already tried that and it wont work. If there is nothing sold of a title, it wont be noticed, so there is nothing standing in Table2 about that title, so there is no ZERO ((thats why Ina used that ' ' I think)) There's just nothing. Henky

LindaC · Jul 25, 2000

Hi Henky,    Have you tried a left outer join between the tables?  I am not sure if you can with .dbf files.  I am used to SQL tables.  But, the following is straight from Crystal Report Online Help (just search for 'join'): "The result set from a Left Outer join includes all the records in which the linked field value in both tables is an exact match. It also includes a row for every record in the primary (left) table for which the linked field value has no match in the lookup table." LindaC <a href=mailto:lcastner@co.orange.ny.us>lcastner@co.orange.ny.us</a> <a href= > </a>

Ina · Jul 25, 2000

Henky, Have you tried using just IsNull(table2.quantity) and leaving out table2.quantity = ' ' (or '')?  The reason for this second part is if the IsNull formula does not catch all the records for some reason. Funny thing, I just went back and checked my IsNull(date) formula that was created in version 6.  It used to display o's for date and then sort properly, but now, in version 7, this formula does not display 0's but does sort correctly. If the IsNull alone does not work, try assigning variables and see if that makes any difference. e.g numbervar qnt; //if the quantity and price are null If (isnull(table2.quantity) and isnull(table2.price)) //then assign zero to variable then qnt := 0; //else, assign value to variable else qnt := table2.quantity ;

Ina · Jul 25, 2000

Blundered again: The only way my rows are coming through from table 2 when there is no matching record from table 1 is because I DO USE left outer join. Sorry!

henky · Jul 25, 2000

LOL, I don not use SQL and that's why I cannot use left outer join in my formula. So I still have a problem. Do you know how I can show the 0 when I am using some dbf files? Henky

LindaC · Jul 25, 2000

How about creating a formula for quantity checking:        if table2.productid > table1.productid then           quanity := 0        else           quanity := table2.quanity. Then create formulas for the other fields you need.    LindaC <a href=mailto:lcastner@co.orange.ny.us>lcastner@co.orange.ny.us</a> <a href= > </a>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

no information when nothing is sold

henky

Programmer

Ina

Programmer

Ina

Programmer

henky

Programmer

LindaC

Programmer

henky

Programmer

LindaC

Programmer

Ina

Programmer

Ina

Programmer

henky

Programmer

LindaC

Programmer

Similar threads

Part and Inventory Search

Sponsor