If then else help 3

[hereigns]

Got a CR v10 pro report that I need help with...basically, the report pulls a field that contains parts from a SQL 2K database. I need the report to 0.00 any "sub" parts it finds otherwise print the cost of the part.

Example, the part below has sub parts in it with cost.
Main Part - 20.00 (tblInvoiceDetail.itemID)
part 2 - 1.00 (tblInvoiceAssemblyDetail.ItemID)
part 3 - 1.00
part 4 - 1.00

Need the report to display instead like so.
Main Part - 20.00
part 2 - 0
part 3 - 0
part 4 - 0

I added the following if-then-else statement which gives me an error - boolean required.

If ({tblInvoiceAssemblyDetail.ItemID})
then "$0.00"
else {tblInvoiceDetail.ItemID}

Help?

 

[synapsevampire]

Sorry, I'm confused.

You can just hardcode zeros if you want to ignore values.

Your if statement doesn't say what ({tblInvoiceAssemblyDetail.ItemID}) equals, hence the error.

Anyway, perhaps there's a language barrier and you mean that you want it to either show a value, or show zero.

If isnull(({tblInvoiceAssemblyDetail.ItemID}))
then "$0.00"
else {tblInvoiceDetail.ItemID}

If there isn't a subpart, then Crystal won't be returning a row, and since your 1st example shows that there are values for the subparts, why would you change them to zero?

Generall it's best to show example data and the required resultant output.

-k

 

[hereigns]

You can just hardcode zeros if you want to ignore values."

How do hardcode zeros for sub parts only?
----------------------------------
"Anyway, perhaps there's a language barrier and you mean
that you want it to either show a value, or show zero."

What I meant was...if the field equals tblInvoiceAssemblyDetail.ItemID (which in all cases is a sub part) then display. $0.00
----------------------------------
"If there isn't a subpart, then Crystal won't be returning a row, and since your 1st example shows that there are values for the subparts, why would you change them to zero?"

For the purposes of this report we need to know ALL part numbers but not the price of the sub parts.
----------------------------------
Regarding showing example data...I tried. Here's another attempt.

ItemID Cost
MIT-1bA 1,267.00
*MIT-MC2 89.00 (sub part)
*MIT-MC4 54.00 (sub part)

ACT-400 48.50

PER-W75 801.54

----- What I want it to look like ------
ItemID Cost
MIT-1bA 1,267.00
*MIT-MC2 0.00 (sub part)
*MIT-MC4 0.00 (sub part)

ACT-400 48.50

PER-W75 801.54

 

[Turkbear]

Hi,
If what field equals tblInvoiceAssemblyDetail.ItemID ?

If you want them all, but want to display 0.00 as the price for the sub parts,use a supress formula on the Cost field:

Not IsNull(tblInvoiceAssemblyDetail.ItemID )

Be aware, however, that this is a display setting only, so the actual cost will be included if you summarize that field...
The are ways to avoid that, if you need to..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Create a formula of:

0

Place that next to the subpart number...

Or if you want to have a formula, use:

If ({tblInvoiceAssemblyDetail.value}) <> 0 then
0
else
0

-k

 

[Turkbear]

Hi,
My answer was incomplete ( it would show a blank if the part was a sub) so create a text box containing just '0.00'..
Place this directly over the Cost field ( that has the suppress formula on it) and, supress this new field :
Not IsNull(tblInvoiceAssemblyDetail.ItemID)

That way, if tblInvoiceAssemblyDetail.ItemID has some value the 0.00 from the new field will show, else the actual cost will be displayed.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[hereigns]

Synapsevampire and Turkbear,
I appreciate both of you taking the time to answer this rookies quetions. I used a combination of both of your suggestions and the report is working well now.

I created a formula for the "main" parts.
{tblInvoiceDetail.Quantity} * {tblPriceBook.BaseCost}

and added $0.00 text to the sub parts.

BUT now I've run into a different problem on the same report. I'm surprise at my lack of finding information on this quetion (must not be searching correctly), how do I get a "grand total" for the formula to appear?

Example:
ItemID Cost
MIT-1bA 1,200.00
*MIT-MC2 0.00 (sub part)
*MIT-MC4 0.00 (sub part)
ACT-400 40.00
PER-W75 800.00

Grand Total: 2,040.00

 

[lbass]

