Problem with global Total Record Count.

[GarrettC]

I would pull my hair out but little remains from some of my other FM battle scars...

I need to have field in each table that stores as a global value the current record count of that table. This is referenced in a number of ways throughout the project.

I have been using the function Get(TotalRecordCount) in a global calculation field. The problem is that although it updates when a record is added, it does NOT update when one is deleted. Why is this?

My other attempt was to include a script step that used set field and set a value with Get ( TotalRecordCount ) to the global field nthis works very well. Since I am using a set of custom scripts for record creation and deletion anyway this is good, it updates instantly and works for deletion and creation but it has a quirk...

At the bottom of each record I have a merge field that concatenates a statement, "Record " @@ " of " <> but the @@ does not show the correct number. In other words I delete a record and there are 44 records I never see "Record 44 of 44" Now why is this?

I would like to know what is wrong with either approach and if should do something else instead or modify some part of these methods? Thanks so much.

Edited October 17, 2005 by Guest

[bruceR]

Your field should just be a standard, unstored calculation, not a global.

[GarrettC]

Bruce, but is this not a an issue in the sense that I do not need to have this field have a value for every record?

Since this is a global value would not want to put it in a global field?

[bruceR]

No, it is not an issue. Your field should just be a standard, unstored calculation, not a global. What happened when you actually tried this?

[GarrettC]

I don't know if this is recommended but I went with the global field and the update via a the script script with a deletion and creation script. What I added to the deletion script was an unspecified sort after the delete record script step, this forces an update of the current record number.

If someone has the time I would still like to know more about the questions above and if this method I am using now is ok or a future gotcha.

[SlimJim]

Hi Garrett

The main problem with the method you are using is that someone, even you, may create or delete a record without using your script and then your global field may not be updated. There is also a possibility that records will be deleted via a relationship and again the global will not generally be updated. This is not a problem, as Bruce has pointed out a couple of times, if you use an unstored calculation field. The amount of storage space used by such a field is, as it says, minimal and it refreshes every time it is required to be viewed on a layout. It also reacts to the number of records no matter how the number is changed. If you intend to put record counts on your layouts this is definitely the way to do it.

I have not come across any problem using @@ to show the record number (except when I have not left enough space to show the number!)

[bruceR]

Get over it. Just use an unstored calculation. It is an absolutely standard technique used in zillions of files and you are just creating problems for yourself by avoiding it.

[Lee Smith]

Hi Bruce,

You can lead a horse to water, but it isn't your fault if they just wallow in the mud.

Lee