Bug in Get ( TotalRecordCount ) ???

[Gary E.]

Ok, so I am fairly new to FileMaker, but so far have had great succes in creating a database to manage a create orders for my business.

However, I have run across one small problem with Get ( TotalRecordCount )

I have defined a field to assign each order a unique number using an auto-enrty of the calcualtion Get ( TotalRecordCount ). This ensures that each new record is assigned a unique number when it is created, based on the number of records in the table.

It works fine as long as I am browsing ALL records. But, if I am looking at a found set and create a new record, the "total record count" is one less than it should be!

For example:

If I have one record and create a new record, the second record is correctly assigned number "2"

If I make another record, it is assigned number "3"

If I do a find for record "2" and then create a new record (the 4th one), it is assigned number "3" again???

Has anyone else run across this "feature"?

Thanks

-Gary

P.S. Yes. I know about the serial number feature, but I do not use it because it will easily skip numbers if a record is created and then deleted.

[Ocean West]

Gary,

There are other well established methods of getting a unique number, I don't think that the Get (TotalRecordCount) is the best method to employ for ensuring uniqueness.

FileMaker itself has a unique number that it uses Get (RecordID)

[QuinTech]

Hi Gary, i don't know the Get(TotalRecordCount) function because i haven't yet begun working in FM7. But i know that in FM6 and earlier, Status(CurrentRecordCount) will return the number you want (just make sure calc results are NOT stored).

Jerry

[Gary E.]

I agree that using Get(TotalRecordCount) is not the best way to ensure a unique number. However I am more interested in sequence than uniqueness (although one implies the other I guess). A skipped number means a (possibly) missed order and it is reassuring to run a report and see all the orders go in sequence and know that all the records are there. Get(RecordID) is like the serial numbering feature in that it easily skip numbers.

It would be nice if FM7 could correctly report how many records it has. It is especially annoying when it gives me a number, and I can see right in the status bar that it is not correct.

All I really need it to do

[Gary E.]

Jerry,

FM7 is the first version if FileMaker I have ever used, and I think it is fantastic.

Looking over the calculation functions in FM7, I don't see any Staus() functions at all. I am guessing they have been replaced by the various Get() functions? According to the FM7 help, Get(TotalRecordCount) is supposed to do exactly what I need. However, it does not work correclty when working with found sets. I can see right in the status bar what the true record count is, but for some reason FM7 calculates one less when working with a found set.

-Gary

[-Queue-]

Though I don't agree with this idea either, you can use Get( TotalRecordCount ) + ( Get( FoundCount ) <> Get( TotalRecordCount ) ) as your auto-entry calc.

[Gary E.]

Cool, that works. I take it that "<>" means "not equals", and if "not equals" is true then it adds one?

I did not know you could add the result of a logical test to a numerical result. That is a clean little shortcut.

Now... don't suppose you know a simple way to find the highest value in the table and add one? Then I can get rid of the whole kludge (which is precarioulsy balancing on the fact that I never delete old records) and generate the numbers properly.

Thanks,

-Gary

[-Queue-]

I do actually. Using FM7's cartesian join ability makes this fairly simple. Check the attachment. One field, one relationship sorted in descending order by the field, and an auto-enter calculation of the first related value (i.e. the highest number) plus one.

And, yes, as long as you put parens around the logic test, it will treat it as a boolean result, thus adding either 0 or 1 to the count, as desired.

GaryE.zip

[Gary E.]

Perfect!!!

That works beautifully. I eneded up using Max(Order Number)+1 for the calculation because the order number field is a text field (sucky, I know, but it has some non-numeric data carried over from the old appleworks database), so sorting descending causes 200 to come above 1000.

I always wondered why you might want to relate a table to itself... now I know. I presume by looking at the file size that FM does not actually make a copy of the table, but just a logical "loop" internally to solve the realtionship. I was a bit leery at first about adding what appear at first to be extra tables, but now I see that it is really an "alias" so to speak.

Very cool.... thanks for the great tip.

-Gary

[-Queue-]

If the text data is only leading or trailing (or both), then you could create a calculation number field equal to the id and sort the relationship by this field. Then you could auto-enter the text with the field. If, however, there is text interspersed with the number, then this probably wouldn't work too well. I think I should warn you, though, that Max( ) is going to be much slower than the relationship once you have a decent number of records.

[QuinTech]

Did you test whether calc results are stored? That will make a big difference.

I completely agree that 7 is a great product. If only you knew what an improvement it is over previous versons...

J

[-Queue-]

It doesn't matter with an auto-enter calc. There is no option to unstore the result since it's only calculated once. The calc seems to evaluate as the record is being created (which is logical) and therefore doesn't take into account the current record (until it's committed) unless all records are shown, which is indeed curious.

[Gary E.]

Thanks for the heads up about the Max() function

I have about 1400 records now and I did not notice any speed hit so far. I am adding about 500 records/mo, so it will probably slow down soon enough. I think I will go and find the few old records with letters and just change the order number to get rid of the leters. I used leading letters, but I can change M100 to 100.1 for example, thereby allowing the sort to work properly.

That will clean things up a bit for the future.

Thanks,

-Gary

[Gary E.]

QuinTech said:

I completely agree that 7 is a great product. If only you knew what an improvement it is over previous versons...

J

Yes, it is a great product. An hour of playing with the free trial was enough to convince me to move some of my data over and try it out for real. Once I started it really blew my mind all the amazing things I could do with it.

Sill it was a big chunk of cash for my (very) small business. Then I found the SendEmail() feature... WooHooo! SOLD!

I bought it the next day and have never looked back.

-Gary

[Michael Myett]

I'm not sure about a "bug" but this certainly doesn't guarantee a unique value.

What if you have 100 records, with ID's 1-100 and delete record no. 38, you now have 99 records with ID's 1-37, and 39-100. Your next record is going to be assigned a value of 100, now you have two records with an ID of 100.

When dealing with RecordID's, uniqueness is whats important.

[QuinTech]

Ahh... another argument for reading the original post more closely.

J