I still think we are missing some information here. If you placed {tblInvoiceAssemblyDetail.ItemID}, {tblInvoiceDetail.ItemID}, and the cost field in the detail section next to each other, what would the display look like?

Does the itemID literally start with an "*" or is that something you added to distinguish the subparts? Or do subparts always start with the first three characters the same? Is there some consistent way in which the main part item ID differs from the subpart ID?

-LB

 

[hereigns]

LB,
Other than different part numbers and cost there would not be a difference between an assemblies and non assemblies.
In the report they are on different lines

ItemID tblInvoiceDetail.ItemID Cost
ItemID tblInvoiceAssemblyDetail.ItemID Cost

Regarding the "*" it's added on the report and is not the actual part number. The only consistency is the actual field, the part names/numbers are similar.

 

[lbass]

This doesn't really answer my question. What do you mean by different lines? Detail_a and detail_b? Group header and detail?

What would actual results look like if you laid them side by side, along with the cost? I'm wondering whether one is null when the other is not, for example.

Are you saying that the only difference between main and subparts is the field name?

-LB

 

[hereigns]

Yes...tblInvoiceDetail.ItemID is on GroupHeader 1a
and tblInvoiceAssemblyDetail.ItemID is on GroupHeader 1b.

If you laid them side by side you would see a cost associated for each of them. Basically tblInvoiceDetail is the "main" part which would have a cost associated with it and tblInvoiceAssemblyDetail are the sub components of tblInvoiceDetai - in all cases they both will have a cost associated with them.

I really do not know how to explain it any better than that...we are dealing with a Main component with parts associated with it.

Part 1 500.00
sub part a 400.00
sub part b 100.00
total: 500.00

 

[lbass]

Sorry, but if the {tblInvoiceAssemblyDetail.ItemID} were in the GH_b, you would only see one value, not multiple values per main part. Maybe it's in a GH#2 section? If they are in different groups, then there is a simple solution for getting your grand total.

If they are not, then please lay the fields out in the detail section and report back with sample results. These fields must exist within the same record. It is important to see how they behave in relation to each other in order to identify a solution.

-LB

 

[hereigns]

LB,
Sorry for all the confusing information. I realize this is a difficult item or atleast I'm making it difficult, trust me I not trying to.

Sorry but I gave you the wrong information regarding how it is laid out...tblInvoiceDetail is on GH_2 and the assembly is on the Detail section.

"Sorry, but if the {tblInvoiceAssemblyDetail.ItemID} were in the GH_b, you would only see one value, not multiple values per main part. Maybe it's in a GH#2 section"

You're right there is only one "main" part and a cost associated with it. However there can be multiple assembly parts with various cost associated with each part.

Regarding the detail section, it has...
ItemID, ItemDescription, ItemType, tblInvoiceDetail.Quantity, along with the newly created formula: {tblInvoiceDetail.Quantity} * {tblPriceBook.BaseCost}

 

[lbass]

Do you get the correct result for the GH_2 cost just by placing your formula (@qtyxcost) in the group header? If so, then to get summary results at the group #1 level and at the grand total level, you could use a variable:

//{@reset} to be placed in GH#1:
whileprintingrecords;
numbervar grp1sum;
if not inrepeatedgroupheader then
grp1sum := 0;

//{@accum} to be placed in GH#2:
whileprintingrecords;
numbervar grp1sum := grp1sum + {@qtyxcost};
numbervar grtot := grtot + {@qtyxcost};

//{@displaygrpsum} to be placed in GF#1:
whileprintingrecords;
numbervar grp1sum;

//{@displaygrtot} to be placed in the report footer:
whileprintingrecords;
numbervar grtot;

-LB

 

[hereigns]

LB,
Thanks for taking the time to respond! Much appreciated!

When I attempt to add the formula accum I get the following error:

"A number is required here" post error it highlights this section of the formula: "grp1sum + {@qtyxcost};"

Am I doing something incorrectly? I changed the name of the qtyxcost formula to match.

 

[lbass]

It sounds like {@qtyxcost} is a currency. Replace the "numbervar" in all formulas to "currencyvar".

-LB

 

[hereigns]

LB,
Thank you very much for all of your efforts and surviving my posts. You were correct, once I replaced numbervar to currencyvar it worked. Your suggestion gave me the results we were looking for ~ thanks!