5 Replies Latest reply: May 15, 2014 10:11 AM by Olly Bond RSS

    Vertex sales tax register - detail - optional lines

    DWLINTON _

      VERTEX SALES TAX REGISTER - DETAIL REPORT

      Trouble doing the template because the number of lines in the detail section are not always the same. There are 5 fixed lines that are always present,

      CITY line

      COMPANY-CD line

      EXEMPT CERTIFICATE line

      INV (invoice) line

      TAX RATE/TYPE line

      /LIST

      followed by 1-4 optional lines. If present they are always in this order:

      GROSS SALES line

      NON-TAXBLE AMT/C line

      EXEMPT AMT/CD line

      ZERO RATE AMT line

      /LIST

      but sometimes lines present are GROSS SALES, EXEMPT AMT/CD, ZERO RATE.

      Other times the lines present are GROSS SALES, NON-TAXBLE AMT/C, ZERO RATE AMT.

      Other times the lines present are GROSS SALES, ZERO RATE AMT.

      it appears that if any optional lines are present, GROSS SALES will be among them, but the other optional lines may or may not all be there.

       

      FINALLY after the 1-4 OPTIONAL lines, 2 more lines are ALWAYS present

      TAXABLE AMT line

      TAX AMT line

      /LIST

       

      the layout of each line (if present) is fixed, so extracting the specific character positions is no problem.

       

      I need to extract fields from each line that is present. I'd like the values for the fields on a missing optional line to be null.

       

      I've tried appending but this incorrectly adds data from previous detail records when one of the optional lines is missing.

       

      I can't use the multiple line detail line completely because the number of lines varies from 7 to 11.

       

      Any help would be appreciated

        • Vertex sales tax register - detail - optional lines
          Olly Bond

          Hello DWLinton, and welcome,

           

          You can use the multiple line detail template, as long as you have one record which has every possible line. If not, you might have to cheat a little by running Notepad, and copying from your report a sample record with everything:

           

          CITY line

          COMPANY-CD line

          EXEMPT CERTIFICATE line

          INV (invoice) line

          TAX RATE/TYPE line

          GROSS SALES line

          NON-TAXBLE AMT/C line

          EXEMPT AMT/CD line

          ZERO RATE AMT line

          TAXABLE AMT line

          TAX AMT line[/CODE]

           

          Save the file in Notepad and open Monarch, and open the text file you created as your report.

           

          Now define your detail template on an 11 line sample, but for all the fields after the first five fixed lines, double click on the fields to get the Field Properties dialog. Then click on the Advanced tab, and enter the preceding string (in lowercase): "gross sales" (or whatever appears in that line as the field identifier).

           

          Now Monarch will pick up the data only when the relevant line appears.

           

          Hope this helps,

           

          Olly

            • Vertex sales tax register - detail - optional lines
              RalphB _

              You can also try what is commonly referred to here as the "Guru Trap".

               

               

              Check out url=http://www.monarchforums.com/showthread.php?3781-Using-an-append-template&highlight=guruGuru Trap[/url] for details.

                • Vertex sales tax register - detail - optional lines
                  DWLINTON _

                  CITY: ELBERTA                   COUNTY: BALDWIN       GEOCODE: 01-003-4158 STR:           T 01-003-4158 F 01-053-0090 A 01-053-0090   

                      COMPANY-CD: 00555  DIVISION-CD: O      CUST-CD: 7027024                           TRANS-CD: N  TYPE: SALE     SUBTYPE: P          

                      EXEMPT CERTIFICATE #:                                                                               

                  INV: 57195700      DATE: 08/03/2011  LINE: 00002  PROD-CD:                                     USER: SO 26541499     00555        

                      TAX RATE/TYPE-----              .040000 ES            .030000 ES            .030000 ES            .000000 ZS                      

                      GROSS SALES------            369.46                369.46                369.46                369.46                             

                      EXEMPT AMT/CD----            369.46  *             369.46  *             369.46  *               0.00                             

                      ZERO RATE AMT----              0.00                  0.00                  0.00                369.46                             

                      TAXABLE AMT------              0.00                  0.00                  0.00                  0.00                             

                      TAX AMT----


                                0.00                  0.00                  0.00                  0.00                   0.00      

                  CITY: ELBERTA                   COUNTY: BALDWIN       GEOCODE: 01-003-4158 STR:           T 01-003-4158 F 01-053-0090 A 01-053-0090   

                      COMPANY-CD: 00555  DIVISION-CD: O      CUST-CD: 7027024                           TRANS-CD: N  TYPE: SALE     SUBTYPE: P          

                      EXEMPT CERTIFICATE #:                                                                               

                  INV: 57195701      DATE: 08/03/2011  LINE: 00003  PROD-CD:                                     USER: SO 26541499     00555        

                      TAX RATE/TYPE-----              .040000 ES            .030000 ES            .030000 ES            .000000 ZS                      

                      GROSS SALES------          1,980.00              1,980.00              1,980.00              1,980.00                             

                      EXEMPT AMT/CD----          1,980.00  *           1,980.00  *           1,980.00  *               0.00                             

                      ZERO RATE AMT----              0.00                  0.00                  0.00              1,980.00                             

                      TAXABLE AMT------              0.00                  0.00                  0.00                  0.00                             

                      TAX AMT----


                                0.00                  0.00                  0.00                  0.00                   0.00      

                  CITY: ELBERTA                   COUNTY: BALDWIN       GEOCODE: 01-003-4158 STR:           T 01-003-4158 F 01-053-0090 A 01-053-0090   

                      COMPANY-CD: 00555  DIVISION-CD: O      CUST-CD: 7027024                           TRANS-CD: N  TYPE: SALE     SUBTYPE: P          

                  /CODE

                   

                  So I create the trap as 11 lines to include all possible included lines. Used the advance tab with preceding text in current line to correctly extract the data from lines 6-10 in the first set.

                  The first set had only 10 lines present.

                  The 2nd set of lines also has only 10 lines present but was missed entirely because it thought the line beginning with CITY: was line 11 of the trap.

                   

                  How do I force the detail trap to start over each time it encounters CITY:

                  Note: there is no blank line between sets of lines.

                   

                  Thanks. I know I'm close.

                    • Vertex sales tax register - detail - optional lines
                      Grant Perkins

                      As Ralph said above, as described this sounds like a typical Guru Trap application.

                       

                      For your detail sample and trap use the lines that always exist with field always in the same place.

                       

                      For the variable fields create an Append template using the exact same trap as the detail and as many samles lines as you need - at least 4 as I read your description but you could make it the same size as the detail. The text in the sample lines is not at all important for this purpose.

                       

                      Use this append template for the 4 fields not always present.

                       

                      You could also use it for the 2 fields that appear at the bottom of the list. Or you could add them to the detail template (because they are always there) and simply use the preceding string feature to 'find' them even if they are floating about in the record.

                       

                      Note that the use of the same trap in both templates means that the Append Trap will always be reset whenever a detail trap is encountered - which can be quite desirable in a number of situations.

                       

                      Paint the 4 fields that sometimes appear in their correct horizontal position on a line. The order of the lines does not matter.

                       

                      For each field go into the advanced properties and set a preceding string value of the field 'name'.  FOr example the preceding string for the field you can call 'Gross Sales' would most likely be 'Gross Sales' for clarity although in this example you might be able to use just 'gross'. So long as there is no duplication of names this will work. 'Exempt', alone would fail as the word Exempt appears on line 5 of the full record. But 'Exempt AMT/CD' is a more unique string and should work as expected. The same comments apply to the 'Tax' lines.

                       

                      The key (and slight variance with Olly's suggestion) is that you need to know and use the smallest number of lines that ever appears in the detail record so that the template does not overlap the next possible record. Using the 'same trap append template' concepts together with one or several appends, depending in how many variable fields you have to capture, gives you the flexibility you need (in most cases found to date!).

                       

                      I hope this helps.

                       

                       

                      Grant

                        • Vertex sales tax register - detail - optional lines
                          Olly Bond

                          Hello everyone,

                           

                          Thanks for spotting that the guru trap would be a better fix in this case, as there aren't enough lines in every record to handle all the required preceding string options.

                           

                          As the original poster is running v9, this is certainly the best approach. In v10 and above, it's also possible to handle it using separate footer templates for each line, each of which is Cleared By the next detail.

                           

                          Best wishes,

                           

                          Olly