10 Replies Latest reply: May 15, 2014 10:02 AM by GailStevenson _ RSS

    All potentially blank fields (detail line)

    rigel7 _

      I've done some searching and was unable to find this answer.

       

      I'm a new Monarch user and I'm currently working with some COBOL text dumps.  The issue I'm running into is that my detail line could potentially have every single field as blank in various scenarios.  Creating mulitple detail line templates would handle it but from what I understand, I can only create 1 detail line template per document.

       

      An important fact about the document is that there is never a case when ALL fields are blank.

       

      My document is 10+ columns but here is a simplified example of the issue:

      A 3-column form...

      Detail Line 1: When Column A is blank, Column B is always populated.

      Detail Line 2: When Column B is blank, Column C is always populated.

      Detail Line 3: When Column C is blank, Column A is always populated.

       

      So as you can see from the simplified example, all 3 fields COULD be blank.  Does anyone know how I can successfully trap all 3 detail line scenarios?

       

      Thanks for looking...

        • All potentially blank fields (detail line)
          joey

          Does the set of three detail lines represent one logical record or three records?

           

          If it only represents one record, can you do a detail trap that is only 3 lines?  You would have 9 fields in the trap (Line1ColA to Line3ColC).

           

          Then have 3 calculated formulas to determine colA, ColB, and ColC

           

          ColA = if( isnull (Line1ColA),if(isnull(Line2ColA),Line3ColA,Line2ColA),Line1ColA)

            • All potentially blank fields (detail line)
              rigel7 _

              Thanks for the response.

               

              Sorry I wasn't more clear.  Each detail line is one record, so in my example, it would be three individual records.

                • All potentially blank fields (detail line)
                  joey

                  so if I'm understanding, you have 10 columns, of which a detail record will have one and only one value in? 

                   

                  If you set you detail trap at one line, you will have 10 columns.  You would like one column that has all of your values in it correct?

                   

                  You could try an 11th calculated column:

                   

                  CalculatedColumn = COL1 + COL2 + COL3.....+COL10

                    • All potentially blank fields (detail line)
                      RalphB _

                      Hi rigel7 and welcome.

                       

                      Is there anything on a line like a description or a date or anything that appears on every line that is in the same position?

                       

                      If so, set your trap on that that and paint each of the columns you want.   Just make sure each column is wide enough to capture all the data you want in each column.

                        • All potentially blank fields (detail line)
                          rigel7 _

                          RalphB...No, that is my issue actually.  It's that I don't have one column that always has data in it.  At any given time, every single column has at least one instance in which it is blank.

                           

                          Joey...I'm not sure we're talking about the same thing.  In my 10 columns, there can be several columns filled in, even all of them at times.  But the fact that each and every column has at least one instance where it is blank is prohibiting me from setting that one detail trap template. 

                           

                          I'm not sure I understand where you're going with the calculated column.  In my 10 columns, I have various field types (dollar amount columns, description columns, etc.)  So if by doing the calculated column, you mean adding the values of the columns themselves, then I can't do that.

                           

                           

                          Thanks for you help, folks.  I'm going to keep working on a solution...

                            • All potentially blank fields (detail line)
                              joey

                              I've had a few cases like that.  What may be most helpful is to post a sample of the report.  Are there any lines you don't want in the report? 

                               

                              There is a numeric OR trap which would work perfectly if all of your columns were numeric.  However, since you have descriptions that won't work.

                               

                              You don't need a trap in a template, you could include all lines. You could include all lines and use filters to exclude the items you don't want.

                                • All potentially blank fields (detail line)
                                  Bill Watson

                                  rigel

                                   

                                  is there anything else in your report file other than the records e.g column titles or page headers?

                                   

                                  If not or if it is something that could easily be filtered out in the table view, you could look at having a detail template which does not actually have any trapping. This would allow you to extract all 10 of your columns and do any clean up required in the table view.

                                   

                                  Just a thought.

                                    • All potentially blank fields (detail line)
                                      GailStevenson _

                                      I have been a lurker for a bit and I thought I would try posting for a change since I can't find the exact same problem as mine on the site already.  I have a very simple report with a very simple model but I want to create a second summary tab that will have just the information from the append template.  The problem is that the detail template for some of the records is blank so the information from the append template is dropped and doesn't show up in the table and hence the summary.  I have tried to capture the data differently by making the append table the detail and capturing the other data using the "guru trap" but it makes the data messy and I have to take several steps to clean it up I don't have the patience for that!  I just want to be able to capture the employee called "Missing, Employee" even though the detail line ("Earnings" where the decimal point captures the earnings/dollars/hours) is blank.  I have a feeling that it is something simple and I will say "duh" when it is pointed out to me.  Here is a sample of the report and I want to capture all of the employee names as well as the earnings data (the names have been changed to protect the innocent):

                                      LIST EMPLOYEES BY:  NAME    

                                       

                                      REPORT VALUES AS OF: COMPILED  10/31/08

                                      START PRINTING SORT 1 AT: BEGINNING                    

                                      STOP PRINTING SORT 1 AT:  END                          

                                      PAGE BREAK ON:     

                                      SUBTOTAL ON:       

                                       

                                                                                    EMPLOYEES WHO ELECT THE DEFINED CONTRIBUTION PLAN

                                                                                                                      -


                                      EARNINGS----


                                         NAME                        EMPL #          STATUS   PAYROLL   TYPE   DEPARTMENT       JOB CODE    EARNING        DOLLARS  HOURS

                                       

                                         Doe, Jane                   17087           TERM     ADMIN     FT     01-6605          0082        REG 1          2562.43  256.8

                                                                                                                      REG 2          1826.80  170.3

                                                                                                                      REG 3           734.40   67.5

                                                                                                                      OT 2            173.09   10.8

                                                                                                                      HOLIDAY         141.11    8.8

                                                                                                                      INS 1           890.73   89.3

                                                                                                                      INS 2           426.52   39.8

                                                                                                                      PTIME1          232.04   23.3

                                                                                                                      PTIME2          160.95   15.0

                                                                                                                      P-NS1           167.17   16.8

                                                                                                                      P-NS2            53.65    5.0

                                                                                                                      PULPRENA          1.50    3.0

                                                                                                                      CALL             22.50    9.0

                                       

                                         Doe, Jim                    16487           ACTIVE   ADMIN     FT     01-6710          0260        REG 1         32318.00 1469.0

                                                                                                                      PTIME1         3927.00  178.5

                                                                                                                      P-NS1           880.00   40.0

                                                                                                                      RETRO           663.75    0.0

                                                                                                                      PTIME2          354.10   15.0

                                                                                                                      PTIME3          390.00   15.0

                                       

                                                                                    EMPLOYEES WHO ELECT THE DEFINED CONTRIBUTION PLAN

                                                                                                                      -


                                      EARNINGS----


                                         NAME                        EMPL #          STATUS   PAYROLL   TYPE   DEPARTMENT       JOB CODE    EARNING        DOLLARS  HOURS

                                                                                                                      CALL            270.00  108.0

                                                                                                                      RETRO           453.70    0.0

                                                                                                                      BONUS          2112.50    0.0

                                       

                                         Missing, Employee           17287           ACTIVE   ADMIN     PRN    01-6650          0478                                                                               

                                      Smith, James                16915           ACTIVE   ADMIN     FT     01-6660          0076        REG 1         35540.80 1385.5

                                                                                                                   /CODE

                                       

                                      My current outcome is this:

                                       

                                      EMPLOYEE .     NAME     STATUS     PAYROLL     TYPE     DEPT     JOB CODE     EARNING     DOLLARS     HOURS

                                      17087     Doe, Jane     TERM     ADMIN     FT     01-6605     0082     REG 1     2562.43     256.8

                                                TERM     ADMIN     FT     01-6605     0082     REG 2     1826.8     170.3

                                                TERM     ADMIN     FT     01-6605     0082     REG 3     734.4     67.5

                                                TERM     ADMIN     FT     01-6605     0082     OT 2     173.09     10.8

                                                TERM     ADMIN     FT     01-6605     0082     HOLIDAY     141.11     8.8

                                                TERM     ADMIN     FT     01-6605     0082     INS 1     890.73     89.3

                                                TERM     ADMIN     FT     01-6605     0082     INS 2     426.52     39.8

                                                TERM     ADMIN     FT     01-6605     0082     PTIME1     232.04     23.3

                                                TERM     ADMIN     FT     01-6605     0082     PTIME2     160.95     15

                                                TERM     ADMIN     FT     01-6605     0082     P-NS1     167.17     16.8

                                                TERM     ADMIN     FT     01-6605     0082     P-NS2     53.65     5

                                                TERM     ADMIN     FT     01-6605     0082     PULPRENA     1.5     3

                                                TERM     ADMIN     FT     01-6605     0082     CALL     22.5     9

                                      16487     Doe, Jim     ACTIVE     ADMIN     FT     01-6710     0260     REG 1     32318     1469

                                                ACTIVE     ADMIN     FT     01-6710     0260     PTIME1     3927     178.5

                                                ACTIVE     ADMIN     FT     01-6710     0260     P-NS1     880     40

                                                ACTIVE     ADMIN     FT     01-6710     0260     RETRO     663.75     0

                                      16915     Smith, James     ACTIVE     ADMIN     FT     01-6660     0076     REG 1     35540.8     1385.5

                                                ACTIVE     ADMIN     FT     01-6660     0076     REG 2     632.03     22.5

                                                ACTIVE     ADMIN     FT     01-6660     0076     OT 1     5850.42     150.8

                                                ACTIVE     ADMIN     FT     01-6660     0076     OT 2     916.55     21.8

                                                ACTIVE     ADMIN     FT     01-6660     0076     INS 1     2123.73     84.5

                                                ACTIVE     ADMIN     FT     01-6660     0076     INS OT 1     28.23     0.8

                                                ACTIVE     ADMIN     FT     01-6660     0076     PTIME1     3076.21     121.8

                                                ACTIVE     ADMIN     FT     01-6660     0076     P-NS1     323.49     12.5

                                                ACTIVE     ADMIN     FT     01-6660     0076     PULLPREM     141.75     47.3

                                                ACTIVE     ADMIN     FT     01-6660     0076     CALL     1682.5     673

                                                ACTIVE     ADMIN     FT     01-6660     0076     BONUS 2     275     0

                                      /CODE

                                        • All potentially blank fields (detail line)
                                          Grant Perkins

                                          Hi Gail and welcome to the forum.

                                           

                                          You could try this for your problem - it seems to work.

                                           

                                          Set you detail trap with a NUMERIC OR rather than the decimal point. In what would really be (and should be) part of the APPEND for the personal details you seem to have a numeric value consistently for Employee Number. So set NUMERIC OR for the first digit of the Employee number and a consistently populated number in one of the value columns and that should do it. (Department number would be an alternative to Employee Number should you need it.)

                                           

                                          You should still set the trap for the append of course - the detail fields will be empty. You may need a space in the first position on the line so as to avoid picking the date containing line from the report header.

                                           

                                          It seems to work for the sample - hopefully it will work for the entire report.

                                           

                                           

                                          HTH.

                                           

                                           

                                          Grant