2 Replies Latest reply on Sep 29, 2010 11:47 PM by Mastema

    data from unrelated table



      data from unrelated table



      In the database i'm using person links to an establishment , and the establishment links to the main company information

      when I add a person, I want to be able to first chose the main company (from a pop up menu), and then chose in another pop up menu the establishment he is working for.    since main company isn't (directly) linked to establishment, I 'don't seem to be able to...

      any sugestions?

        • 1. Re: data from unrelated table

          Just because two tables aren't directly related doesn't mean that they are unrelated.

          From your post, I have People>----Establishment>-----Companies    ( >--- means many to one )

          Ideally you should have these relationships defined in Manage | Database | Relationships:

          People::EstablishmentID = Establishment::EstablishmentID
          Establishment::CompanyID = Companies::CompanyID

          Where bold faced fields are auto-entered serial numbers.

          The company menu is the simplest. In Manage | Value lists, create a new value list, click the specify field button and select CompanyID for column 1 and CompanyName for column 2. If you set a validation rule on CompanyName to enforce unique values, you can hide column 1 and this works fairly well for popup menus.

          For the second value list which should be a conditional value list so that selecting a company causes this list only to display establishments for that company, you'll need to do some more work here.

          Define a field in People, SelectedCompanyID and make a new relationship:

          People::SelectedCompanyID = SelectedCompany::CompanyID (Selected Company is a 2nd table occurrence of Companies.)
          SelectedCompany::CompanyID = SelectedEstablishments::CompanyID ( SelectedEstablishments is a 2nd table occurrence of Establishments.)

          Format SelectedCompanyID as your popup menu with the companies value list. To create SelectedCompany, go to Manage | Database | Relationships, click Companies, click the button with two green plus signs to create a new table occurence, Companies 2 and then double click it to change the name to SelectedCompany.

          Now set up your value list for Establishment similar to how you set up the companies value list, Specify SelectedEstablishments::EstablishmentID for column 1 and SelectedEstablishments::EstablishmentName for column 2. In this value list select "Include only related values starting from People" and you have your value list for your People::EstablishmentID field.

          • 2. Re: data from unrelated table

            Thx, this helped a lot!

            all works perfectly now