Link Tables in Crystal Report

[Former Member]

Hi

I am using Crystal report2008 in SAP Business One.

I have made links between OCRD(BP Record) and ORCT(incoming payment). I want to display BP data Opening balance as well as Incoming payment document total. It displays nothing.

Can anyone help me how to display both in a report?

By

Kalai

[Former Member]

left outer join, and then go to file, report options check null values

this will pull in the nulls if the BP Record is not in the other table

[Former Member]

Hi Kalai,

Check how the 2 tables are linked. If you left it to the default setting, delete the link on DocEntry and leave just the CardCode link.

Heather

[Former Member]

Hi

Thanks for your reply. actually I have deleted all system default links when it gives no results. I have made links Cradname ->Cardname alone even though it doesnt gives results.

Looking for your valuable reply.

By

Kalai

[Former Member]

Hi Kalai,

What type of link on CardName? Linking on CardCode only (inner join) would be sufficient.

Are these the only tables in the report? You mention you want the opening balance for the BP on the report but OCRD only has the BP account (current) balance.

Thanks,

Heather

[Former Member]

HI

Actually I want to get BP Opening balance and curent month Order Bookings as well as Incoming payments for the same BP.

After that I want to calculate In a same report

Opening Balance+Order Booking-Incoming Payment(Outgoing payments for Vendor)= closing Balance

Each fields should be displayed as a single column.

Hope you will help me out.

By

Kalai

[Former Member]

Hi Kalai,

Sounds like you have more than just the OCRD & ORCT tables in this report. What are the tables and the links you currently have in the report?

Heather

[Former Member]

Hi

Thanks for all who made efforts.

Yes these are the only tables. Account balance from OCRD.Balances

Invoice totals from OINV.DocTotal and Incoming payment total from ORCT.DocTotal.

Now the issue is am getting results, But it gives repetition of vlaues

Eg.

A customer has 1500, 2000, 4000 of Invoice total and 1500, 2000 of Incoming payments.

It gives results like

Cardname OINV.DocTotal ORCT.Total

A 1500 1500

A 2000

A 4000

A 1500 2000

A 2000

A 4000

What will be problem, Plaz give me a solution

By

Kalai

[Former Member]

Hi Kalai,

Maybe I'm not fully understanding what you want but going by the following:

Opening Balance+Order Booking-Incoming Payment(Outgoing payments for Vendor)= closing Balance

The Account Balance is not the same as the Opening Balance. The OCRD.Balance (Account) field already includes any invoices and payments so they will be included in the "closing" balance (i.e. account balance). What isn't included in the above calculation are any journal entries or other transactions, which would represent the different between the invoices & payments vs account/closing balance. [I am assuming you are creating a separate but similar report for vendors since the outgoing payments are handled in a different table.]

If you want a list of the invoices and payments, you may want to try having one sub-report to list the invoices and another for the payments, linking the main report to the sub-reports on the CardCode field. This will avoid the repetition. Another option is to create a stored procedure.

I hope this helps,

Heather

[Former Member]

you joined your 3 tables by the customer i gather.

create groups

customer(BP)

date if you need it grouped by date

create manual running totals

reset - gets placed in group header

calculate- gets placed in details (if you are duplicating values then create a group from a unique id to calculate the one value-date perhaps)

display-gets placed in group footer

for

Opening balance

curent month Order Bookings

Incoming payments for the same BP.

closing Balance

the closing balance will use your formula in the calcculate

this should give you the values you want per BP