AnsweredAssumed Answered

Issue Importing Excel Data Into Pop-Up Menu Fields

Question asked by sgtpeppas on May 27, 2014
Latest reply on Jun 3, 2014 by jlamprecht

Greetings! Thanks ahead of time for any guidance


I have a library of musical cues. Each cue can be assisgned up to 4 genre/subgenres. I have a Cue Entry Layout for the user to enter this and other data for each cue. It is based on the Cue Entry Table. I have 2 separate tables for Genre and Subgenre. I am pulling the Genre and Subgenre data through a portal into the Cue Entry Layout via a join table called cues_CATALOG. That data is populating 2 pop-up menus on Cue Entry. Here is the relationship graph:


Screen Shot 2014-05-27 at 11.53.17 AM (2).png


I am importing data from an excel. First I import Title and Cue ID (using auto-enter during import for Cue ID) into the Cue Entry Table so that I have the bare-bones record there ready to be matched with the data to come. Second would be the CATALOG data. I am importing this into the join table cues_CATALOG. Here is the excel sheet as I am importing it:


Screen Shot 2014-05-27 at 11.55.28 AM (2).png


Import mapping is as follows:


Cue ID matched to _CueIDfk

Genre imported to _GenreIDfk

Subgenre imported to _SubgenreIDfk


On the surface it appears as though the import is successful:


Screen Shot 2014-05-27 at 11.46.00 AM.png


This is from the layout Cue Entry. These are portal rows from cues_CATALOG with the fields _GenreIDfk and _SubgenreIDfk. These two fields are Pop-up menus being populated by a value list with values from the related primary key fields __GenreIDpk and __SubgenreIDpk from their respective tables cues_catalog_GENRES and cues_catalog_SUBGENRES but displaying the secondary fields Genres and Subgenres.


When I click on the pop-up I see that there is no check mark next to "JAZZY":


Screen Shot 2014-05-27 at 11.46.14 AM.png


Curious. Additionally the Subgenre pop-up which is dependent upon the Genre pop-up seems to display the imported value but when you click on it you get this:


Screen Shot 2014-05-27 at 11.46.29 AM.png


I suppose that's obvious behavior if there isn't actually a value being defined in the Genre pop-up. Question is why isn't there? I certainly imported data into that field. As you can see (or not?) from the table view of cues_CATALOG it certainly appears as though data is there. Is this about needing to use primary keys instead of values during import? Or is this a text vs number field issue with the foreign key fields?


Screen Shot 2014-05-27 at 12.39.24 PM.png


To sum it up I can't figure out why the data I'm importing into the join table cues_CATALOG does not appear to actually be written into the pop-up fields even though it's displaying properly. As you can see this presents all sorts of problems because my whole scheme is based on the logic that this would perform correctly.


Someone please preach the gospel of filemaker to me...


Kind Regards!