3 Replies Latest reply on Jun 27, 2010 4:25 PM by Steve Wright

    Importing Data into a record with Portal



      Importing Data into a record with Portal


      I have a issue regarding importing data for a single record that contains and large number of related portal records.


      I have a Database created on Filemaker Pro 11, MAC OSX that records the receipts of awards.  The main record has fields that list the <Award_Name> <Award_Type> and the like.   The related portal records lists the recipient details, such as <Surname> <First_Name> <Occupation> <Date>  <Reference> <Page_Number> etc.  


      As you can see I have any given number of awards within the database, but each award can have a large number of recipients.


      An export function works well and a single award record can be exported along with all its related recipients  -  within the one spreadsheet. Obviously the <Award_Name> and <Award_Type> are repeated cells for each recipient within the spreadsheet.


      However, I am stuck when it comes to writing a script and setting up to import  a single award record along with its recipients.....



        • 1. Re: Importing Data into a record with Portal

          The imports and the exports are not really done from or via the portal.  Exports should be done from the child table grabbing relavent fields from the parent table.  If you are importing child records, there have to be parent record.  If you import child records for which there is no parent you will have orphan records.  


          Import sequence should be import the parent table first followed by the import to the child table.

          • 2. Re: Importing Data into a record with Portal

            I undertand what you are syaing.  


            I suppose it may be easy for me to even manually enter the details regarding the parent record, being <Award_Name> and <Award_Type>.


            Then import the large number of child records being <Surname> <First_Name> <Occupation> <Date>  <Reference> <Page_Number> 


            However, I am obviously on the wrong track when I have attempted it.  The records go straight into the parent fields....my script is obviously off.


            The export function I have that work  in reverse isolates the current record and then exports: 


            - Show All Records

            - Omit Record

            - Show Omitted Only

            - Export [ ]



            Anyone got any ideas of a script to import one record plus a large number of related portal records, or just a large number of portal records into a single record created manually...?







            • 3. Re: Importing Data into a record with Portal
              Steve Wright

              If I follow what you are saying, you have data such as


              ParentID,  Award_Name, Award_Type, Child ID, Name, Reference....


              1,  award A, type A, 1, smith, abc

              1,  award A, type A, 2, jones, def

              1,  award A, type A, 3, johnson, ghi

              2,  award B, type B, 1, johnson, ghi


              Sorry for the formatting BTW

              In which case, the first few fields (parent record) are repeated throughout all the child records ?


              Somebody else may chime in with a better idea, but a quick fix could be to 


              If you are talking about a single parent record each time

              1. Import all records into your CHILD table

              2. Set the appropriate fields into Variables, then go to the parent table, create a new record and set the fields from the variables.


              If you are talking about more than 1 parent record, perhaps along the lines of


              1. Import into a temporary table (created for this purpose only)

              2. Import all child records from the temporary table

              3. Create a script to remove duplicates from the temp table, based on the parent ID 

                   (this could then leave you with 1 record for each parent ID)

              4. Import the parent records (minus the duplicates) from the temp table

              5. clear out the temp table