Tables with Different Data

[Former Member]

I am using two flat Excel files as my database linked by the key table Code.

One table (Cost) has my costing figures and the other table (Data) is a dump from Quickbooks with my sales figures.

I need to calculate Total Cost by multiplying Quantity by Cost from the Data Excel file by the Cost in the Cost Excel file.

However, in the Code table of my Data file I have elements which are not in the Cost file, such as Credit Note, Miscelaneous, and Sales Discount.

So, when multiplying Quantity x Cost they are eliminated from the calculations.

I tried the following formula WHICH has not worked...

IF {Data_.Code} = "Credit Note"; "Miscelaneous"; "Sales Discount"

     THEN {Data_.Total Sale}

          ELSE {Quantity} *  {Cost}

Any help?

Thank you.

[Former Member · Accepted solution]

Thank you for your prompt reply Abhilash.

Nevertheless, the suggested approach has not worked.

After entering that formula I get "The ) is missing" error (please see screenshot).

I tried the same formula with one variable ("Credit Note", which was accepted, no error message), and everything else was calculated, but the variable "Credit Note".

I can't get my report to reflect the true final figure because of this odd records...

Any more help would be appreciated.

Thank you.

[abhilash_kumar · Accepted solution]

I'm sorry, the formula should really have been:

IF {Data_.Code} IN ["Credit Note", "Miscelaneous", "Sales Discount"]

     THEN {Data_.Total Sale}

          ELSE {Quantity} *  {Cost}

P.S: I replaced the round braces with square ones.

-Abhilash

[Former Member · Accepted solution]

No error message this time around, however the records ("MISC", "Credit Note","Sales Discount") keep being excluded from the calculations...

I can not figure out what could be wrong...

[abhilash_kumar · Accepted solution]

Do you have a formula to calculate the totals?

-Abhilash

[Former Member · Accepted solution]

If you mean the Totals in {Data_.Total Sale}, no, there's no formula there, just the result of Quantity * Sale Price.

[Former Member · Accepted solution]

I have created a mockup of my report and you can get the CR file and the Excel database here https://www.dropbox.com/sh/pm6ixqf8xv55kqg/AADXY8wu_ORFA81rcG_KVXcaa?dl=0

Maybe you can spot the issue.

Thank you.

[abhilash_kumar · Accepted solution]

Hi Rony,

I'll see what's going on.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash

Thank so much for your attention on this issue...

I am at my wit's end over this matter.

—Rony

[abhilash_kumar · Accepted solution]

Rony,

How have you 'Joined' the two Excel Sheets in Crystal Reports?

You should use a "Left Outer Join' from the Data table to the Cost table on the 'Code' field.

-Abhilash

[Former Member · Accepted solution]

Abhilash, I am thrilled!!! It WORKED!!!

Look Pal, I'm in the wine business, if you ever happen to come over to this neck of the woods, let me know, I'll have a couple of fine bottes of wine packed for you.

Thank you!

[Former Member · Accepted solution]

dirtyrony wrote:

Abhilash, I am thrilled!!! It WORKED!!!
Look Pal, I'm in the wine business, if you ever happen to come over to this neck of the woods, let me know, I'll have a couple of fine bottes of wine packed for you.
Thank you!

"...this neck of the woods..." = Dublin, Ireland.

[abhilash_kumar · Accepted solution]

I'm glad it works. And, thank you for the offer, Rony

-Abhilash

[Former Member · Accepted solution]

My pleasure, Sir!

[abhilash_kumar]

Hi Rony,

Try:

IF {Data_.Code} IN ("Credit Note", "Miscelaneous", "Sales Discount")

     THEN {Data_.Total Sale}

          ELSE {Quantity} *  {Cost}

-Abhilash