3 Replies Latest reply on Sep 19, 2011 10:40 AM by philmodjunk

    Fields in tab control from different tables - is it possible

    gchalklin

      Title

      Fields in tab control from different tables - is it possible

      Post

      FM Pro 11 Advanced
      Vista HP SP2

      Given that the Layout Setup, General tab only allows records to be shown from one table, is there a way in which the tab control I have on the layout can show records from different tables in each of the two (or more) tabs.

      I had previously created a relationship between the two tables, and displayed records from the related table on the second tab until I realised that they were being governed by the contents of the first tab, which was not what I wanted.

      This is my first post, and although new to FM, I was doing quite well up to this point. I appreciate I'm probably doing this in a back to front manner, so would be grateful for any help from the experts on this forum.

      Thank you

      Graeme

        • 1. Re: Fields in tab control from different tables - is it possible
          philmodjunk

          Explaining what you want to see in more detail would help us to help you....

          You've figured out the basic way this works, you can add fields from other tables, but the relationships you define and the value of the current record in your layout will control what data is displayed in those fields. However, there are relationships that may be able to make this work anyway, but it would help to know more about what you want to accomplish here.

          One possibility is to set up a relationship between the two tables that uses the cartesian join operator (x) instead of =. That way any record on one side of the relationship matches to all records on the other side. This is a good option if you want to use a portal on tab 2 to display all the record from the second table or selected records controlled by a portal filter.

          • 2. Re: Fields in tab control from different tables - is it possible
            gchalklin

            Hi,

            Thank you for your detailed reply, and I apologise for not being as quick on the follow-up as you were, as I have just got back to work after a week off.

            To explain in greater detail:

            I have two tables, Company and Contact. The former comprises details of companies which we either supply to or purchase goods from, and the latter comprises the details of contacts within those companies. Both tables have the company name field in common, and that forms the relationship between the two tables, which I have defined as = .

            In order to keep the design clean and avoid switching between layouts, my aim is to have a single layout which has a tab control with two tabs, one for company based data and the other for related contact based data. I am hoping that I will also be able to switch between the two such that when a contact is selected from within the contact tab, the company with which they are associated automatically appears in the company tab, and vice-versa.

            My challenge is that a tab control allows data from only one table; the table defined in the tab control setup.

            Of course, I'm open to suggestions for achieving this by any other reasonably efficient means.

            Thank you for your interest.

            Graeme

            • 3. Re: Fields in tab control from different tables - is it possible
              philmodjunk

              Both tables have the company name field in common, and that forms the relationship between the two tables, which I have defined as = .

              Better to use a serial number field defined in the Company table. Company names are not unique and are subject to change--both issues that can really mess up your database  and which can be avoided by using the serial number--smething that is unique and will not ever change for that company record.

              My challenge is that a tab control allows data from only one table; the table defined in the tab control setup.

              That's not really the case. It's controlled via Show Records From in Layout setup, and relationships can be used to display data from other tabs as I have previoulsy mentioned.

              I still have one key question that I need answered:

              Do you want to work with data from one company at a time shown in tab 1 or do you want to see a list of all companies in tab 1?

              If you restrict tab 1 to one company at a time, you can base your layout on the company layout and put a portal to contacts on the second tab. When you use the "book control" or perform a find to locate a different company, the contacts listed on the second tab will automatically update to list the contacts for this company.

              That would seem the simplest approach to use here.

              What you describe:

              I am hoping that I will also be able to switch between the two such that when a contact is selected from within the contact tab, the company with which they are associated automatically appears in the company tab, and vice-versa.

              Is a bit contradictory. If you list all companies on one tab and all contacts on the other, there's no way to "synch" them because both list all records from their respective tables. You could do this by using conditional formatting to highlight matching current portal rows in portals that list all companies on tab 1 and all contacts an tab 2, but won't go that direction without confirmation that this is what you really want. Aside from it being more complex, I'm concerned that this approach will become difficult to work with for your users once you have more than just a few companies and/or just a few contacts as it could limit you to scrolling the two lists to find a contact or a company.