Disappearing Records-date changes

Posted on 2005-04-19
Last Modified: 2010-04-27
My question is as follows:.
We are using FIlemaker Server and FIlemaker 6.0 for our clients.

Ocassionally, we have records that we know were in the database the day before disappear and no where to be found.
Also, some of our dates change automatically and we don't know why.
For example: We have a field called INvoice sent date which is a calculation- If (Invoice sent = "X", Today," ")
We actually put an X in the invoice sent field which the trigggers Invoice sent date.
The field option do not evaluate if reference field is empty and we have indexing on.

I didn't think that the today function recalculates each day. We use the invoice sent date to perform other functions and do not want it to recalculate each time program opens?

DO you have any suggestions? APpreciate your help
Question by:kcassone
    LVL 19

    Expert Comment

    When you say the records disappear, is it that you can't find the records based on values you expect them to have, or is it that the record is actually deleted?

    There is a known bug with the today() function, and you should use the Status(CurrentDate) function instead. It's possible that bug could have something to do with your problem, but from what you have described, I would expect the Invoice sent date to change if the record is altered.That could also be causing your problem.

    In this case, you would probably be better off using a regular date field instead of a calculation, and use a script to set the date field when someone clicks in the Invoice Sent field. That way you have more control. When the X value changes, you can check to see if there's an existing date already, and prompt the user to be sure they really want to make the change.

    It's also possible you could have a corrupted database, but I not try doing any sort of recovery until after you have fixed the date issues and see if that resolves the problem.

    Author Comment

    Yes, I am told the records are actually deleted.

    Are you saying the invoice sent date should change if any of the a record fields change or if only the INvoice sent and invoice sent date change?

    Usually when some one changes the information incorrectly a ? appears in the field.

    The reason why we want to use an automatic calculation field is because we have users that forget to fill the required fields in properly.

    It's very strange because when I look at lst nights back up the invoice sent date is 4/19/05 but today the date is 4/15/05. It really doesn't make any sense to me.

    What is the bug for the today field that you are talking about?

    LVL 28

    Assisted Solution

    Records getting deleted: never seen this, somebody deletes them, maybe not on purpose! a side effect?
    Change rights to disable records deletion in this table and see if it still happens or if there is a script which complains, you might catch the guilty one this way.

    Now function: there is no bug to me, it's just a matter about how you expect it to work: the way you've done it, it will change the date everytime this record is displayed: a calculatied field is dynamic by definition.
    The controversary about "Now" is that it used to miss updating at midnight in fm4, you had to close and reopen the database to get its value updated. A bug for overnight workers.
    To fix yr problem, you can for instance have the operator to enter the X in a dialog window shown by a script (set the X to be a button)
    1 check if the X is entered or not
    2 if empty, show dialog, do you confirm invoice is sent? if yes enter the X and the date value in appropriate field.

    Bill, congratulations for becoming Master! I can take some holidays now!
    LVL 19

    Accepted Solution

    You should probably confirm for yourself that the records are really deleted. My experience is that generally records aren't really being deleted, they are just not showing up in the expected location. If users generally work with a found set or portal for example, and something changes in the records so that they no longer match the criteria, then they will seem to have "disappeared" when really they are just not showing up in the expected place.

    I assume you have some sort of auto increment field as a primary key. Show all records in the table, then sort by this value, and look for gaps in the sequence.
    For example, if you see the sequence 4005, 4006, 4007, 4012, you can tell that five records were created and then deleted. There will probably be a few such gaps here and there, but if you don't find lots of them, then your records are probably not being deleted.

    Try to pin down when the records are disappearing. Have folks keep count of how many records they enter each day, and check the total number of records in the table at the end of each day. If the numbers appear to change at a time that nobody's using the database, then you may have a corrupted file.  In that case, make sure you have multiple good backup copies of the entire database, then try running a recovery on the file in question.  

    The old Today function in FM4 was a bug, because it didn't work the way the documentation described. Rather than fix the bug, FileMaker just changed their documentation to make it sound like it's that way on purpose. It's stupid though, because if you don't restart the database every day, you get inconsistent behavior. In your case, you'd have to restart the FM server each day to ensure Today works properly. Nobody really uses Today for that reason. In fact, in FileMaker 7, the Today function has been removed completely.

    "It's very strange because when I look at lst nights back up the invoice sent date is 4/19/05 but today the date is 4/15/05."
    This could be the Today problem. When you open the original file hosted on the server, it still shows the date from several days ago, because the file has not been closed and reopened. (It has to be closed and reopened *on the server*) But if you open a backup copy, *that* file *has* been closed and reopened, so it updates. That might explain your experience.

    Using a plain date field instead of a calculation doesn't mean you can't fill the date in automatically. You can still do this with a script.

    Instead of having the user type an X in the text field, create a checkbox field, and make it into a button that runs a script. Then, when someone clicks on the check box, the script can change the value of the checkbox field, so an X appears in it, and also apply the date value to the date field at the same time. The advantage of this is that the date value will not change as a result of someone fiddling about with other fields, or doing a replace, import, etc. The script can also do other things like pop up a warning message if it's a slow paying client, etc. Another big advantage is that you can manually correct the date if it accidentally gets changed.

    Lesouef: Thanks! I feel a whole lot smarter somehow...


    Author Comment

    I just can't get either of your suggested calculations to work.
    Can you please give examples of scripts so date automatically updated in a invoice sent date when you put an X in the invoice sent field. I tried to use the Status(Current Date) function but could not get it to work as well as the check box scenario.  I do not want the invoice sent date to change.
    LVL 19

    Expert Comment

    Here is a simple demonstration of the concept.

    Click the check box field.
    The field is actually a button, which runs a script. The script checks to see if the box is already checked. If it isn't, then
    the script changes the checkbox to checked, and stores todays date in the date field.

    If the box IS checked already, then it looks to see if there's a date in the field. If there is, it asks the user if they are
    sure they want to clear it. If they say yes, it clears the date AND the checkbox.
    If they say no, it doesn't do anything.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
    Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now