11 Replies Latest reply: May 15, 2014 10:01 AM by fd201 _ RSS

    How to use tags to get missing fields

    fd201 _

      Hi, I'm very new to Monarch so there may be something here which is blindingly obvious to everyone else, but i'm not getting it.

       

      I've got a pdf file which i've bought into Monarch (v8.02), set up traps and created field properties for the containing data through a template. The problem i'm having is that the report is extracted from a source which doesn't store the data nicely. For example, if a sale contains more than one item, the items will be put into one record rather than multiple records. Monarch reads this fine, it puts the extra info such as a product on a new row. Problem with this is that the report window doesn't show any info preceding the additional stock numbers. Also when i add a few traps it misses the numbers i need, such as stock and product numbers. This could be due to the first column, which should contain the main reference number, being blank. Actually all but stock and product columns are blank. With some though the numbers will be picked up but will be put in the same field box as the previous number from that field, but i need these as seperate records.

       

      What i'd like to do would be to take the data as it is, and if there are some missing fields, but the row as a whole isn't empty, then for the field above to be copied. I've tried the empty cells option in the field properties but i can't get it to do anything. If someone wants to tell me how to use it properly if it can fix this problem i'd be grateful. The data below is purely as an example. I've asked already if i can show even some modified data, so that no one would know what the original was, but i'm still not allowed. Some very much faked example data is all i can show. This would be an example of the report window, with the data below being in a Detail template.

       

      Salenumber Stocknumber Product Price Supplier

      123/34       S3456      RayGun  19.99  Sony

      129/45       P43rw      Shield  24.99  Philips

                    F42354  Tortoise       Hyundai

      /CODE

      So the above would either not show the third line in the table window or it would add the numbers on to the previous numbers, so the p43rw field would actually show p43rw F42354. There isn't a character among these fields which is shared, nor is there any uniform field size. I've used the floating trap which gets the info i want, but it stores F42354 as a salenumber, Tortoise as a stocknumber and Hyundai as a product. The floating trap is just looking for where it can put the field for the salenumber, rather than what field the stocknumber is below, which is how i thought it would work. The other traps in use are a blank trap (to the left of the first sale number) and a non-blank trap (above the first sale number).

       

      I'd be very grateful for any help you can give.

      Thanks.

        • How to use tags to get missing fields
          Data Kruncher

          Welcome to the forum, and the wonderful world of using Monarch on PDF files.

           

          Before we get too wrapped up in the floating trap challenge, can I ask if sample is an accurate depiction of the PDF, or more how the data appears in Monarch? That is to say, does the shifting of the data along the line appear in the PDF, or is that introduced by Monarch? This shifting is a common problem, but can often be "cured" by changing, usually increasing, the value for "Output Scaling".

           

          But if that's just how it is in the PDF then we'll tackle that.

            • How to use tags to get missing fields
              fd201 _

              Welcome to the forum, and the wonderful world of using Monarch on PDF files.

               

              Before we get too wrapped up in the floating trap challenge, can I ask if sample is an accurate depiction of the PDF, or more how the data appears in Monarch? That is to say, does the shifting of the data along the line appear in the PDF, or is that introduced by Monarch? This shifting is a common problem, but can often be "cured" by changing, usually increasing, the value for "Output Scaling".

               

              But if that's just how it is in the PDF then we'll tackle that.[/QUOTE]

               

              Hi, and thanks for the response. The example isn't a good representation of how the data appears in monarch. The relevant figures i want out of the pdf are all in a neat row. The data all starts at the same point in each field. The only slight issue is that the column header won't always be at the beginning of the field, but they don't encroach into any other fields so they are fine.

               

              The shifting seen in the sample is just me not using the forum code tag correctly.

                • How to use tags to get missing fields
                  Grant Perkins

                  Hi fd,

                   

                  If you are getting consistent width columns of data but just lack information in the first column (and the Price column?) make your detail template for columns 2, 3, 4, and 5 and use an Append to attach the detail in column 1 to any lines that relate to it.

                   

                  If the price info also only appears in line one then include that in the append (or a second append) as well.

                   

                  If the pdf extraction does not give consistent column widths, and an automated process may not since it assess a limited number of lines, then it may be worth seeking to change some of the pdf interpretation controls in order to 'spread' the field's spacing to cater for longer entries. This would be especially true if you will be re-using the model for regular input reports on which the content may vary from report to report. You need to ensure it will deal with the maximum size it might discover.

                   

                  The floating trap MIGHT do this for you but you could be pushing the boundaries of its logic which more relates splitting sections of a line such as a computer log file entry into two or three parts based on clear demarcation sections whereas it looks like you will have variables that may well not fit comfortably into those concepts.

                   

                  By their nature PDF files tend to need individual attention for each report whereas text based reports (PRN, etc.) can be treated more generically in terms of offering suitable advice. If you find yourself still battling this one it would be useful to have a sample version of one of you typical PDF files to play with for a better understanding of what is going on with this particular report. Would that be possible? A test sample data file would be OK as long as it exhibits all the problems you find in the production reports.

                   

                   

                  HTH.

                   

                   

                   

                  Grant

                    • How to use tags to get missing fields
                      fd201 _

                      Thanks for the reply.

                       

                      I think I’ve got the append query to do what I want it to. Only problem is that I’m using the floating trap because some data in some of the reports is too long and is encroaching into the next columns data. The floating trap works fine for the date column but in a column we shall call “Owing”, where all values appear to be 0, the floating trap doesn’t stay with the column of data. I’ve set a trap for both numeric and 0 but neither seem to work with the floating trap. Without it it’ll miss out the lines where the preceding column with data will kinda overwrite. I’ve tried changing the spacing at time of import but whenever I go to add a model the report gets reopened with my changes lost. I’m using a separate template for this append, if it matters.

                       

                      And it’s still no to the sample data, but if someone wants to give me an idea of how to use the forum "Code" tags correctly then I should be able to give everyone a better idea of what I’m dealing with.

                        • How to use tags to get missing fields
                          Grant Perkins

                           

                          I think I’ve got the append query to do what I want it to. Only problem is that I’m using the floating trap because some data in some of the reports is too long and is encroaching into the next columns data. /quote

                           

                          Is that how it is in the original PDF file or the result in the virtual text file after conversion from PDF?

                           

                           

                          The floating trap works fine for the date column but in a column we shall call “Owing”, where all values appear to be 0, the floating trap doesn’t stay with the column of data. I’ve set a trap for both numeric and 0 but neither seem to work with the floating trap. Without it it’ll miss out the lines where the preceding column with data will kinda overwrite.[/quote]I'm prepared to accept I'm wrong if you can make it work but in my experience, even with the additional work put in to pdf conversion in Monarch V9, there are usually enough challenges posed by the many variations of PDF writing programs to suggest that floating traps, especially when pushed well beyond the original intention behind the design concept, are unlikely to work reliably.

                           

                           

                          I’ve tried changing the spacing at time of import but whenever I go to add a model the report gets reopened with my changes lost. I’m using a separate template for this append, if it matters. /quoteWhen you say 'add a model' do you mean 'open an existing model'? If so the rules built into that model will be applied.

                           

                           

                          And it’s still no to the sample data, but if someone wants to give me an idea of how to use the forum "Code" tags correctly then I should be able to give everyone a better idea of what I’m dealing with.[/quote]

                          Basically you need the word CODE surrounded by SQUARE brackets BEFORE the text you want to quote and /CODE surrounded by square brackets AFTER the text you want to quote.

                           

                           

                          If you QUOTE this post as if replying to it you should see how it looks.[/code]

                           

                          Alternatively type in those two tags with a line between them and then cut and paste your sample between them.

                           

                          OR, assuming you are seeing a simple text editor window as you type a post, paste the sample text you want to display with its format retained, highlight the text on screen and then click on the # symbol at the right hand end of the edit commends toolbar.

                           

                          You can PREVIEW your post to make sure that it looks how you want it to look.

                           

                           

                          HTH.

                           

                           

                          Grant

                            • How to use tags to get missing fields
                              fd201 _

                              Is that how it is in the original PDF file or the result in the virtual text file after conversion from PDF?

                              /quote

                              How it is in the PDF, which most likely means there's nothing i can do about it. I can't go back to those who originally got it because i've got no idea who did it.

                              I'm prepared to accept I'm wrong if you can make it work but in my experience, even with the additional work put in to pdf conversion in Monarch V9, there are usually enough challenges posed by the many variations of PDF writing programs to suggest that floating traps, especially when pushed well beyond the original intention behind the design concept, are unlikely to work reliably.[/quote]

                              Fair enough, but surely i'm not pushing it beyond it's concept? The user guide seems to show how they're used in exactly the way i'm trying to use, but the pdf is probably throwing things out of joint.

                               

                              When you say 'add a model' do you mean 'open an existing model'? If so the rules built into that model will be applied.[/quote]

                              I meant open, so thanks for explaining that one to me.

                               

                              Basically you need the word CODE surrounded by SQUARE brackets BEFORE the text you want to quote and /CODE surrounded by square brackets AFTER the text you want to quote.

                               

                               

                              If you QUOTE this post as if replying to it you should see how it looks.[/code]

                               

                              Alternatively type in those two tags with a line between them and then cut and paste your sample between them.

                               

                              OR, assuming you are seeing a simple text editor window as you type a post, paste the sample text you want to display with its format retained, highlight the text on screen and then click on the # symbol at the right hand end of the edit commends toolbar.

                               

                              You can PREVIEW your post to make sure that it looks how you want it to look.

                              /QUOTE

                              That i did in the earlier post, but it looks like rubbish.

                              I'll try again.

                              salesnumber date     product stocknumber owing

                              123         1/1/2008 Toaster 234          0

                              789         2/1/2008 Machine 468          0

                              321         2/1/2008 Kettle  56745643908977443 -50

                              /code

                              After a lot of spacing that looks better.

                              So putting a numeric trap will catch the first two zeroes, but not the third. Trying the floating trap which i'd imagine would work in the same way it did for the date and in the example, the greyed out boxes completely disapear, or they'll go to cover some other boxes which fit the trap. I like the theory of the floating traps, but in practice they don't seem that great.

                                • How to use tags to get missing fields
                                  Data Kruncher

                                  Hi fd,

                                   

                                  At this point I'd try another approach. Floating traps do indeed work very well in some, but not all, cases; particularly when the overlapping situation you've encountered occurs.

                                   

                                  I'd try trapping on the / in your date, and painting the salesnumber, the sale date, and the product name as individual fields as you would normally.

                                   

                                  But then I'd paint the stock number and the owing amount as one big character field. Finally, create two calculated fields to properly isolate the values for each of the two fields. Naming the big combination field StocknumberOwing, the new Stocknumber field (character type) has the expression:

                                  [SIZE=2]

                                  LSplit(,2," ",1)

                                  /SIZE[/CODE]

                                   

                                  If you need it to be a numeric field just use:

                                  Val(LSplit(,2," ",1)[/SIZE])[/CODE]

                                   

                                  And the Owing amount is a numeric field with the expression:

                                  [SIZE=2]

                                  Val(LSplit(,2," ",2))

                                  /SIZE[/CODE]

                                   

                                  Where easy solutions don't readily exist with complex report layouts, Monarch provides many tools by way of calculated fields and functions to get the job done.

                                   

                                  Kruncher

                                    • How to use tags to get missing fields
                                      fd201 _

                                      Hi fd,

                                       

                                      At this point I'd try another approach. Floating traps do indeed work very well in some, but not all, cases; particularly when the overlapping situation you've encountered occurs.

                                       

                                      I'd try trapping on the / in your date, and painting the salesnumber, the sale date, and the product name as individual fields as you would normally.

                                       

                                      But then I'd paint the stock number and the owing amount as one big character field. Finally, create two calculated fields to properly isolate the values for each of the two fields. Naming the big combination field StocknumberOwing, the new Stocknumber field (character type) has the expression:

                                      /QUOTE

                                      Thanks, that was pretty much what i'd already done for the date trap, which i think i mentioned up there looks fine. I liked your idea of splitting the column. However i only got to try it out today and unfortunately it causes another little issue, in that these stock numbers from the database may already have a space in them, eg sn 123 45. I don't know why, it's just how the data was stored before pulling it off.

                                       

                                      So splitting it by space i don't think is possible unless there is another way around it. So the one column could contain sc 234 56 (for the stock number) 9 (for Owing), but the splitting will put sc in one column and the rest in another. Any way around this?

                                        • How to use tags to get missing fields
                                          Data Kruncher

                                          Any way around this?[/QUOTE]

                                           

                                          Yes, and in fact, lucky for us, it's a pretty easy fix.

                                           

                                          Since your Owing amount will never have an embedded space like the stocknumber does from time to time, tackle splitting the combined field from the right instead of the left, using RSplit instead of LSplit.

                                           

                                          The Owing field expression then becomes:

                                          Val(Rsplit(,2," ",1))[/CODE]

                                           

                                          And the Stocknumber field becomes:

                                          Rsplit(,2," ",2)[/CODE]

                                      • How to use tags to get missing fields
                                        Gareth Horton

                                        I may be late to the party here, but the floating trap would work perfectly on the sample you pasted below.

                                         

                                        Here is a template:

                                         

                                        [FONT="Courier New"]            

                                                   BN       B0      BN                B0

                                        321         2/1/2008 Kettle  56745643908977443 -50[/FONT]

                                         

                                        /CODE

                                         

                                        Note that you can adjust the Template width setting in the Field Properties, so you can accommodate fields that could well be wider than might exist in your sample line.

                                         

                                        Note I cheated and just used N and 0 for the numeric and Non-Blank traps in this representation.

                                         

                                        The fact that the fields cut across each other vertically is of no concern to the floating trap, which works horizontally.  As long as you have the right number of "separators" between the data, i.e. the same number of fields and gaps/delimiters, you will be fine.

                                         

                                        Gareth

                                         

                                         

                                        How it is in the PDF, which most likely means there's nothing i can do about it. I can't go back to those who originally got it because i've got no idea who did it.

                                         

                                        Fair enough, but surely i'm not pushing it beyond it's concept? The user guide seems to show how they're used in exactly the way i'm trying to use, but the pdf is probably throwing things out of joint.

                                         

                                         

                                        I meant open, so thanks for explaining that one to me.

                                         

                                         

                                        That i did in the earlier post, but it looks like rubbish.

                                        I'll try again.

                                        salesnumber date     product stocknumber owing

                                        123         1/1/2008 Toaster 234          0

                                        789         2/1/2008 Machine 468          0

                                        321         2/1/2008 Kettle  56745643908977443 -50

                                        /code

                                        After a lot of spacing that looks better.

                                        So putting a numeric trap will catch the first two zeroes, but not the third. Trying the floating trap which i'd imagine would work in the same way it did for the date and in the example, the greyed out boxes completely disapear, or they'll go to cover some other boxes which fit the trap. I like the theory of the floating traps, but in practice they don't seem that great.[/QUOTE]

                                          • How to use tags to get missing fields
                                            fd201 _

                                            Hello and thanks to both of you.

                                            Yes, and in fact, lucky for us, it's a pretty easy fix.

                                             

                                            Since your Owing amount will never have an embedded space like the stocknumber does from time to time, tackle splitting the combined field from the right instead of the left, using RSplit instead of LSplit.

                                             

                                            The Owing field expression then becomes:

                                            Val(Rsplit(,2," ",1))[/CODE]

                                             

                                            And the Stocknumber field becomes:

                                            Rsplit(,2," ",2)[/CODE][/QUOTE]

                                            I was working through the list of formulas in the field editor but only got down to the n's, which is the excuse i'm using for not noticing this formula. Should have done though. Thanks a lot anyway.

                                            I may be late to the party here, but the floating trap would work perfectly on the sample you pasted below.

                                             

                                            Here is a template:

                                             

                                            [FONT="Courier New"]            

                                                       BN       B0      BN                B0

                                            321         2/1/2008 Kettle  56745643908977443 -50[/FONT]

                                             

                                            /CODE

                                             

                                            Note that you can adjust the Template width setting in the Field Properties, so you can accommodate fields that could well be wider than might exist in your sample line.

                                             

                                            Note I cheated and just used N and 0 for the numeric and Non-Blank traps in this representation.

                                             

                                            The fact that the fields cut across each other vertically is of no concern to the floating trap, which works horizontally.  As long as you have the right number of "separators" between the data, i.e. the same number of fields and gaps/delimiters, you will be fine.

                                             

                                            Gareth[/QUOTE]

                                            The dashes will count as numeric values then? I didn't find that they did, but i may have placed it wrong. Anyway a problem i noticed at around the same time as the previous issue with the spaces in the stock number is that sometimes the stock number will end and the owing field will begin, leaving no spaces. I'll have a play and see if i can get everything selected in the way you suggest, but given i'm only showing sample data i'll have to play about with any suggestions really.

                                             

                                            Edit:Having tried the rsplit formula i've noticed another little problem, which just shows how rubbish this data is. There isn't always a value in the Owing column, so with the rsplit it only picks up one part of the stock number, not the others if it's seperated by spaces. Perhaps if i put stock number and product together and did the split stuff? Can't try it now which is why i'm posting it first.