Hi Steve. Welcome to the forum.
The first thing that you'll to try if you haven't already is Monarch's Verfiy feature. This can help if you haven't painted fields wide enough based on all of the data in the report. It'll point out the fields that need to be expanded.
After that, there's the possibility that your detail template trap just isn't robust enough to capture all of the data. To allow us to help in that regard, [URL="http://www.monarchforums.com/showthread.php?t=2290"]posting a sample of the report[/URL] is necessary.
Finally, you'll need a calculated field to properly replace null values with zeros. Let's wait to tackle that one until your extract data balances to the report totals.
Let me try a different line of thinking. I have a report that has a master record and detail records. In most cases, the master record has detail records. But in other cases, the detail record was suppressed and the report only shows the master record.
I have tried setting up the detail template to trap on the detail record which works when there is a master record and a detail record, but doesn't work when there is only a master record. Note: In this scenario, I've already set up an append template to combine the master record to each detail record under the master.
I have also set up the detail template to trap on the master record. And set up the detail record to be a footer template. But still have problems.
Is there a way to set this up or am I out of luck.
Basically unless you can identify some way of creating a 'pseudo' record that always exists and can be treated like an un-required detail (subsequently to be filtered away) for the masters that have no detail lines I doubt that this is a one pass process.
Another way to achieve something similar as an end result would be a 2 pass process which I think you seem to have partly done already.You could extract and export the records WITH detail lines and then process again for record WITHOUT detail lines, add an empty field where the detail would be and export to the same file ensuring that the format is consistent. Re-sort the resulting file as required (ensure you export enough info for sorting purposes even if you don't need it for reporting) and that should give what you need.
Project Exports should help control this process and/or use of a batch file to make the steps easily repeatable time after time if you need that.
I would be happy to review a sample of your input file to see if I can suggest any pseudo record possibilities. It's a bit of a long shot but you never know what might turn up.
Well...I tried to create two models/projects and then link the detail template in the master model to the detail template in the detail model using a customer# that was in both tables, but that didn't work.
Then I tried exporting the detail out of the detail model to an excel spreadsheet and then link the detail back to the detail template in the detail model. This partially worked but it dropped out all rows except the initial row for the detail due to the unique record limitations of the link key on the first row of the detail (think one-to-one instead of one-to-many).
Back to the drawing board....
You will need to create a link TO the detail records. That means creating, in a process somewhere, a Dummy detail record for all the Master records that don't have one. The field or fields in the dummy record might be blank or filled with some identifiable default filler entries. Once you have a list of all possible transaction AND the dummy transactions you can add to them any other information you require using an external lookup or several lookups if required.
So long as you export in the exact same format as the REAL detail and master records from the other model the files should be Ok to export the second appended to the first.
Lookup will not help you much in this case since the detail records form which you need to make the lookup don't exist in the file in all cases ... which is where we came in.
If it is any consolation I have been around this same logic loop on many occasions seeking alternative approaches but in the end if you need to generate to records on whcih to hang a link then generate those records you must! How you do that is another matter but the way I have suggested should work since I have used the concept a number of times in the past.