6 Replies Latest reply on May 19, 2011 6:03 AM by Mitch

    Value List only working for old records.?

    Mitch

      Title

      Value List only working for old records.?

      Post

      Hi

      Hope someone can help.    I have database with two main tables.  Table 1 that I use to record a library or reference of Awards under Category, Award Name and Clasps. The first field, category is a drop down list.  The next field, is the Award Name, followed by a portal that allows for multiple Clasps under the particular Award. 

      On Table 2 I record my main data for recipients of these awards.  I add each award to the recipient in a portal. The first field in the portal is Category where I select the value a drop down list, the same drop down list Table 1 utilises.  The next field, Award Name is also a drop down list linked to a Value List that only lists those Award Names under the appropriate Category and the previous field, the Award Names taken from Table 1.

      The next field in the portal is a Repetition Field for Clasps, again a drop down list linked to a value list that only lists those Clasps under the appropriate Award Name previously selected, with the data also taken Table 1.

      Somewhere along during the ongoing development the database the link between the Repetition Field for Clasps and the Award Names stopped working.  I am still able to type in a Category into the Table 2 portal and the correct corresponding Awards Names are presented in a drop down list in the next field.  But when I go to the Repetition Field to select a Clasp or multiple Clasps the Drop Down list is blank now. If never used to be and worked fine. 

      But for some reason, if I import some old data into the database from when it was all working, the Repetition Field works and all the appropriate Clasps appear in the drop down list.  However, if I go into Table 1 and add new record of an Award Name with one or multiple Clasps, then go back to Table 2, Select the Category, the new Award Name appears, thats working.  But when I go to the Repetition Field to select the Clasps, the drop down list in blank.

      So, I go a try and add another Award Name and Clasps, this time I select a entry from the imported records, and they both work fine.....

      The Value List and associated Relationships obvious are working....But not for new records added into Table 1.....??

      Any ideas?

        • 1. Re: Value List only working for old records.?
          philmodjunk

          It's really hard to follow your description of 2, 3, 4? tables of data (You describe Table 1 and 2, but also two portals...) and how they might be related and what options you did or did not select for your value list definitions. You speak of a "repetition field" and also portals but don't describe either in detail. Is your "repetition field" a repeating field? If so, why a repeating field in place of a portal?

          In any case, since importing data from a backup seems to change the behavior of your value list, it's possible that you may have a damaged index here.

          To rebuild the index of a single field, open Manage | Database | Fields and double click the field

          1. Use either the storage tab or the storage options button to turn off indexing.
          2. Exit Manage | Database, then return and turn indexing back on.

           

          You can also rebuild all your file's indexes by importing all the data into an empty copy (clone) of your file.

          If you have FileMaker 11, you can use Advanced Recovery options to rebuild all your file's indexes:

          1. With the file closed, select Recover from the File Menu.
          2. Select "Use advanced Options"
          3. Select only: "Copy File Blocks as-is" and "Rebuild Field Indexes Now".

          • 2. Re: Value List only working for old records.?
            Mitch

            Sorry Phil,

            I will try and clear this up.  Two Tables: Table 1 - Reference for Awards and Table 2 - Recipients of the Awards.  For the Table 1: Reference of Awards, there is one portal which records the Clasps, one award can have a number of clasps avaiable.   For the Table 2 - Recipients of the Awards, there is also a portal for the Awards they have received.  One recipient can have a number of awards.  Within this Portal there is a repeating field to record the Recipients Clasps for each o their awards, because their Awards can have a number of Clasps.

            When entering the data in the Portal of Table 2: Recipients of the Awards, the Drop Down List of Awards Name is generate based on the Category Selected and the Drop Down List of Clasps is generated based on the Award.  This data is stored in Table 1. 

            • 3. Re: Value List only working for old records.?
              philmodjunk

              Sorry, but that description repeats the issues I noted earlier.

              "For the Table 1: Reference of Awards, there is one portal which records the Clasps "--which would require a third table for the portal.

              "For the Table 2 - Recipients of the Awards, there is also a portal for the Awards they have received"--which would require a fourth table for this portal

              and you'll need relationships defined for each. Wink

              "the Drop Down List of Clasps is generated based on the Award.  This data is stored in Table 1."

              Which data? This list of awards would be in that table, but your reference to a "portal" indicates that the clasps available would be stored in another table that you don't describe here.

              It sounds like you have a at least two fields in Table 1: Award category and Award Name and you have a conditional value list of award names that is controlled by a relationship to Table 1 that is based on an award category field defined in table 2. Does that sound correct?

              And you have a repeating field in Table 2 where each repetition is formatted with a conditional value list of all the clasps available for the selecte award? That requires yet another relationship based on the award field.

              So I need to see the relationships that you've set up for each of these conditional value lists. One way to document relationships in a forum like this is to use this format:

              Table 2::AwardID = ClaspsTable::AwardID

              Which is the relationship I'd use to list the available clasps for a given award.

               

              • 4. Re: Value List only working for old records.?
                Mitch

                Phil,

                Yes, you are correct.  Both the Portals for both Clasps and Awards have separate Tables.  You are also correct with regard the the fields and relationships.

                I have been doing some importing of older back up data and eventually got a file that did not cause this problem.

                Now, I do remember some time ago playing around with a function that duplicated an entry in Table 1: Reference of Awards along with each of the Awards Clasps (Portal Records), but then required some change in the Awards Name.   Mainly because there are a number of Awards that are the same, but of various type, such as ### Award Type I and ### Award Type II, all with the same clasps.  But I felt this was may cause some issues with record IDs etc....  So I deleted the function.  

                If a back up file was made of data with one of these duplicate records in it, could importing this data caused my problems?

                • 5. Re: Value List only working for old records.?
                  philmodjunk

                  That's close to what I suspected was the problem--that some change in what value is entered into the key fields used in these conditional value lists is putting a different value in more recently created records than was originally entered into the older records. That's why I have been asking for more detail. If I knew exactly which fields were used to define the relationships that control these conditional value lists, I'd tell you to pull up a layout where you can see this field so that you can check the values present in this field for your old and new records to see if there's a difference here that's breaking the relationship.

                  • 6. Re: Value List only working for old records.?
                    Mitch

                    Phil.

                    I will check what values are returned in both new and old records.  The problem has not re-appeared since I stopped importing that one (older) back up.