3 Replies Latest reply on Sep 24, 2012 10:23 AM by greglane

    Converting a TOG set to ExecuteSQL()

    DrewTenenholz

      All --

       

      I'm looking for some advice on how to/if it is possible to convert something complex that I made work on the relationships graph into something 'better' with ExecuteSQL(). I've already used the excellent SeedCode SQLExplorer to create a new & useful query, now I'd like to do something with an existing task I solved in the 'old' way, which seems harder...

       

      The basic goal is to display the current task_checklists for a user in the system. So, I have TOs for: Accounts, Projects, Tasks, and Task Checklists which can be linked by IDs in the normal way.

       

      BUT-- I have set up a way to allow one user to see the check lists for other users (e.g. supervisor can see the check lists for all of their workers), per the business rules. Of course, the old-fashioned and useful way in FM has been to user a multi-line text field (Account_Checklist_Visible_Names) on the user preferences record that allows me to make a relationship on the graph between it and a stored, calculated field on the Projects table that combines (project lead¶, project second¶, project consultant). In FM, this gives me all projects that the user can see. Then, I can further link TOs to Tasks & Task_Checklists with simple IDs. Finally, I can filter the portal to display the 'current' task_checklists (by virtue of the task being in the future), and get a nice portal.

       

      I've been struggling to see if there isn't a way to link two tables in ExecuteSQL() using my multi-line fields, but it seems to be impossible. Can anyone confirm this? Is it possible?

       

      -- Drew Tenenholz

       

      P.S. I know that if I were creating the system from scratch, I could create new join tables for the userX is allowed to see userX & userY & UserZ records, and that would help somewhat. I guess I'd also have to create a different join table for projects and the people working on them as well. But, rather than reinvent the entire system which already works quite well, I'm hoping to add on instead of redesign....

        • 1. Re: Converting a TOG set to ExecuteSQL()
          greglane

          Hi Drew,

           

          Here's a calc that would provide a return-separated list of project IDs that should be visible to each user.

           

          ExecuteSQL(

           

          "SELECT id

          FROM project

          WHERE project_lead IN ('" & Substitute(account::Account_Checklist_Visible_Names; "¶"; "','") & "') OR

          project_second IN ('" & Substitute(account::Account_Checklist_Visible_Names; "¶"; "','") & "') OR

          project_consultant IN ('" & Substitute(account::Account_Checklist_Visible_Names; "¶"; "','") & "')";

           

          "";"")

           

          It's not a universal solution for matching multi-line fields in two tables, but it would work in the scenario you described where one of the multi-line fields is actually a combination of other fields. The checkbox field is transformed into a comma-separated list of quoted values, which is used by the IN operator.

           

          Greg

          • 2. Re: Converting a TOG set to ExecuteSQL()
            jbrown

            Greg,

            I have to say your FM Academy video and demo file on ExecuteSQL is an amazing tutorial. I listen to it at least once a week and am very excited about combing through my system to see where I can replace the TOs with the Execute. Its pretty cool. Your video, along with DevCon is helpingme understand the process well.  Kudos to you!

            • 3. Re: Converting a TOG set to ExecuteSQL()
              greglane

              Thanks Jeremy...I really appreciate that!

               

              Greg