1 2 Previous Next 16 Replies Latest reply on Aug 19, 2014 3:29 PM by robrickard

    Merge several tables into one portal?

    user253

      How would I merge several tables into one portal?

        • 1. Re: Merge several tables into one portal?
          Mike Duncan

          Have you done any work with techniques such as Virtual Lists?

          • 2. Re: Merge several tables into one portal?
            user253

            No, I haven't done any work with Virtual Lists.

            • 3. Re: Merge several tables into one portal?
              erolst

              Please give more information about your tables and what you want to achieve; you help your prospective responders to find and suggest the best approach for the problem at hand, e.g. if any merging is necessary at all.

               

              As it stands, the question is too vague to receive other than equally vague answers. (No offense, Mike …)

              • 4. Re: Merge several tables into one portal?
                user253

                Table 1 >> Table 2

                 

                Table 1 >> Table 3

                 

                Table 1 >> Table 4

                 

                From Table 1 Layout show in a single list or single portal records from Table 2, Table 3 and Table 4.

                 

                Thanks,

                Jay

                • 5. Re: Merge several tables into one portal?
                  BruceRobertson

                  That really doesn't help.

                   

                  Please use real, meaningful names for all the tables.

                  Also, describe the purpose for doing this.

                   

                  It seems likely that you have a data structure design problem; so you're trying to do something you should not have to do at all if the database was designed correctly.

                  • 6. Re: Merge several tables into one portal?
                    Stephen Huston

                    By definition, a portal can show only records via one relationship path. That means that you could still have fields from several different tables, if they were along the relationship path used to define the portal, such as:

                    • Table A >> Table B >> Table C >> Table D, etc.

                    By careful placement or layering of some fields on the portal, you might achieve effects similar to what you want (which is still unclear in your posting), but you are more likely to achieve it using virtual list techniques or by restructuring your data, as earlier responders have suggested.

                    • 7. Re: Merge several tables into one portal?
                      robrickard

                      Im looking for the same thing - im sure there is a 'proper' and 'best practice' for this.

                       

                      Examle - four tables.

                      MainStaffDashboard Table (organization data)

                      Company Table (customer data)

                      Client Table  (customer data)

                      Task Table (customer data)

                       

                       

                      Screen Shot 2014-08-14 at 12.36.10 PM.png

                       

                       

                       

                      In the Dashboard Layout i want to show records in a portal of Task Table - but i also want it to display data from ClientCustomer and CompanyCustomer table in that same portal (data of how the task is linked from company to client to task)

                      Example: <<Company::CompanyName>> - <<Client::Name>> - <<Task::Name>>

                       

                      The DashBoad portal will always show the proper data it has the relationship set up in Task - but if i add the data from the other tables it only shows the first found data - not the proper data in the path of the relationship(maybe this is not possible). I dont want to have multiple data in multiple tables if possible (in the past that is how i have done it). example of why: If the phone number changes i dont want to have to change it in all the task records too.

                       

                      Maybe im missing a best practice here - any help is appreciated. Thanks,

                      • 8. Re: Merge several tables into one portal?
                        Mike_Mitchell

                        Rob -

                         

                        The first found record is the "proper data in the path of the relationship". Every relational hop implies a found set. So when I jump from (in your example) MainStaffDashboard to Company, FileMaker is essentially saying, "Show me all the Company records where MainStaffDashboard = LinkStaff / LinkCompany = CompanyID." If you make more than one "hop", then that exercise continues. However, since you can only put a single field in a portal row (i.e., not another portal), you're only going to see the first record. So if you have more than one company (for example) that meets the criteria, you'll see the first one (based on the sort order of the portal or relationship).

                         

                        If you're trying to do what I think you're trying to do, you're going to need to invert your relational chain. Use a global field or calculation to filter from MainStaffDashboard to the TaskID you want, then go from that down the chain to extract the Company information associated with that Task.

                         

                        HTH

                         

                        Mike

                        • 9. Re: Merge several tables into one portal?
                          robrickard

                          I creatd a FM file just for testing to figure this out - I have a portal in the dashboard layout that has a relationship with the Task Table.

                          I want multiple records to show in the portal. I want related data from the Compnay and Client tables to also show in that portal.

                           

                          The below example i have created three companies, clients and tasks.

                          I used colors to show the records that are all related together.

                          I created recrods for a blue company that has a relationship with blue client and a blue task (one records in each table)

                          I created records for a red company that has a relationship with red client and a red task (one records in each table)

                          I created records for a yellow company that has a relationship with yellow client and a yellow task (one records in each table)

                           

                          I want the portal to show the task and the related data from each company and client table in the same line of the portal.

                          <<Company::CompanyName>> - <<Client::Name>> - <<Task::Name>>

                          However, the below result is what shows (notice that the first found record is displayed for all lines for portal, but not the tasks table as that is the one i have the direct relationship set up for (Portal set up - show related records).

                          Yellow task has no related records wiht blue (as shown below)

                          Screen Shot 2014-08-14 at 2.15.37 PM.png

                          How do i set up a portal so it shows the proper related records of the other tables that are all connected in the relationship path.

                          I would upload the test FM file, but im not seeing any tools to do that.

                          • 10. Re: Merge several tables into one portal?
                            robrickard

                            In the past i would add the wanted data to the table i have the relationship with (compnayRecordID, clientRecordID, name, number ect...), but i would love to find a way that the portal can figure out what those relationships are without me loading the end Task Table records with all that.

                            Even if i just used the record IDs of the other tables in the task table to somehow point back to them, but i have clue how or even if that is possible.

                             

                            Thanks for any help.

                            • 11. Re: Merge several tables into one portal?
                              Mike_Mitchell

                              Use the advanced editor to upload the file.

                              • 12. Re: Merge several tables into one portal?
                                robrickard

                                ah - here ya go. (thanks for the tip on uploading files)

                                • 13. Re: Merge several tables into one portal?
                                  Mike_Mitchell

                                  Yes, the problem is as I described it. Follow the relational chain. If I go from MainStaffDashboard to LinkStaffCompany, I'll see this:

                                   

                                  Screen Shot 2014-08-14 at 5.21.05 PM.png

                                   

                                  Note that "Blue Company" comes first in line (since it was first in creation order, presumably). If I then move to Company, I get:

                                   

                                  Screen Shot 2014-08-14 at 5.22.58 PM.png

                                   

                                  which you would expect (show me all the Company records related through this join). Moving on to LinkCompanyClient, however, I get:

                                   

                                  Screen Shot 2014-08-14 at 5.24.29 PM.png

                                   

                                  Definitely not what you would intuitively expect. Why? Well, here's an interesting observation. Add the LinkCompany field (the foreign key) to Company to the portal and you get this:

                                   

                                  Screen Shot 2014-08-14 at 5.30.03 PM.png

                                   

                                  Huh? The foreign key in the join table points to a different company than the Company::CompanyName field. What the ... ?

                                   

                                  Here's the rub: FileMaker does not evaluate related fields from a different context than the current context. In other words, what you're asking it to do is start from the MainStaffDashboard table, go down the chain to the Tasks table, then go back up the chain to the Company and Client tables and determine what the correct relational values ought to be from that context (i.e., Tasks). It can't do that. It only goes in one direction. So when it evaluates the Company::CompanyName field in the context of a join table that's separated by the primary entity table, it only sees the first related record (in this case, Blue Company).

                                   

                                  So you have two choices:

                                   

                                  1) Put another table occurrence of the MainStaffDashboard table on the other end of the chain at Tasks and use scripting to populate a Task field from there, and let FileMaker look back up the chain to determine Company and Client information.

                                   

                                  2) Echo out the parent information in the join tables (using either lookups or unstored calculations) and use the echoed fields in the portals.

                                   

                                  HTH

                                   

                                  Mike

                                  • 14. Re: Merge several tables into one portal?
                                    robrickard

                                    I did some digging and I can not find a script example. What would the scripting look like that would update the fields when the portal is displaying them? link to an example?

                                    (updating the field is important as the relationship/join tables may not have current information as time pass (like phone numbers or contact names)

                                     

                                    A new example of what im looking for: a company table will have contact info that may be updated over time. The tasks or contact table will not have this info. A portal in the staff table would displays the contact info from company table--> and contact table--> and then Task table info. It would would be great to have information from all three tables in that portal.

                                    I understand that a portal can not do that, but i could have a relationship/join table that does - but how do i keep those fields updated for redundant changes in the other company and contact tables?

                                     

                                    I would think this is a logical needs other would have so there would be a solution for it. It is easy to do it backwards with portals. (the task tables has the companyID and contactID and the StaffID in it so having three separate portals would work with ease, but not when we want a list of those tasks in the staff table containing all the data from all the tables. (due to many to many relationships in the direction)

                                     

                                    I hope im explaining myself ok

                                    thanks for any guidance and help,

                                    1 2 Previous Next