5 Replies Latest reply: May 15, 2014 10:03 AM by Grant Perkins RSS

    Using value from previous record

    drobert _

      I have an interesting problem that I have before me. There are certain situations in the report where an account (in this case C 0) may have more then one line to show multiple exceptions. In the example below, there are two lines for Account C 0; one is to indicate that the member has surpassed his/her Line of Credit (LOC) and the other to also indicate that he/she has a HOLD on the account. This information is essentially the same (thank god, as it is the same account), but what poses a problem is that I need to capture the LOC amount to make a calculation for the Hold.

       

       

      This is how I have the templates setup:[LIST=1]

      The detail line is defined to capture all the member's accounts so I trap it using Alpha Trap for the Account character and a Numeric Trap for the numeric portion of the Account. I also fill the blank cells with the information of the previous record for Member, FSR# and NAME.

      I also have an Append template to capture the additional (but occasional) phone number by using the Dogbert Trap - affectionately named by Data Cruncher.

      /LISTFrom the detail, I have a formula to extract the appropriate information from the Reason field, depending on what appears at the beginning (Ex: > LOC, < HOLDS, etc). I should also note that when a member has an LOC and has overdrawn his limit, there is an "EX" appended after the Balance. I have no problems extracting the information that I need (like LOC amount, HOLD amount, etc), the problem is the calculation I need to perform when I find a HOLD.

       

      :confused:This is the tricky part: I need to calculate the exception amount, so when there is a HOLD on the Account and the Account Balance is overdrawn with an LOC (I know this because it has an EX after the balance), I need to know the LOC amount to make my calculation for the HOLD. Since the LOC amount is not on the same line, I don't know how to proceed. I am told that any account that has an LOC and[/B] HOLD, the LOC information will come first.

       

      Ex: ExceptionAmount (for HOLD record) = 3,333.33 (Hold Amount) + -1,111.11 (Balance *-1 since there is an EX appended to it) + 222.22 (LOC amount from previous occurrence of this Member Account - 123456, C 0).

       

      I thought of extracting the LOC amount portion exclusively from the Reason field and fill empty cell with the previous value, but the HOLD amount sometimes interferes with the columns used for LOC (as in the example) or other messages in the Reason field.

       

      Is there a way of saving then retrieving the LOC amount to use when I do the calculation when I find a HOLD amount? Maybe with a newer version? Or Pro version?

       

      SHIP TO  : OPERATIONS                                   XXXXXX CREDIT UNION                     PAGE:   11                            

      RUN      : 05FEB09 23:17                             OVERDRAFTS AND EXCEPTIONS                   FOR: 05FEB09 23:59                   

      REPORT # :  101                                      ALL BRANCHES CONSOLIDATED                                                        

       

         MEMBER FSR# NAME                   PHONE     ACCT  BEN           BALANCE REASON                           NET CHANGE SRC  OD NSF   

         -


      -


      -


                         -


           -


      ---           -


      -


      -


      -


      -- ---   

       

         123456 1234 STUCK, A. LITTLE       555-1212H C   0 PK1        1,111.11EX > LOC OF        222.22  15 DAYS                  43   23  

                                            555-1313W                                                                               

      N   0             3.33                                                                               

      P   0           444.44                                                                               

      Z   0             5.55                                                                               

      C   0 PK1        1,111.11EX < HOLDS   OF         3,333.33                    43   23   /code

        • Using value from previous record
          Grant Perkins

          Hi Daniel,

           

          In terms of an upgrade these comments.

           

          The PRO version would allow you to pre-process the report and, for example, create a small table of hold accounts which you could then use as an EXERNAL lookup table in a second stage of the process. Easy to do, easy to batch script automate.

           

          As an alternmative approach you could make the REASON field a Multi-Line field and then use a calculated field to seek out occurences of HOLD text in the field and parse out the required values. Updates in V9 (so V10 from your perspective) made this exercise MUCH easier and very powerful.

           

          But unless I have missed something in your description it seems to me you can just pick up the HOLD value using a Guru trap and the text HOLD as the preceding string for the field values. (Assuming that the balance value on that line is always the same as the balance value on the first line.

           

          Does this suggest anything that helps?

           

           

          Grant

            • Using value from previous record
              drobert _

              I've read up on the [URL="http://www.monarchforums.com/showthread.php?p=9873#post9873"]Guru trap[/URL] and wasn't able to figure out how it would work for me, so I tried different things and was not able to get it to work. Maybe I am missing something (probably) or need to modify the way I have it setup now instead of creating a new Append template.

               

              I know this is hand-holding:o, but can you give me a bit more guidance as to how I need to setup the append template for the Guru Trap?

                • Using value from previous record
                  Grant Perkins

                  Hi Daniel,

                   

                  Guru trap is much the same as the Dogbert trap in this case.

                   

                  A one line sample, same trap as for your detail trap.

                   

                  Paint a field some way across the line to cover the right hand portion of what is the Reason field. (You can make this easier by changing the sample data, doing so will not affect the trap at all in this instance. Highlight a Hold line as if selecting it for for a trap sample and then use the button with an up arrow on it {iirc for V6} to promote that line to be the new sample line.

                   

                  The reason section will look like this:

                   

                  < HOLDS   OF         3,333.33   /code

                   

                  Paint a field that covers just the value part of tghe line leaving HOLDS (an maybe a bit more) to the LEFT of the field start point.

                   

                  Now set the Advanced Properties for the field (called something else in V6? I can't accurately recall ...) to set a "Start field on .." with a preceding string of HOLDS   .  (This will actually display in lower case.)

                   

                  If you casll the field HOLDS VALUE (or whatever) it should

                   

                  lf give you one of the three values you need. The other two values are both on the first line I think.

                   

                  If you have tried that and it did not work there may be some aomalies that are not obvious in the data sample posted - for which it works fine at my end!

                   

                  Be sure to let us know if that helps at all or if not can you spot a reason as to why not?

                   

                   

                  Grant

                    • Using value from previous record
                      drobert _

                      Ok, now I get it:rolleyes:! It takes a while sometimes...

                       

                      It was reversed of what I was looking for but I was able to adapt it to what I wanted. You see, only when I find a HOLD line that has an EX do I need to have the LOC available to make the calculation, so all the values are all on the HOLD line except for the LOC.

                       

                      I changed it so that it was the LOC amount that I captured. I also had to make it fill the empty cell with the previous value.

                       

                      This method assumes that there are no other LOC lines for other accounts showing up before the HOLD of the same account is encountered, but we tested some scenarios and we found that we did not[/B] have intermixed accounts (i.e.: all messages for the same account follow each other).

                       

                      Because this field was not needed to be displayed, it did not matter that the subsequent members that did not have an LOC had inherited the one from a previous member and also since on the HOLD line I know that there should be an LOC amount (due to the EX by the balance), I was able to use the LOC amount only when needed.

                       

                      Wow, now I am beginning to see the power of the Append Templates:cool:. It's amazing after so many years of using Monarch and sometimes doing multi-extracts as a work-around, that I finally see the potential of doing it all one step (even in version 6)! It makes me want to go back and rewrite all the [URL="http://www.thefreedictionary.com/kludgy"]kludgy[/URL] ones...

                       

                      Seeing that it seems to work for us:D, I have happily completed this extract. On with the next extract, full speed ahead!!!

                       

                      Thanks again for the informative step-by-step instructions.

                        • Using value from previous record
                          Grant Perkins

                          Hi Daniel,

                           

                          Ok, I see what you are saying.

                           

                          I make a few assumptions with these things sometimes, usually with caveats if they may be really stretching the reality of report format and programmer design thinking!

                           

                          I think you mentioned that the LOC line always comes first and I assumed it would be part of the first (detail) line per your example. So one would always trap the balance and anything that MIGHT appear in the description for that line noting if it was a LOC. Then add the Hold value to the mix when it exists using the Guru trap.

                           

                          Now that may result in some combinations that are not required but these can be dealt with by filtering the records for only those combinations that ARE required.

                           

                          By far the most important point to solve your need here was to get all the values into the same record and make the calculation easily possible.

                           

                          Sounds like you are having a bit of a Eureka! moment. Great, aren't they!

                           

                           

                          Have fun.

                           

                           

                          Grant