8 Replies Latest reply on Dec 19, 2012 12:47 PM by MorkAfur

    How to use a field on a layout in a query

    MorkAfur

      Title

      How to use a field on a layout in a query

      Post

           I have a query where I'd like to display the (one or more) roles a particular user has. But, to narrow down the query, I need to use the current user value from one of the form fields.

           I've tried adding an additional where clause that references the table field, but that doesn't work since I'm basically saying where user = user.

           What's the trick to reference a form field and say something like:

           <where clause>

           and

           u.user_name = <user value on form>

           Hope this question is clear.

           Look forward to suggestions.

           - m

            

            

        • 1. Re: How to use a field on a layout in a query
          philmodjunk

               "Where Clause"

               So this is the ExecuteSQL function or is it the Execute SQL script step?

               Can you post the SQL that you have or the Full ExecuteSQL function call?

          • 2. Re: How to use a field on a layout in a query
            MorkAfur

                 Hi Phil,

                  

                 Here's the ExecuteSQL query:

                 ExecuteSQL ( "select distinct r.rol_name from projects pj, projects_tasks pt, tasks t, people p, people_role pr, roles r
                 where  
                    pj.pro_id = pt.prt_profk
                    and
                    pt.prt_tskfk = t.tsk_ID
                    and
                    t.tsk_peoFK = p.peo_id
                    and
                    peo_id=pr.prl_peofk
                    and
                    r.rol_pk=prl_rolfk
                   " ; "" ; "")

                 The problem with this query is it will show all records regardless of who the current user is on the layout. I want to restrict it to show the roles for just the user who's name is shown on the layout. Therefore, I need to add another condition, but I'm not sure how to reference a layout "field".

                 How to display this list is another question. Perhaps I should create a new field to hold the list and then that "list" field could vary from record to record showing the roles the user has.

                 Thanks in advance Phil.

                 - m

            • 3. Re: How to use a field on a layout in a query
              philmodjunk

                   Use the ? parameter to insert data from your current record ( or a record related to it) into your SQL expression.

                   So you could add to your WHERE clause like this:

                   u.user_name = ?

                   and then you can put a reference to the field in the parameters listed after the SQL expression:

                   " ; "" ; "" ; YourTable::YourFIeldWithData )

                   How to display this list is another question.

                   I'd use Char ( 9 ) as the field separator and ¶ as the record separator and set up a large field with either a scroll bar or set to slide up/resize enclosing part to display this data. You can set tab stops via the inspector's appearance tab to control the width of each column of data produced by this function call. The result is quite similar to a "read only" portal.

              • 4. Re: How to use a field on a layout in a query
                MorkAfur

                     Yeah, tried that:

                      

                     ExecuteSQL ( "select distinct r.rol_name from projects pj, projects_tasks pt, tasks t, people p, people_role pr, roles r
                     where  
                        pj.pro_id = pt.prt_profk
                        and
                        pt.prt_tskfk = t.tsk_ID
                        and
                        t.tsk_peoFK = p.peo_id
                        and
                        peo_id=pr.prl_peofk
                        and
                        r.rol_pk=prl_rolfk
                       and
                       p.user_full_name=?
                       " ; "" ; PEOPLE::PEO_FULL_NAME)

                     Just gives me a "?"

                     Is this what you meant?

                     Thanks,

                     - m

                • 5. Re: How to use a field on a layout in a query
                  philmodjunk

                       My sympathies. Yes, that is what I meant and the ? result, if the ? remains when you click in the field, tells you that something is wrong with your SQL syntax, but gives us no clue as to what is wrong with the syntax. If data apears when you click into the field, the ? is just telling you that yoru field is too small to display the data.

                       Did you get a valid result before you added this last detail?

                  • 6. Re: How to use a field on a layout in a query
                    MorkAfur

                         Yes, I can get results, but being able to add a form reference (using a query parameter) doesn't work at all. Using a query parameter with a form variable -- even when I don't get a "?" for the result, I just get a blank value in my custom dialog to display things. :(

                         Have you ever gotten a form reference (a field on a layout) to work as part of a query in addition to the query itself?

                         ------------------------

                         Do you think FMP will make this easier? Also, not being able, as I understand it, to have unbounded fields (just for display) makes things more difficult. For example, just doing an SQL to populate some field for display where that field isn't bound would be WONDERFUL!

                         I would also like to be able to build drop downs for selection entirely using SQL. Then if a user picks a project form a project drop down, he would only see tasks for that project (assuming the database were setup that way 1:M). No need for the data in the dropdowns to be bound at all. They could be added to other fields in a script or whatever if wanted.

                         Look forward to hearing back.

                         thanks,

                         - m

                          

                    • 7. Re: How to use a field on a layout in a query
                      philmodjunk
                           

                                Have you ever gotten a form reference (a field on a layout) to work as part of a query in addition to the query itself?

                           Yes. I find it much easier that doing "text surgery" on the SQL expression to incorporate data from the current record's fields.

                           Do you think FMP will make this easier?

                           I have no idea. I have found that SeedCode's SQL Explorer of some help in constructing SQL queries.

                           You mention 2 potential new features you'd like to see. I suggest that you post them using http://www.filemaker.com/company/contact/feature_request.html

                           For me, I can see the advantage to "unbound" layout objects--something that is possible to a limited degree using merge variables with a conditional format expression that uses Let to assign a calculated value to the variable, but having used them in MS Access I also am aware of the trade offs. Since the calculation would be specific to a given layout, you end up copying and pasting the object or its expression from layout to layout if you need it on more than one layout--and then if you have to update it, you have to carefully revisit eacn and every layout to perform identical updates to each--where an updated calculation field would accomplish the same update from a single update in fhe field definition. So if FileMaker ever offers this, I hope we keep the option to do data level calculations as well so we have both options at our disposal.

                           And yes, I think a conditional value list that allowed one to utilize a query instead of just references to two table occurrences, would be one of several improvements that I'd like to see made to value lists.

                      • 8. Re: How to use a field on a layout in a query
                        MorkAfur

                             Phil -- Good idea....I just submitted three new feature requests.

                             I was impressed with the SQL offering in version 12, they just need to do more with it (like actual error handling (replace the "?"), INSERT, DELETE, and UPDATE, etc.).

                             Thanks,

                             - m