Can't really suggest much without knowing more about the field in question. Is it a summary field of some type. A field from a related table or ....?
Please describe it in detail. If it is a field from a related table, include a description of the relationship.
Thanks for responding PhilModJunk!
So, this field is a text field with no optional functionality set in an edit box. The data is contained in a single table on a seperate database, called Catalog, that is related to another database, called Data Entry, which refers to it. There are no other relationships, just the one. When I open only the Catalog database, the problem occurs with or without the relationship.
This database was designed back in the hay day. I am somewhat a beginner and assigned to maintain the database. Any help will be greatly appreciated - Thanks again...
So the values are in the table of a file called Data Entry and the layout is in the Catalog file?
In Manage | Database | Relationships, there should be a pair of table occurrence boxes for both Catalog and Data Entry that are linked in a relationship. I need to know exactly how this relationship is set up and what fields (Their types and their storage options) are used to define this relationship.
You can use this format to document the relationship:
Catalog::FieldName = DataEntry::FieldName.
THen list the information one each of these fields as to what data type, whether they are stored and if a calculation field, post the expression that defines the calculation.
To find the correct boxes in the relationship graph, you should enter layout mode and select Layout Setup... to see what name is entered in the Show Records From box. This is the name of the table occurrence box for your layout that I am guessing will be named "catalog". Then, return to the layout and double click the field in question. In the specify field dialog that pops up, a second table occurrence name will be listed in a drop down at the top of this dialog. That will be the second table occurrence name.
I need to know how these are linked in detail in order to see if there are any issues involved.
Here goes -
In 2010 Data Entry Database:
Data Entry::Class Code = Class Catalog::Class Code (with allow creation of records using this relationship)
In 2010 Class Catalog Database:
(with allow creation of records in this relationship) Class Catalog 2010::Class Code = 2010 Class Code::Class Code
2010 Data Entry::Class Code field is index and a lookup in Class Catalog 2010::Class Code
Class Catalog 2010::Class Code is index and a lookup in 2010 Class Code::Class Code (a lookup on to itself)
I'm confused, but then again - I'm very new to this concept. I'm beginng to think to just redo the entire system since it is old, and has been patched here and there over the years that it lost the logic and structure.
I would be interested to know though, why a relationship to itself is necessary?
Thanks for all of your help -
A relationship to itself is called a "self join" this may be used to look up values from a different record in the same table with the same class code. This works, but produces redundant, denormalized data (the same values are repeated over and over in different records) and this is a less than optimal design. It's typical of older databases when the developer didn't want to create yet another file in order to define yet another related table. With current versions of FileMaker we can define multiple tables in the same file and thus, even this "reason" is no longer valid.
"self joins" can also be used to identify how many records have the same value in the key field and this can be a useful way to identify records with duplicate values.
"2010 Data Entry Database" suggests that you also create and link in a new file/table each year. This is also a far from optimum design approach for most database solutions. Instead, you can add a field that records the date or at least the year and then you can put multiple years of data in the same table and just use finds and sorts to work with the records in the table, limiting the visible records to just those from a given year or even a range of years.
That said, I'm not seeing any obivous design issues that might create the issues you are seeing. Given the age of these files, you may have a field index that is corrupted. Save a clone of both files. Open both clones and import all records from the original files into your clones. Modify file names so that you can open and use these new copies in place of the originals. (Importing into a cloned copy rebuilds all indexes in the file.)