7 Replies Latest reply: May 15, 2014 10:01 AM by Bbonafilia _ RSS

    Append Date is Separate Column

    Bbonafilia _

      Hello,

       

      I am new to these forums.  I've come here because I've encountered an issue I'd like to resolve through Monarch but have had to use other tools for in the past.  (Namely coding a text-file parsing macro in VBA in Access to read line by line to capture the data the HARD way.)

       

      The problem is that the transaction details I need to capture are in one column, on the right, while the person responsible for the transactions has their information in a column on the left.  This is compounded by the fact that there are three different formats for the SSN/TIN for each customer (I've listed the three formats in the example.) 

       

      Being unable to isolate the format, I've written a trap that will catch all three, but it will also catch some address information if it fits the same pattern of blank/non-blank spaces when that line coincides with the start of a new detail entry.

       

      I can't use a postal trap because the customer information column cuts off after a set number of spaces, sometimes eliminating the entire zip code.

       

      Anyone have any tips on how to handle something like this? I tried to use two models, one for the transactions and another of the owners, but there's no way to reliably catch all the relationships between them that way.  I'm stuck, and with the reports growing in size, my imperfect text-parsing method in access is taking FAR too long.

       

       

      Tax ID                     Post Date   Account Number Rtxn Number OwnCd Transaction Description                  Cash In       Cash Out  

      Name and Address           AcctRole     Branch Number Cashbox Nbr  Emp  Internal Description                                             

      111-11-1111                05-20-2008       999999999        ####  JO   Over Counter Check                                     $,$$$.00  

      Sarah H. Customer          TAXOWN                   #         ###  JC                                                                    

      444 Street St                                                                                -


      -


          

      East Smallvillage MA  010                                                                                0.00       $,$$$.00  

       

       

      22-2222222                 05-20-2008       999999999       #####  S    Over Counter Check                                       $$$.00  

      Local Restuarant           TAXOWN               #####         ###  NMR                                                                   

      C/O Propri Eter            05-20-2008       888888888        ####  JO   Deposit                                   $$$.00                 

      33 Road Rd.                TAXOWN               #####         ###  JG                                                                    

      City  MA  010##-####       05-20-2008       999999999       #####  S    Deposit                                 $,$$$.00                 

                                 TAXOWN               #####         ###  JG                                                                               

      -


      -


                                                                                       

      $,$$$.00         $$$.00

       

       

      Tax ID not available       05-20-2008       777777777        ####  JO   Deposit                                 $,$$$.00                 

      Local Auto Sales           OWN                      #         ###  AS                                                                    

      168 St Thomas Rd           05-20-2008       777777777        ####  JO   Deposit                                 $,$$$.00                 

      Village MA  010##-####     OWN                      #         ###  AS                                                                               

      -


      -


                                                                                       

      $,$$$.00           0.00 /CODE

        • Append Date is Separate Column
          Grant Perkins

          Hi and welcome to the forum.

           

          An interesting challenge. Looks simple enough but clearly is not.

           

          I don't think any normal approaches are going to work - at least I can't see one at the moment. Needs some thought so I will see what comes to mind in the next few days and watch with interest to read other responses.

           

          I'm thinking two models, as you did, and then linking them via a lookup but I guess you already tried exactly that. Can you outline your approach so that we don't spend time going down the same blind alley?

           

          I'm thinking you may need to enhance the trapability by adding something to the report as a first step to help mark the append data but as yet I'm not sure how to approach that with a likelihood of accuracy.

           

          Grant

            • Append Date is Separate Column
              Bbonafilia _

              Thanks.

               

              My approach was to set up a detail template for the two lines, trapping on the NN-NN-NNNN for the date; that works perfectly. 

               

              On a second model I trapped on 000 00 000 (non-blanks) to get all three formats for the SSN/TIN.  This works as well when I use it as a detail template.

               

              However, there's no link between the two models because if I tried to catch, say, the account numbers (which is the minimum I'd need to catch to provide the link between owner and transaction), I'd have to set that up as the detail because there's varying numbers of those.  Then I run into the append/detail issue where it starts a second person entry on the address line if that fits the SSN trap. 

               

              How would you go about making any changes to increase trapability? A simple find/replace in notepad or is there functionality to do that in Monarch? I suppose if I could replace "Tax ID not available" with "000-00-0000" somehow I could use numeric traps that probably wouldn't catch on the address, and just revert it back after somehow.  I'll have to try something like that when I get back to the office.

                • Append Date is Separate Column
                  Grant Perkins

                  That's pretty much what I was thinking.

                   

                  I think you need to link the two on the SSN (as an Append in your detail model) where you can, but the non SSN records are always going to be the challenge. That the date format uses "-" as a separator also limits our options for trying to work with the variable SSN formats.

                   

                  One might consider stripping out the "-"s from the SSNs but it may be that this could create a duplicate?

                   

                  ROWNO() might come into play somewhere if all else fails.

                   

                  Also if on could with certainty identify every line that has an SSN or the 'No identification' text  it would be possible to add a tag field to the line. Then export the result with the tag  and use a new model on the expanded result for an easy trap. The existing report lines would be exported as they are in a single field using a fixed font to retain positioning.

                   

                  I guess ine might be able to use some sort of conditional analysis of the first X characters of each line. If position 3 or 4 is a "-" OR the string found is "Tax ID not available" then it might be a SSN. That sort of idea.

                   

                  What do you think? Is there anything else in the report.

                   

                  The solution for the Tax ID not available lines is elusive though. Hence maybe RowNo() use.

                   

                  Grant

                    • Append Date is Separate Column
                      Grant Perkins

                      I have played a little and have a way to add a column that only populates when the first part of a line looks like a SSN or has the "Tax ID Not Available" text.

                       

                      You may need to do some further verification work but this should get close.

                       

                      if(instr("-",left(,4))>0 .And. instr(" ",rtrim(left(,20)))=0,

                      left(,20),if(instr("Tax ID Not Available",[Full Line])>0,trim(str(Recno()))" "left(,20),""))

                       

                      /*If there is a dash in the first 4 characters AND there are no spaces in the data extracted from the first 20 chars of the line once any spaces to the right have been trimmed, then it's likely a SSN line so use the string from the first 20 characters.

                       

                      If the first 20 characters contains the "Tax ID Not Available" string treat that as an SSN line as well and prefix it with the Record Number from the original report. (Line Number cold also be used - anything to give a reasonable and unique key for later detail processing.) */

                      /code

                       

                      This is based on extracting every line of the report to the table in a single field for each line  (The "Full Line" field.)

                       

                      Add a calculated line with the formula above (Cut and Paste should work). Not I have used RECNO() rather than my original ROWNO() suggestion.

                       

                      The resulting field when included in a fresh export of the table to a file will, hopefully, provide a suitable key to link the detail records with the key field appended to the name and address information extracted in a separate modelling process and used as a lookup table.

                       

                      It may be possible to do more but how does this look for now?

                       

                       

                      Grant

                        • Append Date is Separate Column
                          Bbonafilia _

                          This definitely seems to get me closer to where I want to be.  I might still have to play around with a couple things.  Thanks for the help.

                            • Append Date is Separate Column
                              Grant Perkins

                              This definitely seems to get me closer to where I want to be. I might still have to play around with a couple things. /quote

                               

                              Yep. It's an idea rather than a known solution as presented. It also may well not play out as it is when working with a full report. However, if it offers a lead towards a full solution or sparks some other ideas for you it will have served its purpose.

                               

                              Version 9 supports User Defined Functions and one of the provided examples is a SSN format validator assist. I have had a mind to play with that to see if it could be of use and carried back to V8 for and be of some use here identifying the required lines. I suspect the simple check I used for the sample may not be comprehensive enough for all situations.

                               

                              I would be interested to know how you get on with this project.

                               

                               

                              Grant

                                • Append Date is Separate Column
                                  Bbonafilia _

                                  I was able to get what I needed from your last suggestion, but it tended towards being a little more complicated than I had expected, so I went with your previous suggestion of adding/changing something in the report to make it catch better.

                                   

                                  Because it's all going into MS Access and so much has to be done to the data once it's extracted from Monarch anyway, I set up some import functions in Access to run a find/replace on the "Tax ID not available" line in the report using Word, turning it to a string of zeros.  It then runs a single Monarch model which used the SSN minus the "-" (if there was one) or a combination of the account number and the business name if there was no tax id, as the key field for being put into the database.  Catching on a fully numeric lines eliminates the address issue that came up.

                                   

                                  Thanks for your help; I probably never would have thought about doing anything to amend the original report in any way, and that's what made the difference.