Record Locking issue

[Quartekoen]

I have a script that needs to be run somewhere between once per month and twice per week (however often the boss wants it!) The script basically locates a number of records, then loops through each one performing calculations, then stores the results in a number of fields in that record. Those fields are then reported upon. Because the result of the calculation is important, it's crucial that the results for all the records be correct, and that no record be skipped for any reason.

My issue is with record locking. Some of my users have a bad habit of entering a record and modifying it for whatever reason, then switching windows without committing the changes. This results in the record sitting on their computer, locked. I need a way to test for this lock BEFORE running my script so that I can cancel the script before errors pop up or records get skipped.

I've tried using the Get( RecordOpenState ) function, but it seems that that only returns the open state for any records that I have locked. I've even gone so far as to find a record that was locked by another user (trying to edit it brought up the "This record is being modified by another user" warning). I opened up my Data Viewer and had it Evaluate Get( RecordOpenState ), which resulted in 0 (not locked). I was able to recreate this result using my laptop and desktop versions of FileMaker at the same time, doing the same thing.

Is there any way I can get the state of a record if it's being modified by another user on the network?

Thanks.

[Vaughan]

I'd be looking at ways to avoid having to edit the records to create the report.

Why can't this field be changed to a calculation? Then it won't need to be updated. Will a summary report work?

Another option is to use a virtual list method to create the reports. This has some complexity.

[Quartekoen]

I'd be looking at ways to avoid having to edit the records to create the report.

Why can't this field be changed to a calculation? Then it won't need to be updated. Will a summary report work?

Another option is to use a virtual list method to create the reports. This has some complexity.

What I basically need to do is find the balance for some of our customers. The report needs to show the customer's balance broken down by how old each customer's various invoices are. For example, all invoices between 30 to 60 days old for a certain customer are summed together for a 30-60 day total. This is done for 4 different date ranges. All the customers are then shown in a list view to give us a general overview of where our customers stand.

The main reason I don't want this to be a calculated field is that there would end up being a lot of calculations that are all based on related records. Since the results are going to be shown in a list view on the screen, this would result in a lot of overhead, and the constant need to calculate as the user scrolls would mean a fair bit of slowdown.

Also, having live data isn't that important. It would be okay to run the report once in the morning, then have those results be used throughout the rest of the day.

I could create a new table to store the data in, which would exist just to hold that data for reporting, but I find it somewhat unreasonable to create an entire table for just 5 or 6 fields for a report that might only be run once per month.

Similarly, I could create the required relationships to get the totals, then store the totals in the fields, but it seems unnecessary to me to create multiple relationships (or perhaps a single calculated relationship) when one script could get the job done and store the results in 5 simple number fields.

[Vaughan]

I think this could be resolved with a summary report. If you don't want to generate it constantly then save it as a pdf.