0 Replies Latest reply: Sep 3, 2010 9:12 AM by TMERRYMAN _ RSS

    Records are not in table

    TMERRYMAN _

      I am trying to set up my detail to catch all my records, however, the way I have it set up is leaving out records.  This report shows our customers and whether they are sales tax exempt.  It also shows the customers locations and whether they are sales tax exempt.

       

      Currently, I have the last "TAX CODE:" line under Location as the detail line.  However, this line does not exist for every locaton, because not all locations are exempt.  So, when I go to the table, those records with no exemptions are left out.  I would like them in the table as blank records with a location number.

       

      I also tried to make the detail include the line beginning with "Location:" and stretching it down to include all of the lines, through the "TAX CODE:" lines.  However, I can not seem to figure out how to make it end where the next "Location" begins.  Is this even possible?  Any other suggestions?  I have given up many times on this report, but really hope there is a way to do it that isn't too complicated.  I am not a programmer, so I do the best I can.

       

      Thanks for any help or suggestions! 

       

      LOCATION: 8001 ABC Avenue/PAT120                              PRIMARY LOCN: N             

         ADD.1:                                  PHONE:                   EXT:               

         ADD.2:                                    FAX:                 BATCH: N             

         ADD.3:                                CONTACT:                                      

          CITY: Quoted Freight Rate            SLSPRSN: 1027 Kelly Forester                  

         STATE: TX ZIP: QUOTE             TAX SCHEDULE: TX SMITYL                            

                                             WAREHOUSE: W08 Tyler Warehouse                  

          TAX CODE:  TX                 EXEMPTION:  17528998788                              

          TAX CODE:  TX SMI             EXEMPTION:  17528998788                              

          TAX CODE:  TX SMITYL          EXEMPTION:  17528998788                                                                               

      Run Date: 09/03/2010  TM       Thomas Petroleum, LLC            Page: 68               

      A/R Date: 09/03/2010             CUSTOMER LISTING               Time: 10:14 AM         

                                  PERMANENT CUSTOMERS ONLY                                                                               

      CUSTOMER NO:  0004132      NAME:  Patterson UTI Drilling                               

      -


                                                                                       

      LOCATION: 102 Main St/PAT452                          PRIMARY LOCN: N             

         ADD.1:                                  PHONE: (903) 574-1204    EXT:               

         ADD.2:                                    FAX:                 BATCH: N             

         ADD.3:                                CONTACT:                                      

          CITY: 101 - 125 Miles                SLSPRSN: 1008 Jess Martin                     

         STATE: LA ZIP: FR125             TAX SCHEDULE: LA DESRUR                            

                                             WAREHOUSE: W08 Tyler Warehouse                                                                               

      LOCATION: 505 Hwy 59/PAT131                   PRIMARY LOCN: N             

         ADD.1:                                  PHONE:                   EXT:               

         ADD.2:                                    FAX:                 BATCH: N             

         ADD.3:                                CONTACT:                                      

          CITY: 26 - 50 Miles                  SLSPRSN: 1012 Gus Orellana                    

         STATE: LA ZIP: FR050             TAX SCHEDULE: LA VERRUR                            

                                             WAREHOUSE: W11 Lafayette Warehouse                                                                               

      /CODE

        • Records are not in table
          Data Kruncher

          Is it important that you treat each exemption as a separate record, so that you have:

           

          Location                 TaxCode

          8001 ABC Avenue/PAT120   TX

          8001 ABC Avenue/PAT120   TX SMI

          8001 ABC Avenue/PAT120   TX SMITYL

          /codeor would a single record for each location, with separate exemption fields as necessary, suffice? In that case you'd have Location, TaxCode1, TaxCode2, TaxCode3 ... TaxCodeN and similarly named ExemptionNumber fields.

           

          Location                 TaxCode1   TaxCode2  TaxCode3

          8001 ABC Avenue/PAT120   TX         TX SMI    TX SMITYL

          /codeOf course you could capture the other fields as well.

            • Records are not in table
              TMERRYMAN _

              I think either way would work for me.

               

              Teresa

                • Records are not in table
                  Data Kruncher

                  Great, I have a solution then but don't have the time to write it up just now. Give me 45 minutes or so. OK?

                   

                  The "real" job interferes from time to time.

                    • Records are not in table
                      TMERRYMAN _

                      That sounds great!  I'll wait.  Thanks.

                       

                       

                        • Records are not in table
                          Data Kruncher

                          Right then, where were we? Ah, yes...

                           

                          Start with an eight line detail template, beginning with the Location and ending with the first line with TAX CODE.

                           

                          For this purpose I'll focus only on the Location field and the two tax fields.

                           

                          Paint the Location field, and the TaxCode and Exemption fields. For each of the tax fields, go to the Advanced tab of the field properties dialog and set the End Field On option to End of left Justification. Be sure to make these field Character fields.

                           

                          That's it for the template work. Close it and move to the Table window.

                           

                          Create a new formula-based Character calculated field. Named TaxCode1, give it the formula:

                          TextLine(TaxCode,1)[/code]Use the Calculated Fields dialog to Duplicate this field to create TaxCode2, changing the formula ever so slightly to read:

                          TextLine(TaxCode,2)[/code]Do the same for TaxCode3, and then use a similar approach to create the three Exemption fields.

                           

                          Create more calculated fields as necessary if elsewhere in your report you have instances that display more than three tax codes.

                           

                          Now you can hide the main TaxCode and Exemption fields.

                           

                          How's that?

                            • Records are not in table
                              TMERRYMAN _

                              That sounded like it would work.  However, it is not pulling any values over into the tables for the calculated fields I created.  I am not sure why.

                                • Records are not in table
                                  Data Kruncher

                                  Did you make the TaxCode and Exemption fields Character type?

                                    • Records are not in table
                                      TMERRYMAN _

                                      Yes, I did.

                                       

                                      On the advanced tab, I have Start Field on "Line number 8" and end field on "End of left Justification".  Is that right?

                                        • Records are not in table
                                          Data Kruncher

                                          Your change to Blank field values: 1 is (should be?) interchangeable in this particular instance.

                                           

                                          Hey, whatever works!

                                           

                                          So you're on your way now? Everything looks good and /Iyou understand it too?

                                            • Records are not in table
                                              TMERRYMAN _

                                              I think it is working, which is amazing to me.  (I might cry tears of joy)

                                               

                                              Do I understand it?  Well, I am not sure what the Advanced field functions do and, until now, have never needed them.  I am looking these things up in my learning guide right now.

                                               

                                              I can't tell you much this will improve my job.  Thanks a heap!

                                                • Records are not in table
                                                  Data Kruncher

                                                  https://community.datawatch.com/QUOTEI can't tell you much this will improve my job.[/QUOTE]

                                                   

                                                  Excellent! Really great.

                                                   

                                                  FYI, here's a nice write-up on [URL is no longer valid], and this piece on the [URL is no longer valid] items, which is part of the [URL is no longer valid] series, might also be of interest.

                                                    • Records are not in table
                                                      TMERRYMAN _

                                                      Wow, that's great!  I just printed the articles and am so grateful.

                                                        • Records are not in table
                                                          TMERRYMAN _

                                                          Okay, I have another problem.  With the same report, I am trying to add an append template that gives me the customer number, name, schedule, tax codes, and exemption numbers.

                                                           

                                                          Here is what it looks like.  I'll include the detail too which begins with Location:

                                                           

                                                          CUSTOMER NO:  0001011      NAME:  Apache Corp-Houston Drilling                 

                                                          -


                                                           

                                                              ADDRESS:                                             SORT FIELD:  COLL 1                                                                               

                                                          CITY:  Houston                             TEMPORARY CUST?:  N          

                                                              COUNTRY:  USA United States of America        OPEN ITEM CUST?:  Y          

                                                                STATE:  TX        ZIP CODE: 77227           STATEMENT CYCLE:  X          

                                                             PHONE NO:  (999) 999-9999     EXT:         PRINT DUN MSSG?:  Y          

                                                                  FAX:  (999) 999-9999   BATCH FAX: N         CUSTOMER TYPE:  OGOP       

                                                              CONTACT:                                                                   

                                                                EMAIL:                                                                   

                                                                  URL:                                                                   

                                                          SHIP METHOD:                                                                   

                                                             SCHEDULE:  TX VICVIC                               PRICE LEVEL:  C          

                                                              SHIP-TO:                                        DISCOUNT RATE:      .000%  

                                                          TERMS CODE:  30   Net 30 days                    FIN CHARGE RATE:      .000%  

                                                          SALESPERSON:  0000 House Account                                               

                                                             SALES CD:                                         CREDIT LIMIT:         .00 

                                                              COMMENT:                                      ON CREDIT HOLD?:  N          

                                                          DFLT PMT TYP: CHECK      CHECK                                                 

                                                          C-STORE LOC:  N                                                                

                                                          -


                                                           

                                                          TAX CODE:  TX                 EXEMPTION NUMBER: SIGNED STATEMENT               

                                                          TAX CODE:  TX VIC             EXEMPTION NUMBER: SIGNED STATEMENT               

                                                          TAX CODE:  TX VICVIC          EXEMPTION NUMBER: SIGNED STATEMENT               

                                                          -


                                                           

                                                          LOCATION: 0505 T REALITY -PATT 2500                  PRIMARY LOCN: N     

                                                             ADD.1:                                  PHONE:                   EXT:       

                                                             ADD.2:                                    FAX:                 BATCH: N     

                                                             ADD.3:                                CONTACT:                              

                                                              CITY:                                SLSPRSN: 0000 House Account           

                                                             STATE: TX ZIP: FR100             TAX SCHEDULE: TX NUEROB                    

                                                                                                 WAREHOUSE: W05 Corpus Warehouse         

                                                              TAX CODE:  TX                 EXEMPTION:  SIGNED STATEMENT                 

                                                              TAX CODE:  TX NUE             EXEMPTION:  SIGNED STATEMENT                 

                                                              TAX CODE:  TX NUEROB          EXEMPTION:  SIGNED STATEMENT                                                                               

                                                          /CODE

                                                           

                                                          My problem is the same as before.  Not all customers are exempt, so if they are not, it looks like this.  Notice it does not include the Tax Code lines below the C-Store Loc line.

                                                           

                                                          CUSTOMER NO:  0001010      NAME:  AAA Sanitation                                       

                                                          -


                                                                    

                                                              ADDRESS:  3000 W Clayburn                            SORT FIELD:  COLL 1                                                                               

                                                          CITY:  Tyler                               TEMPORARY CUST?:  N                  

                                                              COUNTRY:  USA United States of America        OPEN ITEM CUST?:  Y                  

                                                                STATE:  TX        ZIP CODE: 75702           STATEMENT CYCLE:  X                  

                                                             PHONE NO:  (555) 123-4567         EXT:         PRINT DUN MSSG?:  Y                  

                                                                  FAX:  (555) 123-7890   BATCH FAX: N         CUSTOMER TYPE:  FLCC               

                                                              CONTACT:                                                                               

                                                          EMAIL:                                                                               

                                                          URL:                                                                           

                                                          SHIP METHOD:                                                                               

                                                          SCHEDULE:  TX SMITYL                               PRICE LEVEL:  C                  

                                                              SHIP-TO:                                        DISCOUNT RATE:      .000%          

                                                          TERMS CODE:  30   Net 30 days                    FIN CHARGE RATE:      .000%          

                                                          SALESPERSON:  0000 House Account                                                       

                                                             SALES CD:                                         CREDIT LIMIT:         .00         

                                                              COMMENT:                                      ON CREDIT HOLD?:  Y                  

                                                          DFLT PMT TYP: NONE                                                                     

                                                          C-STORE LOC:  N                                                                        

                                                          -


                                                                    

                                                          LOCATION: 8000 blah                                        PRIMARY LOCN: N             

                                                             ADD.1: jan                             PHONE:                   EXT:               

                                                             ADD.2:                                    FAX:                 BATCH: N             

                                                             ADD.3:                                CONTACT:                                      

                                                              CITY: 0 - 25 miles                   SLSPRSN: 1008 Jess Martin                     

                                                             STATE: TX ZIP: FR025             TAX SCHEDULE: TX SMITYL                            

                                                                                                 WAREHOUSE: W08 Tyler Warehouse                  

                                                          -


                                                                    

                                                          /CODE

                                                           

                                                          I'm not sure how to get around this.  I tried doing two appends: one grabbing cust no and name and one grabbing tax codes, but I couldn't get that to work.  Then I tried doing a multi-line trap, but that wasn't working either.

                                                           

                                                          I'm sorry to bother you again.

                                                           

                                                          One extra question that isn't as important, but would be handy...is there a way to make Monarch ignore a string that falls in a report?  This annoying string is "------" which falls sporatically throughout my report.  I was just wondering.

                                                            • Records are not in table
                                                              Data Kruncher

                                                              This is a bit trickier, but absolutely manageable.

                                                               

                                                              The detail template stays the same, using the Location name (which may or may not have associated tax codes), but with a minor enhancement. On the Advanced tab, specify that TaxCode contains the "preceding string: tax code in current line". Do the same with the Exemption field, using the word exemption for the preceding string.

                                                               

                                                              Then build the append template to pick up the customer info. It'll be a 23 line template, trapped using customer in position 1. For the customer tax code and exemption fields, use the same approach, employing the preceding string option.

                                                               

                                                              Then you end up splitting the captured CustomerTaxCode and CustomerExemption into CustomerTaxCode1 and CustomerExemption1 (etc.).

                                                               

                                                              But as you mention, the line of dashes rather throws a bit of wrinkle into it, and no, we can't tell Monarch to somehow ignore those lines on input. But not to fear, we work around it instead, taking those dashes into consideration.

                                                               

                                                              That means that CustomerTaxCode1 becomes:

                                                              If(Left(Textline(,1),1)="-","",Textline(,1))[/CODE]

                                                               

                                                              Literally: if the first character in this line is a dash (which I assume is not normal for tax codes), then return a blank (""), otherwise it's not a dash, so use this line.

                                                               

                                                              Do the same with the exemption field and build the other fields as described above and you should be fine.

                                                               

                                                              For reference, CustomerTaxCode2 would be:

                                                              if(left(Textline(,2),1)="-","",Textline(,2))[/CODE]

                                                               

                                                              All good?

                                                                • Records are not in table
                                                                  TMERRYMAN _

                                                                  It's not pulling the correct data.  Which should the end field on be for the customer tax code and exemption fields?

                                                                    • Records are not in table
                                                                      Data Kruncher

                                                                      I used End of left Justification.

                                                                       

                                                                      For reference I put your samples together as follows (which may or may not accurately depict the actual report):

                                                                      CUSTOMER NO:  0001011      NAME:  Apache Corp-Houston Drilling                              

                                                                      -


                                                                                     

                                                                          ADDRESS:                                             SORT FIELD:  COLL 1                                                                               

                                                                      CITY:  Houston                             TEMPORARY CUST?:  N                       

                                                                          COUNTRY:  USA United States of America        OPEN ITEM CUST?:  Y                       

                                                                            STATE:  TX        ZIP CODE: 77227           STATEMENT CYCLE:  X                       

                                                                         PHONE NO:  (999) 999-9999     EXT:         PRINT DUN MSSG?:  Y                           

                                                                              FAX:  (999) 999-9999   BATCH FAX: N         CUSTOMER TYPE:  OGOP                    

                                                                          CONTACT:                                                                               

                                                                      EMAIL:                                                                               

                                                                      URL:                                                                               

                                                                      SHIP METHOD:                                                                               

                                                                      SCHEDULE:  TX VICVIC                               PRICE LEVEL:  C                       

                                                                          SHIP-TO:                                        DISCOUNT RATE:      .000%               

                                                                      TERMS CODE:  30   Net 30 days                    FIN CHARGE RATE:      .000%               

                                                                      SALESPERSON:  0000 House Account                                                            

                                                                         SALES CD:                                         CREDIT LIMIT:         .00              

                                                                          COMMENT:                                      ON CREDIT HOLD?:  N                       

                                                                      DFLT PMT TYP: CHECK      CHECK                                                              

                                                                      C-STORE LOC:  N                                                                             

                                                                      -


                                                                                     

                                                                      TAX CODE:  TX                 EXEMPTION NUMBER: SIGNED STATEMENT                            

                                                                      TAX CODE:  TX VIC             EXEMPTION NUMBER: SIGNED STATEMENT                            

                                                                      TAX CODE:  TX VICVIC          EXEMPTION NUMBER: SIGNED STATEMENT                            

                                                                      -


                                                                                     

                                                                      LOCATION: 0505 T REALITY -PATT 2500                  PRIMARY LOCN: N                        

                                                                         ADD.1:                                  PHONE:                   EXT:                    

                                                                         ADD.2:                                    FAX:                 BATCH: N                  

                                                                         ADD.3:                                CONTACT:                                           

                                                                          CITY:                                SLSPRSN: 0000 House Account                        

                                                                         STATE: TX ZIP: FR100             TAX SCHEDULE: TX NUEROB                                 

                                                                                                             WAREHOUSE: W05 Corpus Warehouse                      

                                                                          TAX CODE:  TX                 EXEMPTION:  SIGNED STATEMENT                              

                                                                          TAX CODE:  TX NUE             EXEMPTION:  SIGNED STATEMENT                              

                                                                          TAX CODE:  TX NUEROB          EXEMPTION:  SIGNED STATEMENT                              

                                                                      -


                                                                                     

                                                                      CUSTOMER NO:  0001010      NAME:  AAA Sanitation                                            

                                                                      -


                                                                                     

                                                                          ADDRESS:  3000 W Clayburn                            SORT FIELD:  COLL 1                                                                               

                                                                      CITY:  Tyler                               TEMPORARY CUST?:  N                       

                                                                          COUNTRY:  USA United States of America        OPEN ITEM CUST?:  Y                       

                                                                            STATE:  TX        ZIP CODE: 75702           STATEMENT CYCLE:  X                       

                                                                         PHONE NO:  (555) 123-4567         EXT:         PRINT DUN MSSG?:  Y                       

                                                                              FAX:  (555) 123-7890   BATCH FAX: N         CUSTOMER TYPE:  FLCC                    

                                                                          CONTACT:                                                                               

                                                                      EMAIL:                                                                               

                                                                      URL:                                                                               

                                                                      SHIP METHOD:                                                                               

                                                                      SCHEDULE:  TX SMITYL                               PRICE LEVEL:  C                       

                                                                          SHIP-TO:                                        DISCOUNT RATE:      .000%               

                                                                      TERMS CODE:  30   Net 30 days                    FIN CHARGE RATE:      .000%               

                                                                      SALESPERSON:  0000 House Account                                                            

                                                                         SALES CD:                                         CREDIT LIMIT:         .00              

                                                                          COMMENT:                                      ON CREDIT HOLD?:  Y                       

                                                                      DFLT PMT TYP: NONE                                                                          

                                                                      C-STORE LOC:  N                                                                             

                                                                      -


                                                                                     

                                                                      LOCATION: 8000 blah                                        PRIMARY LOCN: N                  

                                                                         ADD.1: jan                             PHONE:                   EXT:                     

                                                                         ADD.2:                                    FAX:                 BATCH: N                  

                                                                         ADD.3:                                CONTACT:                                           

                                                                          CITY: 0 - 25 miles                   SLSPRSN: 1008 Jess Martin                          

                                                                         STATE: TX ZIP: FR025             TAX SCHEDULE: TX SMITYL                                 

                                                                                                             WAREHOUSE: W08 Tyler Warehouse                       

                                                                      -


                                                                                     

                                                                      /code

                                                                        • Records are not in table
                                                                          TMERRYMAN _

                                                                          I am not sure what it is doing.  My value in the CustExemption field for one of my records is "SIGNED STATEMENT SIGNED STATEMENT SIGNED STATEMENT" and it looks like it is pulling from some location lines as well.

                                                                           

                                                                          Here is a bigger sample of my report:

                                                                           

                                                                          CUSTOMER NO:  0001010      NAME:  AAA Sanitation                                       

                                                                          -


                                                                                    

                                                                              ADDRESS:  1500 W Claude                            SORT FIELD:  COLL 1                                                                               

                                                                          CITY:  Tyler                               TEMPORARY CUST?:  N                  

                                                                              COUNTRY:  USA United States of America        OPEN ITEM CUST?:  Y                  

                                                                                STATE:  TX        ZIP CODE: 75702           STATEMENT CYCLE:  X                  

                                                                             PHONE NO:  (903) 593-5909         EXT:         PRINT DUN MSSG?:  Y                  

                                                                                  FAX:  (903) 593-2425   BATCH FAX: N         CUSTOMER TYPE:  FLCC               

                                                                              CONTACT:                                                                               

                                                                          EMAIL:                                                                               

                                                                          URL:                                                                           

                                                                          SHIP METHOD:                                                                               

                                                                          SCHEDULE:  TX SMITYL                               PRICE LEVEL:  C                  

                                                                              SHIP-TO:                                        DISCOUNT RATE:      .000%          

                                                                          TERMS CODE:  30   Net 30 days                    FIN CHARGE RATE:      .000%          

                                                                          SALESPERSON:  0000 House Account                                                       

                                                                             SALES CD:                                         CREDIT LIMIT:         .00         

                                                                              COMMENT:                                      ON CREDIT HOLD?:  Y                  

                                                                          DFLT PMT TYP: NONE                                                                     

                                                                          C-STORE LOC:  N                                                                        

                                                                          -


                                                                                    

                                                                          LOCATION: 8000 YARD                                        PRIMARY LOCN: N             

                                                                             ADD.1: ROXANNE                          PHONE:                   EXT:               

                                                                             ADD.2:                                    FAX:                 BATCH: N             

                                                                             ADD.3:                                CONTACT:                                      

                                                                              CITY: 0 - 25 miles                   SLSPRSN: 1008 Jess Martin                     

                                                                             STATE: TX ZIP: FR025             TAX SCHEDULE: TX SMITYL                            

                                                                                                                 WAREHOUSE: W08 Tyler Warehouse                  

                                                                          -


                                                                                                                                                           

                                                                          Run Date: 09/03/2010  TM       Thomas Petroleum, LLC            Page: 2                

                                                                          A/R Date: 09/03/2010             CUSTOMER LISTING               Time: 02:05 PM         

                                                                                                      PERMANENT CUSTOMERS ONLY                                                                               

                                                                          CUSTOMER NO:  0001011      NAME:  Apache Corp-Houston Drilling                         

                                                                          -


                                                                                    

                                                                              ADDRESS:  PO Box 27709                             SORT FIELD:  COLL 1                                                                               

                                                                          CITY:  Houston                             TEMPORARY CUST?:  N                  

                                                                              COUNTRY:  USA United States of America        OPEN ITEM CUST?:  Y                  

                                                                                STATE:  TX        ZIP CODE: 77227           STATEMENT CYCLE:  X                  

                                                                             PHONE NO:  (281) 997-4480         EXT:         PRINT DUN MSSG?:  Y                  

                                                                                  FAX:  (281) 485-7698   BATCH FAX: N         CUSTOMER TYPE:  OGOP               

                                                                              CONTACT:                                                                               

                                                                          EMAIL:                                                                               

                                                                          URL:                                                                           

                                                                          SHIP METHOD:                                                                               

                                                                          SCHEDULE:  TX VICVIC                               PRICE LEVEL:  C                  

                                                                              SHIP-TO:                                        DISCOUNT RATE:      .000%          

                                                                          TERMS CODE:  30   Net 30 days                    FIN CHARGE RATE:      .000%          

                                                                          SALESPERSON:  0000 House Account                                                       

                                                                             SALES CD:                                         CREDIT LIMIT:         .00         

                                                                              COMMENT:                                      ON CREDIT HOLD?:  N                  

                                                                          DFLT PMT TYP: CHECK      CHECK                                                         

                                                                          C-STORE LOC:  N                                                                        

                                                                          -


                                                                                    

                                                                          TAX CODE:  TX                 EXEMPTION NUMBER: SIGNED STATEMENT                       

                                                                          TAX CODE:  TX VIC             EXEMPTION NUMBER: SIGNED STATEMENT                       

                                                                          TAX CODE:  TX VICVIC          EXEMPTION NUMBER: SIGNED STATEMENT                       

                                                                          -


                                                                                    

                                                                          LOCATION: 0505 TEASON REALITY -PATT 130                    PRIMARY LOCN: N             

                                                                             ADD.1:                                  PHONE:                   EXT:               

                                                                             ADD.2:                                    FAX:                 BATCH: N             

                                                                             ADD.3:                                CONTACT:                                      

                                                                              CITY:                                SLSPRSN: 0000 House Account                   

                                                                             STATE: TX ZIP: FR100             TAX SCHEDULE: TX NUEROB                            

                                                                                                                 WAREHOUSE: W05 Corpus Warehouse                 

                                                                              TAX CODE:  TX                 EXEMPTION:  SIGNED STATEMENT                         

                                                                              TAX CODE:  TX NUE             EXEMPTION:  SIGNED STATEMENT                         

                                                                              TAX CODE:  TX NUEROB          EXEMPTION:  SIGNED STATEMENT                                                                               

                                                                          LOCATION: 1001 Pierce Estate B156/BIG6                     PRIMARY LOCN: N             

                                                                             ADD.1:                                  PHONE:                   EXT:               

                                                                             ADD.2:                                    FAX:                 BATCH: N             

                                                                             ADD.3:                                CONTACT:                                      

                                                                              CITY: Quoted Freight Rate            SLSPRSN: 1029 Nicki Martin                    

                                                                             STATE: TX ZIP: QUOTE             TAX SCHEDULE: TX VICVIC                            

                                                                                                                 WAREHOUSE: W01 Victoria Warehouse               

                                                                              TAX CODE:  TX                 EXEMPTION:  SIGNED STATEMENT                         

                                                                              TAX CODE:  TX VIC             EXEMPTION:  SIGNED STATEMENT                         

                                                                              TAX CODE:  TX VICVIC          EXEMPTION:  SIGNED STATEMENT                                                                               

                                                                          Run Date: 09/03/2010  TM       Thomas Petroleum, LLC            Page: 3                

                                                                          A/R Date: 09/03/2010             CUSTOMER LISTING               Time: 02:05 PM         

                                                                                                      PERMANENT CUSTOMERS ONLY                                                                               

                                                                          CUSTOMER NO:  0001011      NAME:  Apache Corp-Houston Drilling                         

                                                                          -


                                                                                                                                                           

                                                                          LOCATION: 1002 Pierce Estate C-250 / Big 6                 PRIMARY LOCN: N             

                                                                             ADD.1: AFE: GP-09-003-DH                PHONE:                   EXT:               

                                                                             ADD.2:                                    FAX:                 BATCH: N             

                                                                             ADD.3:                                CONTACT:                                      

                                                                              CITY: Quoted Freight Rate            SLSPRSN: 1029 Nicki Martin                    

                                                                             STATE: TX ZIP: QUOTE             TAX SCHEDULE: TX VICVIC                            

                                                                                                                 WAREHOUSE: W01 Victoria Warehouse               

                                                                              TAX CODE:  TX                 EXEMPTION:  SIGNED STATEMENT                         

                                                                              TAX CODE:  TX VIC             EXEMPTION:  SIGNED STATEMENT                         

                                                                              TAX CODE:  TX VICVIC          EXEMPTION:  SIGNED STATEMENT                         

                                                                          /CODE

                                                                            • Records are not in table
                                                                              Data Kruncher

                                                                              I think that we need to refine the CustomerExemption and LocationExemption files just a touch.

                                                                               

                                                                              For the CustomerExemption, set the preceding string to:

                                                                              exemption number:

                                                                               

                                                                              For the LocationExemption, set the preceding string to:

                                                                              exemption:

                                                                               

                                                                              Fortunately they have different descriptors so we can take advantage of them.

                                                                                • Records are not in table
                                                                                  TMERRYMAN _

                                                                                  I changed them.  The records where I have clicked on the values and went to the source of the record have showed to come from locations instead of customers.  The CustomerTaxCode, LocationTaxCode, CustExemption, and LocationExemption all seem to have more than one value in each field

                                                                                    • Records are not in table
                                                                                      Data Kruncher

                                                                                      Good, that's what you want. Then you use those fields in the calculated fields outlined earlier and you'll wind up fields as in this example:

                                                                                      CustomerNo Location                        CustTaxCode                       CustTaxCode1    CustTaxCode2    CustTaxCode3

                                                                                      0001010    8000 YARD               

                                                                                      0001011    0505 TEASON REALITY -PATT 130   TX TX VIC TX VICVIC -


                                                                                          TX              TX VIC          TX VICVIC

                                                                                      0001011    1001 Pierce Estate B156/BIG6    TX TX VIC TX VICVIC -


                                                                                          TX              TX VIC          TX VICVIC[/FONT]

                                                                                       

                                                                                      /code

                                                                                        • Records are not in table
                                                                                          TMERRYMAN _

                                                                                          But when I click on the field for custexemption, it should not refer me to a location exemption on the report.  Right?

                                                                                            • Records are not in table
                                                                                              Data Kruncher

                                                                                              Correct. That's why I thought that differentiating using the exemption: and exemption number: text should get that right.

                                                                                               

                                                                                              Send me a Private Message (click on my user name then select Send a private message), with your email address Teresa and I'll send you the model that I built for your review.

                                                                                                • Records are not in table
                                                                                                  TMERRYMAN _

                                                                                                  I am still not convinced that it is pulling the location data from the location taxcodes and the customer data from the customer taxcodes.  The reason I feel that way is because when I click in the table view on a value for the customer, it jumps back to the report on a location taxcode line.  It does this in your model too.  I applied your model to my larger report to see what the effects would be.

                                                                                                   

                                                                                                  Also, since the customerexemption template is an append, I think the customertaxcode should be on every location line for that customer, but it isn't.

                                                                                                   

                                                                                                   

                                                                                                  I apologize for dragging this out all day, and now it's 5pm!

                                                                                                    • Records are not in table
                                                                                                      Data Kruncher

                                                                                                      You mean that when you're in the Table and type Ctrl-F5 (Display Source of Record) it goes to the Location section of the report? That's because the location records are the Detail template.

                                                                                                       

                                                                                                      I did note however that when I opened model accidentally with v10.5 (I built it with v9 to mirror your version) I noticed the shading was overlapping. V10 allows you to shade the various template with different colors instead of grey for everything.

                                                                                                       

                                                                                                      If you make one more tweak to the LocationTaxCode I think all will be fine.

                                                                                                       

                                                                                                      Change the preceding string in the current line from tax code: to String warehouse anywhere in the previous line.

                                                                                                       

                                                                                                      A very particular report layout with similar terminology in different sections played a bit of havoc here.

                                                                                                        • Records are not in table
                                                                                                          TMERRYMAN _

                                                                                                          That makes sense. I made the change.  I am about to verify the field values match my report (just so I feel confident lol). 

                                                                                                           

                                                                                                          I was still wondering why the customer tax code and exemption (if not blank) was not included on every record of the same customer number.

                                                                                                          • Records are not in table
                                                                                                            Data Kruncher

                                                                                                            As if there weren't enough happening here already, I noticed that the last location of your sample wasn't in the Table. This is because of the page header that appears just prior to it.

                                                                                                             

                                                                                                            Add a page header template without painting any field in it. I trapped on run date and used six lines for the template.

                                                                                                             

                                                                                                            Better, and maybe even complete. Maybe.

                                                                                                             

                                                                                                            Hold on. Got ahead of myself again. This won't work well.

                                                                                                              • Records are not in table
                                                                                                                TMERRYMAN _

                                                                                                                Yes, I had included a header in my model once I saw that problem.

                                                                                                                 

                                                                                                                 

                                                                                                                  • Records are not in table
                                                                                                                    Data Kruncher

                                                                                                                    It's another interesting wrinkle that will be easier to test with the full report than a small sample, so continue to review the results pretty carefully.

                                                                                                                      • Records are not in table
                                                                                                                        TMERRYMAN _

                                                                                                                        All looked good until I came across a snag.

                                                                                                                         

                                                                                                                        One record company's customer /Btax codes and exemptions are being copied to the previous record.

                                                                                                                          • Records are not in table
                                                                                                                            TMERRYMAN _

                                                                                                                            Oh, this is where the error occurred:

                                                                                                                             

                                                                                                                            Run Date: 09/03/2010  TM       Thomas Petroleum, LLC            Page: 45              

                                                                                                                            A/R Date: 09/03/2010             CUSTOMER LISTING               Time: 02:05 PM        

                                                                                                                                                        PERMANENT CUSTOMERS ONLY                                                                               

                                                                                                                            CUSTOMER NO:  0001077      NAME:  Wagner Oil Company                                  

                                                                                                                            -


                                                                                                                                                                                                             

                                                                                                                            LOCATION: 5001 GEORGE MERRIMAN GU#2/ORION                  PRIMARY LOCN: N            

                                                                                                                               ADD.1:                                  PHONE:                   EXT:              

                                                                                                                               ADD.2:                                    FAX:                 BATCH: N            

                                                                                                                               ADD.3:                                CONTACT:                                     

                                                                                                                                CITY: 51 - 75 MIles                  SLSPRSN: 1002 Laura Varela                   

                                                                                                                               STATE: TX ZIP: FR075             TAX SCHEDULE: TX NUEROB                           

                                                                                                                                                                   WAREHOUSE: W05 Corpus Warehouse                

                                                                                                                            -


                                                                                                                                                                                                             

                                                                                                                            CUSTOMER NO:  0001078      NAME:  DuPont DeNemours EI & Co                            

                                                                                                                            -


                                                                                                                                     

                                                                                                                                ADDRESS:  Invista Inc                              SORT FIELD:  COLL 1            

                                                                                                                                          20 Old Hickory Blvd                                                     

                                                                                                                                   CITY:  Old Hickory                         TEMPORARY CUST?:  N                 

                                                                                                                                COUNTRY:  USA United States of America        OPEN ITEM CUST?:  Y                 

                                                                                                                                  STATE:  TN        ZIP CODE: 37138           STATEMENT CYCLE:  M                 

                                                                                                                               PHONE NO:  (615) 847-6920         EXT:         PRINT DUN MSSG?:  Y                 

                                                                                                                                    FAX:  (361) 572-2350   BATCH FAX: N         CUSTOMER TYPE:  INDU              

                                                                                                                                CONTACT:                                                                               

                                                                                                                            EMAIL:                                                                               

                                                                                                                            URL:                                                                          

                                                                                                                            SHIP METHOD:                                                                          

                                                                                                                               SCHEDULE:  TX VICVIC                               PRICE LEVEL:  C                 

                                                                                                                                SHIP-TO:                                        DISCOUNT RATE:      .000%         

                                                                                                                            TERMS CODE:  30   Net 30 days                    FIN CHARGE RATE:     1.500%         

                                                                                                                            SALESPERSON:  1001 Clifford Goebel                                                    

                                                                                                                               SALES CD:                                         CREDIT LIMIT:         .00        

                                                                                                                                COMMENT:                                      ON CREDIT HOLD?:  N                 

                                                                                                                            DFLT PMT TYP: CHECK      CHECK                                                        

                                                                                                                            C-STORE LOC:  N                                                                       

                                                                                                                            -


                                                                                                                                     

                                                                                                                            TAX CODE:  TX                 EXEMPTION NUMBER: 11136485288                           

                                                                                                                            TAX CODE:  TX VIC             EXEMPTION NUMBER: 11136485288                           

                                                                                                                            TAX CODE:  TX VICVIC          EXEMPTION NUMBER: 11136485288                           

                                                                                                                            -


                                                                                                                                     

                                                                                                                            LOCATION: 1001 DuPont Plant                                PRIMARY LOCN: N            

                                                                                                                               ADD.1:                                  PHONE:                   EXT:              

                                                                                                                               ADD.2:                                    FAX:                 BATCH: N            

                                                                                                                               ADD.3:                                CONTACT:                                     

                                                                                                                                CITY: 0 - 25 Miles                   SLSPRSN: 1001 Clifford Goebel                

                                                                                                                               STATE: TX ZIP: FR025             TAX SCHEDULE: TX VICVIC                           

                                                                                                                                                                   WAREHOUSE: W01 Victoria Warehouse              

                                                                                                                                TAX CODE:  TX                 EXEMPTION:  11136485288                             

                                                                                                                                TAX CODE:  TX VIC             EXEMPTION:  11136485288                             

                                                                                                                                TAX CODE:  TX VICVIC          EXEMPTION:  11136485288                             

                                                                                                                            -


                                                                                                                                     

                                                                                                                            /CODE

                                                                                                                             

                                                                                                                            I can see that the cust no. 1078 is not being picked up by the trap.  There is another "header" for each page that includes the customer no and name.  It's picking that line up instead of the actual customer info.

                                                                                                                             

                                                                                                                            I just changed the Trap line to the Address line; I think it's fixed? 

                                                                                                                             

                                                                                                                            Gosh, you are so patient.  Bless you!

                                                                                                                              • Records are not in table
                                                                                                                                Data Kruncher

                                                                                                                                Check your email for a new model. I've changed the approach for the append templates.

                                                                                                                                 

                                                                                                                                Thanks for hanging in there. I know this can be a frustrating beast sometimes. But I also know that (practically) everything is possible, and the payoff is SO worth the effort.

                                                                                                                                  • Records are not in table
                                                                                                                                    TMERRYMAN _

                                                                                                                                    I am looking at it now.  But, I felt pretty confident with the end results of the last one.  Do you not think it's right?

                                                                                                                                     

                                                                                                                                    I'm not sure I see the "wholesale" change you are talking about.

                                                                                                                                      • Records are not in table
                                                                                                                                        Data Kruncher

                                                                                                                                        I split the append template into two templates. This allows for instances where the customer number isn't 20+ lines from the location value. The customer number and the customer tax codes are trapped using separate templates.

                                                                                                                                         

                                                                                                                                        If the end results are exactly the same as your last result (they ought to be) then that's good. It's a more manageable model for future modeling and works as you intended.

                                                                                                                                         

                                                                                                                                        I hope.

                                                                                                                                  • Records are not in table
                                                                                                                                    TMERRYMAN _

                                                                                                                                    I just noticed that the model we created last week for my sales tax exemption report is not capturing the customer tax code and exemption number fields when it continues to the next page.  I spent all day Saturday trying to fix this.  Can you help?  Below is a sample.  The page header begins at the "run date" line and goes through "Permanent Customers Only" line.  I think what is confusing it is the additional header of the "customer no" line.  When the page doesn't begin a new customer, it puts the customer no and name as an additional heading so you know what customer the locations belong to.

                                                                                                                                     

                                                                                                                                    CUSTOMER NO:  0004491      NAME:  Conway SouthernBBCorpus                             

                                                                                                                                    -


                                                                                                                                             

                                                                                                                                        ADDRESS:  Chaybark Buyback Acct #59927            SORT FIELD:  BUYBACK           

                                                                                                                                                  PO Box R                                                                

                                                                                                                                           CITY:  Concord                             TEMPORARY CUST?:  N                 

                                                                                                                                        COUNTRY:  USA United States of America        OPEN ITEM CUST?:  Y                 

                                                                                                                                          STATE:  CA        ZIP CODE: 94524           STATEMENT CYCLE:  X                 

                                                                                                                                       PHONE NO:                         EXT:         PRINT DUN MSSG?:  Y                 

                                                                                                                                            FAX:                   BATCH FAX: N         CUSTOMER TYPE:  BUYB              

                                                                                                                                        CONTACT:                                                                               

                                                                                                                                    EMAIL:                                                                               

                                                                                                                                    URL:                                                                          

                                                                                                                                    SHIP METHOD:                                                                          

                                                                                                                                       SCHEDULE:  TX NUEROB                               PRICE LEVEL:  Z1                

                                                                                                                                        SHIP-TO:                                        DISCOUNT RATE:      .000%         

                                                                                                                                    TERMS CODE:  30   Net 30 days                    FIN CHARGE RATE:      .000%         

                                                                                                                                    SALESPERSON:  0000 House Account                                                      

                                                                                                                                       SALES CD:                                         CREDIT LIMIT:         .00        

                                                                                                                                        COMMENT:  BUYBACK                             ON CREDIT HOLD?:  N                 

                                                                                                                                    DFLT PMT TYP: NONE                                                                    

                                                                                                                                    C-STORE LOC:  N                                                                       

                                                                                                                                    -


                                                                                                                                             

                                                                                                                                    TAX CODE:  TX                 EXEMPTION NUMBER: 1257942552                                                                               

                                                                                                                                    Run Date: 09/10/2010  TM       Thomas Petroleum, LLC            Page: 2137            

                                                                                                                                    A/R Date: 09/10/2010             CUSTOMER LISTING               Time: 03:24 PM        

                                                                                                                                                                PERMANENT CUSTOMERS ONLY                                                                               

                                                                                                                                    CUSTOMER NO:  0004491      NAME:  Conway SouthernBBCorpus                             

                                                                                                                                    -


                                                                                                                                             

                                                                                                                                    TAX CODE:  TX NUE             EXEMPTION NUMBER: 1257942552                           

                                                                                                                                    TAX CODE:  TX NUEROB          EXEMPTION NUMBER: 1257942552                           

                                                                                                                                    -


                                                                                                                                             

                                                                                                                                    LOCATION: 5000 320 S NATION BLVD                       PRIMARY LOCN: N            

                                                                                                                                       ADD.1: 361-458-1070                     PHONE:                   EXT:              

                                                                                                                                       ADD.2:                                    FAX:                 BATCH: N            

                                                                                                                                       ADD.3:                                CONTACT:                                     

                                                                                                                                        CITY: 0 - 25 Miles                   SLSPRSN: 0000 House Account                  

                                                                                                                                       STATE: TX ZIP: FR025             TAX SCHEDULE: TX NUEROB                           

                                                                                                                                                                           WAREHOUSE: W05 Corpus Warehouse                

                                                                                                                                        TAX CODE:  TX                 EXEMPTION:  12925212345                             

                                                                                                                                        TAX CODE:  TX NUE             EXEMPTION:  12925212345                             

                                                                                                                                        TAX CODE:  TX NUEROB          EXEMPTION:  12925212345                           

                                                                                                                                    -


                                                                                                                                                                                                                     

                                                                                                                                    CUSTOMER NO:  0004492      NAME:  Conpark Southern-BBTyler                             

                                                                                                                                    -


                                                                                                                                             

                                                                                                                                        ADDRESS:  PO Box R                                 SORT FIELD:  BUYBACK                                                                               

                                                                                                                                    CITY:  Concord                             TEMPORARY CUST?:  N                 

                                                                                                                                        COUNTRY:  USA United States of America        OPEN ITEM CUST?:  Y                 

                                                                                                                                          STATE:  CA        ZIP CODE: 94524           STATEMENT CYCLE:  X                 

                                                                                                                                       PHONE NO:                         EXT:         PRINT DUN MSSG?:  Y                 

                                                                                                                                            FAX:                   BATCH FAX: N         CUSTOMER TYPE:  BUYB              

                                                                                                                                        CONTACT:                                                                               

                                                                                                                                    EMAIL:                                                                               

                                                                                                                                    URL:                                                                          

                                                                                                                                    SHIP METHOD:                                                                          

                                                                                                                                       SCHEDULE:  TX VICVIC                               PRICE LEVEL:  Z4                

                                                                                                                                        SHIP-TO:                                        DISCOUNT RATE:      .000%         

                                                                                                                                    TERMS CODE:  10   Net 10 days                    FIN CHARGE RATE:      .000%         

                                                                                                                                    SALESPERSON:  1029 Martpol                                                       

                                                                                                                                       SALES CD:                                         CREDIT LIMIT:         .00        

                                                                                                                                        COMMENT:  BUYBACK                             ON CREDIT HOLD?:  N                 

                                                                                                                                    DFLT PMT TYP: NONE                                                                    

                                                                                                                                    C-STORE LOC:  N                                                                       

                                                                                                                                    -


                                                                                                                                             

                                                                                                                                    TAX CODE:  TX                 EXEMPTION NUMBER: 125858585252                           

                                                                                                                                    TAX CODE:  TX VIC             EXEMPTION NUMBER: 125858585252                           

                                                                                                                                    TAX CODE:  TX VICVIC          EXEMPTION NUMBER: 125858585252                           

                                                                                                                                    -


                                                                                                                                             

                                                                                                                                    LOCATION: 8000 main Southern-BBTyler                     PRIMARY LOCN: N            

                                                                                                                                       ADD.1: 58252 HWY 155                    PHONE:                   EXT:              

                                                                                                                                       ADD.2:                                    FAX:                 BATCH: N            

                                                                                                                                       ADD.3:                                CONTACT:                                     

                                                                                                                                        CITY: 0 - 25 Miles                   SLSPRSN: 1008 Jess

                                                                                                                                       STATE: TX ZIP: FR025             TAX SCHEDULE: TX SMITYL                           

                                                                                                                                                                           WAREHOUSE: W08 Tyler Warehouse                 

                                                                                                                                        TAX CODE:  TX                 EXEMPTION:  1234567890                             

                                                                                                                                        TAX CODE:  TX SMI             EXEMPTION:  1234567890                             

                                                                                                                                        TAX CODE:  TX SMITYL          EXEMPTION:  1234567890                             

                                                                                                                                    -


                                                                                                                                             

                                                                                                                                    /CODE

                                                                                                                                      • Records are not in table
                                                                                                                                        Data Kruncher

                                                                                                                                        I think that you'll be OK if you create a four line page header template starting with the run date line. Treat the customer number line as a regular append.

                                                                                                                                         

                                                                                                                                        Does that help? It seems to be working well in my model.

                                                                                                                                          • Records are not in table
                                                                                                                                            TMERRYMAN _

                                                                                                                                            I think that you'll be OK if you create a four line page header template starting with the run date line. Treat the customer number line as a regular append.

                                                                                                                                             

                                                                                                                                            Does that help? It seems to be working well in my model.[/QUOTE]

                                                                                                                                             

                                                                                                                                            Okay; I changed the page header to be 4 lines.  However, the customer number line is included in my 23 line append.  Did you mean to add an additional append line to capture that "one liner" customer no?

                                                                                                                                              • Records are not in table
                                                                                                                                                Data Kruncher

                                                                                                                                                Yes, I have the customer number captured in only a single line append template.

                                                                                                                                                 

                                                                                                                                                The CustomerTaxCode and CustomerExemption fields reside in a three line append template.

                                                                                                                                                 

                                                                                                                                                Finally, I have the Location, LocationTaxCode and LocationExemption in an eight line detail template.

                                                                                                                                                • Records are not in table
                                                                                                                                                  TMERRYMAN _

                                                                                                                                                  I just noticed that while using the model with 2 appends (one for customer info, and one for customer codes and exemptions), the CustomerTaxCode does not include the first tax code listed for that customer, which is the state.  (when it breaks across the page)  I currently am using one append for all customer data including the codes and exemptions, however I can change it to use the two appends if it would be better.  I don't know which problem is an easier fix.  What do you suggest?

                                                                                    • Records are not in table
                                                                                      TMERRYMAN _

                                                                                      Well, I just tried something. 

                                                                                       

                                                                                      End field on "Blank fkeld values:  1"

                                                                                       

                                                                                      It seems to have worked.  Does that make sense?  I want to be sure it is pulling the correct info.