13 Replies Latest reply on Sep 5, 2014 1:31 PM by Steven_FL

    Relationship with out relationship

    Steven_FL

      Hi, I'm new to this forum.

       

      I want to make a centralized Tooltip solution. This is how I have it now:

      1. File with Tooltips Table
      2. Relate each other table (i.e. Addresses) to that tooltips table. (problem lots of work and gets messy)

       

      This is my Idea of the funtion I'm looking for:

      Get( from File "Settings" from Table "Tooltips" from Recordfield "xyz" ="sort by year"; field "explenation;"")

       

      This would make it super simple and i could copy past that button any where and it would work always.

       

      would be great to know if I can do it and how.

        • 1. Re: Relationship with out relationship
          Mike_Mitchell

          If I understand your post correctly, you could use ExecuteSQL to accomplish this task, as long as the Tooltips table has a table occurrence (TO) in the current file.

           

          Example:

           

               ExecuteSQL ( "SELECT Tooltips.explanation FROM Tooltips WHERE Tooltips.xyz = 'sort by year'" ; "" ; "" )

           

          Note that I'm doing this mostly in the blind and you might have to make some adjustments.

           

          HTH

           

          Mike

          • 2. Re: Relationship with out relationship
            PSI

            Steven,

             

            This is a perfect use for the script step ExecuteSQL()

             

            I attached a very simple example...

             

            John Morina

            PSI

            • 3. Re: Relationship with out relationship
              Mike_Mitchell

              John -

               

              Don't you mean the function ExecuteSQL ( ) (as opposed to the script step Execute SQL) ?  

               

              Good example, though.

               

              Mike

              • 4. Re: Relationship with out relationship
                PSI

                Yes! Function not Script Step...

                 

                My examples are using fields but you get the same result.

                 

                John

                • 5. Re: Relationship with out relationship
                  Steven_FL

                  Thanks alot. That really helps. to Mike and PSI

                  • 6. Re: Relationship with out relationship
                    Steven_FL

                    Dear John, I just looked at it and works great.

                    How do I have to change it so it comes from an external file

                    ExecuteSQL ( "SELECT Tip FROM ToolTips WHERE Link=?"  ; "" ; "" ; "NameFirst" )

                     

                    That table "Tip" is in file "Settings"?

                    The different file is set up in External Data Sources and is called Setting.

                    • 7. Re: Relationship with out relationship
                      PSI

                      Steven,

                       

                      Just put the Tip table on the graph in the main file and it will work. This is the same if you want to access global fields from another file.

                       

                      John

                      • 8. Re: Relationship with out relationship
                        Steven_FL

                        And it works like stated. Sensational John Thanks a lot. Really this changes my whole perspective of FM.

                        • 9. Re: Relationship with out relationship
                          Steven_FL

                          Dear John,

                          As this opens a total new perspective I have an other situation for ExecuteSQL

                           

                          These are the tables and their relationships: (arrow shows relationship)

                          relationships.JPG

                           

                          Seeing on "Account" all its "Persons" is simple with Portals. (but FM can't have portals within portal)

                           

                          so what I would like to see in a field on "Person" is:

                           

                          a. in a field "All Phones on "Person" all its numbers, (a person can have 1, 2, 3 etc. numbers) " Cell 777-555-6666  Direct Phone 222-444-555 Home 333-777-6666

                          b. in a field "Cell" on "Person" which shows just his cell, so the ExecuteSQL has to depend on two tables ("Phone" and "Phone Purpose") and two variables ("Phone::Person ID" and "Phone Purpose::Person ID" )

                           

                          How would the ExecuteSQL look for

                          a.     ExecuteSQL(      )

                          b.     ExecuteSQL(      )

                          • 10. Re: Relationship with out relationship
                            beverly

                            Remember that ANY table/table occurrence referenced in an ExecuteSQL call must be on the relationship graph. However, any "relationship criteria" is ignored, so any query to a TO will seem as if the base table is called. So you need not have any relationship to the table/T.O., but it must be on the graph for the query.

                             

                            Beverly

                            • 11. Re: Relationship with out relationship
                              Steven_FL

                              Dear Beverly,

                               

                              Thanks I understand that. Its easy and FM is a really great product.

                               

                              Have a great weekend

                              Steven

                              • 12. Re: Relationship with out relationship
                                PSI

                                Steven,

                                 

                                I simulate a portal in a portal with a field and relationship not SQL although it could work.

                                 

                                First of all SQL doesn't like spaces so you should remove spaces in field names. I can't tell what tables you really have based on the chart. I do hope phone purpose isn't a separate table from phone?

                                 

                                Assuming the PhoneID and Purpose ID are in the same table

                                 

                                The structure would be basically the same as the Tool Tips.

                                 

                                ExecuteSQL(sqlQuery; fieldSeparator; rowSeparator {;arguments...})

                                 

                                "

                                SELECT PurposeID, PhoneID

                                FROM the table?

                                WHERE PersonID=? ; ": " ; "¶" ; Persons::PersonID ) - don't quote me on the separators but i think it would work?

                                 

                                ----------------if you have multiple criteria like isolating a single type.

                                 

                                WHERE PersonID=? and PurposeID=? ; ": " ; "" ; Person::PersonID ; "Cell" )

                                 

                                Then you can use fieldSeparator to and a colon and space between the PurposeID and Phone and a Carriage return or comma space to separate the rows.

                                 

                                I attached a copy of Beverly's treatise on SQL. this will provide you with all the info you need on SQL for FMP.

                                 

                                John

                                • 13. Re: Relationship with out relationship
                                  Steven_FL

                                  Dear John,

                                  Thanks alot. I have now something to work with. We used in your company an CRM / ERP opensource called Opentaps. After 3 years with this product we are completly in mess, very poorly done, tons of bugs many languages etc etc. So I decided to go back to FM and write it myself.

                                  Regarding your statement "I do hope phone purpose isn't a separate table from phone?" Yes each of these are sepearte tables - might mare.

                                       Account
                                       Relationship

                                       PersonPhone

                                       PhonePurpose

                                   

                                  But I have to convert them so ...

                                   

                                  Thanks for pointing out the spaces for SQL.

                                   

                                  Your info really helped alot and I think I can make it now. Against thanks - very helpful. Wishing you a great weekend.