9 Replies Latest reply: May 15, 2014 9:54 AM by AndrewC _ RSS

    Floating fields on badly-formatted report

    AndrewC _

      I presently have a real challenge for a Client in migrating data from a very old accounting/payroll system. 

       

      I have used Monarch many times for this type of task, but the format of some reports here is beginning to make it look impossible.

       

      I have an Employee report that has all the static data I want on it (sorry, can't post a sample due to confidentiality/Data Protection legislation).   However I can't get a model that works on it due to the following:

      1. Trapping the Employee Code at the top of the report allows me to map a few fields below it on the Detail template, but then the Address fields below which can be 3 or 4 lines force the rest of the report downwards and misalign the remaining fields with the mask. 

      2. I tried setting the Address as a separate trap in a Footer template, and that picked up a few more fields until again additional data on other records randomly alters the spacing (both vertically and horizontally!) for the remaining fields, throwing them all out of alignment and making mince of the resultant data.  As I can only have 1 footer template, I am now stuck on that approach.

       

      I am still using Monarch Pro V6.01, and would happily buy an upgrade to V9 if that would help the problem - but would it work?  Or does anyone know how to work around a report with multiple floating fields (not traps) like this!

      TIA for any expert advice!

        • Floating fields on badly-formatted report
          Grant Perkins

          Andrew, welcome to the forum.

           

          Your challenge sounds quite similar to JoeSalvatore's. Although the specific solution may be different (very data and format dependent) the approach and concepts may be similar.

           

          Does you report have anything that may be used as a unique (to a single field but possibly many unique fields) preceding string?

           

          In theory the basis of the approach should be possible in V6 BUT V9 will always offer more options should you need them - more appends possible for example and of course things like Multi-Column Regions which arrived with V7. Also multiple footers though you may not need them in this case.

           

          I will try to get an initial suggestion for Joe's needs tonight which might allow you to consider things useful for you own requirements.

           

          HTH.

           

           

          Grant

          • Floating fields on badly-formatted report
            AndrewC _

            Grant

             

            Thanks for taking the time to reply.

             

            There are many unique field names in the report - for example "Start Date" followed by an 8-digit date.  However what I am seeing is the distance between the field-name and the field is varying sometimes by a couple of characters.

             

            Someone has suggested to me tonight that this sort of thing might be caused by my Client's method of producing the reports for me.  He thinks they may be printing them to the screen in the application, then saving them as text files, rather than printing the reports direct to file.  I need to investigate this with them tomorrow ASAP, as it does sound a likely cause of the "randomness" I am seeing.

             

            I am encouraged by your information on V9 so will probably upgrade to it.  I might be able to use the multiple footers to handle the variable number of address lines issue.

             

            And I'll look for your response to Joe, whose post I also read previously.

             

            Many thanks.

            • Floating fields on badly-formatted report
              Grant Perkins

              Hi Andrew,

               

              Ah! Random horizontal positioning as opposed to fields randomly being present. (I have also seen both before now ...)

               

              If the files are being delivered as text files your suspicion sounds about right. Or they may have originally been PDF documents now converted to text - a process which can produce similar results. ALso I assume it is not just a font-type issue?

               

              Would the format of your report allow you  to extract the field as an over sized field? So make it big enough to capture any likely variation and then sort out the anomalies for specific fields, for example dates, after extraction to the table by using Calculated fields where necessary?.

               

              For variable numbers of address lines I would normally seek to use the Address Block functionality that I believe was available in V6. (I do have V6 but not on a system that is readily available just at this moment.) This assumes that the addresses are at least vaguely structured of course! That does not stop me heartily recommending V9 though. You will find significant benefits I am sure.

               

              I appreciate you are unable to share a sample of the file on the forum but could you grab a few sample records, make them anonymous without losing any useful to Monarch attributes and share them privately? If so I would be happy to have a look outside the forum and offer some ideas.

               

              More soon.

               

              Grant

              • Floating fields on badly-formatted report
                AndrewC _

                Grant

                 

                >Ah! Random horizontal positioning as opposed to fields randomly being present. (I have also seen both before now ...)

                Yes - and I haven't seen that!

                 

                >If the files are being delivered as text files your suspicion sounds about right.

                I will be checking with the Client tomorrow (they have gone home now).

                 

                >Would the format of your report allow you to extract the field as an over sized field? So make it big enough to capture any likely variation and then sort out the anomalies for specific fields, for example dates, after extraction to the table by using Calculated fields where necessary?.

                 

                Yes, I think that would work, if fixing the above issue doesn't resolve it.  I could trim the fields back to size.

                 

                >For variable numbers of address lines I would normally seek to use the Address Block functionality that I believe was available in V6. (I do have V6 but not on a system that is readily available just at this moment.) This assumes that the addresses are at least vaguely structured of course! That does not stop me heartily recommending V9 though. You will find significant benefits I am sure.

                I will have to have another look at that - I had thought UK-format Postcodes were not recognised, but I see from the Help that they might be after all.

                 

                >I appreciate you are unable to share a sample of the file on the forum but could you grab a few sample records, make them anonymous without losing any useful to Monarch attributes and share them privately? If so I would be happy to have a look outside the forum and offer some ideas.

                 

                That would be very welcome.  I'll first see if I can get a better output of the report, though, so there are not too many red-herrings to deal with!  How do I share it privately?

                 

                Many thanks for your advice so far.

                • Floating fields on badly-formatted report
                  Grant Perkins

                  Hi Andrew,

                   

                  UK Post codes should work in V6 iirc. Use the Address Block feature NOT the Postal Trap. Postal trap is an earlier method retained for backward compatibility. Again I think you are OK with V6.

                   

                  If we get to needing to work with a real sample of the report I will send you a Private Message with my email address. Usually the files are small enough when zipped to attach to mail. There are other options if that is not a suitable way to go.

                   

                  More by morning - I hope.

                   

                   

                  Grant

                  • Floating fields on badly-formatted report
                    Grant Perkins

                    Andrew,

                     

                    [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001841#000003"]Long post here[/url] which covers certain things you may find interesting although in the end I left it quite V9 centric as that is what Joe has and I though the response was long enough as it is!

                     

                    Quite how some of the slice and dice concepts might be applicable to your problem is as yet unknown. If you can get a better starting point from the client that would be a useful step forward since dealing with inconsistency, whilst possible, is usually messy and can be time consuming, especially ensuring that all possible inconsistency has been identified and covered.

                     

                    Based on your more recent responses I now donlt think your initial difficulty i=s close to Joe's  - but the solution may be similar anyway so worth a read even if only for the info about additions in V9!

                     

                    Have fun.

                     

                     

                    Grant

                    • Floating fields on badly-formatted report
                      AndrewC _

                      Grant

                       

                      Just to let you know that I have resolved the issue with the floating fields.

                       

                      I discovered that records for employees who had left were coming out differently from those that were still current.  We were able to print the report just for those that were current, and I rewrote the model to match those, using a Footer to identify if the address block has moved down (which it still does).

                       

                      I'll still have a look at your post on V9 to Joe, but for now I'll stick with V6.01 so I can complete the project on a version that I know.

                       

                      Again, many thanks for your interest.

                      • Floating fields on badly-formatted report
                        Grant Perkins

                        Originally posted by AndrewC:

                         

                        I discovered that records for employees who had left were coming out differently from those that were still current.  /b[/quote]Good work! Such things happen quite often but are not always simple to identify and resolve.

                         

                         

                          [b]... I rewrote the model to match those, using a Footer to identify if the address block has moved down (which it still does).[/b][/quote]OK, but be careful that your records all have an address that is captured. If there is a glitch for any reason the footer concept will attach the next footer it finds even if it is from a subsequent record.

                         

                        Chances are you will be OK but where the data is not of a footer nature by design it would be better, if at all possible, to make it part of the detail or, possibly, an Append template that has the same trap as the detail record.

                         

                        When you do look at V9 you will see a lot of new functionality compared to V6 but nothing that will make it seem utterly different. Monarch development is, as far as the user interface is concerned, nicely progressive rather than revolutionary so it should still feel comfortable and familiar to you.

                         

                        HTH.

                         

                         

                        Grant

                        • Floating fields on badly-formatted report
                          AndrewC _

                          Grant

                           

                          Thanks for that tip.  The footer is reliable - I have just checked - as each address is prefixed with the word "Address" which I have used as a trap.