Summarized field doesn't function in version 10

[SonOfZeus]

I have a calculated field, with a formula inside it that runs fine and can be summarized in Crystal 8.5 When I try the exact same values and formula in Crystal 10.0 it says the "Field cannot be summarized". When I comment out one of the fields (it's fomula below), all works fine. Why would this work in Version 8.5, but not 10?

FORMULA1

//check if item is stocked and if not do not compute new balance (should be 0)
If {IMITMIDX_SQL.stocked_fg} = "Y" Then

// computes running total of the quantity balance
// transaction types that do not impact the balance
if {IMINVTRX_SQL.doc_type} = "A"
or {IMINVTRX_SQL.doc_type} = "B"
or {IMINVTRX_SQL.doc_type} = "C"
or {IMINVTRX_SQL.doc_type} = "G"
or {IMINVTRX_SQL.doc_type} = "L"
or {IMINVTRX_SQL.doc_type} = "O"
or {IMINVTRX_SQL.doc_type} = "W"
or {IMINVTRX_SQL.doc_type} = "H"
or ({IMINVTRX_SQL.doc_type} = "T"
// AND {@F_FromLocation} = {@F_ToLocation}
)
then {IMINVTRX_SQL.old_quantity}
else
// Deleted Item or negative Qty Transactions (Subtract)
if {IMINVTRX_SQL.doc_type} = "R"
then ({IMINVTRX_SQL.old_quantity} + IMINVTRX_SQL.quantity})
else
// Issue Transactions (Subtract)
if {IMINVTRX_SQL.doc_type} = "I" then
//if negative old_quantity then add
if {IMINVTRX_SQL.source} = "R" and {IMINVTRX_SQL.old_quantity} < 0 then
({IMINVTRX_SQL.old_quantity} + IMINVTRX_SQL.quantity} * -1))
else
({IMINVTRX_SQL.old_quantity} - ({IMINVTRX_SQL.quantity} * -1))
else
// Transfer Transactions (Add or Subtract)
if {IMINVTRX_SQL.doc_type} = "T"
then
if {IMINVTRX_SQL.lev_no} = 0 then
{IMINVTRX_SQL.old_quantity} + ({IMINVTRX_SQL.quantity} * -1)
else
{IMINVTRX_SQL.old_quantity} + {IMINVTRX_SQL.quantity}
// all other transaction types except Transfers
else
{IMINVTRX_SQL.old_quantity} + {IMINVTRX_SQL.quantity}
Else
0;

 

[synapsevampire]

Just a quick glance shows that somnething is wrong, perhaps you didn't copy the formula in it's entirety or the TGML here messed up your paste, but you have a close paren with no open paren:

FORMULA1

//check if item is stocked and if not do not compute new balance (should be 0)
If {IMITMIDX_SQL.stocked_fg} = "Y" Then

// computes running total of the quantity balance
// transaction types that do not impact the balance
if {IMINVTRX_SQL.doc_type} = "A"
or {IMINVTRX_SQL.doc_type} = "B"
or {IMINVTRX_SQL.doc_type} = "C"
or {IMINVTRX_SQL.doc_type} = "G"
or {IMINVTRX_SQL.doc_type} = "L"
or {IMINVTRX_SQL.doc_type} = "O"
or {IMINVTRX_SQL.doc_type} = "W"
or {IMINVTRX_SQL.doc_type} = "H"
or ({IMINVTRX_SQL.doc_type} = "T"
// AND {@F_FromLocation} = {@F_ToLocation}
)

So what does "When I comment out one of the fields " translate to, what are you commenting out???

-k

 

[SonOfZeus]

I likely did copy wrong, but bracket is there. This formula is stored in a newbalance field that is then used as a factor in multiplying times the rate change. So I've got a TransactionValue = FormulaResult<- field multiplied by current-old rate for the item. It works fine in 8.5, that's the kicker.

@NewBalance was created by the formula

{IMINVTRX_SQL.new_unit_cost} * {@NewBalance} -
{IMINVTRX_SQL.old_unit_cost} * {IMINVTRX_SQL.old_quantity}

 

[lbass]

If the following is the problem:

AND {@F_FromLocation} = {@F_ToLocation}

...you need to show the contents of the formulas, including the contents of any nested formulas. In fact you should always do that when posting.

-LB

 

[dgillz]

So which Macola report did you copy to create this one? Filename please?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[synapsevampire]

Uh-oh, we got a ringer here

-k

 

[SonOfZeus]

I didn't copy the report, was trying to add the field for the transactional value for Inventory Transaction History report. I did one using the same tables in 8.5, and using a summary command worked fine.

as for "AND {@F_FromLocation} = {@F_ToLocation}", I simply removed them from the test so as to keep things clean

 

[SonOfZeus]

My apologies, it is the @F_FromLocation formula that was blowing things up, didn't have it in my second report. Is where I actually have to look.

 

[dgillz]

Ok, several issues,

If you did not copy the report,the next time you update Macola the report may well be updated too, so you need to copy the report and name it as something else.

Also what does "I was trying to add the field for the transactional value for Inventory Transaction History report" mean?

Finally on your "my apologies" post, do you have this report working, or not? This is not clear from your post.


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[SonOfZeus]

The problem was in moving from Crystal 8.5. to 10 the shared values of subreports aren't allowed in calculations. It's been determined that for the one report they will leave it at this time. I hate losing functionality when upgrading, I thought Access/.NET were the main culprits.

Thanks for your help and suggestions all

 

[dgillz]

I disagree that the shared variables cannot be used in calculations in Crystal v10.

If you want to explorer this please post the formulas in question.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[Madawc]

As dgillz says, shared variables work fine in Crystal 10. We did exactly that upgrade with lots of reports that used them and were written in 8.5 and all were fine.

The one problem we hit was caused by file connections. For the same links in 'Datebase Expert', it generated different SQL, putting a different interpretation. I don't quite see how that would apply in your case, but do take a look at the SQL.

Also try re-writing the formula as several separate formulas. Do the tests separately, each as a 'boolian', no IF and it will return True or False. See which part of the test is not working the same.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10