Data disappearing

[brownees]

I am running Filemaker v5.03 with Filemaker Server 5 on Windows NT platform.

My Purchase Order database has been running fine for a year, but now having problems with data disappearing. A portal in the Purchase Order file indicates invoices paid against the Purchase Order to date. The user enters new invoice details in global fields and a script checks the information and adds the invoice to the Invoice file. The weird thing is that when a new invoice is added, it sometimes overwrites the previous invoice entered - weird and erratic.

Any ideas before I get the dreaded Access dumped on me?

[CobaltSky]

Hello brownees,

You've said your script adds the invoices to the invoice file, but youv'e not explained exactly how it is doing that.

However if the method used is to create new invoice records via the relationship, then presumably the script is set to generate a new unique key value, then use it to pivot the new data into place on a new invoice record.

If that is the case, the problem would appear to relate to the process the script is using to generate a new/unique key value for the next invoice - on occasions it is re-using the same value and thhus the new data is being written over the last invoice instead of creating a new one.

If the file is multi-user and the script is using global fields to manage the allocation of new invoice numbers, then the problem may arise from the fact that global field values are not shared between different computers over the network? Alternatively, you may find that the new invoice numbers are being calculated based on a count of related records - in which case whenever a record is deleted from the invoice file, a duplicate number would be issued the next time a record is created.

There are a few other possibilities of this type, but you'd need to say a bit more about how your solution is used and how the script that creates the new records is set up in order for anyone here to be able to clearly define the source of the problem for you.

[brownees]

Hi Ray

Thanks for your reply. I have detailed out the procedure below in an effort to nail down the problem and may have succeeded with the "Enter Browse Mode" additional script line (meaning that no invoices have dropped out this morning). Would appreciate your comments on the overall methodology.

Background: I manage projects for a local authority. Involves requesting a Purchase Order be raised, then receiving invoices from contractors during the project, approving the invoices and logging them against the Purchase Order, ensuring that the Purchase Order total isn't exceeded. A summary of approved invoices is provided every month and historical spend reports may be requested at any time.

PURCHASE ORDERS file

Relationship: PURCHASE ORDERS::PO ID = INVOICES::PO ID

New PURCHASE ORDERS records have an automatic non-modifiable calculation to create primary key in the form 00116L-036068-311180

Calc = Right( "000000" & Status(CurrentRecordID), 5) & Middle("0123456789ABCDEFGHIJKLMNOPQRSTUVXYZ", Round(Random*35,1), 1) & "-" & Right( "00000" & Status(CurrentTime), 5) & Middle( "0123456789ABCDEFGHIJKLMNOPQRSTUVXYZ", Round(Random*35, 1), 1) & "-" & Right( "000000" & Status(CurrentDate), 5) & Middle( "0123456789ABCDEFGHIJKLMNOPQRSTUVXYZ", Round(Random*35,1), 1)

User finds an existing Purchase Order to log a new invoice against

Portal in PURCHASE ORDERS layout shows all invoices already paid against that Purchase Order

User keys in new invoice details gInvoice(text), gAmount(number) & gNotes(text) global fields and clicks Submit button

PO ID is copied into gPO ID using Set Field, to copy across to INVOICES file

Script checks PO total not exceeded, checks an amount has been entered, checks no alpha in amount, checks invoice number has been entered.

If all OK, external script in INVOICES is called

On Exit Script, globals are cleared

INVOICES file

Relationship: Invoices::PO ID = Purchase Orders::PO ID

Script called from PURCHASE ORDERS

Enter Browse Mode (Just added today - suspected cause of problem??)

New record/request

ID (number) autoentered (used for deleting record later if needed)

Creation Date (date) autoentered

Month (text) autoentered from global in startup script

Set Field sets PO ID, Invoice No, Amount, Notes fields from globals

Status(Current Error) is checked, shows message box if <> 0

Exit Script returns to PURCHASE ORDERS

Thanks

Philip

[CobaltSky]

