Problem finding Invoices by Number

[SlimJim]

A problem which has started in the last week or two. I can no longer find invoices by number. My Invoice numbers are serial numbers, (of number type) starting at 1. When I input a particular number, which I know exists I get back no records. If I put in the find "> 0" which should find all the invoices it only finds the ones in the last two weeks roughly.

My first thought was that somehow the numbers had been changed to text so I put in an extra field defined as a calculation GetAsNumber(InvoiceNumber) and then when it had calculated converted it back to a number field. Fnding using this field works as normal. The next step was to replace all the InvoiceNumbers by the numbers in this new field, this made no difference to the finding problem! I checked the validity of the InvoiceNumber field using a script looping through all the records, again no problem. They are all numbers but they still cannot be found.

I want to avoid replacing the InvoiceNumber field by the new field as InvoiceNumber is used in numerous places, in relationships, scripts, calculation fields and auto-enters.

Can anybody shed any light on this?

[Inky Phil]

Hi Jim,

Have you tried reindexing the field

Phil

[ThatOneGuy]

I think Phil's on the right track here ... further in that regard, have you applied the 8.0v2 and 8.0v3 updates?

[SlimJim]

Hi Jim,

Have you tried reindexing the field

Phil

No I haven't and that is what was required. Many thanks Phil

[SlimJim]

I think Phil's on the right track here ... further in that regard, have you applied the 8.0v2 and 8.0v3 updates?

Missed your post while replying to Inky Phil. I have actually just done the v3 update. I have been thinking back and the only change I can recall making was a modification of the auto-enter calculation on InvoiceNumber and it rather looks as if that was the culprit. Many thanks to both you and Phil for responding so quickly.

[Inky Phil]

Glad to be of assistance and thank you for giving some thought to what might have caused the problem.

I have never had to do this in FM myself but I have seen various references in here to a field 'losing' it's index. I fact it seems to be the single least reliable native feature of Filemaker and gives me more than a little cause for concern.

How does one support for instance a runtime that one has distributed if it loses one of its indexes intermittently or does one have to make a change to the structure as you yourself did in order to scupper the index.

Can anyone reassure me on this one?

Phil

[SlimJim]

Regarding loss of index - I don't know how easy it is but I think this is the first time this has happened to me and I have been using Filemker since Version 2.1. However, 'using' may be the key. I generally make databases for my own use and once they are up and running I only make changes when I want to change the structure. In this case the database was for business use, the company split into two parts and needed to have separate invoice numbering for each of the two parts. So I added a new field to identify which company was issuing the invoice and a new auto-enter based on an additional relationship to determine the invoice number after the company field was populated. I am now certain that this is what caused the problem with indexes. Invoices issued after this change could be found the ones before couldn't.