1 2 Previous Next 25 Replies Latest reply on Apr 9, 2014 12:24 AM by tunesmith

    Value List can't find existing record

    tunesmith

      Title

      Value List can't find existing record

      Post

           Hi, I am on a layout that reflects Table "Recordings", which is many-to-many related to "Rehearsals 2" through "RecordingRehearsal".

           I have a popup menu that displays data from RecordingRehearsal::rehearsal_id .

           This popup menu gets its values from a Value List:

             
      1.           First field: RecordingRehearsal::rehearsal_id
      2.      
      3.           Second field: Rehearsals2::Session Date

           In this particular example, I am looking at recording_id 347, which maps to one rehearsal, rehearsal_id 78.  I have verified that that Rehearsal record 78 exists, and that there is a row in RecordingRehearsal mapping 347 to 78.  (There are also twelve other recordings that map to rehearsal_id 78.)

           The problem can be described in a couple of different ways.

           First, my table view for Recording looks like this (leaving out other columns and rows):

                                                                                                                                                                                                                                                                                                                                                     
                          recording_id                     ::rehearsal_id
                          332                     10/5/2007
                          333                     10/6/2007
                          334                     10/6/2007
                          347                     78
                          348                     10/15/2007

           The other twelve recording_ids for that rehearsal also show "78" for ::rehearsal_id instead of 10/11/2007.  I don't know why these rows have different behavior.

           The other way it can be described is that the popup menu can't find 10/11/2007 in the value list.  Before I click on the menu, it says "78".  (Recordings from other rehearsals show the date.)  When I click on the menu, it goes to the top of the menu of dates, with nothing pre-selected.  10/11/2007 does not show up in the menu.

           If I pick 10/15/2007, then the RehearsalRecording row that used to say 348 / 78 is updated to 348 / 79, as expected, and the above table view will show 10/15/2007.  The popup menu will show "10/15/2007" instead of 79, and clicking it will show that the "10/15/2007" row is pre-selected.  Then, I can edit RehearsalRecording directly to change 79 back to 78, and the above table view will show 78 again, while the popup menu reverts to the prior behavior.

           I've tried deleting and recreating the relationships in my relationship graph.  No change.

           Why/how did this happen and how can I fix it so that the popup menu will show the date, so the date will be pre-selected, and so that the value list will find this particular rehearsal?

           Thanks!!

            

        • 1. Re: Value List can't find existing record
          philmodjunk
               

                    Table "Recordings", which is many-to-many related to "Rehearsals 2" through "RecordingRehearsal".

               

                    First, my table view for Recording looks like this

               But your description of the relationships indicates that for any one record in Recordings, you may have multiple related records in RecordingRehearsals--each of which are presumably linked to a record in Rehearsals 2. But such a table view will only show the First of those possibly many related records in the rehearsal_id field. To see all of the related records, you'd need a portal to either RecordingRehearsal or Rehearsals2 and you'd need to use either a form or list view instead of a table view.

               You appear to be using dates for Rehearsal IDs. That seems very unlikely to be an effective value to uniquely identify rehearsals unless you never have more than one rehearsal in a given day and even then, I wouldn't use a date as a rehearsal ID.

               It's impossible to tell from here why some rehearsal ID's show a number and others a date. I can only share the obvious conclusion that your setup has or  had more than one way to enter a value into Rehearsal ID--one that entered a date and another that entered a number.

               

                    If I pick 10/15/2007,

               I can't follow your description of what you are doing here.

               Are you in Browse or Find mode when you do this?

               Is this the Rehearsal ID field from RecordingRehearsals or Rehearsals2?

               if you are in Browse mode, you are editing a field in either the recordingRehearsal table or Rehearsals table rather than finding data. This might be a case of entering data for a new record in RecordingRehearsals, it might mean that you are reconnecting the RecordingRehearsals record to a different Rehearsals record or you might be changing the ID of a record in Rehearsals (not a good thing to have happen.)

               If you are in find mode and performing a find, then this action finds a record in Recordings that has at least one related record with that selected Rehehearsal ID. But since you only see one out of possibly many values from a related table in column 2, the ID shown may not match the ID specified as that could be from a second, third or however many related record and thus can't be seen on your layout.

          • 2. Re: Value List can't find existing record
            tunesmith

                 Hello, let me try to clarify both questions.

                 Recordings has a "recording_id" (347 in this case), and then a bunch of other columns.

                 RehearsalRecording has two columns: recording_id and rehearsal_id .

                 In almost all cases, a recording has only one rehearsal.  That is also true for recording_id 347, and all recordings that are associated with rehearsal_id 78; they each only have the one rehearsal.  It was just while importing the 1000+ rows that I saw a couple of other recordings were associated with multiple rehearsals, so I made it many-to-many.

                 Rehearsals has a "rehearsal_id" (78 in this case), and then a few other columns including "Session Date".

                 So, no, I am not using dates as Rehearsal IDs.  Rehearsal IDs are *always* numbers.

                 Ok, second section. 

                 I am in Form View.  The popup menu is there, and it says "78".  (I guess that means I am in Browse mode; I'm not doing a Find - sorry, I am still learning terminology.)  If I click on the popup menu, the value list pops up.  Every rehearsal except 10/11/2007 (id 78) is there.  If I pick 10/15/2007 (the one that would be right after 10/11/2007 in the list if 10/11/2007 were there), then it selects that entry successfully.  (10/15/2007 is not the rehearsal id from any table; it is what the value list displays.)  If I then go to the RehearsalRecording table view and find the row, I see that the row that used to be this:

                 rehearsal_id: 78; recording_id: 347

                 is now this:

                 rehearsal_id: 79; recording_id: 347

                 which is to be expected.  If I go back to the Recording layout, then I again see that the popup menu displays 10/15/2007 (not 79).  If I click on it to bring up the value list, the list has 10/15/2007 pre-selected.  10/11/2007 is not available as an option.  I click outside the value list to cancel.

                 I go back to RehearsalRecording table view, find the row for recording_id 347, and change rehearsal_id from 79 to 78.

                 I go back to the Recording layout, and I see that the popup menu displays 78, not 10/11/2007.  If I click on it to bring up the value list, nothing is pre-selected.  10/11/2007 does not show up in the value list.

                 Again, the actual tables do not have odd data in them.  My Rehearsals table has 202 rows, each with a unique numeric rehearsal_id, going from 1 to 202.  My RecordingRehearsal table has only numeric values.  And my Recordings table has 1033 rows, each with a unique numeric recording_id, going from 1 to 1033.

            • 3. Re: Value List can't find existing record
              philmodjunk
                   

                        So, no, I am not using dates as Rehearsal IDs.  Rehearsal IDs are *always* numbers.

                   yet the example data you show has values such as 10/5/2007, 10/6/2007 and 10/15/2007 These are most definitely not numbers and appear to be dates.

                   

                        I am in Form View....

                   But what FIELD have you formatted with this value list? And from which table? This is absolutely critical info that I cannot find nor deduce from your posts thus far.

                   From the further info posted, it would appear that you have a pop up menu and that the value list has an ID number for field 1 and a date for field 2. And the ID number is set up in the value list to be hidden. Is this correct?

                   If so, you will see the ID number instead of the date if there is no record in the table from which these values are taken with that ID number. Depending on how you set up your layout, you may be changing values in the rehearsals table that then result in records with a given Rehearsal ID no longer have the expected ID number--but that's a guess hanging on a long chain of guesses that may not be correct.

              • 4. Re: Value List can't find existing record
                tunesmith

                     Hi Phil,

                     If I hit "Edit Layout" and then select my popup menu, the info tab says it is displaying data from RecordingRehearsal::rehearsal_id .  Under Field Entry, both Browse Mode and Find Mode are checked.  The "Values From:" are from a value list.  Its "Use values from field" dialogue has the following.  First field is RecordingRehearsal::rehearsal_id .  Second field is Rehearsals 2::Session Date.  I have "Include all values" selected, and I have "Show values only from second field" checked.  So yes, you are right.

                     However, there *is* a row in RecordingRehearsal for Rehearsal ID 78, and there *is* a record in Rehearsals for Rehearsal ID 78.  That's the mystery. It is as if Filemaker's internal representation of the table does not match with what is actually in the tables.  

                     I tried deleting my Rehearsals 2 Table Occurrence and recreating it (to Rehearsals 3) and rewiring the components, but still, no change.  I also tried deleting and recreating the relationships between Recordings and RecordingRehearsal, and between RecordingRehearsal and Recordings 3.  What else can I do?  I'm sure I could have overlooked something simple since I've only been at this a few days, but it really seems as if the record is there, and that the value list should find it.

                      

                • 5. Re: Value List can't find existing record
                  tunesmith

                       BTW, I realized I had to widen the column for the table view of my Recordings layout.  That column that is sometimes date format and sometimes id format is pulling from RecordingRehearsal::rehearsal_id .  I don't know why it sometimes (most of the time) formats as date, when my RecordingRehearsal table only has ids in its two columns.

                  • 6. Re: Value List can't find existing record
                    philmodjunk

                         List view and table view are not the same thing. In two posts you say "table view" and in one post you say "I am in list view". That is confusing.

                         

                              First field is RecordingRehearsal::rehearsal_id .  Second field is Rehearsals 2::Session Date.

                         .change the first field to Rehearsals 2:: rehearsal_id and I predict that you will see dates for when RecordingREhearsal_ID = 78

                    • 7. Re: Value List can't find existing record
                      tunesmith

                           Sorry, prediction incorrect.  sad  It still says "78" (in Form View) for Recording 347, and "10/15/2007" (rehearsal 79) for Recording 348.  Meanwhile, I still have rows mapping 347 to 78 and 348 to 79 in RehearsalRecording (which I verified in Table View), and I still have rows 78 and 79 in Rehearsals.

                           I tried deleting my "RehearsalRecording" Table Occurrence, and recreating it, and rewiring everywhere.  Same behavior.

                      • 8. Re: Value List can't find existing record
                        philmodjunk

                             Next step. keep the value list so that both values come from Rehearsals.

                             Now add a portal to RecordingRehearsals to your layout. Make it several portal rows in height and include a scroll bar. Put the pop up menu formatted RecordingRehearsals::Rehearsal_ID field inside the portal row. Include the date field from Rehearsals3 in the portal row as well.

                             View your layout in form or layout view, NOT table view and tell me what you get in the portal when you inspect your records .

                             

                                  and I still have rows 78 and 79 in Rehearsals.

                             But just to rule out some picky details, do the records with IDs of 78 and 79 in rehearsals have dates in the date field?

                             Is there one and only one record in rehearsals with each of those ID values?

                             Do you have more than one record in Rehearsals with the same date--the same date as the ID 78 or 79 records?

                             Are both Rehearsal ID fields defined in manage database to be of type number?

                        • 9. Re: Value List can't find existing record
                          tunesmith

                               (I renamed that TO back to Rehearsals 2 instead of Rehearsals 3 and rewired)

                               Ok, the value list has its first field set as Rehearsals 2::rehearsal_id, and the second field set as Rehearsals 2::Session Date.  The popup menu remains formatted as RecordingRehearsals::rehearsal_id .

                               I created the portal to RecordingRehearsals and put the popup menu in, and I added an edit box for Rehearsals 2::Session Date.

                               Now, viewing in Form view.

                               For recording 347, I see one row, with "78" as the value for the popup menu, and "Oct 11, 2007" as the value in the edit box.

                               For recording 348, I see one row, with "10/15/2007" as the value for the popup menu, and "Oct 15, 2007" as the value in the edit box.

                               Yes, the records for rehearsals 78 and 79 have dates in the date field.

                               Yes, rehearsal ids 78 and 79 have one and only one record.  They are unique.

                               Dates 10/11/2007 and 10/15/2007 each have one and only one row in Rehearsals.  (There might be other dates that have more than one rehearsal, but they will have unique rehearsal ids.)

                               Rehearsals::rehearsal_id, Recordings::recording_id, and the two id columns in RecordingRehearsal are all of type number.

                          • 10. Re: Value List can't find existing record
                            tunesmith

                                 By the way, I realized that the only reason that the Table View is sometimes displaying id#'s, and sometimes displaying dates, is because that column in the table view is actually displaying the value list itself.  So in the cases where the date isn't in the value list, it displays the ID instead.  So, this problem just reduces down to the question of why that value list is not finding every rehearsal that is reflected in RecordingRehearsal and Rehearsals.

                            • 11. Re: Value List can't find existing record
                              philmodjunk

                                   Exactly. My guess was that you had either additional records that duplicated the IDs, text field IDs where an invisible character such as a space kept the values from matching as expected or there was no actual date in the date field for that ID. And the possibility that you might have more than one record in the join table for a given recording record could also be a source of some confusion.

                                   But we now seem to have ruled out all of those possibilities. sad

                                   Before we try anything else, let's do a bit of a "cheap longshot". Odds that this will fix things are low, but it's easy to do and costs nothing to try:

                                   Launch FileMaker without opening this file.

                                   Select Recover from the File menu.

                                   Recover this file.

                                   Let me know if Recover reports finding/fixing any issues with the file.

                                   Whether it finds an issue or not, test the recovered copy and see if you still get numbers where you expect to see dates. The recovered copy will have all new field indexes so if the recovered copy works, we can try just rebuilding the indexes to see if that fixes this issue.

                                   If not, I'll want to look at the file to see if I can spot anything that I can't do just via posted text in this forum.

                              • 12. Re: Value List can't find existing record
                                tunesmith

                                     Hi Phil, thanks - I tried the recovery, and it found no errors.  The recovered file exhibits the same behavior.

                                     A couple of things - the filemaker file is 4.44 GB (lots of recordings in it).  Also, I'm on a trial license.  Not sure how to share it, although I could try making a copy of the file and emptying out all the containers, which might make it easier to share since it'll be smaller.  Although if you're able to fix it, I'd have to know how to duplicate the behavior in my "real" file.

                                      

                                • 13. Re: Value List can't find existing record
                                  philmodjunk

                                       You could save a copy and delete all records in the save a few that illustrate the issue you have here--especially any files in container fields. You can then upload the copy to a FileSharing site such as DropBox and post download link here or in a private message to me. If there's a password protecting the file, be sure to send me a full access password to open the file.

                                  • 14. Re: Value List can't find existing record
                                    tunesmith

                                         Ok, thanks Phil.  I sent you a message with the link to the file, which is now only 1.8 MB!

                                    1 2 Previous Next