31 Replies Latest reply: May 15, 2014 10:05 AM by elginreigner _ RSS

    Problem with External Lookup and Filtering

    Scott_loughner _

      Here's the deal.

       

      Been a Monarch user for 10+ years, and have always been able to find a work around until now.

       

      The Problem

       

      I have a multi-step process in which I merge data from several different text file.

      Step 1 - Open Text file 1 - apply template,filters and so forth - Project Export to DBF.

      Step 2 - Open Text file 2 - apply template, create external lookup to DBF created in step 1,[U] when applying the filter on a field that is looked up from the DBF, No results show.[/U], project export to DBF2

      Step 3 - Open Text file 3 - apply template, create external lookup to DBF created in step 2 which doesnt have any records, project export to excel spreadsheet with 20 worksheets.

       

      Note I have a project for each step above.

       

      I am currently in the process of writing VBA script to automate the process but the result of step 2 is all ways blank.  If I remove the filter in step 2 I can see the data and see records that should be filter.

       

      :confused:Crazy thing is works great for the girl that sits next to me with an older pc.  These are company PC's in which I have been upgraded and she has not. We have all the same software versions Monarch Version 10.  Both windows XP service pack 2 wth .net framework version 3.

        • Problem with External Lookup and Filtering
          Olly Bond

          Hello Scott,

           

          Firstly, please check out www.********************[/url] (DataKruncher's excellent site) and download the free tool to help you build the VBA script - it will save you time and headaches!

           

          Secondly, it would look as if Monarch is applying the filter on your data before performing the external lookup. That sounds like there's something wrong deep down inside - if you manually "refresh external lookups" from the File menu does it fix it?

           

          Simple workaround is to create an extra step. Step 2 stays at it is without a filter. Step 2b opens the DBF2, applies the filter, exports to DBF2b. Edit step 3 to lookup from DBF2b.

           

          Best wishes,

           

          Olly

            • Problem with External Lookup and Filtering
              Grant Perkins

              Scott,

               

              A quick off the top of my head check list - just in case.

               

              Are you completely sure that you are both running the same release of 10?

               

              Also there may be some other updates for windows/office/DBF files that might not be in place - you may have checked that already but I would imagine that might not be easy.

               

              I assume that you are copy the models/projects that fail on your machine to the older machine and trying it, not using what may already have been there?

               

              For some reason the problem sounds familiar but it may take a little digging to see if there is anything relevant in the depths of the forum threads.

               

              I'll try a few searches.

               

              It's not the same but I wonder of the ideas in [URL="http://www.monarchforums.com/showpost.php?p=12430&postcount=1"]this thread[/URL] might throw up some concepts to test to see what happens. You never know what ideas may arise.

               

               

               

               

              Grant

                • Problem with External Lookup and Filtering
                  Grant Perkins

                  Scott,

                   

                  I also found [URL="http://www.monarchforums.com/showpost.php?p=2370&postcount=1"]this thread[/URL].

                   

                  Now without knowing how you filter is working this may be a mile off but it did occur to me that the you step one export could be using different export option settings as defaults when compared to you co-worker's machine.

                   

                  You have probably already checked this but I thought I would toss it into the pot - it's an easy one to overlook especially if your new machine has a fresh Monarch install that may have changed some default parameters you have set once and forgotten about. I've had similar things happen to me.

                   

                   

                  Grant

              • Problem with External Lookup and Filtering
                Nigel Winton

                Hi Scott

                I found in V9 that if I appply a filter to a table and export, I sometimes get unexpected results. If however I leave the table unfiltered and unsorted and set up the project export and apply the filter there it works OK.

                Maybe you are getting something similar.

                 

                Regards

                 

                Nigel

                • Problem with External Lookup and Filtering
                  sookie49 _

                  Scott,

                   

                  I've been experiencing the problem of filters not working properly with fields that are external look ups since I went to Version 9. 

                   

                  I've sent files to Datawatch but they have been unable to recreate the problem.

                   

                  It is very scary that this problem occurs.  It makes me very suspicious any time a filter returns no records.  I think this could possibly give incorrect or inconsistent audit results when used by an auditor.

                   

                  I've been able to temporarily get it to work by copying the filter formula to a new filter but even that eventually stops working.  Sometimes I was able to get the filter to work by changing one of the numeric fields, i.e. I changed the format or the number of decimal places or even the width of the display which caused the program to recalculate. Frequently I would have to make several changes before it would work.

                   

                  I've tried recreating the problem on other computers in the office.  I've been able to recreate it on some but not all computers.  The ones I've been able to create it on are Dell.  Don't know if that is significant.

                   

                  Regards,

                   

                  Jim

                    • Problem with External Lookup and Filtering

                      I am the "girl" that Scott refers to in the first post.  There are 3 of us in our group - all experienced Monarch Users - we are the ones who get asked to figure out what is wrong with other employees models.  We have upgraded our PCs to the biggest and fastest available to us.  The PCs have very similar builds, but Monarch 10.5 remains inconsistant.

                       

                      Part of our problem may be related to how much data we are working with.  The second step in this process is trying to filter out 130K records from 1 million +.  Some days this works fine on my PC - other days it fails.  Sometimes it can be fixed by refreshing the external data, but not always.  I have tried - close all, closing and reopening Monarch and rebooting the PC.  The most recent thing that has limited results is sorting the data or resorting the data. 

                       

                      Today - in different report - I had a much smaller amount of data about 23K records and had the same 0 records returned!  When you take off the filter, you can see that the field was successfully joined and there is data that matches the filtering criteria.  It is very frustrating - I love using Monarch and I am having a hard time recommending it to new users because of the data inconsistancies.

                        • Problem with External Lookup and Filtering

                          This is a 3 step process that we have built for Product Tracking - I think I built it in V9 - All steps are projects - so settings shouldn't be a problem.  

                           

                          Step 1 - filters out 130K from 150K - exported as a .dbf (we have also tried .mdb - same results)

                           

                          Step 2 - Join .dbf from Step 1 to a text file of demographics with 1 million+ records.  Filter out the 130K and export as a .dbf. - This is the step that works about 20% of the time on the first try - I have been able to get it to work about another 70% on the second try - About 10% of the time it takes 3 - 5 tries before it works.

                           

                          Step 3 - Has 8 external lookups (no reports of missing data from the end user) to translate codes into 'English' and 6 calculated fields - Project Exports - 45 to 4 or 5 different Excel Spreadsheets.  This step doesn't seem to fail.

                            • Problem with External Lookup and Filtering
                              Olly Bond

                              Hello speidelml,

                               

                              Have you tried splitting up stage 2 into two or three steps?

                               

                              First step, convert 1m records from text to "big.dbf".

                              Second step, open "big.dbf" and do the external lookup to output of your first project, export again.

                              Thirdly, open this joined dataset and filter it, then export again?

                               

                              This might help identify where the problem lies, if not solve it?

                               

                              Best wishes,

                               

                              Olly

                                • Problem with External Lookup and Filtering
                                  RalphB _

                                  We are also experiencing the same thing at times.  We can run the process once and everything works great and the next time it does not work.  Run it again and it works.  We never know when this will happen. This process does not use projects so that can't be the problem.

                                   

                                  This is used to balance out cash receipts for our client and we don't know about the error until the person balancing can't.  We use multiple reports to gather the amounts needed.

                                   

                                  This is frustrating when it fails since the whole script process can take up to 20 minutes to run.

                                    • Problem with External Lookup and Filtering

                                      I have created a fourth step to get around the issue that we are having with this process, however - this should work in 3 steps.  I shouldn't have to take off a filter, export 800K records to another file, and apply the same filter to get the results.  It works some of the time - so there is an issue within Monarch that needs to be addressed. 

                                       

                                      I am in the process of documenting the issue - in as much detail as I can - so that I can get with technical support to see if this can be fixed.

                                • Problem with External Lookup and Filtering
                                  Grant Perkins

                                  Today - in different report - I had a much smaller amount of data about 23K records and had the same 0 records returned! When you take off the filter, you can see that the field was successfully joined and there is data that matches the filtering criteria. It is very frustrating - I love using Monarch and I am having a hard time recommending it to new users because of the data inconsistancies.[/quote]

                                   

                                  Is this the same symptom presenting in exactly the same sort of way as your multi-step process problem or is it an example of another inconsistency that seem similar?

                                   

                                  Also, when you say the lookup up link clearly HAS worked if you remove the filter it suggests to me that the the filter is the problem (somehow) but that seems to be at odds with the earlier descriptions (from all who have commented on the problem ...)  Of course I may be misunderstanding the comments.

                                   

                                  Given I don't have you systems or your data files it is clearly something that would be difficult to even attempt to diagnose. It might be useful, even if only for elimination of potential problems areas, for all who have the problem to share information about their systems and specifications - including version of the operating system. From Scott's original post it seemed you had no problems with your previous machine at the time. Did that remain the case up to the point that it was replaced?

                                   

                                  And finally for now - have you gone to the trouble of totally recreating the models involved on the process from scratch? Now I know that may sound silly but going way back in testing in much earlier version I did have one or two odd events that were resolved when I recreated a new model file. I suspect that somehow the file content had become slightly corrupt to the point where it would work sometimes and not others. It's a long shot and I must stress it was very very infrequent and I think in some cases may have been the result of the way  I was testing  but, again, for diagnostic purposes the need to confirm whether or not the problem continues to exist when using new versions of the model files is another step in the investigation that can be achieved without too much pain (usually!). It is just possible that something in the file that is acceptable to V9 may fall foul of the needs of V10 and its underlying technologies.

                                   

                                  Beyond those rather weak suggestions I think all you can do is seek help from Datawatch development people but if you are not getting a catastrophic failure I doubt any event or log files will provide clues so it seems to be one of those awkward situations where a route map to resolution may be tricky to find other than by experimenting.

                                   

                                  Sorry I can't offer something more useful.

                                   

                                   

                                  Grant

                                    • Problem with External Lookup and Filtering
                                      RalphB _

                                      Grant, I tried your suggestion of creating a new model but I'm still experiencing the same problem.

                                       

                                      I repeatedly opened up the database and model and it would work most of the time but not always.  So the problem is either with Monarch or Windows.

                                       

                                      This isn't just happening on my machine, it is happening on others as well as I am not the primary user of this script.

                                       

                                      My operating system is Windows XP Pro Version 2002 with service pack 2 installed.  We have an 8 user network license for Monarch Pro 10.5.  I have also had Monarch re-installed a few times also.

                                        • Problem with External Lookup and Filtering
                                          RalphB _

                                          I have been playing around testing to get this to fail and when it does, if I tell Monarch to "Refresh Database and External Lookups" it populates the table correctly.

                                           

                                          It looks like Monarch does not refresh the database and lookups when opening and applying the model.

                                           

                                          I looked through the Programmers Guide and I cannot find any way to tell Monarch to refresh using VBA.  Anybody out there have any suggestions?  Sandy, Nick, Olly, Grant, Gareth....?

                                           

                                          BTW, I do have pauses built in before and after loading the database.

                                            • Problem with External Lookup and Filtering
                                              Data Kruncher

                                              This really is a bit of an odd one. That said, I seem to recall running into a similar situation sometime last summer, but I wasn't using, or building, an automated solution. I had to force the refresh manually.

                                               

                                              I think[/B] that the solution then was to either recreate the external lookup definition (delete it, save the model and close Monarch, reopen and rebuild the lookup). I don't believe[/I] that I had to rebuild the entire model.

                                               

                                              You're right - there's nothing in the object model that lets you refresh at will. Monarch's object model is good and very functional, but as Nick can likely attest, it does have a few shortcomings. Items that are probably on his wishlist.

                                               

                                              Does adding a DoEvents call after opening the model help at all?

                                               

                                              PS: Don't ask Grant about coding unless you enjoy seeing a grown man cry.

                                                • Problem with External Lookup and Filtering
                                                  ZJSoule _

                                                  Some of us have the same problem at my company.  I have had the issue since version 8, others in my office encountered the problem when we upgraded to version 9. 

                                                   

                                                  I have found that editing the detail template, or editing the date settings can correct it as well as the external look-up refresh (I'm assuming changing these things causes the lookup to refresh).  Many of these projects/models are in Datapump as well, and they have no issues when they are automated. 

                                                   

                                                  I first posted about this a few years ago, when I was still on version 8, it started happening when I received a new computer, it never had the issue before that.  Maybe its a registry issue?

                                                  • Problem with External Lookup and Filtering
                                                    RalphB _

                                                    Sandy,

                                                     

                                                    No.  I ran it several times to get it to fail and then inserted the DoEvents but it did not help.

                                                     

                                                    I thought I best include the Grand Master guru on the list.  Didn't want to think he was left out.  That is the only reason why I listed him.

                                                      • Problem with External Lookup and Filtering
                                                        Data Kruncher

                                                        You're so polite. Are you sure that you're not Canadian? :D:D

                                                         

                                                        Anyway, The Guru kindly reminded me offline about that situation that I'd encountered that was similar. We now suspect that it didn't get resolved by rebuilding the external links. I tried unsuccessfully to find the email history. No matter.

                                                         

                                                        What happened was that my lookups did indeed stop working properly. Fine one day, useless the next.

                                                         

                                                        It turned out that the corporate IT group pushed out some silent Windows patches that ended up downgrading the .Net softare installed on my computer. I'd wouldn't have known had they not sent out a message about making sure that your computer was on so that their critical process could run.

                                                         

                                                        It can't hurt to make sure that you've got the latest .Net and MDAC software (v2.8?) installed.

                                                         

                                                        I got the latest packages from the Microsoft site and all was well in minutes. No issues since, which I why I didn't recall this precisely immediately. Well, either that or I'm just getting older.

                                                         

                                                        I'll stick to the "set it and forget it" story.

                                                          • Problem with External Lookup and Filtering
                                                            RalphB _

                                                            I have the lastest versions of both.  So....

                                                              • Problem with External Lookup and Filtering
                                                                Data Kruncher

                                                                Hmm...

                                                                 

                                                                Nope, I got nothin'.

                                                                 

                                                                Anybody else want to play this game?

                                                                  • Problem with External Lookup and Filtering
                                                                    ZJSoule _

                                                                    I'm grasping at straws here, but maybe it has something do with the size of the DBF thats being referenced?  I've noticed that usually happens when we do a lookup to a DBF thats 250mb plus, whereas smaller ones don't seem to have the issue.

                                                                      • Problem with External Lookup and Filtering
                                                                        RalphB _

                                                                        I'm grasping at straws here, but maybe it has something do with the size of the DBF thats being referenced?  I've noticed that usually happens when we do a lookup to a DBF thats 250mb plus, whereas smaller ones don't seem to have the issue.[/QUOTE]

                                                                         

                                                                         

                                                                        I don't think size has anything to do with it.  The database today is 40 MB and the lookup db is only 5 MB.

                                                                         

                                                                        Grant, I know.  I was just trying to compliment you so you'd throw a few suggestions my way when I need help.

                                                                         

                                                                        A little soft soap goes a long way...:D

                                                                        • Problem with External Lookup and Filtering
                                                                          Data Kruncher

                                                                          I Googled to dig into ZJS's idea regarding dbf file problems, and while I didn't find anything relating to exactly the issue at hand, it seems that it's not uncommon for the Jet engine, a technology that's integral to Monarch, to have issues with dbf files.

                                                                           

                                                                          Personally, I haven't had first hand experience with dbf files since the '80s, so I'm on the outside looking in a little here, as it were. I usually prefer to Excel with Monarch. ;):D

                                                                           

                                                                          I have been working quite successfully with pretty large csv files of late though.

                                                                           

                                                                          Can you convert your dbf to a csv and have a go at the process using the csv file instead of the dbf?

                                                                            • Problem with External Lookup and Filtering
                                                                              RalphB _

                                                                              I Googled to dig into ZJS's idea regarding dbf file problems, and while I didn't find anything relating to exactly the issue at hand, it seems that it's not uncommon for the Jet engine, a technology that's integral to Monarch, to have issues with dbf files.

                                                                               

                                                                              Personally, I haven't had first hand experience with dbf files since the '80s, so I'm on the outside looking in a little here, as it were. I usually prefer to Excel with Monarch. ;):D

                                                                               

                                                                              I have been working quite successfully with pretty large csv files of late though.

                                                                               

                                                                              Can you convert your dbf to a csv and have a go at the process using the csv file instead of the dbf?[/QUOTE]

                                                                               

                                                                               

                                                                              Sandy, these are MSAccess mdb files and not dbf.  I cannot even rember if and when I worked with dbf files unless it was B-Trieve files or some payroll programs.

                                                                               

                                                                              I thought I'd try your suggestion so I converted samples of the mdb tables I use to csv files and created a new model.....same result.  Does not return values every time.  Another theory shot...

                                                                               

                                                                              Any other ideas...Anyone?

                                                                            • Problem with External Lookup and Filtering

                                                                              Grant - these filtering issues happen all of the time since my PC has been upgraded.  (I resisted having my PC replaced for a very long time because other employees were having issues.)  I have had it happen with brand new processes and with older established processes. 

                                                                               

                                                                              It can happen with only one file or one that has external lookups.  You never know when it will happen and I hate not having confidence in the Monarch results.

                                                                                • Problem with External Lookup and Filtering

                                                                                  I tend to use dbf files because they export faster and in one step (no table name).  In this case Monarch is creating all of the databases that I am using and the problem isn't joining/reading the external data - it is filtering out the data once it is joined.

                                                                                   

                                                                                  I have a co-worker who rebuilt some of this using .mdb instead of .dbf and we had the same issues.

                                                                                   

                                                                                  We have worked with our techs to see if we had software conflicts and we did find a couple of conflicts, that we corrected.  This fixed Monarch closing out randomly, but didn't address the filtering issues.

                                                                                    • Problem with External Lookup and Filtering
                                                                                      Grant Perkins

                                                                                      A couple of wild thoughts based on the premise that an upgraded system may very likely be a faster system. Significantly faster. And may have opened up an anomaly for internal performance enhancement algorithms.

                                                                                       

                                                                                      Firstly - has anyone initiated and checked the Logfile to see if it offers any clues?

                                                                                       

                                                                                      Secondly - there are some hidden settings that offer control over certain performance parameters.

                                                                                       

                                                                                      Notably

                                                                                      TableRemainingLoadTime[/B]

                                                                                      TableCompletionThreshold[/B]

                                                                                       

                                                                                       

                                                                                      look kind of interesting to play with in this context. BUT I MUST RE-ITERATE THAT I AM SPECULATING HERE!

                                                                                       

                                                                                      I don't to have any useful understanding (and I have NO practical experience) of what these settings do but it seems to me they may be in the loop somewhere and so playing with them could be worth the effort - even if only to eliminate them from immediate enquiries.

                                                                                       

                                                                                      The description of both the LogOperations setting and the Table performance related settings can be found in the Help under Settings.

                                                                                       

                                                                                      The Hidden settings are all Windows registry related stuff - you will need access rights if you decide to play.

                                                                                       

                                                                                      Hope that is of some interest and use.

                                                                                       

                                                                                       

                                                                                      Grant

                                                                              • Problem with External Lookup and Filtering
                                                                                Grant Perkins

                                                                                I have the lastest versions of both. So....[/quote]

                                                                                 

                                                                                That is not necessarily a positive of course. Likely it IS positive, but it ain't guaranteed.

                                                                                 

                                                                                What have others got?

                                                                                 

                                                                                 

                                                                                Grant

                                                                            • Problem with External Lookup and Filtering
                                                                              Grant Perkins

                                                                              Sandy,

                                                                               

                                                                              No. I ran it several times to get it to fail and then inserted the DoEvents but it did not help.

                                                                               

                                                                              I thought I best include the Grand Master guru on the list. Didn't want to think he was left out. That is the only reason why I listed him.[/quote]

                                                                               

                                                                              Ralph,

                                                                               

                                                                              You are too kind.

                                                                               

                                                                              On this particular subject you are way too kind. 

                                                                               

                                                                              I love the intrigue of the depths of program technology and, on occasion if slightly familiar with the language, might even be able to make a little sense of reading the code.

                                                                               

                                                                              But getting it to work by writing the stuff? Nah. Lost on me. I leave that to true guru's and practioners of the dark arts.

                                                                               

                                                                               

                                                                               

                                                                              Grant