linking records based on two table fields

[AzizKamal]

I am developing a report in crystal reports 2008. The report extracts data from two peachtree/sage tables; JrnlHdr and JrnlRow. The tables JrnlHdr and JrnlRow are linked by a field called PostOrder. One PostOrder value in JrnlHdr can have multiple entries in JrnlRow table. I have to print all rows of JrnlRow table for a given PostOrder. The issue is that JrnlRow does not contain all transactions in the JrnlHdr's linked PostOrder. There is also another field in JrnlRow called LinkToAnotherTrx that contains information of additional PostOrders and I have to print them too. One example is as follows:

1. User entered reference number through parameter field (?referenceno)
2. Find all Post Order Numbers in JrnlHdr matching (JrnlHdr.Reference = ?referenceno) (Two PostOrders (JnrlHdr.PostOrder) Found: 266, 267)
3. Find all entries in JrnlRow where {JrnlRow.PostOrder} in [266, 267] (Two entries found for 266 and two entries found for 267)
4. And here is the difficult part: There is another field in JrnlRow called JrnlRow.LinkToAnotherTrx. It contains either zero or another post order number. If it contains another post order number, then I need to print the entries of that post order number as well. In this example, I got the following:

JrnlHdr (got two PostOrder 266, 267)

JrnlRow:
PostOrder LinkToAnotherTrx
266 0
266 265
267 0
267 264

If I view the entries for 264 and 265 in JrnlRow, I get:
PostOrder LinkToAnotherTrx
264 0
264 0
265 0
265 0
265 0

What I need in the detail section of the report is:
JrnlRow.PostOrder JrnlRow.Amount
264 100
264 200
265 300
265 400
265 500
266 600
266 700
267 800
267 900

I tried creating an alias of JrnlRow as JrnlRow_1 but could not accomplish the above requirement.

Regards,
Aziz

 

[lbass]

Is the Reference field also available in the JrnlRow table?

-LB

 

[IanWaterman]

How is your SQL?

I would use a command

Select JrnlRow.*
from JnrlHdr
inner join JrnlRow on JnrlHdr.PostOrder = JrnlRow.PostOrder
where JrnlHdr.Reference = {?referenceno} -- Command parameter created in command dialog box and NOT a normal crystal parameter
union all
Select JrnlRow.*
from JnrlHdr
inner join JrnlRow on JnrlHdr.LinkToAnotherTrx = JrnlRow.PostOrder
where JrnlHdr.Reference = {?referenceno} -- same param as listed above

Ian

 

[AzizKamal]

Thanks lbass and Ian.

@lbass
The Reference field is not available in the JrnlRow table. It is only available in the JrnlHdr table (JrnlHdr.Reference). Peachtree/Sage assigns PostOrder to every transaction. However, this number is not visible on the front-end screens. Users keep track of the transaction by entering Reference Number. Usually, there is one PostOrder for one Reference number in JrnlHdr table. However, sometimes users enter the same reference number for multiple transactions for printing these transactions on the same voucher. Peachtree issues a warning when the same reference number is used in more than one transactions. However, if the user ignores the warning, it still accepts the transaction. In that case, there can be two postorder numbers for one reference number as in my example.

@Ian
LinkToAnotherTrx field is in JrnlRow table. It is not available in JrnlHdr table. Peachtree data is stored in Pervasive database. I have to select Btrieve in Available Data Sources in Create New Connection in crystal reports 2008. With this connection setting, Show SQL Query option is disabled and I cannot use Add Command. Union of table data can prove useful in this scenario, but as far as I know, Union of table rows can be accomplished only through SQL query. It is not possible through selecting and linking the tables in Database Expert. Also, in the Link Options, only two options are enabled in Join Type; Inner Join and Left Outer Join. Right Outer Join and Full Outer Join are disabled.

Regards,
Aziz

 

[lbass]

Try an equal join FROM JrnlHdr.PostOrder to JrnlRow.PostOrder and also a left outer join FROM JrnlRow.LinktoAnotherTrx to JrnlRow_1.PostOrder (using an alias table). Then create a formula {@PO}:

If {JrnlRow.LinktoAnotherTrx} = 0 then
{JrnlRow.PostOrder} else
{JrnlRow_1.PostOrder}

Group on {@PO}. I'm not sure whether this will create duplicate values in the detail section. If it does, you would probably need to summarize using running totals.

-LB

 

[AzizKamal]

Thanks lbass.

