9 Replies Latest reply on Feb 10, 2016 11:06 AM by siplus

    How do I view a field in another table?


      I have two related tables.


      I have no trouble viewing related data from Table 1 in Table 2.


      But sometimes ALL I want to see is a specific field (in this case the serial number) in the currently VISIBLE Table 1 record and view it in Table 2. The visible Table obviously might not be related to the current visible record in Table 2.



      I have tried every kind of field type, calculation, lookup, and script I can think of, but it (of course) pulls the number from a related record, not from the currently VISIBLE record.



      This should be dead simple and I can't figure it out.

        • 1. Re: How do I view a field in another table?

          When you use the term "see a specific field" you need to be thinking about layouts and how they work with TOs'


          First rule to learn is that Layout are bound to Table Occurrences and Layouts, and there constituent graphical objects, respect the settings of the relationship graph.


          Context is everything!


          Lets say you have tables "T1" and "T2", "T1" and "T2" Table Occurrences on the relationships graph and the relationship is such that T2 is the many side of a 1 to many relationship...


          So we have schema:







          and a equijoin ("=") relationship that links T1 and T2 as stated.


          From a layout based on Table Occurence "T1" you can build a form view that shows all of the fields from T1 and, if you want, a portal into T2. The T2 portal will only show data from T2 where there is a valid relationship. A portal control is the preferred object to show the many side of the equation from the 1 side.


          If you put an edit box control NOT IN A PORTAL from T2 on the layout in question it will show data from the FIRST RECORD in T2 where there is a relationship match regardless of how many matches between T1 and T2 exist.

          If you navigate through the T1 records you will "see" the data from T2 that is related to the current T1 record.


          If you go to a form type layout based on T2 with the same setup you can see all of the data in the T2 fields. If you put an edit box control from T1 on the layout then it will show the related data across the relationship.

          If you navigate through the T2 records you will "see" the data from T1 that is related to the current T2 record. Because this is the Many side of the relationship the field from T1 on the layout will only show a different value when you navigate to a T2 record that matches a different T1 record across the relationship.


          Having said all that your question is not completely clear so you should explain in detail with specifics of what you have done and what is not working as you expect.

          Posting the file is also a good idea so we can see the issue for ourselves.

          • 2. Re: How do I view a field in another table?

            Are you saying there's no way to see a field from an open record until it has been related directly?


            Let's say I have a table called VIEWER. It never needs to pull data from anywhere, it's only used to display data.


            Okay, let's say I have Table 1 with a serial number and Table 2 with a serial number. Both are open to a random record.


            I want the VIEWER (Table 3) to display the serial number from Table 1 and underneath it to display the serial number from Table 2. I don't want to do anything with the data (yet), I just want to see the two numbers. I want the VIEWER to tell me which records are front and center on the two open tables (which may not all be visible in the window if there are several of them stacked on top of each other, for example).


            So there's no "view field from open record" field or calculation or scripting option in Filemaker unless you copy and paste something from the other (open) record into the VIEWER first to relate them?  I don't need to relate them. I only want to view them.

            • 3. Re: How do I view a field in another table?

              correct! there is no way directly to know which fields/records are open in various windows (without focus) from the current front-most window (relationship or not).



              • 4. Re: How do I view a field in another table?

                Filemaker drives me crazy. I run my entire business with this thing and I really wish I had chosen something else.


                • Filemaker knows which tables are open.
                • Filemaker knows which record is visible at any given time.


                How do I access that information?

                • 5. Re: How do I view a field in another table?

                  Define "Open"?


                  Again, I suggest you post the file and some specifics.

                  • 6. Re: How do I view a field in another table?

                    You're a data entry operator...

                    Each table has a number of records. Each record has a serial number.



                    Table 1 is open on the right side of the screen. You can see it. It is "open" to Record #5 (Record 5 is visible to the operator).

                    Table 2 is open on the screen. You can see most of it (it's partly behind Table 1). Record 3 is visible.



                    On the left side of the screen is Table 3 which I call VIEWER.


                    The Viewer has a column of fields for serial numbers (it doesn't matter how they are organized, you just have to be able to see a few at a time and it's unlikely there would ever be more than 2 or a few).


                    I want the Viewer Table to display the serial numbers of the visible records. In other words, Filemaker knows Table 1 and Table 2 are open/visible. It knows Table 1 is open/visible to record 5, and Table 2 is open/visible to record 3.


                    I want the serial numbers of Table 1 Record 5 and Table 2 Record 3 to be displayed in the Viewer Table (Table 3).

                    • 7. Re: How do I view a field in another table?

                      The only way to make something like this work is through scripting. You can set a global field (or fields) in the "viewer" table to detect which record is currently visible, then use a Script Trigger to set that global field, which points to the target table and establishes the relationship.


                      Or, more simply, just put a merge variable on the Viewer layout and set it via the same method.

                      • 8. Re: How do I view a field in another table?

                        I have a mountain of work to attend to now but I will try your suggestion later today and report back to you.

                        • 9. Re: How do I view a field in another table?

                          So you have a field in T1, say SerialNumber, and you want to see in T2, which is in a different window, what value SerialNumber has in the T1 record that you are looking at in Window 1 ?




                          1)  Create a X relationship between table 1 and a TO of table 2, call it SetGlobal


                          2) Create a global field in T2, let's call it gPeekinT1


                          3) Create a script "OnWhatever" which simply does Set Field [SetGlobal::gPeekinT1; T1::SerialNumber]


                          4) In layout mode, on the T1 layout, enter Layout Setup, go to Script Triggers and attach the OnWhatever script to both OnRecordLoad and OnRecordCommit.