Assistance with Correct Link Option/Record Selection

[ehk]

Crystal XI with Advantage Database Server

Two tables: Batch.dbf and Ledger.dbf have a one to many relationship

Batch.dbf contains Batches opened to track Insurance Payments in a doctor's office.

Ledger.dbf contains records for all ledger transactions. Only Insurance Payments are posted into a Batch.

Options for fields to link are:
{Batch.BtchUnique} and {Ledger.BtchUnique}
or
{Batch.Opened} and {Ledger.PostDate}

Normal Scenario: Physician receives an insurance check from Blue Cross in the amount of $280.00 that pays for two patient claims.

A Batch.dbf record is opened with Target Credit $280.00. This record is assigned {Batch.BtchUnique}=42.

Credits are posted into the Ledger.dbf for the two patients while this Batch is active. Each of these records has a different {Ledger.Unique} but they are both assigned {Ledger.BtchUnique}=42.

Issue: There are instances when staff will create a new Batch, deposit the insurance check into the bank but never post payments into the Ledger.

**I am trying to create an Un-posted Batch report that locates these Batches. There will be a Batch.dbf record with no corresponding Ledger.dbf records within a defined posting period. Not sure how to set this up.

{Batch.BtchUnique} 42
{Ledger.BtchUnique}42 Patient A Payment $180.00
{Ledger.BtchUnique}42 Patient B Payment $100.00
*this is legal

No Batch record
{Ledger.BtchUnique}0 Patient C Charge $445.00
*this is legal

{Batch.BtchUnique} 43
No Ledger records
**This is the Batch I'm looking for

Thanks for the help.

 

[lbass]

Try using a left join FROM the batch table TO the Ledger table and link on both fields (as long as the dates are always a match when posted to Ledger). Then use a record selection formula like this:

isnull({Ledger.BtchUnique}) and
{Batch.Opened} = {?daterange}

Be sure not to select on the Ledger table.

-LB

 

[ehk]

Thanks, LB

{Ledger.PostDate} can be >= {Batch.OpenDate}
Does this rule out linking the Batch and Ledger tables by that field?

If so, should linking by BtchUnique only give me the same results?

Thanks.

 

[lbass]

I would still link on the date and then in the subreport, go into report->selection formula->record and change the link on the appropriate line so that it reads:

{Ledger.PostDate} >= {?pm-BatchOpened}

-LB