2 Replies Latest reply on Apr 7, 2013 8:42 AM by ericjlawson

    Value list - include only related values from ...

    ericjlawson

      Hi,

       

      I am really stuck on this and I think its do do with the tables and relationships.

       

      I have the following tables that are relevant

       

      CLIENT (PK is ClientID)

       

      who has a

       

      QUOTE / INVOICE (PK is Client ID, Invoice ID)

      (same entity just a different status on the record)

       

      the work performed is at a Client

       

      LOCATION (PK is ClientID, LocationID)

       

      A Client has many locations

      A Client as many Invoices

      An Invoice has one Location

      An Invoice has one Client

      -----

       

      Some of the clients are Real Estate agents and have many properties so I have a LocationName filed on the QUOTE / INVOICE table so that when the document is emailed to the Client it will show the LOCATION related to the work. I am struggling to expose the LocationName field from the LOCATION table on the Invoice layout.

       

      I can get it to work if I configure the Value List (from field) to show all values. However I only want to show Locations for the Client the Invoice is for.

       

      The app logic is

       

      Step 1 - open a Client record on the Client layout and then press Create Invoice button

      Step 2 - now on the Invoice layout with a new Invoice record, with Client Name prepopulated (simple, just like the Invoice starter app from FM12)

       

      The Data viewer shows the correct Client ID in the Invoice table (as picked up from the Client table). This is obvious as the Client Name correctly displays on the Invoice layout.

       

      I have a relationship defined on Client ID between CLIENT and INVOICE.

       

      I have tried creating a relationship between INVOICE and LOCATION but with or without it, the list of locations is either empty when I use the "include only related values from ..." option or shows all records with I dont.

       

      I have tried using a relationship for both directions.

       

      - Invoice (Client ID) > Location (Client ID)

      - Location (Client ID) > Invoice (Client ID)

       

      This produces the alias table and when I add the Client ID attribute of that table to the Watch list it has no value. Clearly thats related to the problem, but I have no idea if its a cause or symptom.

       

      Any help would be appreciated.

       

      Regards

      Eric

        • 1. Re: Value list - include only related values from ...
          LyndsayHowarth

          Seems to me, Eric, that you don't have any direct relationship between Client and Location.... it seems to always be via Invoice.

           

          If you have another relationship from Client to Location using Client ID then your "Client Locations" could be used for the filtered valuelist.

           

          Alternatively you could do it in Invoices and have a self relationship by Client ID to then use all the "Client Invoice Locations" but that would not give you any Locations which have not been Invoiced before.

           

          HTH

           

          - Lyndsay

          1 of 1 people found this helpful
          • 2. Re: Value list - include only related values from ...
            ericjlawson

            Hi Lyndsay,

             

            Thanks for your reply the other day. I re-examined those aspects but could not resolve the problem. I then tried to create a simple portal on Locations from Client and that was empty too so I new I had some basic error going on.

             

            It literally gave me a headache so I have left it for a couple of days and have now fixed it in 10 minutes. I was allowing the user to do data entry directly into Locations without navigating from Client so the Client ID was not being populated in the Location table. Clearly this is going to upset the Value List.

             

            Anyway, thanks again.

             

            Regards

            Eric