3 Replies Latest reply on Jun 30, 2016 2:01 AM by arnojansen1

    Find query help needed




      I am using FM pro advanced on mac. I have one table with supplier data and need help.


      A supplier can be in a certain city so I search on city first by typing that in in the find mode.

      Then I want to narrow that search down by using a drop down list with names of suppliers in that city only.


      My problem is when I go to find mode I get a list of every possible supplier in my data base and not related to the city I typed in already.


      your help is appreciated.


        • 1. Re: Find query help needed
          Johan Hedman

          To have a value list only showing related records for that city you need to safe that into a global field and then build a relationship from that global field to your suppliers. When you have that relationship you build a new value list that goes over this new relationship and in search mode you will only see suppliers for that city.

          • 2. Re: Find query help needed

            Hi Arno,


            All you need to do is create another TO in your relationships graph, based on your original table, and create a self-join relationship. If you're not a developer and that all means nothing to you then here are some screenshots.

            Go to Manage Database, Relationships tab. highlight the table you want to copy, and click the double plus button. This will duplicate the table for you. Call this something obvious (the convention as you start to develop more is to have the name of the table in capitals and the function it is serving in lowercase appended after 2 underscores).


            Then join the tables together. In this case, you want to see suppliers in matching cities, so grab CITY from one TO and drag it to CITY in the other TO (TO = table occurrence which is what we call the representation of tables in the relationship graph)


            For the sake of ease, this is all you need to do for this relationship to work so we won't go any deeper into relationship options.


            Your Relationship graph should look something like this...


            Relationship Graph.png


            Now go to your layout. The easiest way to show matching suppliers is to put in a portal to the new TO you have created.


            You can see in the next image that I have added a portal based on the new TO in my Supplier Data layout. This will have the Name and City fields from the new TO in it.



            Then, if you go to Browse mode, you will see a list of suppliers in that portal who are from the same city.





            This is slightly different from your need for a drop-down, but that can be easily added in if it is an option that you need - I have deliberately kept this succinct and easy to give you an idea of the process.


            Hope that helps.



            • 3. Re: Find query help needed

              Thanks very much Johan and James that will give me something to work with and try to build in my database and see if that works for me