1 2 Previous Next 20 Replies Latest reply on Apr 10, 2009 7:50 PM by filler

    Repeating field lookup

    filler

      Title

      Repeating field lookup

      Post

      Is there a way to paste in repeating field 2 (a text field) lookups from another table based on repeating field 1 (a text field)?

       

      Details:

      Table 1 is Employees

      Table 2 is Meetings

       

      In a Meetings layout, I have repeating field Employee.  I would like to populate repeating field Company by pulling from Table 1.  (A relationship is based on Employee name.)  The Lookup function only populates the first repetition in Company.  I know I can create a script to do this, but I want to define the field in a calculation to automatically do it.

        • 1. Re: Repeating field lookup
          philmodjunk
            

          filler wrote:

          Is there a way to paste in repeating field 2 (a text field) lookups from another table based on repeating field 1 (a text field)?

           

          Details:

          Table 1 is Employees

          Table 2 is Meetings

           

          In a Meetings layout, I have repeating field Employee.  I would like to populate repeating field Company by pulling from Table 1.  (A relationship is based on Employee name.)  The Lookup function only populates the first repetition in Company.  I know I can create a script to do this, but I want to define the field in a calculation to automatically do it.


           

          Your best approach is probably not to use a repeating field, but to use a table where "company" resides in a series of related records. If you can describe your tables, fields and relationships in greater detail. We can discuss that option.

           

          Repeating fields can be very useful, but rarely.


          • 2. Re: Repeating field lookup
            filler
              

            Thanks.  I know it would be better not to use repeating fields and just have a connecting table.  I'm working on that solution separately, though there seems to be an inherent problem, outlined below.  I've managed to do workaround wonders with the repeating field (including creating custom functions to aggregate text) in the current db, but this particular problem is proving more difficult.

             

            Regarding the connecting table solution, there is an inherent problem if you have more than 1 portal per layout.  I have 4 Tables: Employees, Companies, Meetings, and then the Connections.  Within a Meetings table layout, I have 2 portals: Employee and Company.  Adding records within those portals creates a new Connections table record.  The problem is that the new Connections table records are only linked by the Meeting ID (since I'm adding them through portals on the Meetings table layout).  Therefore, I can do reports within the Connections table, but only summarized by Meeting ID.  The individual records in the Connections table are not related at all by Employee or Company.  That limits my reporting capability.

             

            If specific examples would help with either solution, I can explain further.  Thanks.

            • 3. Re: Repeating field lookup
              philmodjunk
                

              filler wrote:

               

              Regarding the connecting table solution, there is an inherent problem if you have more than 1 portal per layout.  I have 4 Tables: Employees, Companies, Meetings, and then the Connections.  Within a Meetings table layout, I have 2 portals: Employee and Company.  Adding records within those portals creates a new Connections table record.  The problem is that the new Connections table records are only linked by the Meeting ID (since I'm adding them through portals on the Meetings table layout).  Therefore, I can do reports within the Connections table, but only summarized by Meeting ID.  The individual records in the Connections table are not related at all by Employee or Company.  That limits my reporting capability.

               


              Proper table and relationship design will give you everything you need quite easily.

               

              Please describe how you relate each of your 4 tables to each other.


              • 4. Re: Repeating field lookup
                filler
                  

                4 Tables: Employees, Companies, Meetings, Connections (which aggregates info - theoretically)

                 

                Here are the table relationships:

                 

                Meetings -(by meeting ID)- Connections

                Employee -(by employee ID)- Connections(Employee instance) -(by meeting ID)- Meetings

                Companies -(by company ID)- Connections(Company instance) -(by meeting ID)- Meetings

                 

                Each record in Meetings table Layout has a portal for Employee and Company.  Adding to either portal adds a new record in Connections, carrying the meeting ID and either an employee ID or company ID.  The reason I created different instances of Connections is because without it, it unpredictably added to existing records in Connections, causing relationship errors.  The problem is that since each new entry in either portal creates a new record in Connections, I am only able to summarize by the meeting ID (the only common field in each new Connections record).  There just doesn't seem to be a way to use a Connections table to aggregate info from more than 2 tables and then summarize reports on anything but the ID from the table of the layout you're on (in other words, the Connections table can't summarize by the entries created through the portal; they can only do so by the entry info of the layout the portals are on).

                • 5. Re: Repeating field lookup
                  philmodjunk
                    

                  4 Tables: Employees, Companies, Meetings, Connections (which aggregates info - theoretically)

                   

                  Here are the table relationships:

                   

                  Meetings -(by meeting ID)- Connections

                  Employee -(by employee ID)- Connections(Employee instance) -(by meeting ID)- Meetings

                  Companies -(by company ID)- Connections(Company instance) -(by meeting ID)- Meetings

                   

                  Each record in Meetings table Layout has a portal for Employee and Company.  Adding to either portal adds a new record in Connections, carrying the meeting ID and either an employee ID or company ID.  The problem is that since each new entry in either portal creates a new record in Connections, I am only able to summarize by the meeting ID (the only common field in each new Connections record). 

                   

                  Ok, the last sentence is where you have a problem. You CAN summarize by either companies or employees via your relationships.

                   

                  There just doesn't seem to be a way to use a Connections table to aggregate info from more than 2 tables and then summarize reports on anything but the ID from the table of the layout you're on (in other words, the Connections table can't summarize by the entries created through the portal; they can only do so by the entry info of the layout the portals are on).

                   

                  Not true, as long as you've set up valid relationships, you should be able to get what you want.

                   

                  Which fields in which tables are set as auto-enter serial numbers?

                  Which parts of which relationships, if any, are set to automatically create a related record?

                   

                  Give me an example of the report/layout you want to see.

                   

                  The details of this have to be just right, but you should be able to make this work.

                   

                  • 6. Re: Repeating field lookup
                    filler
                      

                    Sorry -

                    Still interested in this Connections table issue, but the original question still stands:

                     

                    Is there any way to populate a repeating field by doing a lookup based on another repeating field in the same layout?

                    Thanks.

                    • 7. Re: Repeating field lookup
                      philmodjunk
                        

                      filler wrote:

                      Sorry -

                      Still interested in this Connections table issue, but the original question still stands:

                       

                      Is there any way to populate a repeating field by doing a lookup based on another repeating field in the same layout?

                      Thanks.


                       

                      No problem, bringing us back to the original question.

                       

                      Unfortunately, I don't think you can unless you break up your repeating field into individual fields and define a look up for each (yech!). Which is why I am trying to steer you away from that approach. :smileywink:


                      • 8. Re: Repeating field lookup
                        filler
                          

                        Which fields in which tables are set as auto-enter serial numbers?

                         

                        The ID field for each table is auto-entered serial number. 

                         

                        Which parts of which relationships, if any, are set to automatically create a related record?

                         

                        When a new portal field is added in either the Employee or Company portal on the Meeting layout, it automatically creates a new record in Connections.  The new Connections record reflects the Meeting ID and either the Employee or Company ID (whichever portal is used).

                         

                        Give me an example of the report/layout you want to see.

                         

                        I would like to be able to make reports by Employee and by Company.  I know I can do those within each table (Employee or Company).  But I don't want to use a portal for certain fields (like meeting details, since you can't slide up fields within each row of a portal).  So, ideally, I would create all reports from the Connections table.

                         

                        In the Connections table, I can summarize by Meeting ID, since all the new Connections records are made from the Meeting table (layout).  But the employees and companies within the Connections records are not related (other than by meeting ID), so I can't summarize by those fields.

                         

                        By the way, to complicate things - the reason I keep asking about populating repeating fields is because I really don't want to have to add company to each Meeting record.  I want it to automatically be added when I add employee (based on the company assigned to employees in the Employee table).  I know I can do this using the Connections table and a portal, but this is the one outstanding problem I have with my existing db using repeating fields.  I know there's got to be a way to do it with what I've got.  Thanks.

                        • 9. Re: Repeating field lookup
                          philmodjunk
                            
                          filler wrote:

                          Which fields in which tables are set as auto-enter serial numbers?

                           

                          The ID field for each table is auto-entered serial number. 

                          Well not exactly. Going by all the information, In company, Employee, and Meeting you have auto-entered serial numbers. In Connections, you have matching number fields that are NOT auto-entered serial numbers. That should be obvious, but I've learned not to assume anything when participating in this forum.

                           

                          Which parts of which relationships, if any, are set to automatically create a related record?

                           

                          When a new portal field is added in either the Employee or Company portal on the Meeting layout, it automatically creates a new record in Connections.  The new Connections record reflects the Meeting ID and either the Employee or Company ID (whichever portal is used).

                          Therefore, when you open up the relationships graph and click on the link between a connections  table occurrence and one of the other fields, you see the check box selected for create a new related record selected for the connections occurrence. I had to be sure.

                           

                          Give me an example of the report/layout you want to see.

                           

                          I would like to be able to make reports by Employee and by Company.  I know I can do those within each table (Employee or Company).  But I don't want to use a portal for certain fields (like meeting details, since you can't slide up fields within each row of a portal).  So, ideally, I would create all reports from the Connections table.

                           

                          In the Connections table, I can summarize by Meeting ID, since all the new Connections records are made from the Meeting table (layout).  But the employees and companies within the Connections records are not related (other than by meeting ID), so I can't summarize by those fields.

                          So you want a report of all employees attending a given meeting grouped by company?  

                          If so, your report might look like:

                           

                          Meeting name and detail information from the meetings table.

                           

                          Company1 name and any details from the company table

                                Employee name1 and any other details from the employee table.

                                Employee name2 and any details

                                Etc.

                           

                             Total employees scheduled from company 1: 23

                           

                            Company name and any details...

                                Employee name24 and any other details.

                                Etc.

                           

                             Etc.

                           

                          Total companies scheduled:  5

                           

                          By the way, to complicate things - the reason I keep asking about populating repeating fields is because I really don't want to have to add company to each Meeting record.  I want it to automatically be added when I add employee (based on the company assigned to employees in the Employee table).  I know I can do this using the Connections table and a portal, but this is the one outstanding problem I have with my existing db using repeating fields. 

                           

                          The main problem here is you don't have your tables and relationships set up correctly. Your design had me thinking that you could schedule either a company or an employee for a given meeting.

                           

                          What you need to do first is link the Employee table to the Company table. Each employee record belongs to one Company record corrects.

                          Then in your connections table, you select an employee and automatically, selecting an employee will select the employee's company via this relationship.

                           

                          So first: let me know if I'm on the wrong track. There's no point in pursuing this further if I'm taking you in the wrong direction.

                           

                          Then I'll take you to the next step.

                           

                          • 10. Re: Repeating field lookup
                            filler
                               Thanks again for all your help on this.  I think it's just too hard to explain.  Is there any way for me to safely share a db example?  (And, yes, I misspoke about my assigning a serial number for Connections, and I do have the "create new related record" checkboxes selected.)
                            • 11. Re: Repeating field lookup
                              TSGal

                              All:

                               

                              A repeating field is ONE field that has many values.  If a repeating field is set up to look up from another table, it will find the first occurrence in the other table, and the information from that field is put into the repeating field.  If the looked up field is also a repeating field, then the repeating values are entered into the field.

                               

                              If you are trying to lookup up values from several records, then use a portal.  If you want those values in a repeating field, then you will have to write a script.  For example...

                               

                              Create a calculation field (PortalEntries) to count the number of entries in the portal.  The script may look like the following:

                               

                              If [ PortalEntries = 0 ]

                                 Exit Script

                              End If

                               

                              Set Variable [ $counter ; 1 ]

                               

                              Loop

                                 Go to Portal Row [ Select ; No dialog ; $counter ]

                                 Set Variable [ $value ; Value: <field from portal you want to populate> ]

                                 Set Field [ <Repeating field> [ $counter ]  ; $value ]

                                 Go to Portal Row [ Next ]

                               

                                 Set Variable [ $counter ; $counter + 1 ]

                                 Exit Loop If [ $counter > PortalEntries ]

                              End Loop

                               

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

                               

                              In essence, this checks to see if there are any related records.  If not, then exit.  If records exist, we want to grab the first to the last portal rows and put them into a repeating field.  We initialize the variable $counter to 1.  This represents the first portal row.

                               

                              The script then enters the loop.  We go to the first portal row (since $counter is set to 1), grab the value you want to populate and put it into a variable $value.  We then go to the first repetition and set the value to $value.  We then increment $counter by 1, see if it is greater than the number of portal rows, and if so, exit the script.  If not, we go back to the top of the loop, grab the second portal row, replace the second repetition and continue until all portal values are put into the repeating field.

                               

                              If you need clarification for any of the above steps, please let me know.

                               

                              TSGal

                              FileMaker, Inc. 

                               

                              • 12. Re: Repeating field lookup
                                filler
                                  

                                Thanks for the info.  Will this work to pull from a repeating field rather than a portal and is there a way to put this into a field definition rather than a script?  Here's the setup:

                                 

                                Tables: Employees, Companies, Meetings.

                                 

                                Relevant relationships: Employees - Companies (each employee is assigned a company in the Employee table).

                                 

                                On a layout in the Meetings table, I have a repeating Employee field and a repeating Company field.  I want the Company field to automatically update (within the field definition) based on what is in the Employee field (through the relationship).  Right now, I've got the first repeating row in Company field to display the related company based on the first repeating row in Employee field (based on a simple Lookup formula).  But I want that to happen for all repeating rows.

                                • 13. Re: Repeating field lookup
                                  TSGal

                                  filler:

                                   

                                  What is your "key" field for the lookup?  If it is a non-repeating field, then the lookup will find the related repeating field and populate the repeating field in the current table.  For example, if I have an ID field that matches an ID field in the related file, and I set my repeating field to be a lookup using this relationship, then I can grab the repeating field from the related table and populate the repeating field in the current table.  Being more specific, the repeating field in the current table is Employee, and it is set to be a lookup based upon the relationship where ID = ID.  If I enter "1" into the ID field, FileMaker will look in the related table for ID=1.  If found, it copies the contents of the Company field and places those values in the Employee field of the current table.  If Company had three values/repetitions of "IBM", "Apple", "Microsoft", those values/repetitions will now appear in the Employee field.

                                   

                                  TSGal

                                  FileMaker, Inc. 

                                  • 14. Re: Repeating field lookup
                                    filler
                                       Right.  But I don't want the related company to appear in the Employee field.  I want it to appear in the Company field on the same Meeting table layout.  In other words, on the Meeting table layout, I select Employee 1 in repeating row 1 of the Employee field, and by definition row 1 of the Company field automatically displays Apple.  I then select Employee 2 in repeating row 2 of the Employee field, and by definition row 2 of the Company field automatically displays Microsoft.  And so on.  This automatic update by definition of Company field only works for row 1 (through Lookup()), but not the additional rows of the repeating field.
                                    1 2 Previous Next