[SOLVED] Strange bug with value list

[NewBoard]

I have stumbled upon a strange bug in one of my Databases concerning a value list.

We have a table containing part numbers, which is related to a table containing "revisions" for the part numbers, which is related to a table of inspections for the "revisions".

On a layout of the Inspections table, I have a value list. This value list selects the UUID of the revision, but displays the Part Number for the user.

This has worked perfectly for several months. However, this morning one of my users discovered that he was unable to located "0000194" from this list. At first I thought maybe the UUID's somewhere along the line had become disconnected from their respective foreign keys on related tables. This was not the case. I then tried deleting and remaking the value list. This did not solve my problem. Then I figured if I deleted and rebuilt everything related to 0000194 that would fix it. This was not the case either. As a last ditch effort I restarted our FileMaker server, to no such luck.
So then I began playing around with various aspects of 0000194. First I changed the company name, but it still didn't show up on the value list. Then I changed the part number to "TESTPART", and it showed up perfectly. So I tried "0194" and that showed up. Same for "00194" and "000194", but as soon as I add a fourth zero making it 0000194, it disappears from my value list. I'm 95% sure that this part number used to show up on the value list just fine, as we've already done inspections using this part number. So I'm thinking this bug has something to do with the leading zeros. I'm unsure of how to debug this or if there's any workarounds for this issue.

Let me know if I need to supply any more information.

Thank you in advance for any help you all can offer!

[SOLUTION] At some point one of our scripts was run by a user without full permissions. The script added part number "0000194" to another customer, which I never noticed. Since Value Lists are indexed, it was not showing "0000194" because it was not a unique value. Deleting the second occurrence of "0000194" cleared my problem up.

Edited October 5, 2017 by NewBoard
Problem solved

[LaRetta]

Hi, I highly suspect that you don't have a 0000194 Revision record.  Attached is a sample.  Does this match your file's structure and value list specifications?

Notice how, in Inspections, the value list indeed shows 0000194.  Now go to the Revisions layout, record number 4 which is the 0000194 revision record.  Change it from 0000194 to a different part number.  Now return to Inspections and pop the value list open ... 000194 is gone.

Since your Revisions value list uses the PartNumber as the description, if it indeed matches my file, then no RevisionID for 000194 is available to fetch.  Might this be what you are experiencing? :-)

TestVLbug.fmp12

[NewBoard]

11 minutes ago, LaRetta said:

Hi, I highly suspect that you don't have a 0000194 Revision record.  Attached is a sample.  Does this match your file's structure and value list specifications?

Notice how, in Inspections, the value list indeed shows 0000194.  Now go to the Revisions layout, record number 4 which is the 0000194 revision record.  Change it from 0000194 to a different part number.  Now return to Inspections and pop the value list open ... 000194 is gone.

Since your Revisions value list uses the PartNumber as the description, if it indeed matches my file, then no RevisionID for 000194 is available to fetch.  Might this be what you are experiencing? :-)

TestVLbug.fmp12

Thank you very much for taking the time to build a database and reply!
Your relationships are setup like mine, as well as the value list.
However, your diagnosis is off. I wish it were something so simple, as I would've definitely noticed that!

Here you can see that I do indeed have 0000194 as a record in the Revisions table

But when I go to use my value list, it is non-existent:

However, when I change the part number to "000194" it shows up just fine.

[LaRetta]

Did you recently change your PartNumber in the parts table to number instead of text?  If so, the index will identify the first type 00194 you entered but will no longer see the 0000194.  So check that the fields are all text.

I have a few additional 'hail Mary' suggestions then ... check your raw data.  Sometimes the problem lies there.

1. Go to your Parts table and show the fields.  Delete the PartNumber value and re-add it, then check your popup.
2. Go to your Revisions table and search for that PartNumber.  Is there more than one Revision record?  Do the IDs all match?
3. Rebuild your indexes.

You may have a bug in Windows but I would try these items first.

[NewBoard]

6 minutes ago, LaRetta said:

Did you recently change your PartNumber in the parts table to number instead of text?  If so, the index will identify the first type 00194 you entered but will no longer see the 0000194.  So check that the fields are all text.

I have a few additional 'hail Mary' suggestions then ... check your raw data.  Sometimes the problem lies there.

1. Go to your Parts table and show the fields.  Delete the PartNumber value and re-add it, then check your popup.
2. Go to your Revisions table and search for that PartNumber.  Is there more than one Revision record?  Do the IDs all match?
3. Rebuild your indexes.

You may have a bug in Windows but I would try these items first.

I'm not sure what you mean by rebuild your indexes. How would I go about doing this?

But yes, Part Number field is text. I've already tried deleting everything related to 0000194, including the customer, and rebuilding it. Which did nothing. I only show one occurrence of that part number. And I've checked all UUID's and Foreign Keys which match.
Thank you again LaRetta for your attention on this matter. I fear if rebuilding my indexes doesn't work I may need to contact customer support! This is such a strange issue.

[LaRetta]

A quick way to test rebuilding your indexes ... run Recover like this:

1. Close your file
2. Select File > Recover and select your file.
3. When it asks for the file name for the new file, go into the 'Advanced' options at the bottom.
4. Specify 'copy file blocks as is'
5. Uncheck everything else except 'Rebuild Indexes now.'

Then check the new file and see if it is fixed.  If not then you probably have a bug in Windows.  If so, you can report it as a bug here: Report an Issue.

And if you deleted everything about the part number and reentered them then I doubt the rebuild index will work.  I wish you well with the issue.