How would I merge several tables into one portal?
Have you done any work with techniques such as Virtual Lists?
No, I haven't done any work with Virtual Lists.
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 …)
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.
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.
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:
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.
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)
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,
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.
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)
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.
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.
Use the advanced editor to upload the file.
ah - here ya go. (thanks for the tip on uploading files)
Yes, the problem is as I described it. Follow the relational chain. If I go from MainStaffDashboard to LinkStaffCompany, I'll see this:
Note that "Blue Company" comes first in line (since it was first in creation order, presumably). If I then move to Company, I get:
which you would expect (show me all the Company records related through this join). Moving on to LinkCompanyClient, however, I get:
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:
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.
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,
Let me note that your Relationship Graph and your data structure is a bit simplistic – and by starting your traversal at Companies, you have it a… over backwards
I haven't the time to put in sample data (and/or a UI that creates them) to show how this can/will work, but take a look at this revised version of your file. In essence, reconsider the structure and/or create a relationship chain for your dashboard that fits the purpose (and you shouldn't name a table for an Interface feature: a Staff table is a Staff table is a Staff table …)
robrickard wrote: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.
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.
Then create a list that summarizes its related data, and display that field in the portal. If in …
Company --< Staff --< Communication_phoneOnly
… you want to use a portal on a Company layout to display a list of Staff members along with their respective phone numbers, then in Staff crate a calc field as List ( Communication_phoneOnly::data ) – plus the usual manipulations to make this a line rather than a list – and display that field in the Staff portal on the Company layout.
If necessary, create a calc field that creates a list of related list fields (though that may get slow …)
I tried to keep the 'test/demo' file i made simple. I was trying to get just one feature working for a larger solution. I find it useful to focus on one aspect in a single file when trouble shooting.
Thank you for your reply and the file. I feel like i can develop well and build working solutions, but every now and then there is always something i cant get my head wrapped around like this. I feel i may be missing a few best practices.
I really appreciate your help - I dont see it this moment, but i will. Im going to need to dive in and figure this out.
Retrieving data ...