3 Replies Latest reply on Jun 3, 2014 11:40 AM by jlamprecht

    Issue Importing Excel Data Into Pop-Up Menu Fields


      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!

        • 1. Re: Issue Importing Excel Data Into Pop-Up Menu Fields

          Each row in a spreadsheet is only linked (inasmuch as there is a link between columns in a spreadsheet) by the CueID field. If you want to maintain this as a link between separate tables in a FM database then you must import this CueID field into each table, together with the data for that table, then set up relationships between the tables using the CueID field. That will give you an live join between the various imported elements. Personally I would also set up, in each table, a unique ID field that autoenters using the Get ( UUID ) function, only use the imported CueID as a temporary link to enable establishment of more robust on-going links using the new UUID fields.


          As far as lists are concerned, you are presumably wishing to create lists based on the contents of one of the tables (eg. subgenre). You need to remeber that in order to build proper links from the list the list's primary field needs to be the UUID field, but with the list et to only display data from a second field, the human-readable one. Then you set the list on the foreign key UUID field so that the linking data is entered correctly from the list.

          • 2. Re: Issue Importing Excel Data Into Pop-Up Menu Fields

            Thanks for the reply


            Are you saying that in addition to the linked Cue ID fk I have in my join table that I should also have a Cue ID fk in my Genre and Subgenre tables?


            Apologies, I am not clear yet on how this relates to my conundrum?

            • 3. Re: Issue Importing Excel Data Into Pop-Up Menu Fields

              I would check out your value list for Genre 1. If JAZZY is not appearing checked, I believe it is not in the value list. You may have a different Field referenced for Value 1, possibly the Genre ID field?


              This would also explain why there are no values in the value list for the Subgenre. It appears you are using the relationship between cues_CATALOG and cues_catalog_SUBGENRE based on the foreign keys Genre ID. If the correct value is not being populated, you won't get any values shown.


              I hope this helps.