19 Replies Latest reply: May 15, 2014 9:52 AM by starpower _ RSS

    Want to have NULL fields

      Hi all,

       

      i have a list something like this:

       

      101020[/b] aaa

      1 BBBBBB

      2 cccccc

      3 eeeeee

      4 ,CODE: 4DDS[/b]

      5 rrrrrrr

      101030[/b] bbb

      1 bbbbbb

      2 ggggggg

      3 fffffff

      4 ggggggg

      6 hhhhhhh

      101040[/b] ccc

      1 fffffff

      2 jjjjjjj

      3 ,CODE: 4DDF[/b]

      101050[/b] ddd

      .....

       

      I`ve defined the the ordernumber eg. 101020 and the code. (Monarch V6)

       

      The result should be:

      101020 4DDS

      101030 NULL

      101040 4DDF

      101050 ...

       

      What i get is, where is no code, the ordnernumber won`t be generated.

       

      Does anybody know how to get success ?

       

      Thank you in advance.

       

      Regards

      Rolf

        • Want to have NULL fields

          Hi all,

           

          i have a list something like this:

           

          101020[/b] aaa

          1 BBBBBB

          2 cccccc

          3 eeeeee

          4 ,CODE: 4DDS[/b]

          5 rrrrrrr

          101030[/b] bbb

          1 bbbbbb

          2 ggggggg

          3 fffffff

          4 ggggggg

          6 hhhhhhh

          101040[/b] ccc

          1 fffffff

          2 jjjjjjj

          3 ,CODE: 4DDF[/b]

          101050[/b] ddd

          .....

           

          I`ve defined the the ordernumber eg. 101020 and the code. (Monarch V6)

           

          The result should be:

          101020 4DDS

          101030 NULL

          101040 4DDF

          101050 ...

           

          What i get is, where is no code, the ordnernumber won`t be generated.

           

          Does anybody know how to get success ?

           

          Thank you in advance.

           

          Regards

          Rolf

          • Want to have NULL fields
            Bruce _

            Rolf

             

            I had something similar, and I only found a work-around and not a solution.  Perhaps this would apply to your problem.

             

            I had to do a sales report, with months going accross the page in columns (Jan, Feb, Mar...Dec), but the trouble was were customers did not buy anything for a certain month, the report would "skip" the month & make the report look strange. (Jan, Mar, Jun,...Dec) as Feb, Apr & May had no data. I wanted EVERY month to show regardless.

             

            What I did was make a access table wth all the customer with a dummy item, for every month in my planning horizen, but with a ZERO qty sold. This way Monarch would pick up the month column. I then exported to Excell & deleted the dummy items.

             

            Long way around, but it works.

             

            Hope this helps it little bit.  smile.gif[/img]

            • Want to have NULL fields
              Bruce _

              Rolf

               

              I had something similar, and I only found a work-around and not a solution.  Perhaps this would apply to your problem.

               

              I had to do a sales report, with months going accross the page in columns (Jan, Feb, Mar...Dec), but the trouble was were customers did not buy anything for a certain month, the report would "skip" the month & make the report look strange. (Jan, Mar, Jun,...Dec) as Feb, Apr & May had no data. I wanted EVERY month to show regardless.

               

              What I did was make a access table wth all the customer with a dummy item, for every month in my planning horizen, but with a ZERO qty sold. This way Monarch would pick up the month column. I then exported to Excell & deleted the dummy items.

               

              Long way around, but it works.

               

              Hope this helps it little bit.  smile.gif[/img]

              • Want to have NULL fields

                Hi Bruce,

                 

                thanks for your reply but i`m not quite sure, that i got it, but in my case the source is changing and growing and i don`t have fixed values like month eg.

                 

                Is there a chance to use a function like:

                 

                If (the code-field has no matching) then set colum B='NULL' else (take what you found)[/b]

                 

                I need the ordernumber generated (it`s a key-field) to combine it with a second file.

                 

                Regards

                Rolf

                • Want to have NULL fields

                  Hi Bruce,

                   

                  thanks for your reply but i`m not quite sure, that i got it, but in my case the source is changing and growing and i don`t have fixed values like month eg.

                   

                  Is there a chance to use a function like:

                   

                  If (the code-field has no matching) then set colum B='NULL' else (take what you found)[/b]

                   

                  I need the ordernumber generated (it`s a key-field) to combine it with a second file.

                   

                  Regards

                  Rolf

                  • Want to have NULL fields
                    Bruce _

                    Rolf

                     

                    It would depend on the layout of the report. Can you post a sample of the report (strip off any confidential information) to the forum?

                     

                    You would probably have to change the lowest level of the report to a field that is ALWAYS present, and then look for the field "code".

                     

                    Regards,

                    • Want to have NULL fields
                      Bruce _

                      Rolf

                       

                      It would depend on the layout of the report. Can you post a sample of the report (strip off any confidential information) to the forum?

                       

                      You would probably have to change the lowest level of the report to a field that is ALWAYS present, and then look for the field "code".

                       

                      Regards,

                      • Want to have NULL fields

                        Hi Bruce,

                         

                        that is exactly what i did:

                        [font="courier"]201410.1    D 1 01.04.04 OLSSON, SIV N INET PTAGVINPEGE..-- ..--I 102607   

                        1 F GOTAYT6A GOT AYT6A 29.05.04     7  AYTGOT6AAYT GOT  05.06.04    2 3 -         

                        2 H INPEGE      29.05.04   1 05.06.04   7   1ZK   A    2  1     16644.00          

                        3 Q NAAYT A FR_HBUCHERVORTEIL - DEUTSCH    1F 2  2               -400.00          

                        4 Q NAAYT A FR_HBUCHERVORTEIL - DEUTSCH    1F 1  2               -200.00          

                        5 Q NAAYT A ELLOS-RABATT-DEUTSCH           1F 2  2               -200.00          

                        6 Q NAAYT A ELLOS-RABATT-DEUTSCH           1F 1  2               -100.00          

                        7 B AQUA    GRATISEINTRITT IN EINEN AQUAPARK !               1                    

                        8 B ELLI    *** ELLOS KUNDE ***                              1                    

                        9 H INPEGE      29.05.04   1 05.06.04   7   1DZ   A       2     11096.00          

                        10 Q NAAYT A FR_HBUCHERVORTEIL - DEUTSCH    1F 2  2               -400.00          

                        11 Q NAAYT A ELLOS-RABATT-DEUTSCH           1F 2  2               -200.00          

                        12 B AQUA    GRATISEINTRITT IN EINEN AQUAPARK !               1                    

                        13 B ELLI    *** ELLOS KUNDE ***                              1                    

                        14 B ELLOS   ,CODE: SRTZUC                                                      

                        15 E VERS    REISER_CKTRITTSKOSTENVERSICHERUNG             5 V    1000.00          

                        16 L PRIS    ERMAESSIGUNG WRONG PRICE PRICELIST /CATHA       V   -1500.00          

                         

                        <- new order ->

                         

                        201445.1    Z 1 29.03.04 BERGDAHL, ING INET PTARVALHERE..-- ..--I 102704   

                        1 F ARNAYT6A ARN AYT6A 29.05.04    14  AYTARN6AAYT ARN  12.06.04    2 1 -         

                        2 H ALHERE      29.05.04   2 12.06.04  14   1A21  N    2  1      8894.00          

                        3 Q NAAYT A FR_HBUCHERVORTEIL - DEUTSCH    1F 2  2               -400.00          

                        4 Q NAAYT A ELLOS-RABATT-DEUTSCH           1F 2  2               -200.00          

                        5 B AQUA    GRATISEINTRITT IN EINEN AQUAPARK !               1                    

                        6 B ELLI    *** ELLOS KUNDE ***                              1                    

                        7 B MOORE   *** SPECIAL *** ******                                                         

                        8 E VERS    REISER_CKTRITTSKOSTENVERSICHERUNG             3 V     600.00          

                         

                        <- new order ->

                         

                        200670.1    Z 1 16.03.04 LILJESSON, LA INET PTACVSICINE..-- ..--I 101200   

                        1 F CPHAYT7A CPH AYT7A 30.05.04    14  AYTCPH7AAYT CPH  13.06.04    2 3 -         

                        2 H SICINE      30.05.04   2 13.06.04  14   1F23  F    2  3     15190.00          

                        3 Q NAAYT A FR_HBUCHERVORTEIL - DEUTSCH    1F 2  2               -400.00          

                        4 Q NAAYT A ELLOS-RABATT-DEUTSCH           1F 2  2               -200.00          

                        5 B AQUA    GRATISEINTRITT IN EINEN AQUAPARK !               1                    

                        6 B TTXKVB  KEINE VERSICHERUNG GEWÇ_NSCHT !                                       

                        7 B ELLOS   ,CODE: SRTZUC                                                         

                        8 B ELLC    ELLOS ANGEBOT FUER ELLOS KONTO.                                       

                        9 B ELLC    -


                        2.                                                           

                        10 B ELLC    -


                        3.                                                           

                        11 B ELLC    -


                        4.                                                           

                        12 B ELLC    -


                        5.                                                           

                        13 B ELLC    -


                        6.                                                           

                        14 B ELLC    -


                        7.                                               

                         

                        <- new order ->           

                        201019.1    Z 1 16.03.04 JONSSON, LARS INET PTACVINFUGE..-- ..--I 101816   

                        1 F CPHAYT7A CPH AYT7A 30.05.04     7  AYTCPH7AAYT CPH  06.06.04    2 - -         

                        2 H INFUGE      30.05.04   1 06.06.04   7   1DZ   A    2         7896.00          

                        .

                        .

                        .

                         

                          /font[/quote]I`ve defined the ordernumber eg[b] 201410[/b] to a detail the ,CODE:[/b] as 1st attached and selector. The Code itself eg. SRTZUC is the field (6 digits long).

                         

                        The result is:

                        I`ll get no ordernumber, when no ,CODE: is found and the sorting become confused.

                        What i want is getting also the ordernumber and if no code is found , generat a empty or NULL field. Not very heavy i think.

                         

                        Maybe i made a mistake and Monarch is not the right tool to do this for me.

                         

                        Regards

                        Rolf

                         

                        [size="1"][ May 05, 2004, 04:14 PM: Message edited by: starpower ][/size]

                        • Want to have NULL fields

                          Hi Bruce,

                           

                          that is exactly what i did:

                          [font="courier"]201410.1    D 1 01.04.04 OLSSON, SIV N INET PTAGVINPEGE..-- ..--I 102607   

                          1 F GOTAYT6A GOT AYT6A 29.05.04     7  AYTGOT6AAYT GOT  05.06.04    2 3 -         

                          2 H INPEGE      29.05.04   1 05.06.04   7   1ZK   A    2  1     16644.00          

                          3 Q NAAYT A FR_HBUCHERVORTEIL - DEUTSCH    1F 2  2               -400.00          

                          4 Q NAAYT A FR_HBUCHERVORTEIL - DEUTSCH    1F 1  2               -200.00          

                          5 Q NAAYT A ELLOS-RABATT-DEUTSCH           1F 2  2               -200.00          

                          6 Q NAAYT A ELLOS-RABATT-DEUTSCH           1F 1  2               -100.00          

                          7 B AQUA    GRATISEINTRITT IN EINEN AQUAPARK !               1                    

                          8 B ELLI    *** ELLOS KUNDE ***                              1                    

                          9 H INPEGE      29.05.04   1 05.06.04   7   1DZ   A       2     11096.00          

                          10 Q NAAYT A FR_HBUCHERVORTEIL - DEUTSCH    1F 2  2               -400.00          

                          11 Q NAAYT A ELLOS-RABATT-DEUTSCH           1F 2  2               -200.00          

                          12 B AQUA    GRATISEINTRITT IN EINEN AQUAPARK !               1                    

                          13 B ELLI    *** ELLOS KUNDE ***                              1                    

                          14 B ELLOS   ,CODE: SRTZUC                                                      

                          15 E VERS    REISER_CKTRITTSKOSTENVERSICHERUNG             5 V    1000.00          

                          16 L PRIS    ERMAESSIGUNG WRONG PRICE PRICELIST /CATHA       V   -1500.00          

                           

                          <- new order ->

                           

                          201445.1    Z 1 29.03.04 BERGDAHL, ING INET PTARVALHERE..-- ..--I 102704   

                          1 F ARNAYT6A ARN AYT6A 29.05.04    14  AYTARN6AAYT ARN  12.06.04    2 1 -         

                          2 H ALHERE      29.05.04   2 12.06.04  14   1A21  N    2  1      8894.00          

                          3 Q NAAYT A FR_HBUCHERVORTEIL - DEUTSCH    1F 2  2               -400.00          

                          4 Q NAAYT A ELLOS-RABATT-DEUTSCH           1F 2  2               -200.00          

                          5 B AQUA    GRATISEINTRITT IN EINEN AQUAPARK !               1                    

                          6 B ELLI    *** ELLOS KUNDE ***                              1                    

                          7 B MOORE   *** SPECIAL *** ******                                                         

                          8 E VERS    REISER_CKTRITTSKOSTENVERSICHERUNG             3 V     600.00          

                           

                          <- new order ->

                           

                          200670.1    Z 1 16.03.04 LILJESSON, LA INET PTACVSICINE..-- ..--I 101200   

                          1 F CPHAYT7A CPH AYT7A 30.05.04    14  AYTCPH7AAYT CPH  13.06.04    2 3 -         

                          2 H SICINE      30.05.04   2 13.06.04  14   1F23  F    2  3     15190.00          

                          3 Q NAAYT A FR_HBUCHERVORTEIL - DEUTSCH    1F 2  2               -400.00          

                          4 Q NAAYT A ELLOS-RABATT-DEUTSCH           1F 2  2               -200.00          

                          5 B AQUA    GRATISEINTRITT IN EINEN AQUAPARK !               1                    

                          6 B TTXKVB  KEINE VERSICHERUNG GEWÇ_NSCHT !                                       

                          7 B ELLOS   ,CODE: SRTZUC                                                         

                          8 B ELLC    ELLOS ANGEBOT FUER ELLOS KONTO.                                       

                          9 B ELLC    -


                          2.                                                           

                          10 B ELLC    -


                          3.                                                           

                          11 B ELLC    -


                          4.                                                           

                          12 B ELLC    -


                          5.                                                           

                          13 B ELLC    -


                          6.                                                           

                          14 B ELLC    -


                          7.                                               

                           

                          <- new order ->           

                          201019.1    Z 1 16.03.04 JONSSON, LARS INET PTACVINFUGE..-- ..--I 101816   

                          1 F CPHAYT7A CPH AYT7A 30.05.04     7  AYTCPH7AAYT CPH  06.06.04    2 - -         

                          2 H INFUGE      30.05.04   1 06.06.04   7   1DZ   A    2         7896.00          

                          .

                          .

                          .

                           

                            /font[/quote]I`ve defined the ordernumber eg[b] 201410[/b] to a detail the ,CODE:[/b] as 1st attached and selector. The Code itself eg. SRTZUC is the field (6 digits long).

                           

                          The result is:

                          I`ll get no ordernumber, when no ,CODE: is found and the sorting become confused.

                          What i want is getting also the ordernumber and if no code is found , generat a empty or NULL field. Not very heavy i think.

                           

                          Maybe i made a mistake and Monarch is not the right tool to do this for me.

                           

                          Regards

                          Rolf

                           

                          [size="1"][ May 05, 2004, 04:14 PM: Message edited by: starpower ][/size]

                          • Want to have NULL fields
                            Bruce _

                            Rolf

                             

                            Would it be OK for your report to pick up all the ",CODE" & match it to the proper order? However you would lose all the orders that do NOT have ,code in them.

                             

                            If this is OK for you, make the detail line the line with ",CODE", and then use a page header to add the order number.

                             

                            Perhaps some of the experts on this forum have a better idea.

                             

                            Good luck

                            • Want to have NULL fields
                              Bruce _

                              Rolf

                               

                              Would it be OK for your report to pick up all the ",CODE" & match it to the proper order? However you would lose all the orders that do NOT have ,code in them.

                               

                              If this is OK for you, make the detail line the line with ",CODE", and then use a page header to add the order number.

                               

                              Perhaps some of the experts on this forum have a better idea.

                               

                              Good luck

                              • Want to have NULL fields

                                Hi Bruce,

                                 

                                first off all thank you for you help.

                                 

                                But i need ALL ordernumbers with or without.

                                 

                                Who can i arrange it that somebodyelse will have a look to my problem. The thread is here for many days ?

                                 

                                Best regards

                                Rolf

                                • Want to have NULL fields

                                  Hi Bruce,

                                   

                                  first off all thank you for you help.

                                   

                                  But i need ALL ordernumbers with or without.

                                   

                                  Who can i arrange it that somebodyelse will have a look to my problem. The thread is here for many days ?

                                   

                                  Best regards

                                  Rolf

                                  • Want to have NULL fields
                                    Scott Johnson

                                    In looking at how you have went about modeling the report, I would think that you are getting the wrong code attached with the order number.  I would start with the detail being all of the information listed with the sequencial numbers going down on the left.  Each one would be a unique record.  This would pick up each instance of the CODE reference.  You would then append the Order number to each of the records.  Then I would make a calculated field to identify the CODE references using the INSTR Function.  After that it would just be a matter of creating a summary that showed all of the unique record numbers and any CODE references that might be attached with them.

                                    • Want to have NULL fields
                                      Scott Johnson

                                      In looking at how you have went about modeling the report, I would think that you are getting the wrong code attached with the order number.  I would start with the detail being all of the information listed with the sequencial numbers going down on the left.  Each one would be a unique record.  This would pick up each instance of the CODE reference.  You would then append the Order number to each of the records.  Then I would make a calculated field to identify the CODE references using the INSTR Function.  After that it would just be a matter of creating a summary that showed all of the unique record numbers and any CODE references that might be attached with them.

                                      • Want to have NULL fields
                                        Steve Caiels

                                        Hi,

                                         

                                        The preceding string should do the trick.

                                         

                                        Select the detail line (i.e. the line that starts 2014 . . . ) and use a trap that selects each of these lines (maybe the . in the 7th column position for example.

                                         

                                        Highlight a field so that it is in the correct column to cover the code field.  I think this will be from the first 4 of the 01.04.04

                                         

                                        Go to the field properties and select the advanced tab.  Now type CODE: in the preceding string box of the start field options.

                                         

                                        This will cause Monarch to search down the page and highlight the field only the word CODE: is found in the columns to the left or your field highlight before it gets to the next template.

                                         

                                        "Maybe i made a mistake and Monarch is not the right tool to do this for me"[/quote]:eek:  Monarch is  always[/i]  the right tool     

                                        Regards

                                        Steve

                                         

                                        [size="1"][ May 07, 2004, 11:09 AM: Message edited by: Steve Caiels ][/size]

                                        • Want to have NULL fields
                                          Steve Caiels

                                          Hi,

                                           

                                          The preceding string should do the trick.

                                           

                                          Select the detail line (i.e. the line that starts 2014 . . . ) and use a trap that selects each of these lines (maybe the . in the 7th column position for example.

                                           

                                          Highlight a field so that it is in the correct column to cover the code field.  I think this will be from the first 4 of the 01.04.04

                                           

                                          Go to the field properties and select the advanced tab.  Now type CODE: in the preceding string box of the start field options.

                                           

                                          This will cause Monarch to search down the page and highlight the field only the word CODE: is found in the columns to the left or your field highlight before it gets to the next template.

                                           

                                          "Maybe i made a mistake and Monarch is not the right tool to do this for me"[/quote]:eek:  Monarch is  always[/i]  the right tool     

                                          Regards

                                          Steve

                                           

                                          [size="1"][ May 07, 2004, 11:09 AM: Message edited by: Steve Caiels ][/size]

                                          • Want to have NULL fields

                                            Hi Steve,

                                             

                                            never thought of how easy it could be.  smile.gif[/img] 

                                             

                                            This simple trick does it very well.

                                             

                                              Monarch is always the right tool  /quoteAbsolutly true.    

                                             

                                            Thanks to all who have helped me.

                                             

                                            Regards

                                            Rolf

                                            • Want to have NULL fields

                                              Hi Steve,

                                               

                                              never thought of how easy it could be.  smile.gif[/img] 

                                               

                                              This simple trick does it very well.

                                               

                                                Monarch is always the right tool  /quoteAbsolutly true.    

                                               

                                              Thanks to all who have helped me.

                                               

                                              Regards

                                              Rolf