5 Replies Latest reply on Aug 12, 2015 8:25 AM by philmodjunk

    Entering large amounts of data into join tables

    b24mike

      Title

      Entering large amounts of data into join tables

      Post

      Last year, thanks to Guy Stevens help, I was able to construct a database for a World War II bomb group that was part of the 8th Air Force in England. For the past 6 months I've been verifying data in Excel spreadsheets and am now ready to try importing the data.  The attached image is the Missions Table layout that contains 2 portal fields - Join_Acft_Missions_Tbl (contains the records for each plane that flew a given mission; max of 48 planes on any given mission) and Join_Acft_Crew_Selected_Tbl (contains the names of each crewman on a given plane on a given mission).  For example, the first mission flown there were 15 aircraft and a total of 155 crewmen who participated in the mission.  The second mission had 35 planes and 355 crewmen.  By clicking on the green button in the left portal window next to a given airplane, I can see the crew for that plane in the right portal window.

      I have my Excel data configured to allow me to populate each of the two join tables that I've created.  The Join_Acft_Missions_Tbl input will populate the correct number of aircraft for each mission.  The Join_Acft_Crew_Selected_Tbl should populate each plane of each mission, but it doesn't seem to work with the small number of crews I'm testing with.

      Any thoughts, comments would be greatly appreciated as I need to get about 7,700 aircraft entered into the 280 missions flown by the bomb group and the corresponding nearly 78,000 crewmen.  I will be demonstrating this database at the 8th Air Force Historical Society's 2015 reunion in Omaha, NE in mid-October as a prototype for a possible 8th Air Force wide database.

      If you would like to contact me directly, my email address is Mike@445BG.org.

      Mission_layout_with_2_portals.JPG

        • 1. Re: Entering large amounts of data into join tables
          SteveMartino

          Well it really depends on what the excel ss look like, configured, and how you are trying to do the import

          but it doesn't seem to work with the small number of crews I'm testing with.

          What's working and what's not working?

          Generally I would think of 2 approaches.  Import all the info to a temporary table where it can be properly parsed out.  Or multiple imports-first import and create the missions to the proper table.  Then import the aircraft to a temp table where you can assign the proper foreign key.  Then import the crew to the proper table related to aircraft.

          And keep plenty of back up copies.  Don't forget, after you do an import, you are left with a found set of records.  Don't change that found set until you can verify they are correct.  If the import is incorrect, you can delete that entire found set of records and try again.

          Usually it's best to perform the import manually first, test, then test some more, then script the import and test and test

          • 2. Re: Entering large amounts of data into join tables
            b24mike

            Steve:  Turns out the problem was missing a field in my Join_Aircraft_Missions_Tbl called 'SelectedAircraft'.  There's a script that runs to link a particular aircraft by ID number to the Join_Acft_Crew_Selected_Tbl.  I'm still very new to the idea of using scripts to pull selected data, but I'm jumping in with both feet.  I do have one question about scripts.  On occasion I need to have two parameters in the Set Field command.  When I try using the GUI to create a script, it only allows a single parameter.  Can I create a script manually if I know the parameters needed?  Thanks for your ideas and help.

            I recently retired as a Systems Staff Test Engineer working for General Dynamics on a WCDMA based satellite waveform for a software-defined radio (military project) called MUOS.  I'm very familiar with multiple tests and regression testing.

            • 3. Re: Entering large amounts of data into join tables
              SteveMartino

              On occasion I need to have two parameters in the Set Field command.  When I try using the GUI to create a script, it only allows a single parameter

              What's a GUI?

              Can you elaborate with an example.  Do you mean 2 'thing's in one field, or 2 script parameters for a script?  If it's the first, usually you set a variable, then set a field.  You can set a variable to 2 things by appending it.

              If it's the second, you can't set 2 script parameters, but you can set a multi-script parameter, then parse out the parts as needed. 

              Can I create a script manually if I know the parameters needed

              Technically, all scripts are created manually, do you again mean hard coding a script parameter?

               

              • 4. Re: Entering large amounts of data into join tables
                b24mike

                Steve:  I think of a dialog box as a sort of graphical user interface or GUI.  In this case it's the scripting Edit dialog that I'm referring to.  Using the 'Set Field' command, I get Set Field [] in the script steps display window.  If I check the 'Specify target field' box and click on the 'Specify ...' button, I can select a particular table and field to set.  Another responder on this forum, Guy Stevens, provided the script below which had two entries in the script and I was just trying to figure out how they were entered.

                • 5. Re: Entering large amounts of data into join tables
                  philmodjunk

                  When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.