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....