5 Replies Latest reply: May 15, 2014 10:03 AM by Grant Perkins RSS

    Some couple questions

    sarndt _

      I've created my traps for a report that I'm pulling data out of.  The totals that result from my traps don't equal the totals printed at the bottom of the report.  Because the report is over 900 pages long, it's very tedious to go page-by-page thru the report to find which rows didn't get tagged by the traps.  Is there a way that Monarch can tell either take me thru the report and stop at each row that didn't get trapped and/or provide me a report of non-trapped rows?

       

      Also - How do I set the traps so that (NULL) values are replaced with zeros?

       

      Thanks

       

      Steve

        • Some couple questions
          Data Kruncher

          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.

           

          Kruncher

            • Some couple questions
              sarndt _

              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.

               

              Thanks

               

              Steve

                • Some couple questions
                  Grant Perkins

                  Is there a way to set this up or am I out of luck.

                   

                  Thanks

                   

                  Steve[/quote]

                   

                  Hi Steve,

                   

                  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.

                   

                  HTH.

                   

                   

                  Grant

                    • Some couple questions
                      sarndt _

                      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....

                        • Some couple questions
                          Grant Perkins

                          Steve,

                           

                          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.

                           

                           

                          HTH.

                           

                           

                           

                          Grant