5 Replies Latest reply on Jul 3, 2013 5:40 PM by ingredlee

    How do I use a relationship to filer a pick list

    ingredlee

      I have an application where items in a table have a relationship to other items in the same table. This relationship is formed by an intersection table that has the primary key fo each item plus a comment about the relationship. I've created a pick list that contains all items in the main table when an item is picked from the list a new row in the intersection table is created. I would like the pick list to show only items that do not already have a relationship with each other. I have tried may iterations without success. I can get a list of the items I want to filter out. But everytime I try to apply that to the pick list, I am unsuccessful. I am quite experienced with SQL Server. But sometimes I struggle in trying to translate that to FileMaker relationships. Any help will be much appreciated.

        • 1. Re: How do I use a relationship to filer a pick list
          mikebeargie

          Hi, and welcome to technet,

           

          I have created a sample file to demonstrate this sort of method. It's a bit trickier since you're using an intermediary table. This shows the schema needed to calculate a related value list from a selfjoin. However the calculation pulled through the selfjoin is incomplete (just showing all records now). You said you were familair with SQL server so you can probably write the rest of the SQL query from here.

           

          You will need to update the ExecuteSQL() calculation field in the sample table to account for only showing the records you need. I didn't have time right now to work that out.

           

          Here's a guide though about everything in ExecuteSQL()

          http://www.filemakerhacks.com/?p=6406

           

          There might be other alternatives as well to doing this. But this seemed like the lightest weight way for me to calculate what you need.

          • 2. Re: How do I use a relationship to filer a pick list
            ingredlee

            Thank you. I will take a look at this and let you know if I have any more questions.

            • 3. Re: How do I use a relationship to filer a pick list
              ingredlee

              In looking at the guide about ExecuteSQL, is there a way to parse the results from a multi column query into distinct fields in a portal?

              • 4. Re: How do I use a relationship to filer a pick list
                mikebeargie

                Yes, you can parse data however you see fit. What I normally do is set a custom field separator (by default it's a comma, but you can set it to be something like |*|.

                 

                From there, my ExecuteSQL "sqldata" would look like:

                 

                field1|*|field2|*|field3

                field1|*|field2|*|field3

                field1|*|field2|*|field3

                etc...

                 

                Then you could parse it out with a few calculations:

                 

                To get a row of data: GetValue( sqldata ; recordnumber)

                To get a cell of data (IE field1): left( sqldata ; length(sqldata) - position(sqldata ; "|*|" ; 1 ; 1 ) )

                 

                The method most filemaker developers (I know of) are using for parsing temporary "scratch" data is the virtual list technique, where you store "sqldata" as a global variable (IE $$sqldata) and you have a table of scratch records that have fields calculating the values from $$sqldata based on the record ID.

                 

                IE:

                GetValue( $$sqldata ; scratchtable::recordid )

                 

                Here's good coverage of virtual listing:

                http://www.mightydata.com/blog/virtual-list-in-three-easy-steps/

                 

                I'd highly recommend using a copy of filemaker advanced if you don't already, and running your queries through the data viewer to see what is returned. You can also use script debugger and data viewer in combination to see what variable values are returned by your scripts.

                • 5. Re: How do I use a relationship to filer a pick list
                  ingredlee

                  I wanted to take the time to say thank you.

                   

                  Between your examples and the documents you suggested for reading. I was able to design the exact solution I was looking for.