I selected three tables in Database Expert; JrnlHdr, JrnlRow, and JnrlRow_1 (Alias).
Clicked Clear Links to remove all the links.
Created a link JrnlHdr.PostOrder --> JrnlRow.PostOrder (Join Type: Inner Join)
Created a link JrnlRow.LinkToAnotherTrx --> JrnlRow_1.PostOrder (Join Type: Left Outer Join)
Dragged JrnlRow.PostOrder and JrnlRow.Amount from Field Explorer to Detail Section
In Select Expert -- Record: JrnlHdr.Reference is equal to R1 (For testing the example reference number)
When I preview the data, it shows only 4 transactions out of 9:

266 600
266 700
267 800
267 900

Then I created a formula PO and copied the code from above.
Then Insert -> Group grouped by PO.
It created 4 PostOrders in Group Header; 264, 265, 266, 267

When I preview, I get 4 post orders in GH1. The complete data is as follows:

GH1 264
D 267 900
D 267 900

GH1 265
D 266 700
D 266 700
D 266 700

GH1 266
D 266 600

GH1 267
D 267 800

I could not get all 9 rows in the detail section as follows:
264 100
264 200
265 300
265 400
265 500
266 600
266 700
267 800
267 900

I also created sample JrnlHdr and JrnlRow tables in SQL Server. Then in Cyrstal Reports, I entered the following query using Add Command. This query is showing 9 rows perfectly as per my requirement. However, in Peachtree Pervasive Database Driver, I don't have the option of Add Command. I need to accomplish the same without using Add Command.

select JrnlRow.*
from JrnlRow 
where JrnlRow.PostOrder in (select PostOrder from JrnlHdr where Reference = 'R1')
union
select JrnlRow_1.*
from JrnlRow, JrnlRow JrnlRow_1
where JrnlRow.LinkToAnotherTrx = JrnlRow_1.PostOrder
and JrnlRow.PostOrder in (select PostOrder from JrnlHdr where Reference = 'R1')

Regards,
Aziz

 

[IanWaterman]

You will probably need to use a subreport to bring in the data from JrnlRow.LinkToAnotherTrx linking SR on POSTORDER

If you want totals etc you will need to pass total of SR back to main report using a shared variable.

Ian

 

[AzizKamal]

Thanks Ian.

I am also thinking to use subreport to get the required data. With the @PO formula suggested by lbass, at least I have got all 4 post orders correctly in GH1 (264, 265, 266, 267). I will place a subreport in Group Footer # 1 and enter @PO in the Field(s) to link to. I will print all rows in the detail section of the subreport. This should give me all rows of GH1 PostOrders in Group Footer. I will post my results.

Regards,
Aziz

 

[lbass]

Use my earlier suggestion for the group field, and then for the detail section, use a similar formula to get the correct amount:

If {JrnlRow.LinktoAnotherTrx}=0 then
{JrnlRow.Amount} else
{JrnlRow_1.Amount}

-LB

 

[AzizKamal]

Thanks lbass.

I created another formula POAmt and entered the following:
If {JrnlRow.LinktoAnotherTrx}=0 then
{JrnlRow.Amount} else
{JrnlRow_1.Amount}

Dragged POAmt in the detail section. Also copied and pasted Group # 1 Name in detail section so that I could view the PostOrder as well. When I clicked Preview, there were 7 records in the detail section out of the required 9 records as follows:

D 264 100
D 264 200
D 265 300
D 265 400
D 265 500
D 266 600
D 267 800

More specifically, it did not print those rows, where LinkToAnotherTrx was not zero. JrnlRow table has the following entries:
PostOrder LinkToAnotherTrx Amount
264 0 100
264 0 200
265 0 300
265 0 400
265 0 500
266 0 600
266 265 700
267 0 800
267 264 900

JrnlHdr table has the following entries:
Reference PostOrder
R1 266
R1 267

 

[AzizKamal]

Finally succeeded in accomplishing it as follows. I just used Database Expert and Select Expert.

Selected Tables
JrnlHdr
JrnlHdr_1
JrnlRow
JrnlRow_1

Links
JrnlRow.PostOrder --> JrnlHdr.PostOrder (Left Outer Join)
JrnlRow_1.PostOrder --> JrnlHdr_1.PostOrder (Left Outer Join)
JrnlRow.PostOrder --> JrnlRow_1.LinkToAnotherTrx (Left Outer Join)

Fields to Display
JrnlRow.PostOrder
JrnlRow.Amount

Create New Parameter
referenceno

Record Selection Formula
{JrnlHdr.Reference} = {?referenceno} or
{JrnlHdr_1.Reference} = {?referenceno}
Changed Exceptions For Nulls to Default Values For Nulls

Clicked Preview, entered R1 for referenceno and got all required 9 rows in the detail section.