Report Consolidating Data From Two Tables

kb2gmx

Hello,

I have a problem trying to create a report with two tables.

I'm trying to show all sales quotes Currently Open and Archived.
The Archived Quotes reside in a customized table.

The task is to show Quotes Statistics by the person who entered the quote.
I can show each table in two separate reports but I want to put this information in 1 report.

The tables cannot be linked as a Quote is in on or the other table.
The tables are are designed in a similar way.

My question is how can I write 1 report that accumulates statistics about the Quotes (Open and Archived) subtotaled by the Quoted By field?
I'm thinking that I must create some kind of Temp file in the 50,000 range.
Make two passes at the sales Quotes Open and Archive and populate the temp file with minimal information about each quote.

Make a final pass at the Temp File and accumulate my data that way.

Is there a better way? I'm Using NAV 5.0 and the Navision Report Writer.


Thanks,

Dave Frantz

gerrykistler

I assume that Open Quotes may or may not be Archived - is that correct?

What you describe is probably along the right direction.

Assuming I am correct in my assumption, then I would set up a temp variable for the Archived table. Loop through the quotes and add each to the temp archived variable. Then loop through the Archived adding to the variable if that Quote No. does not already exist. Then use an Integer data item to loop through the temp Archived table - filtered on 1 to the number of records in the temp variable.

kb2gmx

Open quotes are not Archived.
Archive Quotes are not Open.

Think of it this way, I have some of the numbers from 1 to 100.

The Even Numbers are in Table A and not sorted.
The Odd Numbers are in Table B Also in no particular order.

The Result must be the all Numbers and sorted in the correct order.

How can the two tables be read and reported ?

gerrykistler

Mostly the way I described, assuming the numbers are unique across both tables - no duplicates:

Set up a temp variable for the Archived table. Loop through the quotes and add each to the temp archived variable. Then loop through the Archived adding to the variable. Then use an Integer data item to loop through the temp Archived table - filtered on 1 to the number of records in the temp variable.