8 Replies Latest reply on Aug 2, 2017 10:31 AM by JulioSandoval

    Grab External Source Name

    JulioSandoval

      Hello,

       

      I have a few databases that are linked by External Data Sources.

      I use a front end file just for the UI.

       

      I would like to know if there is a way to grab the External Database name for whichever table I am working in?

       

      Thank you all.

        • 1. Re: Grab External Source Name
          andersmonsen

          Have you tried this:

          ExecuteSQL ( "SELECT * FROM FileMaker_Tables WHERE TableName = 'YourLocalFileTO'" ; "" ; "" )

          It returns the following:

          1. TableName

          2. TableID

          3. BaseTableName

          4. BaseFileName

          5. ModCount

           

          "YourLocalFileTO" is the TO in your UI file that points to the external table. You also can narrow it down with this:

          ExecuteSQL ( "SELECT BaseFileName FROM FileMaker_Tables WHERE TableName = 'YourLocalFileTO'" ; "" ; "" )

           

          -

          Anders

          • 2. Re: Grab External Source Name
            JulioSandoval

            I am trying to understand how to use this;

             

            "YourLocalFileTO" <- Am I supposed to be hardcoding this for and placing this SQL Query on each table I want to do this on?

             

            And for FileMaker_Tables, is that used as it is?

             

            Im not well versed in SQL so excuse my ignorance.

             

            Thanks

            • 3. Re: Grab External Source Name
              andersmonsen

              One option is to create a script and set variables to this query for each table you want to check, if you're checking multiple tables.

               

              The only thing you need to change is YourLocalFileTO in the query. BaseFileName and FileMaker_Tables do not need to be changed.

               

              If your TO in the Relationship Graph (in the UI file) is called "Contacts_Data" for example, your query is:

              ExecuteSQL ( "SELECT BaseFileName FROM FileMaker_Tables WHERE TableName = 'Contacts_Data'" ; "" ; "" )

              Make note of the single quotes around the TO name.

               

              If your file is hosted it will include the name of the server in brackets, ie "Data File (server.domain)"

              1 of 1 people found this helpful
              • 4. Re: Grab External Source Name
                JulioSandoval

                Understood. I wil give that a try.

                I didn't know you can pass variables into a SQL query.

                 

                Thanks again.

                • 5. Re: Grab External Source Name
                  philmodjunk

                  ExecuteSQL ( "SELECT BaseFileName FROM FileMaker_Tables WHERE TableName = ?" ;

                  "" ; "" ; Get ( LayoutTableName ) )

                   

                  Might be useful in a script. This will return the BaseFileName for the current layout's Table Occurrence (TO). Change to a different layout and get a possibly different result for that layout.

                  • 6. Re: Grab External Source Name
                    andersmonsen

                    Yes, you can pass variables into an SQL query, but in this case I meant in the script itself.

                     

                    Script:

                    Set Variable [ $contactsSource ; "ExecuteSQL..." ]

                    Set Variable [ $invoiceSource ; "ExecuteSQL..."]

                    etc.

                    Your script then can check all the sources you want with individual SQL queries for each TO you want to check.

                    • 7. Re: Grab External Source Name
                      beverly

                      You can "pass" variables if they are the parameters. The "?" in the ExecuteSQL() and the variable is appended to the end. There woudl be two "placeholder ?" in the query.

                      ExecuteSQL ( <<query>> ; "" ; "" ; $parameterVar ; $parameterVar2 )

                      OR you can concatenate the TEXT of the query (it won't evaluate the variable otherwise):

                      " SELECT abc, def

                      FROM xyz

                      WHERE abc = " & $variable1 & "

                           AND def = " & $variable2 & "

                      Beverly

                      • 8. Re: Grab External Source Name
                        JulioSandoval

                        Phils post worked very easily and got exactly what was needed.

                         

                        Thank you all for your help! I appreciate it.