using a formula to display data in the page footer

[llarose]

I am trying to use a formula to print information in the report footer section using the section expert. This is a packing slip and I need to put the items that did not ship at the bottom of the page. see example below - syntax seems to be my issue.

select sd.item, sd.custitem, sd.item_desc, sd.requested_qty, sd.total_qty
from
shipment_detail sd
where
{rpt_ShipmentPackDetails.internal_shipment_num} = sd.internal_shipment_num

 

[lbass]

A formula is unlikely to be the answer. It would help to know what the selection formula is for the main report and also how you know if something has not shipped. Do you have an orders table linked to the shipment table? Please provide more information.

-LB

 

[llarose]

There are two stored procedures within the report. One pulls shipment header and the other shipment detail and container detail. How I know is from the shipment detail has two fields requested qty and total qty. If total qty is zero or less than requested qty than I know that item has not shipped or not entirely shipped.

The detail section of the report contains all the shipped items. On the report footer there is totals of items shipped and a listing of items not shipped.

Basically need another detail section but in the report footer.

 

[lbass]

I guess you could try a formula, but you might run into string limits:

Place this formula in the detail section:

whileprintingrecords;
stringvar notshipped;
if {table.totalqty} = 0 then
notshipped := notshipped & {table.item} & " " & totext({tablerequestedqty},0) & chr(13) else
if {table.totalqty} < {table.requestedqty} then
notshipped := notshipped & {table.item} & " " &
totext({table.requestedqty} - {table.totalqty},0) & chr(13) else
notshipped := notshipped;

Then in the report footer use:

whileprintingrecords;
stringvar notshipped;

Format this display formula to "can grow."

If you want not shipped amounts per invoice, then the display formula belongs in the invoice group footer, and you would need a reset formula in the group header:

whileprintingrecords;
stringvar notshipped;
if not inrepeatedgroupheader then
notshipped := "";

-LB

 

[llarose]

I apologize for my lack of knowledge as I have only had two day course in Crystal.....

Do you mean in the section expert of the detail section? Would I choose the suppress section and enter the formula there? That is what I did and got an error that formula must result in a boolean.

 

[llarose]

Ok - already figured out that is not what you mean't. I created a formula field and inserted in into the detail section. I made the field as small as possible and would rather not see it at all if possible.

The other formula field I created I inserted in the report footer and it printed the item which is what I wanted. So that is great. I will need to display other fields than just the item but that is the start that I needed for sure.

I did not know how to modify to 'can grow'.

also have not tried your step three yet but will- I didn't want you to respond to my other thread where I was lost.

 

[lbass]

Right click on the formula in the report footer->format field->common tab->check "can grow". My formula contained both the item and the quantity not shipped, so I'm not sure how you implemented this.

For the formula in the detail section, just select it->right click->format field->common tab and check "suppress".

-LB

 

[llarose]

ok - my last issue and it is a show stopper. I have the three tables that my stored procedures are pulling the data from. The problem is that the table that is running the show is the shipping container table and it only contains records that have been shipped. So I do see the partially shipped items but not the items that were not shipped at all. That is because they would not be in the shipping container table but only in the shipment detail table.

I did modify the code a bit to get it to what I needed.

whileprintingrecords;
stringvar notshipped;
if {shipment_detail.TOTAL_QTY} = 0 or ({Shipment_Detail.TOTAL_QTY} < {Shipment_Detail.REQUESTED_QTY}) then
notshipped := notshipped & " " & {Shipment_Detail.item} & " " & {Shipment_Detail.customer_item}& " " & {Shipment_Detail.item_desc} & " " &totext({Shipment_Detail.REQUESTED_QTY},0) & " " & totext({Shipment_Detail.REQUESTED_QTY} - {Shipment_Detail.TOTAL_QTY},0)&chr(13) else
notshipped:= notshipped;

How do I get it to also look at the other records? Would I have to do a select on the shipment detail table?

 

[lbass]

You would have to have a left join FROM the table that contains ALL of the records TO the table that contains only some of the records. It sounds like you are saying the shipping detail has the requested amount, so that should be the lead table with a left join TO the shipping container table, and no selection criteria on the right hand table (shipping container) (although this might depend upon how the stored procedure is designed).

-LB

 

[llarose]

I did try that approach and it did not appear to make any difference. I have been making so many changes that it is hard to keep track of.

I will try this again.

 

[llarose]

left join SHIPPING_CONTAINER sc
on sd.internal_shipment_line_num = sc.internal_shipment_line_num

where
sc.tree_unit in (select tree_unit from shipping_container
where internal_shipment_num = @INTERNAL_SHIPMENT_NUM)
;


I am not sure what the tree_unit does and that may be what is causing my issue.

 

[lbass]

The selection on tree unit is the problem. Try changing it to:

