8 Replies Latest reply on Sep 17, 2013 1:00 PM by Vicky

    primary keys, value lists, and importing

    Vicky

      Title

      primary keys, value lists, and importing

      Post

           Hi,

           I am trying to import files from excel into a database I created. I already imported the factories into the factory table, models into the model table, and have filemaker create serial numbers for them with no problem. On the model layout I have a pop up so users can select the factory that each model belongs to. When I imported the factories based on the match field of model, everything looked ok until I tried to use a find on the factory pop up. It could not find any records based on the factory I had selected. I realized it's because Factory::_pkFactoryID was not set in the model layout.  Is there any way to set the correct serial number for Factory::_pkFactoryID based on the factory in Model::_fkFactoryID or do I have to go through each record in _fkFactoryID and manually select each factory in the pop up so that _pkFactoryID will be set?

            

      Screen_Shot_2013-09-16_at_2.11.02_PM.png

        • 1. Re: primary keys, value lists, and importing
          philmodjunk

               Is there any data in Model other than the empty _fkFactoryID field that will correctly match to only one record in the factory table? If there is, then you can use that field in a relationship to copy over the primary key value of the correct factory record.

          • 2. Re: primary keys, value lists, and importing
            Vicky

                 I think there is not, unless factoryName can be used?

                 The current relationship between the two tables is

                 Factory::_pkFactoryID = Model::_fkFactoryID

                 The value list for the pop up has two fields, the first is Factory::_pkFactoryID and the second is Factory::factoryName.

            • 3. Re: primary keys, value lists, and importing
              philmodjunk

                   If the factory name is unique, it could be used in a supplementary relationship so that you can get the needed factory ID. The main requirement is that somehow, FIleMaker has to "Know" which factory record has the right ID for a given newly imported model record.

                   Two common options:

                     
              1.           Import model records only for one factory at a time, then a script or replace field contents can be used to update the found set of newly imported records to link them to the same correct Factory ID.
              2.      
              3.           Import records from multiple factories in one import, but then use a field in the imported data, such as the factory name, to match up records to the correct Factory record, then an auto-enter calculation or replace field contents can be used to copy over the correct ID into each newly imported record.
              • 4. Re: primary keys, value lists, and importing
                Vicky

                     I tried making a smaller two table database with only the primary keys, foreign key, factoryName and modelName fields to try to work out a solution to this problem and found that after importing into Model::_fkFactory, Factory::_pkFactory is automatically set.  I do not understand why it works correctly on this small sample database and not the original one.

                • 5. Re: primary keys, value lists, and importing
                  philmodjunk

                       Nor do I from what little you have described here about your trial database.

                       Note that I said that you would need a "supplementary relationship" if you were to import the factory name. By that, I meant that you'd link a new Table Occurrence of Factory to your Model table and specify that the factory name fields be used as the match fields in this relationship. Then Replace field contents can be used to copy over the ID number from this new occurrence after import or you can set the _fkFactoryID field to autoenter the __pk value from this new occurrence of Factory provided that you enable auto-enter options during the import.

                  • 6. Re: primary keys, value lists, and importing
                    Vicky

                         Hi Phil,

                         I tried using Replace field contents in a script but I am not writing it correctly.  When I run the script it just clears the _fkFactoryID from all of the records in the model layout.

                         Replace Field Contents[ model::_fkFactoryID; factory 2::_pkFactoryID]

                         Was I suppose to set variables or use if statements? I tried both but it did the same thing where it just clears _fkFactoryID.

                          

                    • 7. Re: primary keys, value lists, and importing
                      philmodjunk

                           Your replace field contents step and your relationship appear to be correctly set up. The _fkFactoryID field shouldn't have any data yet so it is not clear why it is "clearing" a field that should be empty in the first place, but what you report suggests that the text in Model::FactoryName does not match any record in Factory 2.

                           If you place the Factory 2::FactoryName field on your Model Layout, do you see a factory name? This relationship will only work if the text in Model::FactoryName and Factory 2::FactoryName matches.

                      • 8. Re: primary keys, value lists, and importing
                        Vicky

                             Thank you Phil for clearing up my confusion. It works perfectly now :)