15 Replies Latest reply: May 15, 2014 10:00 AM by FrankK _ RSS

    Model Assistance

    FrankK _

      Hello,

       

      I've been a monarch user for a few years and still learning.  This is the first time i've been to the forum so forgive me if i don't include all the necessary information.

       

      I have a HTML file (first time working with one) that i'm working on.  I'm having a few problems but i'll mention the one i'm more concerned with.  The report is a Trial Balance and i'm trapping on the Account, Debit, and Credit amounts.  However, at the bottom of the report is information on specific days.  This data isn't part of my actual account number, debit and credit section but since the data is similar, it too was trapped and included in my totals throwing everything off. 

       

       

      If i need to include something else, please let me know and i'd be happy to. 

       

      Thank you,

       

      Frank

        • Model Assistance
          Data Kruncher

          Hi Frank,

           

          Without seeing a sample of the report, and assuming that your regular debit and credit do not have dates fields which could be appended, can you append the dates you mentioned to the amounts you don't want to include, then filter the results to only include records with null dates with IsNull(MyDate)?

            • Model Assistance
              RalphB _

              Another option besides what Kruncher's is if the date info is on a separate line by itself is to treat is a blank footer.  That way, Monarch will ignore that line when it comes to it.

                • Model Assistance
                  FrankK _

                  My report doesn't have any date fields but i'd be happy to give you a sample, if someone could explain to me how.  I read Mike's post on copy/pasting a sample but didn't know where to find the #icon.

                    • Model Assistance
                      Data Kruncher

                      To get all of the post editing gadgets, click the "User CP" link at the top left. Now select Options and scroll all the way down select Enhanced Editing - Full WYSIWYG (I think that's what it's called). Now save the changes.

                       

                      Now when you edit/create posts you have the # icon and plenty of others too.

                        • Model Assistance
                          FrankK _

                          Here is a sample from my HTML report. Currently, my trapps are picking up the the Account, which would be the first bolded area, and then my debits and credits, which in some cases, could be 0 (showing nothing at all). Since the Days section at the bottom are structured like my accounts, they pull them into my totals with the debits and credits. The Days section begins with the 400-9000-010. I need to fix my model to show everything but that section. Does this help?

                           

                          900-9820-920 - PERSONAL PROPERTY TAXES[/B]

                          <TABLE=002 NEST=07 ROW=037 COL=02 ID=1835>

                           

                          <TABLE=002 NEST=07 ROW=037 COL=03 ID=1836>

                           

                          <TABLE=002 NEST=07 ROW=037 COL=04 ID=1837>

                          262.71[/B]

                          <TABLE=002 NEST=07 ROW=038 COL=01 ID=1838>

                          900-9900-000 - INSURANCE OFFICERS LIFE

                          <TABLE=002 NEST=07 ROW=038 COL=02 ID=1839>

                           

                          <TABLE=002 NEST=07 ROW=038 COL=03 ID=1840>

                          14,547.50

                          <TABLE=002 NEST=07 ROW=038 COL=04 ID=1841>

                           

                          <TABLE=002 NEST=07 ROW=039 COL=01 ID=1842>

                          950-9805-830 - HO COSTS CAPITAL

                          <TABLE=002 NEST=07 ROW=039 COL=02 ID=1843>

                           

                          <TABLE=002 NEST=07 ROW=039 COL=03 ID=1844>

                          31,182.00

                          <TABLE=002 NEST=07 ROW=039 COL=04 ID=1845>

                           

                          <TABLE=002 NEST=07 ROW=040 COL=01 ID=1846>

                          950-9830-610 - LEASE &RENT BUILDING

                          <TABLE=002 NEST=07 ROW=040 COL=02 ID=1847>

                           

                          <TABLE=002 NEST=07 ROW=040 COL=03 ID=1848>

                          1,414,948.00

                          <TABLE=002 NEST=07 ROW=040 COL=04 ID=1849>

                           

                          <TABLE=002 NEST=07 ROW=041 COL=01 ID=1850>

                          950-9840-505 - CAPITAL LEASE EQUIP EXPENSE

                          <TABLE=002 NEST=07 ROW=041 COL=02 ID=1851>

                           

                          <TABLE=002 NEST=07 ROW=041 COL=03 ID=1852>

                          84,622.40

                          <TABLE=002 NEST=07 ROW=041 COL=04 ID=1853>

                           

                          <TABLE=002 NEST=07 ROW=042 COL=01 ID=1854>

                           

                          <TABLE=002 NEST=07 ROW=042 COL=02 ID=1855>

                           

                          <TABLE=002 NEST=07 ROW=042 COL=03 ID=1856>

                           

                          <TABLE=002 NEST=07 ROW=043 COL=01 ID=1857>

                          Grand Total

                          <TABLE=002 NEST=07 ROW=043 COL=02 ID=1858>

                           

                          <TABLE=002 NEST=07 ROW=043 COL=03 ID=1859>

                          25,165,757.50

                          <TABLE=002 NEST=07 ROW=043 COL=04 ID=1860>

                          25,165,757.50

                          <TABLE=002 NEST=07 ROW=044 COL=01 ID=1861>

                           

                          <TABLE=002 NEST=07 ROW=045 COL=01 ID=1862>

                          Net Income (Loss)

                          <TABLE=002 NEST=07 ROW=045 COL=02 ID=1863>

                           

                          <TABLE=002 NEST=07 ROW=045 COL=03 ID=1864>

                           

                          <TABLE=002 NEST=07 ROW=045 COL=04 ID=1865>

                          889,148.62

                          <TABLE=002 NEST=07 ROW=046 COL=01 ID=1866>

                           

                          <TABLE=002 NEST=07 ROW=047 COL=01 ID=1867>

                          Days/Units Accounts

                          <TABLE=002 NEST=07 ROW=047 COL=02 ID=1868>

                           

                          <TABLE=002 NEST=07 ROW=047 COL=03 ID=1869>

                           

                          <TABLE=002 NEST=07 ROW=047 COL=04 ID=1870>

                           

                          <TABLE=002 NEST=07 ROW=048 COL=01 ID=1871>

                          400-9000-010 - Private Pay Days

                          <TABLE=002 NEST=07 ROW=048 COL=02 ID=1872>

                           

                          <TABLE=002 NEST=07 ROW=048 COL=03 ID=1873>

                          20,156.00

                          <TABLE=002 NEST=07 ROW=048 COL=04 ID=1874>

                           

                          <TABLE=002 NEST=07 ROW=049 COL=01 ID=1875>

                          400-9000-012 - Hospice Days

                          <TABLE=002 NEST=07 ROW=049 COL=02 ID=1876>

                           

                          <TABLE=002 NEST=07 ROW=049 COL=03 ID=1877>

                          4,278.00

                          <TABLE=002 NEST=07 ROW=049 COL=04 ID=1878>

                           

                          <TABLE=002 NEST=07 ROW=050 COL=01 ID=1879>

                          400-9000-020 - Medicare A Days

                          <TABLE=002 NEST=07 ROW=050 COL=02 ID=1880>

                           

                          <TABLE=002 NEST=07 ROW=050 COL=03 ID=1881>

                          28,946.00

                          <TABLE=002 NEST=07 ROW=050 COL=04 ID=1882>

                           

                          <TABLE=002 NEST=07 ROW=051 COL=01 ID=1883>

                          400-9000-030 - Medicaid Days

                          <TABLE=002 NEST=07 ROW=051 COL=02 ID=1884>

                           

                          <TABLE=002 NEST=07 ROW=051 COL=03 ID=1885>

                          75,649.00

                          <TABLE=002 NEST=07 ROW=051 COL=04 ID=1886>

                           

                          <TABLE=002 NEST=07 ROW=052 COL=01 ID=1887>

                          400-9000-045 - Insurance Days

                          <TABLE=002 NEST=07 ROW=052 COL=02 ID=1888>[/CODE]

                            • Model Assistance
                              Grant Perkins

                              Here is a sample from my HTML report. Currently, my trapps are picking up the the Account, which would be the first bolded area, and then my debits and credits, which in some cases, could be 0 (showing nothing at all). Since the Days section at the bottom are structured like my accounts, they pull them into my totals with the debits and credits. The Days section begins with the 400-9000-010. I need to fix my model to show everything but that section. Does this help?

                                        

                              900-9820-920 - PERSONAL PROPERTY TAXES[/B]

                              <TABLE=002 NEST=07 ROW=037 COL=02 ID=1835>

                               

                              <TABLE=002 NEST=07 ROW=037 COL=03 ID=1836>

                               

                              <TABLE=002 NEST=07 ROW=037 COL=04 ID=1837>

                              262.71[/B]

                              <TABLE=002 NEST=07 ROW=038 COL=01 ID=1838>

                              900-9900-000 - INSURANCE OFFICERS LIFE

                              <TABLE=002 NEST=07 ROW=038 COL=02 ID=1839>

                               

                              <TABLE=002 NEST=07 ROW=038 COL=03 ID=1840>

                              14,547.50

                              <TABLE=002 NEST=07 ROW=038 COL=04 ID=1841>

                               

                              <TABLE=002 NEST=07 ROW=039 COL=01 ID=1842>

                              950-9805-830 - HO COSTS CAPITAL

                              <TABLE=002 NEST=07 ROW=039 COL=02 ID=1843>

                               

                              <TABLE=002 NEST=07 ROW=039 COL=03 ID=1844>

                              31,182.00

                              <TABLE=002 NEST=07 ROW=039 COL=04 ID=1845>

                               

                              <TABLE=002 NEST=07 ROW=040 COL=01 ID=1846>

                              950-9830-610 - LEASE &RENT BUILDING

                              <TABLE=002 NEST=07 ROW=040 COL=02 ID=1847>

                               

                              <TABLE=002 NEST=07 ROW=040 COL=03 ID=1848>

                              1,414,948.00

                              <TABLE=002 NEST=07 ROW=040 COL=04 ID=1849>

                               

                              <TABLE=002 NEST=07 ROW=041 COL=01 ID=1850>

                              950-9840-505 - CAPITAL LEASE EQUIP EXPENSE

                              <TABLE=002 NEST=07 ROW=041 COL=02 ID=1851>

                               

                              <TABLE=002 NEST=07 ROW=041 COL=03 ID=1852>

                              84,622.40

                              <TABLE=002 NEST=07 ROW=041 COL=04 ID=1853>

                               

                              <TABLE=002 NEST=07 ROW=042 COL=01 ID=1854>

                               

                              <TABLE=002 NEST=07 ROW=042 COL=02 ID=1855>

                               

                              <TABLE=002 NEST=07 ROW=042 COL=03 ID=1856>

                               

                              <TABLE=002 NEST=07 ROW=043 COL=01 ID=1857>

                              Grand Total

                              <TABLE=002 NEST=07 ROW=043 COL=02 ID=1858>

                               

                              <TABLE=002 NEST=07 ROW=043 COL=03 ID=1859>

                              25,165,757.50

                              <TABLE=002 NEST=07 ROW=043 COL=04 ID=1860>

                              25,165,757.50

                              <TABLE=002 NEST=07 ROW=044 COL=01 ID=1861>

                               

                              <TABLE=002 NEST=07 ROW=045 COL=01 ID=1862>

                              Net Income (Loss)

                              <TABLE=002 NEST=07 ROW=045 COL=02 ID=1863>

                               

                              <TABLE=002 NEST=07 ROW=045 COL=03 ID=1864>

                               

                              <TABLE=002 NEST=07 ROW=045 COL=04 ID=1865>

                              889,148.62

                              <TABLE=002 NEST=07 ROW=046 COL=01 ID=1866>

                               

                              <TABLE=002 NEST=07 ROW=047 COL=01 ID=1867>

                              Days/Units Accounts

                              <TABLE=002 NEST=07 ROW=047 COL=02 ID=1868>

                               

                              <TABLE=002 NEST=07 ROW=047 COL=03 ID=1869>

                               

                              <TABLE=002 NEST=07 ROW=047 COL=04 ID=1870>

                               

                              <TABLE=002 NEST=07 ROW=048 COL=01 ID=1871>

                              400-9000-010 - Private Pay Days

                              <TABLE=002 NEST=07 ROW=048 COL=02 ID=1872>

                               

                              <TABLE=002 NEST=07 ROW=048 COL=03 ID=1873>

                              20,156.00

                              <TABLE=002 NEST=07 ROW=048 COL=04 ID=1874>

                               

                              <TABLE=002 NEST=07 ROW=049 COL=01 ID=1875>

                              400-9000-012 - Hospice Days

                              <TABLE=002 NEST=07 ROW=049 COL=02 ID=1876>

                               

                              <TABLE=002 NEST=07 ROW=049 COL=03 ID=1877>

                              4,278.00

                              <TABLE=002 NEST=07 ROW=049 COL=04 ID=1878>

                               

                              <TABLE=002 NEST=07 ROW=050 COL=01 ID=1879>

                              400-9000-020 - Medicare A Days

                              <TABLE=002 NEST=07 ROW=050 COL=02 ID=1880>

                               

                              <TABLE=002 NEST=07 ROW=050 COL=03 ID=1881>

                              28,946.00

                              <TABLE=002 NEST=07 ROW=050 COL=04 ID=1882>

                               

                              <TABLE=002 NEST=07 ROW=051 COL=01 ID=1883>

                              400-9000-030 - Medicaid Days

                              <TABLE=002 NEST=07 ROW=051 COL=02 ID=1884>

                               

                              <TABLE=002 NEST=07 ROW=051 COL=03 ID=1885>

                              75,649.00

                              <TABLE=002 NEST=07 ROW=051 COL=04 ID=1886>

                               

                              <TABLE=002 NEST=07 ROW=052 COL=01 ID=1887>

                              400-9000-045 - Insurance Days

                              <TABLE=002 NEST=07 ROW=052 COL=02 ID=1888>[/code][/quote]

                               

                               

                              Not wanting to jump in when others are responding already but ...

                               

                              Two options come to mind so far.

                               

                              Either make the trap specific so that the 400- numbers are excluded, e.g. by specifying "9" as the first digit in the code trap (though that is likely to be too excluding I suspect, but you never know ....) or;

                               

                              Take the whole set, including the 400's and then filter to ensure that the 400's are not used in any subsequent analysis or exported output. You may get a way with a simple filter or it may need to be complex with multiple non-contiguous values, etc., etc.  Lots of options so we made need to take this into a little more detail if you go along that route.

                               

                              HTH.

                               

                               

                              Grant

                                • Model Assistance
                                  Data Kruncher

                                  Copy and paste into Notepad and save as "model.xmod"

                                   

                                  [Account Category]&lt;&gt;"Days/Units Accounts" .And. /SIZE

                                  val(left(,1)) .In.(1,2,3,4,5,6,7,8,9)[/SIZE]

                                  </model>[/SIZE]

                                  /code

                                   

                                  Does this approach work for you? It uses the same concept of exclusion filtering to get the data you really want.

                                   

                                  Kruncher

                                    • Model Assistance
                                      FrankK _

                                      Kruncher,

                                       

                                      Forgive me but i'm not sure i understand what you're asking.  Copy and paste the text in the box into a Notepad and the file name woudl be "model.xmod"?  My extension would remain .txt, correct?

                                        • Model Assistance
                                          Data Kruncher

                                          If you enclose the file name with double quotes, Notepad won't add the default .txt extension.

                                           

                                          Even if you did wind up with the model.xmod.txt name you can safely rename the file and take off the .txt part. Windows will give you little warning, but it's fine.

                                            • Model Assistance
                                              FrankK _

                                              If you enclose the file name with double quotes, Notepad won't add the default .txt extension.

                                               

                                              Even if you did wind up with the model.xmod.txt name you can safely rename the file and take off the .txt part. Windows will give you little warning, but it's fine.[/QUOTE]

                                              Kruncher,

                                               

                                              I did get this to work and was able to change some things around and worked just how i needed it to.  Though, i've been looking at the filters and trying to understand it which i'm still struggling but I won't stop until it makes sense.  Thank you for your help. 

                                               

                                              Frank

                                        • Model Assistance
                                          FrankK _

                                          Grant,

                                           

                                          Unfortantely, i can't trap on the numbers because before that, i have a group of 100, 200, and 300's, not just the 400 and 900.  The report was large and i didn't know how much i could put in here so i only put a section. 

                                           

                                          I did create a filter that gives me the 400's and excludes everything else.  Haven't figured out how to get everything else minus the 400's.

                                            • Model Assistance
                                              Grant Perkins

                                              Grant,

                                              I did create a filter that gives me the 400's and excludes everything else.  Haven't figured out how to get everything else minus the 400's.[/quote]

                                               

                                              What was your formula for the filter that gave you the 400s only?

                                               

                                              Grant

                                                • Model Assistance
                                                  FrankK _

                                                  What was your formula for the filter that gave you the 400s only?

                                                   

                                                  Grant[/QUOTE]

                                                  Here is my filter

                                                  Trim()="Private Pay Days".Or.

                                                  Trim()="Hospice Days" .Or.

                                                  Trim()="Medicare A Days" .Or.

                                                  Trim()="Medicaid Days" .Or.

                                                  Trim()="Insurance Days" .Or.

                                                  Trim()="Leave/Hold Days-Medicaid"

                                                   

                                                  The filter works except that it doesn't include the Leave/Hold Days-Medicaid.  I'm not sure why or maybe it's due to the forward slash and minus sign.  If i can get that to be included, that would break out all my 400's. 

                                                  Then i'll just need a caculated field i imagine to pull over everything but the 400s.  I'm assuming a formula to take all of the accounts minus my filter or something, if that's even possible.  Any thoughts?

                                                   

                                                  Frank

                                                  /SIZE

                                                    • Model Assistance
                                                      Grant Perkins

                                                      Frank,

                                                       

                                                      If you don't want any of the 400's - which is how I read what you have written, then you can simply filter on the first part of the code. There may be some different options open to you depending on which version of Monarch you are using but based on the filter being INCLUSIVE and if your Account Number field is called "Account Number" (change as required)  then this should work.

                                                       

                                                      LEFT(,3)<>"400"

                                                       

                                                      If you have a list of account you wish to filter out you could 'list' them, as you did based on the description, or with a late version of Monarch at your disposal, you could use the .Notin. operator in the formula.

                                                       

                                                      Or various combinations and collections.

                                                       

                                                      The filters might become complex so I would suggest creating and testing them one at a time and then using the Compound Filter feature to combine the simpler individual filters into their more complex versions for the final definition you require.

                                                       

                                                      HTH.

                                                       

                                                      Grant

                                                        • Model Assistance
                                                          FrankK _

                                                          Frank,

                                                           

                                                          If you don't want any of the 400's - which is how I read what you have written, then you can simply filter on the first part of the code. There may be some different options open to you depending on which version of Monarch you are using but based on the filter being INCLUSIVE and if your Account Number field is called "Account Number" (change as required)  then this should work.

                                                           

                                                          LEFT(,3)<>"400"

                                                           

                                                          If you have a list of account you wish to filter out you could 'list' them, as you did based on the description, or with a late version of Monarch at your disposal, you could use the .Notin. operator in the formula.

                                                           

                                                          Or various combinations and collections.

                                                           

                                                          The filters might become complex so I would suggest creating and testing them one at a time and then using the Compound Filter feature to combine the simpler individual filters into their more complex versions for the final definition you require.

                                                           

                                                          HTH.

                                                           

                                                          Grant[/QUOTE]

                                                          Grant,

                                                           

                                                          I tried the filter and it worked great.    I do have V.9 Pro and saw the .Notin. function and I'm curious as to hell it works.  I clicked on it to see what it required and kind of confused.  As for listing them out like i did, how would i have gone about excluding them.  Would that have been part of the .Notin. function? 

                                                           

                                                          I appreciate your help.  You and Data Kruncher have showed me knew ways to get data and I appreciate it. I'm always looking to learn more with this software. 

                                                           

                                                          thanks,

                                                           

                                                          Frank