5 Replies Latest reply on Jul 22, 2011 10:40 AM by philmodjunk

    How to import from Excel into a portal field?

    MarkPeters

      Title

      How to import from Excel into a portal field?

      Post

      I am using a portal field to be able to select "People Names" (stored in a separate table) involved with each of my "Research Notes" records.  

      I have some "Research Notes" already written in Excel, which have "People Names" in a corresponding column.  The other fields are "regular fields", so there is no issue importing those.  But is there a trick to importing the "People Names"?  

      I made a "join table" (Research Notes_People Names) so I think could just import the other fields into the "Research Notes" table, then take the "Research Notes ID"s that are generated, match them with the "People Names", then import both of these fields/columns into the join table (Research Notes_People Names).  

      I found this thread on the topic, but it's old, and i've never used scripts really, so I'm wondering if there's a different technique these days.  Thanks!:

      Question: 

      "Title:
      Import from Excel into a Portal field
      Post:

      I have a portal field list that I need to import data into from an excel spreadsheet -- is this possible?  The portal is in my mail list data page, but related to our registration database...  I have the portal field name included in the excel spreadsheet, but the data doesn't drop in during an import.  ANy help would be greatly appreciated!!"

      Answer:

      "Melinda, you need to import directly into a layout based upon same table as your portal.  If you want to capture the related parent key so you can relate your newly imported portal records, you can use a variable or a global (or even a script parameter) to grab it, something like:

      Set Variable [ $RegistrationID ; Registrations::RegistrationID ]

      Go To layout [ layout based upon mail list portal ]

      Set Error Capture On

      Import [ ]

      Replace Field Contents [ Mail List::RegistrationID ; $RegistrationID ]

      Commit Records/Requests

      Go To Layout [ original layout ]"

        • 1. Re: How to import from Excel into a portal field?
          philmodjunk

          The same basic technique would be used today. The key question then and now is whether your excel file contains the needed data to correctly link your imported data to the other tables in your system. If the data is in your excel file, you can import it along with the other columns that contain data for your portal's table. If it does not, then you need to use one method or another to update the portal's key field(s) to that the imported data is correctly linked. A script is one approach, replace fields with the calculation option is another.

          • 2. Re: How to import from Excel into a portal field?
            MarkPeters

            yes the names in Excel match the names in FM.  Although I do use "People ID" numbers in FM in my join table and as the used values in my value list (while the full names are the displayed, 2nd value), I can easily convert the full names to the appropriate FM "People ID" number in my excel sheet.  

            DO you mind walking me through this(im shameless)?  I have no idea where to start, and even if I knew how to "set variable", im pretty sure that's not the first step.  I'm guessing this is writing a script, but which script step do I choose? 

            • 3. Re: How to import from Excel into a portal field?
              philmodjunk

              The join table implies that you have a many to many relationship between notes and people. Is this the case with your imported data?

              Do you have one research note and a list of people to which it must be linked or just a single "people" record?

              • 4. Re: How to import from Excel into a portal field?
                MarkPeters

                Right now, in Excel, I have a row of "Research Notes" data, which includes 6 columns of "People" names per "Research Notes".  None or all of these columns can be filled for each "Research Note" row.  

                I guess I will just import the rest of the "Research Notes" fields to the main table in FM, then convert this excel sheet to look like an FM join table: giving each "People" name per record its own row (record) and match these rows with the correct "Research Notes ID" that has just been created, then import these two columns to the join table (thereby bypassing the portal).

                • 5. Re: How to import from Excel into a portal field?
                  philmodjunk

                  Unless this is a fairly small spreadsheet, that sounds like a lot of work and it might not be much more work to copy and paste text from the spreadsheet one cell at a time directly into your database.

                  You may want to set up a table with fields to match your spread sheet as it now exists, then import the spreadsheet into it. Once imported, a script could loop through these records and use the data in the fields to create both research note and join table records with the appropriate links to connect them.

                  Say your temp table has the fields: Note; People 1, People 2, People 3... People 6 defined.

                  After importing into this temp table, the following script should do the job:

                  Go To Record/Request/Page [first]
                  Loop
                     Set Variable [$Note ; Value: Temp::Note ]
                     Go To Layout [ResearchNotes]
                     New Record/Request
                     Set Field [ResearchNotes::ResearchNote ; $Note ]
                     Set Variable [$ResearchID ; Value: ResearchNotes::ResearchID ]
                     Go To Layout [Temp]
                     Set Variable [$PRep ; Value: 1 ]
                     Loop
                         Exit Loop If [$PRep > 6]
                         Set Variable [$Name ; Get Field ["Temp::People " & $PRep ]
                         If [ Not IsEmpty ( $Name ) ]
                            Go To Layout [People]
                            Enter Find Mode []
                            Set Field [People::Name ; $Name]
                            Set Error Capture [on]
                            If [ Not Get (FoundCount ) ]
                               Show custom Dialog ["Error " & $Name & " not found in People table"]
                               Exit Script []
                            Else
                                Set Variable [$PID ; Value: People::PeopleID]
                                Go To Layout [Research Notes_People]
                                New Record/Request
                                Set Field [Research Notes_People::PeopleID ; $PID]
                                Set Field [Research Notes_People::ResearchNotesID ; $ResearchID]
                                Go To Layout [Temp]
                             End If
                          End If
                          Set Variable [$PRep ; Value: $PRep + 1 ]
                       End Loop
                       Go To Record/Request/Page [ Next ; Exit after last ]
                  End Loop