Other than using ExecuteSQL and Manage | Database there are no other options for setting up relationships in FileMaker. And letting others have access to Manage | Database is a really bad idea.
30K records doesn't seem that big to me (Some of mine exceed several million). I suggest describing what you are dealing with in more detail. There's probably a different approach to get what you need that works better with larger data sets.
I've 2 main tables.
1 table has about 40K records on it, with about 100 fields in each record.
The other table has about 5K records in it with same amount of fields.
To create a relationship between them I made 1 field with just "x" in it for each table, and made this the relationship so all records relate to all records on both tables.
When I am viewing portal tabs on the 5K record table each portal tab takes about 3-5 seconds to load. When I am viewing portals on the 40K table layout then there is no lagging.
I asked the question as when I add another relationship to the tables, i.e., I add first the relationship of the "x" field, then I add another relationship, such as industry, then the portals load instantly. But when I take that relationship off and am left with just the "x" field relationship it all slows up again.
If there's a way to make things always instant I'd be very interested in that :)
How do you use that portal based on the X relationship? Is there a portal filter specified for it?
Is there a script that uses Refresh Window [Flush cached join results] that is used to refresh your layout?
What I am leading up to here is to see if we can set up something that you can use in place of that portal so that the need for a delay while it refreshes is not needed. And in some cases, there may be a way to keep the portal but eliminate the need to run a script with Refresh/flush to update the layout display if that is a factor in the delays that you are seeing.
The portal displays members of staff from the client company. So the main table is client companies, and the related table is staff that work at the companies.
The X relationship is needed because on other layouts I need to see how companies staff are related, so I cannot just relate the tables by companies. However, to speed things up, when I do just need this relationship I add it on top of the X relationship and it works much quicker.
The portal filter shows staff from the company, narrowing down the fact that for the main, the X relationship relates all records.
Where would I find the flush cached join results script, if it is something I would need to add a portal when making it, then I haven't done that.
However, to speed things up, when I do just need this relationship I add it on top of the X relationship and it works much quicker.
What exactly do you mean by that?
Refresh Window [Flush cached join results] is a possible source of slow screen refreshes so it's a method to avoid rather than to use here. I asked about it to make sure that you weren't using it to refresh a filtered portal after modifying the filter criteria.
It may just be that I don't have a complete enough understanding of your database design, but I don't see why you need to use a relationship with the X operator if you only want to see records for a specific company. It would seem a very simple thing to set up a relationship that matches by a company ID instead of the X operator. And in cases where you do need the "match to all records" relationship, you can use a different occurrence of this table with that operator.
What exactly do you mean by that?
I have the two tables related by the universal key, I click into the equals sign box and add another relationship under the first relationship so
AND second relationship
When I have this in place the database moves through records much quicker.
No, not using refresh window on the portal.
I need to learn about occurrence of tables. I first saw it when I tried to connect/make relationship between one table and two other tables. It told me that I would need to make an occurrence of the table. I've also seen your posting on the subject, but still trying to understand the whole thing.
Table1::MatchField1 = Table2::MatchField2 AND
Table1::anyfield X Table2::anyfield
Produces identical results as this relationship:
Table1::MatchField1 = Table2::MatchField2
Table Occurrences are the "boxes" that you see in Manage | Database | Relationships. Think of them as "labels" for the tables you've defined on the tables tab. You can create as many "labels" as you want for the same table and use each to define a different relationship.
Let's say you want two portals to records in Table2. One portal needs to list all records in Table2. The other table needs to list only the records in Table 2 that have a value in MatchField2 that = the value in MatchField1 of the current record.
In Manage | Database | relationships, make a new table occurrence of Table 2 by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be: Table 2|AllRecords.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
Table 2|AllRecords>------x-----<Table 1------<Table 2
To get a portal of all Table 2 records, put a portal on your Table 1 layout and select Table 2|AllRecords from Show All Related Records. To get a portal of just the matching records, select Table 2 from that drop down.
Just spent the morning working all this out. Your comment "Think of them as "labels"" was fantastic.
I've always been worried about getting into Occurrences, not sure why, but it just seemed like more tables and relationships, and everything would end up getting tangled up. But the labels idea really helped it make sense and cleared up with the database was doing.
I've created a table occurrence for (Table 1) called (Table 1a) and created a relationship between this Occurrence (table 1a) and the (Table 1.)
It all seem to be working, and I set up the relationship so i am allowed to create details in Table 1 using table 1a. I will use this to add branches of companies of the same company when I am in it's record. I used to do this in Excel using the split screen option, and I guess making an Occurrence of a table and relating back to the same table is FMs way of doing it?
Is it okay to do this. I hope so as I have a full duplicate table at the moment (table 1 duplicate) to achieve this result. that I need to keep updating as I add new details into (table 1). Very long winded ...
That's called a "self join" and is a way of linking records in one table to each other. The basic concept is sound. The devil, as always, is in the details.
I could only like the comment above, not super like :)