13 Replies Latest reply on Feb 12, 2011 11:25 AM by LewisLorenz

    Getting name of external file in table relationship

    LewisLorenz

      Title

      Getting name of external file in table relationship

      Post

      I am using FMP 7. I am trying to get the name of the database file in a relationship table. I do not know how to do this since upgrading from FMP 6.

      In FMP 6 I was able to get the name of the related database file using the RelationshipInfo design function. I used that file name to create a list of the fields from the related database. The interface allowed my user to select a relationship in the front database and then be able to select a field name from a list containing the names of the fields in the related file. The list changed via a calculation field that extracted the file name from the relationship information and then getting the default list of fields from the related database.

      i understand that the relationships in FMP6 are now tables in FMP7 but the related database file name is no longer avaiable in the new table RelationInfo design function.

      How can I achieve the same functionality in FMP7 as I described doing in FMP6? Is there a way to retieve the file reference name in a table?

      Any help is appreciated.  Thank you.

        • 1. Re: Getting name of external file in table relationship
          philmodjunk

          I know how you do this in FileMaker 10 and 11, but skipped from 5.5 to 10 when I upgraded and am unclear as to how much the interface has changed from 7 to 10.

          In these more recent files, you'd open Manage | Database | Relationships to get to the relationships graph. I believe version 7 has a different menu option, but you get to the same graph. If so, open the relationship graph and double click one of these boxes to bring up a dialog box for that table occurrence (that's what we call these boxes) and see if you can find an option in the dialog (in my version it's a drop down) where you can specify "add FileMaker data source". This option should open a dialog where you can select the file, then opens a dialog with a list of tables from that file where you can select the table you want.

          Since it's unlikely that you would want to change an existing table occurrence to point to this table, you'll need to figure out how to add a new "box" to your graph then double click it and follow the above steps to point it at your table in an external file. There's probably a button at the lower left corner of your window that will do this for you.

          • 2. Re: Getting name of external file in table relationship
            LewisLorenz

            I need to be able to do what you descibe programmattically, in a calculation field.

            In FMP6 I was able to use the Design function to retireve the name of the file that owned or possessed the related field.

            The RelationInfo design function in FMP7 does not give this external file name.

            I need a way to retrieve that name insdie a calculation field.

            • 3. Re: Getting name of external file in table relationship
              philmodjunk

              If you create a table Occurrence in your relationship graph named ExternalTable.

              Then you can refer to ExternalTable::fieldName to refer to a field in that related table.

              • 4. Re: Getting name of external file in table relationship
                LewisLorenz

                This will not return all of the field names in the related file.

                What I have done in FMP6 is use the Design function FieldNames to get all the fields in the related database.

                The help file for this function says, in part, that "FieldNames(fileName;layoutName)":

                Returns a list of the names of all fields on layoutName, in fileName file, separated by carriage returns. Related fields are displayed in tablename::fieldname format.
                If layoutName isn't specified, then the field names of the first table created (the "default table") will be returned.
                Note   If FieldNames returns a question mark (?) or the name of only one field, go to the Specify Calculation dialog box and make sure the Calculation result is text. Also, you can increase the size of the field on the layout to show more field names.
                Examples
                FieldNames("Customers";"") returns a list of all the fields in the default table of the Customers database file.

                I used the RelationInfo function in FMP6 to get the name of the related file. In FMP6 the first carriage return-separated value returned in the RelationInfo function was the name of the related file. This is what I need for the fileName parameter in the FieldNames function. And, in my solution, this parameter changes as the user selects from a variety of Relationship/Table names. I need to be able to get the filename the table is built on -  the "File" selected in the Specify Table dialog in the Relationship graph.

                Think of a relationship between files/tables as consisting of a "left-side" file and field pointing to a "right-side" file and field. The "RelationInfo(fileName;tableName)" design function was changed in FMP7 so that the first value returned by the function is the name of the database file containing the table ("left-side" file); the second value is the related field in the owning database file ("left-side" field); the third value is the name of the related field in the table (the "right-side" field name). No where does this function give the name of the related database file (the "right-side" file).

                • 5. Re: Getting name of external file in table relationship
                  philmodjunk

                  I think I'm missing some part of the big picture of what you want to do here.

                  What is the purpose of getting this list of field names from another file? Would you post an example?

                  I originally assumed you needed to access the data in the other file--which can be done in much simpler fashion than this in both Current versions of FileMaker and in FileMaker 6 as well.

                  I think I can conceive of a way to get what you want, but prefer to get a clearer picture before I suggest something that might be way, way off base here.

                  • 6. Re: Getting name of external file in table relationship
                    LewisLorenz

                    I have a document assembly solution that consists of three database files: 'Documents', 'People', and 'Contacts'. (All of the database files have a field named 'ID_Person'.) The 'Documents' file allows the user to define documents that consist of text paragraphs. The text for these paragrapghs are stored as records in the 'Documents' database. The user has the option of embedding merge fields in these paragraphs which will be replaced with data beforre the document is printed. The merge fields are formatted the same as FMP merge fields embedded in text objects on layouts. An example paragraph would look like:

                    "Hello <<Person::Salutation>>. Please confirm that <<Person_Email::Contact_String>> is your current email address."

                    In this example I have created 2 tables in the 'Documents' database. Table 'Person' is a relationship to the 'People' database file. The relationship is created by making the 'ID_Person' field in 'Documents' equal the 'ID_Person' field in 'People'. The same concept for the 'Person_Email' table: the relationship is created by making the 'ID_Person' field in 'Documents' equal the 'ID_Person' field in 'Contacts'.

                    Before a document is printed a script will parse each paragraph and replace each merge field with the results of the GetField() function, using the extracted merge field as the parameter. This part works great.

                    While composing the paragraphs for the document, I give the user the option of choosing which merge field to embed in the paragraph. The user selects a table/relationship name (selected_relation) from a popup list containing the names of the tables/relationships in the 'Documents' database. This value list is derived from the contents of a calculation field that  populates itself using the TableNames('Documents') design function. Using this selection I use Realtioninfo('Documents', selected_relation) to set a field (selected_database) with the name of the database of the related field from that relationship. This formerly was the first value in the carriage return-separated result. The user is them able to set a field (selected_field) with a field name from a popup list containing all the fields in the selected_database. This value list is derived from the contents of a calculation field that  populates itself using the FieldNames(selected_database, "") design function. Once these two selections are made, I have a button with a script that combines the selected_relation and selected_field into a merge field string that is inserted into the paragraph.

                    My problem is with getting the related database file name using the RelationInfo() design function in FMP 7.

                    • 7. Re: Getting name of external file in table relationship
                      philmodjunk

                      That's an intriging setup!!

                      I think it can be done with different functions, but don't have time to pull it all together right this moment. I'll check back tomorrow and if you are still seeking a solution, I'll see what I can come up with.

                      • 8. Re: Getting name of external file in table relationship
                        philmodjunk

                        After mulling that over a bit, I think I see a way to do this, though it's more complex than your original approach and requires some scripting.

                        To reflect back what I understand you to want  here:

                        You want to select a table name in field 1 and then want to select a field from that table in a list in field 2 that only lists values from the selected table. That it?

                        A combined list of field names from all relevant tables may be easier to set up and more useful to the user, but that's just a slightly different variation on the same theme.

                        Here goes:

                        Define a table, TableData, with two fields: TableName, FieldList

                        Write a script that updates the records in this table. You can attach this script to a button that you click each time you modify a table definition or set it to run everytime the field is opened:

                        Go to Layout [TableData]
                        Show All Records
                        Delete All Records [no dialog]
                        Set Variable [$TableList ; List ("Table1" ; "Table2" ; "Table3" ]//list the tables in the file from which you want the field names
                        Loop
                           Set Variable [$I ; $I + 1]
                           Exit Loop If [ ValueCount ( $TableList ) < $I ]
                           new Record/Request
                           Set Field [TableData::TableName ; GetValue ( $TableList ; $I )]
                           Set Field [$TableData::FieldList; FieldNames ( Get ( FileName ) ; GetValue ($TableList ; $I ) ]
                        End Loop

                        This also requires layouts for each table where you place each of the fields you want listed in this table. In the script example, these layouts would have the same name as their table, but you can modify the expression used in the FieldNames function to refer to a modified layout name if you want.

                        Now you can define a value list for tables that refers to TableData:TableName for the table names and another value list that refers to TableData::FieldList for a list of field names. If you make this second list a conditional value list, you can select a table in one field and see only the field names for that table in the value list of a second field.

                        • 9. Re: Getting name of external file in table relationship
                          LewisLorenz

                          Thank you PhilModJunk. Your approach will do the trick. I don't like having to manually update the fields since computers are supposed to automate such mundane tasks. It is mind boggling that FileMaker would limit the ablility to automate this procedure by deleting the file information from the RelationInfo() function result.

                          • 10. Re: Getting name of external file in table relationship
                            philmodjunk

                            On the other hand, I find it's very, very rare to want to simply list all fields in a table. By using the layouts, you can control which fields are included and which aren't from any given table.

                            • 11. Re: Getting name of external file in table relationship
                              LewisLorenz

                              I have been using FileMaker since version 2 and I have never known the program to remove functionality in an upgrade. One of the biggest complaints I have had with FMP has been its constraints on programming. I like it when I am allowed to do something easier in my solutions and this has usually been because constraints have been lifted and new functions have been made available in ScriptMaker. For example, I have been giddy with being able to pass script parameters to my scripts. That new feature alone has been worth upgrading to FMP 7.

                              I cannot see the reason for the removal of functionality from the RelationInfo() function. A relationship always has two files in it: a left side and right side file. Sometimes those files are the same, but sometimes they are different. I used to be able to get both files in FMP6 using RelationInfo(). I can't get it now using FMP7. RelationInfo() gives only the name of the file owning the table; which is kind of dopey, because you have to give the function the name of file owning the table as a parameter - the left side file is already known! There is no reason to limit the result to the name of that file only. I want the name of the right side file like I was able to get before.

                              I won't stop using FMP because of this. But, going backward in an upgrade - re-constraining programming funtionality - makes no sense.

                              • 12. Re: Getting name of external file in table relationship
                                philmodjunk

                                Hmmm, you can perform a script in the external file that then captures the data using Get (fileName) as the parameter for the file name whenever you don't want to use the actual file name (and you wouldn't here).

                                The change from 6 to 7 was a major, major change in FileMaker. Many features had to change due to the fact that files now can have more than one table for storing data. For one thing, we no longer name relationships, we name table occurrences and these aren't quite the same thing.

                                I suspect this change in RelationInfo was a casualty of that change.

                                One lack that I've noticed is that it was easy in older versions to create multiple copies of the same relationship with just a few mouse clicks and then I could edit each relationship to produce a group of relationships to the same table, but linking to a different field very quickly. I can't find any equivalent to that in recent versions of FileMaker.

                                That said, I wouldn't use FileMaker 6 or even 7 if I had any choice at all in the matter. FileMaker 10 and 11 offers so many more features for the developer--including variables, conditional formatting, script triggers (similar to visual basic event handlers), and portal filters--just to name a few. Then add custom functions, database design reports and a script debugger in FileMaker Advanced...

                                If you haven't already, you ought to download a free trial copy of FileMaker 11 and see for yourself.

                                • 13. Re: Getting name of external file in table relationship
                                  LewisLorenz

                                  Your suggestin jogged my mind to create a workaround for this problem.

                                  In each database I defined a global calculation field named 'Filename' the content of which is the result of the Get(Filename) function. I have a calculation field named 'Database Name' in the 'Documents' database that uses the user's selected table occurrence to retrieve the contents of the 'Filename' field in the table occurrence: GetField(selected_relation & "::Filename"). I can then use the contents of 'Database Name' in the  same maner as I used the result from RelationInfo().

                                  lt works great. I can use the Design functions to get all the lists I need. The only drawback is that I have to be sure to define the 'Filename' calculation in every table I intend to use this way, but this achieves the automation I expected.

                                  I have checked out FMP 11 and am eager to get the funds to up grade all the machnes in my office. I am all for anything thing that expands my programming options.