left join SHIPPING_CONTAINER sc on sd.internal_shipment_line_num = sc.internal_shipment_line_num and
sc.tree_unit in
(select tree_unit from shipping_container
where internal_shipment_num = @INTERNAL_SHIPMENT_NUM)

-LB

 

[llarose]

oh that sent it into a loop and brought back records that were not related to the internal shipment num.

I was confused as to why you would change a where to an and?

What if I took out that section?

 

[llarose]

from
SHIPMENT_DETAIL sd

left join SHIPPING_CONTAINER sc
on sd.internal_shipment_line_num = sc.internal_shipment_line_num
/*and
sc.tree_unit in (select tree_unit from shipping_container */
where sd.internal_shipment_num = @INTERNAL_SHIPMENT_NUM
;


This is what I changed it to. Now instead of getting only the item that partially shipped I get one of the items that didn't ship at all. So am on the right track for sure. Have I said how much I appreciate your helping me. I have sung your praises all over the office today for sure.

I think that something is wrong in the first formula it is almost like the concatenate of notshipped is not right.

 

[lbass]

I just don't have enough information here, but let me explain the change from the 'where' clause to the 'and' in the 'from' clause. If you make a selection on a table that is left joined, i.e., using a where clause that references the right hand table, it effectively "undoes" the left join. If instead you build the selection into the from clause by requiring the selection in the link, the left join is maintained, and you will only get records from the right hand table that meet the criterion.

I don't follow the logic of the what you were trying to do with your original approach. I guess you could try:

left join SHIPPING_CONTAINER sc on
sd.internal_shipment_line_num = sc.internal_shipment_line_num and
sc.internal_shipment_num = @INTERNAL_SHIPMENT_NUM

I didn't notice how you changed my original suggestion, but yes, you implemented it incorrectly. Try this:

whileprintingrecords;
stringvar notshipped;
if {shipment_detail.TOTAL_QTY} = 0 then
notshipped := notshipped & " " & {Shipment_Detail.item} & " " & {Shipment_Detail.customer_item}& " " & {Shipment_Detail.item_desc} & " " &totext({Shipment_Detail.REQUESTED_QTY},0) & chr(13);
if {Shipment_Detail.TOTAL_QTY} < {Shipment_Detail.REQUESTED_QTY} then
notshipped := notshipped & " " & {Shipment_Detail.item} & " " & {Shipment_Detail.customer_item}& " " & {Shipment_Detail.item_desc} & " " & totext({Shipment_Detail.REQUESTED_QTY} - {Shipment_Detail.TOTAL_QTY},0) & chr(13) else
notshipped:= notshipped;

-LB

 

[llarose]

I have corrected the formula as outlined above. It is still only printing one of three lines that should go into that not shipped section. It appears to be only the first item.

When I make the change you noted to the stored procedure using the tree section my report goes into a loop gathering records that do not meet the criteria.

This is what I have the stored procedure as.

from
SHIPMENT_DETAIL sd

left join SHIPPING_CONTAINER sc
on sd.internal_shipment_line_num = sc.internal_shipment_line_num
/*and
sc.tree_unit in (select tree_unit from shipping_container */
where sd.internal_shipment_num = @INTERNAL_SHIPMENT_NUM
;


All records show in the detail section now whether or not they been shipped. So they are appearing above in detail instead of below in the not shipped section.

 

[lbass]

First, you MUST format the display formula to "can grow". That formula is in the report footer and looks like this:

whileprintingrecords;
stringvar notshipped;

Right click on the formula->format field->common tab->"can grow".

The left join is working as it is supposed to, that is, all records from the shipping detail are showing, and only those records from the container table that were shipped. You NEED all of these records in the report in order to be able to show those that were NOT shipped.

I now think you would be better off using your original query for the main report detail section and inserting a subreport in the report footer that uses only the shipment_detail table, and which has a selection formula that builds in the following criteria:

(
{shipment_detail.TOTAL_QTY} = 0 or
{{Shipment_Detail.TOTAL_QTY} <{Shipment_Detail.REQUESTED_QTY}
)

Not sure what the selection formula is for the main report, but if there is one shipment number selected for the main report, then you would want to use this as a link to the subreport on the corresponding fields in the sd table.

-LB

 

[lbass]

PS. If you take the subreport route, you no longer need the earlier formulas I suggested.

-LB

 

[llarose]

I am pretty close now as I got two out of the three to display in the not shipped field. I can suppress them in the detail area so that isn't a problem there. Not sure why the third one is not printing. I did finally do the can grow as suggested. That seemed to help some in the display in the not shipped area.


As a last resort will go to subreport. It is almost there now hate to change direction now.

 

[llarose]

ok went to the subreport route and that worked great....

Somewhere along the line I lost my group header display. I have a group header for the data called container id that list all items within that container. I have it set up and can see the container id's if I browse the data but it will not display on the report.