4 Replies Latest reply on Nov 20, 2014 6:42 AM by EmelieLeht

    Get data from the second field in a value list?

    EmelieLeht

      Title

      Get data from the second field in a value list?

      Post

      Hi!

      I am building a form for a shipping register. On this form I want to have a field Customer with a popup menu where the user easy can chose customer from a list. The customers are all listed in the table Contacts and have a field, Relation, which is set to "Customer". There is also a field in the shipping table with a field Relation which is auto-set to "Customer" adna relationship is defined between the Relation fields in these tables.

      I have so far managed to create a valuelist based on the relationship that shows all contacts marked as "Customer":
      First field = Relation
      Second field = Company name
      and I display only second fields in the valuelist.

      So far so good.

      However, I need to perform a find based on the company name to also get the adress for the company. But in the field with the popup menu, only the data from the valuelists first field is stored (according to http://www.filemaker.com/help/13/fmp/en/html/edit_layout.10.46.html)

      The link above suggests a lookup to retrieve the data from the second field but I do not see how this would work for me since the related records are all contacts marked as "Customer".

      How do I get hold of the company name for my choen value list item so that I can perform my find? Any suggestions welcome. Or if there are simpler/more functional ways to automatically retrieve the customer adress as soon as customer company is chosen I would be glad to know!

      Best regards,

      Emelie

        • 1. Re: Get data from the second field in a value list?
          philmodjunk

          If you have a relationship such as:

          Customer----<Invoices

          Customer::__pkCustomerID = Invoices::_fkCustomerID

          with _fkCustomerID set up on the invoices layout as a "use values from field" value list with __pkCustomerID as the "first field" in the value list....

          You can add any fields from Customer that you need to the Invoices layout and they will display data from the selected customer once you select a value in the value list. You can also enter find mode and specify find criteria in this field. But keep in mind that such a find, finds all invoices by that customer. You won't be able to use this method to find a customer that does not yet have an invoice in your database.

          • 2. Re: Get data from the second field in a value list?
            EmelieLeht

            Hi!

            Always nice to get help from you Phil, much appreciated!

            It seems like a cleaver way to solve it as you suggested. I think my problem then maybe lies within my Contacts -table. This table contains all company contacts, suppliers, customers and other contacts. I do not have a separate Customers-table. It is only the Relation-field that states the status of the contact. With your suggestion the setup would rather look like

            Contacts::__pkCompanyID = Invoices::_fkCompanyID

            If I create a value list based on this it would show all the contacts and not only my customers. That is why I based the relation on the Relation field instead and could pick out only the ones marked as "Customer".

            Would it be a better design to have different  tables for different types of contacts (customers, suppliers, others) and gather them all together with a portal in some way to display all contacts ?

            Best regards,

            Emelie

             

            • 3. Re: Get data from the second field in a value list?
              philmodjunk

              Ok, that's the next step of your project--limiting your value list to just those contacts that represent customers. I would use a "hardwired conditional value list" for that.

              See the "hardwired" CVL examples in "Adventures in FileMaking #1 - Conditional Value Lists" for two different ways that you can use to get such a value list.

              • 4. Re: Get data from the second field in a value list?
                EmelieLeht

                Wow! What a resource!

                Thank you very much. This will hopefully help me through!

                Best,

                Emelie