4 Replies Latest reply on Apr 3, 2009 4:38 PM by philmodjunk

    Aliasing an ID to a name in relational tables?

    kyzaadrao

      Title

      Aliasing an ID to a name in relational tables?

      Post

      Greets,

       

      Hit a stumbling block when trying to make filemaker display the "name" of a record for human consumption, when it uses the ID field under the hood. Here's my scenario.....

       

      I have an accounts table with an accountTypeID field. I have an AccountTypes table with it's own ID.

      I want to show the "names" of the account types from the account types table in a dropdown in the Accounts table field under AccountTypeID, but the relationship is via ID's between the two tables.

       

      Got it partially working by making a value list that uses the AccountTypes ID and AccountTypes Name and looks like this in the value lists window...

      use values from field: "AccountTypes::AccountID" and "AccountTypes::Name"

       

      The above is set to "include all values" and "show only values from second field"

       

      Then I plug this list into my dropdown field in my layout for editing the AccountType ID of the Accounts record...it works fine in part ... it shows the account type names in the dropdown, but it still shows the ID number for account type after it's chosen....so while my user will see the account type names in the dropdown, they still only see the ID number when flipping through records.

       

      So I guess my question is how can I completely "alias" the AccountType ID to display as the AccountType Name to the user, or is this even possible?

      I use ID's throughout my tables and of course like users to choose by name, so it's a step forward for me in smooth user experience.

      Hope that wasn't too confusing.

        • 1. Re: Aliasing an ID to a name in relational tables?
          philmodjunk
            

          Option 1: Format your field as a pop-up menu

          Option 2: Place the related "name field" immediately to the right of the drop down formatted ID field.

           

          • 2. Re: Aliasing an ID to a name in relational tables?
            Jade
              

            Kyzaadrao wrote:

             

            So I guess my question is how can I completely "alias" the AccountType ID to display as the AccountType Name to the user, or is this even possible?


            Hi,
            Everything you have described sounds right.  I think you just have one more step.  Add another AccountsTypes::Name field to the layout.  Place it on top of the drop-down and size it to fit.  Make it inaccessible in Browse mode.
            Now when you choose a Name from the drop-down, it will populate this new field and hide the AccountTypes:AccountID value underneath it.
            You may still see the AccountID when the drop-down list is displayed.  If you find this confusing, change the pen color to match the background. 
             There are several variations on this… 

             

            • 3. Re: Aliasing an ID to a name in relational tables?
              kyzaadrao
                 Thanks for the input! I opted for changing it to a popup rather than a dropdown and it worked perfectly. Gained a little more understanding of Filemaker in the process, in the other suggestions, so thanks again.
              • 4. Re: Aliasing an ID to a name in relational tables?
                philmodjunk
                  

                Editorial Comment:

                 

                Much as I like Filemaker, MS Access has way more options/features for managing multi-column value lists.

                 

                I've recently requested the following features from FM inc's feature request form:

                 

                The ability to sort on a column if it is hidden.

                The ability to base the sort on the order specified in the referenced relationship.

                To be able to use auto-complete when first column is a hidden number field.