1 2 Previous Next 19 Replies Latest reply on Aug 31, 2014 10:56 PM by StefanSaeys

    Making my imported excel database less static

    StefanSaeys

      Title

      Making my imported excel database less static

      Post

      Hello,

            
           I have a “static” database that I would like to convert to a more flexibel one.
           By posting here I am looking for some tips for a roadmap; or even better a solution :-)
            
           Situation:
           FM13, 1 database
           2 tables:
           => one table that contains companies with contact persons.
           => 2nd table that contains properties
            
           Since one contact can be a member of one or more companies (and several contacts can be a member of one company), I would like to make a new table “contacts”.
           In case the contact information changes I only need to update it once.
            
           Unfortunately the table companies doesn’t contain a unique ID, because they can also be linked to several properties.
           One company can own several properties, but it wasn’t setup with a unique ID. The current link is being done through a portal.
           Identical ID’s are being used in properties to link them with one company.
            
           Idealy I would like to achieve 3 tables that are linked in an easy way: company, property and contact.
           If anyone has some tips how to achieve this, this would be greatly appreciated.
            
           Thank you !

        • 1. Re: Making my imported excel database less static
          philmodjunk

               What you describe is a classic many to many relationship. You need a a third table to serve as a "join" so that one contact can link to many companies and one Company can link to many contacts.

               Start with these relationships:

               Contacts-----<Contact_Company>-----Companies

               Contacts::__pkContactID = Contact_Company::_fkContactID
               Companies::__pkCompanyID = Contact_Company::_fkCompanyID

               You can place a portal to Contact_Company on the Contacts layout to list and select  Companies records for each given Contacts record. Fields from Companies can be included in the Portal to show additional info about each selected Companies record and the _fkCompanyID field can be set up with a value list for selecting Companies records by their ID field.

          • 2. Re: Making my imported excel database less static
            philmodjunk

                 For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

            • 3. Re: Making my imported excel database less static
              StefanSaeys

                   I've played around with your suggestion but I'm not sure that it will make my database less static.

                   Currently I have the table companies that contains also FirstName, LastName, Address information (of the company).
                   If I "duplicate" the table, I have a double were the contacts have the company address and the contacts Name is still in the companies table.
                    

                   Maybe the screenshot makes more sense, it shows your suggestion. Or maybe I am still in the dark here :-)

                    

              • 4. Re: Making my imported excel database less static
                philmodjunk

                     I don't know what you have in mind when you say "less static" but the relationships that I've described are required if you are to get this to work without needless duplication of data for a system where contacts can link to more than one company and companies can link to more than one contact.

                     There should not be any duplicated table here. Not even a duplicated table occurrence--though that would not duplicate any data.

                     But you don't have the match fields set up as I recommended and contact_company is not shown linked to a table of Contacts in this screen shot..

                     Contact_Company must link to both contacts and Companies, not just to Company. Using your names, this part would be:

                     Contacts::ContactID = Contact_Company::ContactID

                     Companies::CompanyID = Contact_Company::CompanyID

                     Company links to the join table by CompanyID, not ContactID. There should not be a contactID field in Company.

                     You've then added in a properties table and I apologize for not referring to it in my last post.

                     It should be linked to Company in one of two ways:

                     If a property is never linked to more than one Company:

                     Companies::CompanyID = Properties::CompanyID

                     But if a property can be linked to more than one company, then another joint table is needed:

                     Companies----<Company_Property>------Properties

                     Companies::CompanyID = Company_Property::CompanyID
                     Properties::PropertyID = Company_Property::PropertyID

                      

                • 5. Re: Making my imported excel database less static
                  StefanSaeys

                  Ok, thank you for your help. It helped me a lot forward.
                       I've added your suggestions:

                       Companies::CompanyID = Contact_Company::CompanyID
                       Contacts::ContactID = Contact_Company::ContactID

                       When creating a portal in companies I receive the error "CompanyID" is defined to contain unique values only.
                       CompanyID (and ContactID) have been setup as number, indexed, auto-enter serial, Always validate, required value, Unique, numeric only.
                       Is this not how it should be? A unique number? If I change this it works, but I wonder if the relation will continue to work in the future.

                       I've also made a portal on Companies Layout. When I add a new row, it saves the name, but when I go to the related record I can't see it.
                       Were is the data being stored?

                       The relation between properties::REF and companies:REF is currently the only thing that forms the relation between those two tables. Unfortunately this is not done with a unique number. So I'm afraid to break the connection between them if I only add a Properties::CompanyID.

                       I'm sorry for all the questions...

                        

                  • 6. Re: Making my imported excel database less static
                    philmodjunk

                         When creating a portal in companies I receive the error "CompanyID" is defined to contain unique values only.

                         Just adding a portal to your layout shouldn't trigger that message. That would come from trying to create a new record in a way that produces a new record with a value that duplicates an existing record in the same table. And note that CompanyID is the name of a field in two different tables. So we can't tell just from the error message if this came from a duplication Companies or Contact_Company. And you haven't told me on what layout you added this portal either.

                         But from your screen shot, it would appear that the field options for Contact_Company::CompanyID and Contact_Company::ContactID are not set up correctly as I don't see the "crows feet" that should be there. These two fields should NOT be auto-entered serial number, nor unique.

                         

                              I've also made a portal on Companies Layout.

                         And that would be a portal to what?

                         WIth regardst to REF, what data is put in that field?

                         Assuming that each property can be owned by one  and only one company and that a company can own many properties, you should be using CompanyID as the match field to a companyID field in properties.

                         Note: I use the following naming convention for a reason: __pkCompanyID, this would be the auto-entered serial number defined in Companies to uniquely identify each company. pk means "primary key".  _fkCompanyID, this would be the simple number field (no auto-enter serial number), define as the match field in a related table. fk means "foreign key". If you had used some such naming convention here, your error message would be a bit less confusing.

                    • 7. Re: Making my imported excel database less static
                      StefanSaeys

                           The error is indeed there if I try to add a new row in the portal. By disabling the unique, check value the error is gone.
                           I don't find the data that I've added in the new row though.

                           The portal is located at Companies Layout and points to the table Contacts. One company could have multiple contacts so I've allowed creation of new records in this relationship.

                           REF contains numbers but is not unique. One property can have more companies linked to it, so sometimes the REF number is a duplicate (up to 10 identical numbers in some cases).

                           I think that I've had the crow feet in my first screenshot, but left it out because you said "There should not be a contactID field in Company."
                           Thank you for the tips, I will now change the naming convention as you suggested and follow the other instructions.

                      • 8. Re: Making my imported excel database less static
                        philmodjunk
                             

                                  The portal is located at Companies Layout and points to the table Contacts. One company could have multiple contacts so I've allowed creation of new records in this relationship.

                             But this should be a portal to Contact_Company, not Contacts and the "unique values" validation should not be specified in the Contact_Company table for either ID field.

                             I believe that the "companyID" field responsible for this error message is defined in Contact_Company not Companies. This field cannot be set to "unique" anyway as there can be many records in this table with the same value in this field as you can have many contacts linked to the same company.

                             

                                  One property can have more companies linked to it,

                             Then it would appear that one company can be linked to many properties and many properties can be linked to one company. This is yet another many to many relationship requiring another join table, this time between Properties and Companies.

                        • 9. Re: Making my imported excel database less static
                          StefanSaeys

                               ok, now I've created _pkCompanyID and __pkContactID, both with auto-enter serial and Numeric only.
                               _pkCompanyID in the table companies and __pkContactID in the table contacts.

                               I've also created __fkCompanyID and __fkContactID with a numeric field and use it in the Contact_company table.

                               The relationship between companies and Contact_companies is:
                               __pkCompanyID = __fkCompanyID (allow creation of records on both sides)

                               The relationship between contact_companies and contacts is:
                               __fkContactID = __pkContactID (allow creation of records on both sides)

                               Somehow all my linked data is now gone, so I have to start from a -hopefully- recent backup :-)

                               I understand that I have to do the same for properties, but I first want to understand this very well.


                                

                          • 10. Re: Making my imported excel database less static
                            philmodjunk

                                 Something is seriously wrong here. I should not see the same fields listed in each of these table occurrence boxes.

                                 If you hover the mouse over the arrow in the left hand corner of these table occurrence boxes, what data source table name pops up?

                                 Each of these should have a different data source table. Data source tables are what you create on the tables tab.

                                 To me, it looks like you have one data source table where you need three.

                            • 11. Re: Making my imported excel database less static
                              StefanSaeys

                                   All three of them point to the same:

                                   Source table: organisations (before I've renamed this to companies)
                                   Data source: current file

                              • 12. Re: Making my imported excel database less static
                                philmodjunk

                                     And that won't work. You need to click over to the tables tab, create 3 distinct tables, click over to fields and set up each table with only the fields each table needs. Then use the table occurrences for these new tables in order to set up the relationships that we have been discussing.

                                • 13. Re: Making my imported excel database less static
                                  StefanSaeys

                                       If I use duplicate this new table is empty (doesn't contain any records).
                                       I assume that lookups is the proper way to go. Although exporting and importing a table looks easier :-)

                                       I assume I have to select "starting with table: companies" and "lookup from related table contact_company"
                                       Copy value from field __pkCompanyID.

                                       Does this look ok?

                                  • 14. Re: Making my imported excel database less static
                                    philmodjunk

                                         "Duplicate"??? There is no duplicate option on the tables tab so I don't know what you mean by that. You can click a duplicate button on the Relationships tab, but this is NOT what you should be doing here as you need completely different data source tables for each of the table occurrence boxes that make up this many to many relationship.

                                         I am describing the tables and relationships that you need. Once you have them set up, you can use Import Records to move any existing data that you have in your excel file or already imported into a different table in your database into these new tables.

                                    1 2 Previous Next