24 Replies Latest reply: May 15, 2014 10:01 AM by rorselli _ RSS

    Weird Issue involving Date Format Option and Filters

    ZJSoule _

      Good afternoon all!

       

      I'm hoping someone else has encountered this very strange error.  I am using Monarch v8.01.  Whenever I have a project that has an external lookup to a .DBF file, if I change the filters, they don't return any information (by change, I mean actually edit the formula within the filter, not just switching between them).  If I turn off filtering, and change the date format under the input menu to something else, and then switch it back to its original, then turn filters back on, the filters work again.  Every time I make a change I have to do this.  This is quite a strange and annoying problem, and I use quite a few DBF files for joins, so it affects me on a daily basis.  Any ideas?

        • Weird Issue involving Date Format Option and Filters
          Grant Perkins

          Just to clarify this;

           

          Is it only with the DBF based external lookup that this problem arises or can you, for example, produce the same problem using a different lookup source?

           

          Is the dbf data call based on a link using date? Or is the date just called in to the database through the lookup? Is the filter based on the lookup key or a non-key data field  read in from the lookup?

           

          There are a number of possible combinations - just trying to narrow things down.

           

          Grant

            • Weird Issue involving Date Format Option and Filters
              ZJSoule _

              Well to answer your questions:

               

              1.  It only happens with DBF based external lookups.

               

              2. The data call is not date based, it is based on policy number.  There isn't even a date field being pulled in.

               

              3.  I'm not sure what you mean by "Is the filter based on the lookup key or a non-key data field read in from the lookup?"  Could you clarify this?

               

              I appreciate your help with this!

                • Weird Issue involving Date Format Option and Filters
                  Grant Perkins

                  OK, on the basis of your answer to 2 I don't think Q3 matters.

                   

                  Are you opening the report (? Report of database) manually and applying a model or have you got this set up as a project at first load?

                   

                  Do the default Input date format and the format in the model differ when loaded (i.e. before edit).

                   

                  The problem certainly does sound odd but seems vaguely familiar fior reasons I can't recall at the moment. However, as I don't use dbf files the possible connection may be unimportant to you problem, even if I can recall what it was!

                   

                   

                  Grant.

                    • Weird Issue involving Date Format Option and Filters
                      ZJSoule _

                      I have it set as a project, but if I open the base file and apply the model, the problem remains.  The date formats do indeed match, M/D/Y.

                        • Weird Issue involving Date Format Option and Filters
                          Grant Perkins

                          OK, let's see if I have this right.

                           

                          You open a model against a report and part of that model is an external lookup to a .dbf file. The lookup works but the filters fail. This applies to a number of models that all have an external .dbf lookup as a common feature.

                           

                          You open the model and change the filter definition as you have previously described you can get the filter to work.

                           

                          You then save the 'revised' model but it fails next time you use it. You have checked that the model referred to by the project definition and your

                          personally applied 'manual' processing is in fact being updated when saved.

                           

                           

                          This problem only occurs with (ALL) models that have .dbf based external lookups. It applies even though there are no date related fields used for the link or brought in from the external lookup.

                           

                          I have assumed that the dates (from the original report)  are used in the filter formula - is this the case?

                           

                          Have you tried creating a NEW model (maybe simpler than the problem model for testing purposes) from nothing  but using a few fields, the .dbf lookup  and a filter to see if it gives the came problem?

                           

                           

                          Grant

                            • Weird Issue involving Date Format Option and Filters
                              ZJSoule _

                              Actually, it is the opposite.  The filters work when I open the model, but if I edit the formula, they all fail.  The only way I have found to make them work again is to set it to "no filter", go into the input menu and change the date format to something different, set it back to M/D/Y and then the filters display information again.

                               

                              As for dates in the filters, I don't usually have much use for them.  Some have them but most don't.  It doesn't matter which one I edit, the end result is the same.

                               

                              I have built multiple reports using different .DBF files and have had the same problem with all of them, however I will try again and see what I come up with.

                               

                              No one else in my office who uses Monarch has this issue, and they use the exact same models/projects/base files as I do.  We have checked all our settings to make sure they match.  It is quite a pickle!

                                • Weird Issue involving Date Format Option and Filters
                                  Grant Perkins

                                  Sorry, yes, I remember now you said they worked fine on first opening but not after edit until you made a second edit. As you say, quite a pickle.

                                   

                                  From the fact that your colleagues have no problems and the settings - for .dbf links, Monarch and (presumably) PC/Windows are all the same this is certainly a real puzzle.

                                   

                                  I assume you could use a colleagues machine, do what you can't do on your machine, and have no problems.

                                   

                                  That rather suggests there must be some sort of anomaly hidden on your PC somewhere.

                                   

                                  If it is not a specific problem of date interpretation, just a related editing activity that  makes things work again the date aspect may be a red herring.

                                   

                                  I think without knowing what, specifically, is read/changed when you open a filter for edit and then what specifically is read/changed when you reset the date format the solution to this problem may remain elusive.

                                   

                                  Sorry I can't think of anything else.

                                   

                                  Grant

                                    • Weird Issue involving Date Format Option and Filters
                                      ZJSoule _

                                      Grant,

                                       

                                      I appreciate your thoughts, even if they didn't culminate in a solution.  We are supposedly upgrading to v9 in the very near future, at which point, hopefully, the problem will clear up. 

                                       

                                      Thanks!

                                        • Weird Issue involving Date Format Option and Filters
                                          Grant Perkins

                                          Hopefully the V9 installation will resolve the problem.

                                           

                                          It might be worth checking that your windows installation has any updates that may relates to dbf files (or any that may result in problems where there were none before!)

                                           

                                           

                                          Grant

                                            • Weird Issue involving Date Format Option and Filters
                                              frugalman _

                                              When I am building a model with external lookups and filters (I'm using V9) frequently I will get no records.  I know that my filter and lookups are right.  If I save my model, close all, reopen my report and model, bingo, I have records again!  I had the same problems in V8 and just accept my work around method of closing and saving everything and reopening everything again.

                                                • Weird Issue involving Date Format Option and Filters
                                                  Grant Perkins

                                                  Sounds like V9 alone won't make any difference.

                                                   

                                                  In which case I suspect it will be something related using what is in memory, modified by the edit, rather than in a saved file and some confusion between the two.

                                                   

                                                  Pure speculation and why it should be so apparently selective I have no idea!

                                                   

                                                   

                                                  Grant

                                                    • Weird Issue involving Date Format Option and Filters
                                                      Gareth Horton

                                                      It would be great if you could contact support about this and give them some files to try and reproduce the issue.

                                                       

                                                      Gareth

                                                        • Weird Issue involving Date Format Option and Filters
                                                          ZJSoule _

                                                          I would, except there is sensitive information in our files (SSN, etc.)  No one else in my office has this issue, and they use the files for the same things I do.  I'm suspecting its something local to the PC.

                                                            • Weird Issue involving Date Format Option and Filters
                                                              Joe Berry

                                                              This week I have had the exact same issue.  I too have sensitive data in my files.  I worked around the issue and really did not think too much of it at the time since this was the only time I have had the issue and I have used Monarch since the DOS versions.  If it repeats on other data, I will contact support.

                                                                • Weird Issue involving Date Format Option and Filters
                                                                  Grant Perkins

                                                                  Such things do seem very odd.

                                                                   

                                                                  If we consider that be may be the specific PC what are the possible variables compared to what worked ok previously or what works OK for other machines in the department.

                                                                   

                                                                  I propose a list of possible influences. As a starter (please add more when visiting.) .... :

                                                                   

                                                                  =====================================================

                                                                   

                                                                  - Local date and time/geography settings for Windows.

                                                                   

                                                                  - perhaps access rights/security issues though it seems unlikely in this case.

                                                                   

                                                                  - Windows Updates

                                                                   

                                                                  - Application Updates or updates for other applications that may have changed something common/shared.

                                                                    • Weird Issue involving Date Format Option and Filters
                                                                      ZJSoule _

                                                                      It could very well be any of those things.  I unfortunately cannot change any of that, as my IT department has all of that on lockdown, except for local date/time.  I will keep noodling and investigating, hopefully figure something out.

                                                                        • Weird Issue involving Date Format Option and Filters
                                                                          Bruce _

                                                                          I sometimes have the same result, re-do something & poof no data is displayed.

                                                                           

                                                                          It SEEMS to be related to the size of the database. If I run on it on a small input or database file I can't recreate the problem.

                                                                           

                                                                          However when I run on a huge data-set  (about 175 Meg for an access table, or about 90 meg for text files) I almost always get a problem with the filter not recalculating.

                                                                           

                                                                          Sorry it's not a solution, but more of hint of what the problem might be.

                                                                           

                                                                          Bruce

                                                                            • Weird Issue involving Date Format Option and Filters
                                                                              ZJSoule _

                                                                              The dbf files I use are indeed huge. 200 meg plus.  I will try with a smaller dbf when I get a few free moments and see if that makes a difference.

                                                                                • Weird Issue involving Date Format Option and Filters
                                                                                  ZJSoule _

                                                                                  Ok, I had an opportunity to use some smaller DBF files in external lookups, and I have encountered the same problem, so the size of the file doesn't seem to be a factor for me.

                                                                                    • Weird Issue involving Date Format Option and Filters
                                                                                      Bruce _

                                                                                      ZJ

                                                                                       

                                                                                      If it happens to any size file it should (hopefully) be easy to recreate a small dummy dbf file & contact Monarch's customer support.

                                                                                       

                                                                                      Maybe ask your own IT department to sit in on the phone call so they can unlock things if you are asked to

                                                                                       

                                                                                      Good luck.

                                                                                        • Weird Issue involving Date Format Option and Filters
                                                                                          ZJSoule _

                                                                                          Well,

                                                                                           

                                                                                          Version 9 is up and running here, and we love it.  However... this particular issue has not gone away.  Also, now some other people in our organization are having this issue.  It doesn't happen for me under specific cirumstances anymore, but now its more of a random issue.  Any new thoughts?

                                                                                            • Weird Issue involving Date Format Option and Filters
                                                                                              rorselli _

                                                                                              I'm trying to apply filters to a file which brings some fields from a .DBF file through lookup.

                                                                                               

                                                                                              Acctually is a filter to select only the registers with a match in the join.

                                                                                               

                                                                                              Monarch's behavior is very instable and sometimes I can see the filtered values, sometimes the result is an empty table...

                                                                                               

                                                                                              I've tried lots of forms to filter the registers, all of them in the theory works, but when selected they seem not to scan the registers and output an empty table...

                                                                                               

                                                                                              I'm using the 'monarch 9.01 pro', I'm migrating from a 'monarch 5.01 pro' where the same files and filters are working

                                                                                              I've tryied to create the models from zero but had no effect. The error are the same.

                                                                                               

                                                                                              In my company everybody is having the same problem (other 5 coleagues) using the same OS:

                                                                                               

                                                                                              Windows XP professional version 2002 - SP 2 - english

                                                                                               

                                                                                              Can anyone shed light to the problem?

                                                                                                • Weird Issue involving Date Format Option and Filters
                                                                                                  Olly Bond

                                                                                                  Hello rorselli,

                                                                                                   

                                                                                                  I was about to reply to your other post suggesting this might be the root of the problem.

                                                                                                   

                                                                                                  Filters on database imports are, in my experience, slightly flaky at least as far as Monarch 9. I haven't tested these in Monarch v10 in anger yet.

                                                                                                   

                                                                                                  It's perhaps easier to open the whole database, apply the filter in the table rather than at the import stage, export this filtered data to MDB as a project export, then start again in a second project from this new source.

                                                                                                   

                                                                                                  Using Kruncher's batch file generator, it should be easy to automate this small extra step, and it should overcome the erratic behaviour you're experiencing.

                                                                                                   

                                                                                                  Best wishes,

                                                                                                   

                                                                                                  Olly

                                                                                                    • Weird Issue involving Date Format Option and Filters
                                                                                                      rorselli _

                                                                                                      I've found a solution for my problem, i think it can help the others.

                                                                                                       

                                                                                                      when editing the filter for the version 9.01 of monarch, there are 3 tabs:

                                                                                                      formula / advanced / test

                                                                                                       

                                                                                                      on the formula tab I did no modifications to the existing filter that is causing problem

                                                                                                       

                                                                                                      on the advanced tab you do the following

                                                                                                       

                                                                                                      -select the option:

                                                                                                      'select rows according to their uniqueness with respect to the specified keys'

                                                                                                       

                                                                                                      -then check only the option:

                                                                                                      'Duplicated rows'

                                                                                                       

                                                                                                      -in the Duplicated rows select:

                                                                                                      'all duplicated rows'

                                                                                                       

                                                                                                      in the combo list in the right side, there is a list of fields that exists on the model called:

                                                                                                      'Specify Keys'

                                                                                                       

                                                                                                      Here is the trick, you now have to select a field in the list where every value is the same for all register.

                                                                                                      In my case I've choosen the 'Data' field that for my table was constant in every row

                                                                                                      (sugestions for this step, create a calculated field with a constant value and use it!)

                                                                                                       

                                                                                                      Now I have a perfect functional filter that accepts modifications and behave as expected

                                                                                                       

                                                                                                      I hope I can help somebody