4 Replies Latest reply on Dec 4, 2014 2:00 PM by DanielHewlette

    Filtered Value List - Showing Second Field in Reports



      Filtered Value List - Showing Second Field in Reports


      I've setup a simple CRM type system where I have a "Companies" table and an "Inventory" table. The Companies table has a field called "company type" to include things such as "customer," "vendor," "partner," etc.

      I have a value list that successfully filters out all Company types except "vendor." Setup through a Companies TO (Inventory_Companies_Vendor) related to Inventory via a connection that only allows records through if a calculation field (in inventory) that always say "vendor" matches the CompanyType field in the Companies TO. The value list selects from this Inventory_Companies_Vendor TO the CompanyID in field one, CompanyName for field two. It only includes related values starting from Inventory, and only shows the second field value (CompanyName) in all the views (record, table, and list...even in "preview" mode for printing).

      So far it works exactly as expected and I think it is setup as suggested.

      Issue is when I export records or go to print a list view of Inventory, the Vendor Inventory field (CompanyName from the Vendor value list) always shows the CompanyID of the Vendor (field one of the value list) and not the CompanyName (field two of the value list) of the Vendor.

      Whats the best way to carry the Vendor's CompanyName through to XLS exports?


        • 1. Re: Filtered Value List - Showing Second Field in Reports

          Add that field from the related TO to your layout. Both the Specify fields dialog box and the field picker can be used to do that. You select the TO first from the drop down found in both tools, then select the field for your layout.

          • 2. Re: Filtered Value List - Showing Second Field in Reports

            Phil, as always, appreciate the fast response and help.

            Unfortunately, I've tried that and it doesn't work--it always shows one specific Vendor, regardless of the actual Vendor chosen from the popup menu's Vendor value list.

            I think the issue is that the Inventory records reference the same actual table (Companies, but via different TOs, one of which is filtered) for both the Customer and the Vendor. So when an Inventory record is related to both "Customer A" and "Vendor 1" (both originating from the same actual table, but different TOs--one directly to "Companies" and one to the filtered "Inventory_Companies_Vendor" TO), "Customer A" CompanyName is correctly displayed/changes based on the record, but "Vendor 7" (i.e. the wrong one) is always displayed, even if "Vendor 1" is selected from the filtered value list popup. Adding "Inventory_Companies_Vendor:CompanyName" is what is displaying the incorrect CompanyName for the Vendor. The same Vendor's CompanyName is always shown, no matter the record.

            Hope that makes sense.


            • 3. Re: Filtered Value List - Showing Second Field in Reports

              You need this relationship:


              Your field names will be different and you may need to add a TO to support this that has a different name than companies, but using the above example as a point of reference, you can add the needed name field from Companies to your inventory layout to show the name of the company for which you selected the ID via your value list.

              • 4. Re: Filtered Value List - Showing Second Field in Reports

                That worked perfectly. Took me a second to realize what you were saying, but I've got it now!

                Just had to add a new TO for Companies and relate Inventory:Vendor to Companies 2:CompanyID and place Companies 2:CompanyName on the Inventory table layout.

                Thanks for your help. Makes interpreting these reports much easier.