12 Replies Latest reply on Feb 26, 2014 12:26 PM by synergy46

    FM 13 Adv - Import 'many' table

    synergy46

      I have a membership application.

      One of my users wants to bring his excel data in my app.

      Tables are;

       

      MEMBERS;

      PKMEMBERID

      LASTNAME

      FIRSTNAME

      ADDRESS1

      ADDRESS2

      CITY

      ...ETC.

       

      PROGRESS:

      PKProgressID

      FKMEMBERID

      DatePetitioned

      DateAccepted

      ....ETC

       

      These two tables are related 1 to many.

       

      As is typical of many users, *ALL* the information is in a giant Excel spreadsheat with one row for each member.

       

      The Excel spreadsheet has:

       

      MemberID (Unique membership number)

      NameLast

      NameFirst

      Date Petitioned

      Date Accepted

       

      I can easily import the appropriate Members data into MEMBERS and FM agreeably generates a PK.

      HOWEVER, when I try and import the Excel::Date Petitioned and Excel::Date Accepted fields, there seems to be no way

      to generate the appropriate PROGRESS::FKMemberID

       

      My sense of it is that I can import the EXCEL::MemberID in both the Members:: import and the Progress:: import to link the two FM tables. But, after that I am stuck. I need to relink the Members::PK to the Progress::FK... Ideas???

      Thank you

       

      Ron

        • 1. Re: FM 13 Adv - Import 'many' table
          DavidZakary

          Assuming you're not using the member ID field that exists in the Excel file as the main relationship. Could you? If not, use the existing ID info to build a relationship to create your new key field data

           

          Create a relationship based on the imported MEMBERID field for primary to foreign as a temporary thing.. You could then set the foreign key to the generated primary key with a REPLACE command. Should be pretty simple.

          • 2. Re: FM 13 Adv - Import 'many' table
            synergy46

            RE: 

            If not, use the existing ID info to build a relationship to create your new key field data.

             

            Are you referring to the existing ID in Excel or the Existing ID in Members or the Existing ID in Progress.

             

            Since there is 'one' Members table related to about 7 'many' tables, of which Progress is but a single instance, I don't see how to get the Progress::FKMemberID to hold the appropriate Members::PKMemberID.

            • 3. Re: FM 13 Adv - Import 'many' table
              PSI

              Ron,

               

              My take is to add a field to both Members and Progress tables. MemberIDxls for the MemberID field in the Excel file. Create an additional relationship from Members to Progress using this field. Lets call it Members_Progress|MemberIDxls this relationshop will be used to get the PKMemberID from Members.

               

              When creating progress records, not via import, if FKMemberID is a lookup then do this...

               

              In Progress setup an auto-enter calculation for FKMemberID...

               

              Case ( IsEmpty ( MemberIDxls ) ; Lookup ( Members::PKMemberID ) ; Lookup ( Members_Progress|MemberIDxls::PKMemberID ))

               

              The first lookup would be the one you are currently using, moved to the calculation above, which fires off if the MemberIDxls doesn't contain a value. Otherwise it will look that value up using the new relationship. Just make sure you import to Members before importing to Progress.

               

              Make Sense?

              John

              • 4. Re: FM 13 Adv - Import 'many' table
                synergy46

                John,

                Thank you for the reply.

                Perhaps I am not being clear.  There are about 400 rows of data in about 5 columns stored in the Excel spreadsheet.  I need to bring that data into the Progress Table and after it is brought in, it should, via the Members::PKMemberID -->Progress::FKMemberID be associated with a specific member.  The same as if it was entered in the Progress portal on the Members layout.

                 

                Sorry, but I don't understand what you saying when you write:  "MemberIDxls for the MemberID field in the Excel file."

                 

                Also, the Members::PKMemberID is a serial auto enter value.  Not a lookup.  And, it needs to stay that way because there are several other 'child' tables that rely on it for their relationships.

                 

                 

                importTOC.png

                • 5. Re: FM 13 Adv - Import 'many' table
                  PSI

                  Ron,

                   

                  You are being very clear. I understand exactly what you are trying to do...

                   

                  Sorry, but I don't understand what you saying when you write:  "MemberIDxls for the MemberID field in the Excel file."

                   

                  The Excel spreadsheet has: MemberID (Unique membership number)...this field is the "key" to getting PKMemberID, your primary key field in Members, into the Progress table.

                   

                  Also, the Members::PKMemberID is a serial auto enter value.  Not a lookup.  And, it needs to stay that way because there are several other 'child' tables that rely on it for their relationships.

                   

                  Nothing iI said in the original post mentioned changing PKMembersID in Members. Please reread the original post...

                   

                  John

                  • 6. Re: FM 13 Adv - Import 'many' table
                    synergy46

                    MemCanRelationship.png

                     

                    Hi John,

                    I setup the relationship above (which I hope is the one you had in mind)

                    Do I have this part right?

                    • 7. Re: FM 13 Adv - Import 'many' table
                      PSI

                      Almost...remove KP_MemberID you only need MemberIDxls. also uncheck the 2 check boxes.

                       

                      John

                      • 8. Re: FM 13 Adv - Import 'many' table
                        synergy46

                        OK.  Deleted the KP relationships.

                        But, I am perplexed by your lookup formula:

                         

                        Case ( IsEmpty ( MemberIDxls ) ; Lookup ( Members::PKMemberID ) ;

                        Lookup ( Members_Progress|MemberIDxls::PKMemberID ))

                         

                        I read this (In English) 'if MemberIDXL is empty then lookup Members::PKMemberID otherwise

                        lookup (Progress::PKMemberID)??? 

                         

                        AUTOENTERFK.png

                         

                        But, I just deleted the relationship of Members::PKMemberID to Progress::FKMemberID.  Doesn't

                        Lookup require a relationship to work?  I am 'flumixed'....  Thanks

                         

                        Ron

                        • 9. Re: FM 13 Adv - Import 'many' table
                          PSI

                          If the relationship below not a new relationship then put it back to KP_MemberID = FK_MemberID with the check boxes checked.

                          (Also why does it say candidates not progress on the right side?)

                           

                          In my original post I said Create a new relationship using the new field MemberIDxls. Between Members and Progress.

                          synergy46 wrote:

                           

                          MemCanRelationship.png

                           

                          Hi John,

                          I setup the relationship above (which I hope is the one you had in mind)

                          Do I have this part right?

                          • 10. Re: FM 13 Adv - Import 'many' table
                            PSI

                            I just posted a response about the relationship you need. Once that is fixed i think the calculation will make more sense...

                             

                            John

                             

                            synergy46 wrote:

                             

                            OK.  Deleted the KP relationships.

                            But, I am perplexed by your lookup formula:

                             

                            Case ( IsEmpty ( MemberIDxls ) ; Lookup ( Members::PKMemberID ) ;

                            Lookup ( Members_Progress|MemberIDxls::PKMemberID ))

                             

                            I read this (In English) 'if MemberIDXL is empty then lookup Members::PKMemberID otherwise

                            lookup (Progress::PKMemberID)??? 

                             

                            AUTOENTERFK.png

                             

                            But, I just deleted the relationship of Members::PKMemberID to Progress::FKMemberID.  Doesn't

                            Lookup require a relationship to work?  I am 'flumixed'....  Thanks

                             

                            Ron

                            • 11. Re: FM 13 Adv - Import 'many' table
                              PSI

                              Another issue which hasn't been mentioned. what happens when a new xls file comes in and it contains a member id that already exists in FMP? Can that happen?

                               

                              John

                              • 12. Re: FM 13 Adv - Import 'many' table
                                synergy46

                                John,

                                Thank you for offering to look at my running app.  It was a real eye opener for me.

                                 

                                For the benefit of others reading this thread, this is the solution:

                                First, I need to establish these relationships:

                                relations3.png

                                relation2.png

                                relation1.png

                                Where MemberIDxls is a field that holds the unique value on the spreadsheet.

                                In my case it holds a unique, assigned membership number:

                                 

                                spreadsheetMemNum.png

                                 

                                The kicker to all this is the Lookup calculated field:

                                 

                                lookup2.png

                                The only question I have is:  "It seems that KF_MemberID, (if the list has all unique MemberIDxls values) never get executed?  And when it does get executed, it returns "" because KF_MemberID is blank.  Have I got this right?

                                 

                                Thanks again for helping me with my 'free ware' app.  All my users and I are grateful.

                                 

                                Ron