Hi Philip,

Having had a look over the process as you've outlined it, the logic appears sound.

The 'Enter Browse Mode' command is a good idea. Certainly the sequence would otherwise fail if the related file were not in browse mode when the script was called.

Without that step, the script would produce unintended outcomes in the event that the file was in Find or Preview mode at the point when the script was called. If the file was in find mode, the subsequent steps would create and populate an additional (invalid) find request and the data would be lost when the file was returned to browse mode. If the file was in preview mode when the script was run, the new information would be written over the details on an existing record (the one that was being previewed - which might not necessarily relate to the same PO).

I'd hazard a guess therefore, that you have another process (manual or automated) that periodically leaves the Invoices file in preview mode, and that that is the source of your problem. If that's the case then your additional script step should solve the immediate problem - and that seems to be borne out by recent experience.

You might wish to give some thought to the question as to why the file may have been being left in preview mode and whether the fact taht your script disturbs its state will present a problem or produce ideosyncratic behaviour in some other area of the operation. If so, you might wish to consider adding steps which will capture the state of the file at the start the script, and return it to the same state (record number and mode) at the conclusion.

[brownees]

Hi Ray

There are a lot of scripts doing stuff all over the place and I haven't clicked on yet to what process was leaving the INVOICE file in preview or find mode. The good news is that no data was lost yesterday so looks like I nailed the problem!

While in fix-it mode, can you point me in the right direction for fixing globals that lose data across the network. I want to use globals to hold admin type information that only changes periodically, but every time I shut down and reopen the globals are empty. I have resorted to populating the globals in my startup script but this is hardly satisfactory.

Thanks

Philip

[CobaltSky]

Hi Philip,

Glad to hear that things are looking promising in relation to the 'fix' for your earlier problem.

As regards globals, what you are experiencing is 'normal behaviour for global fields in a networked environment. Global field values are global to a file, but are local to the user - each user edits their values independently. Only changes to globals that are made on the host computer are saved with the file when it is closed.

One answer to your problem, therefore, would be to take the file offline, open it on the desktop of your computer (directly, not over the network) make the changes, then shut down (thus saving the changes to the globals) then place the file back online. However you would need to repeat this procedure any time a change to a global value is required.

An alternative - and more 'friendly' solution would be to create a separate 'resources' file with a single record in it and place the values that you currently have in global fields into normal data fields accessed via a relationship to that single record. Such an arrangement ensures that any machine on the network can make changes to the 'global' values - and those changes will instantly be seen by all other client computers (and also will be saved to disk on the server).

In fact, it is desirable in multi-user solutions of any complexity, to have both options available - ie a resources file for shared values that are required to be saved, plus global fields (whether located in the resources file or elsewhere) for user-specific and 'temporary' values.

[brownees]

Hi Ray

After discussing with a colleague I had come to the same conclusion, create a normal data field in my startup file (which only has a single record). I am completely blank on how the relationship should work though. Do I need a self-join on my startup file linking the global and the normal data field, plus a relationship in my Invoice file linking back to the global? All the combos I have tried don't work. No doubt there's an easy answer?

Your advice is very much appreciated.

Regards

Philip

[brownees]

"An alternative - and more 'friendly' solution would be to create a separate 'resources' file with a single record in it and place the values that you currently have in global fields into normal data fields accessed via a relationship to that single record. Such an arrangement ensures that any machine on the network can make changes to the 'global' values - and those changes will instantly be seen by all other client computers (and also will be saved to disk on the server)"

Can anyone help with how to set up the relationships?

Thanks

Philip

[CobaltSky]

Yes, not a problem Philip,

The simplest form would be to create a stored calculation field with result type specified as number and enter '1' as the formula.

Then in your other files you should add a global field and enter a 1 into it.

With those fields in place you can then create a relationship from each file to the resources file, matching the global field to the calc field in the resources file - and via that relationship you will always be able to access all the fields in the first (and only) record of the resources file - from anywhere in the solution.