You can self join tables. Create another instance of the same table and make the relationship. You can use global fields in this relationship to filter the portal rows in/out.
The thing is already 1.5GB in size, and will need to be distributed after a stand-alone is made. Even zipped it's quite large. Two tables will put me over my transmission limits :)
techlarry - there seems to be a misunderstanding. Creating a new table instance doesn't really add file size. You're not creating a duplicate table. You are just referencing the same table twice so you can look up information.
And by the way, you shouldn't need to make 2 files. Databases and tables are not the same thing. 1 file can have hundreds of tables in it - since FM 7 I believe.
Do I have to create the table manually, or can an empty copy be created automatically somehow?
Sorry, I was pretty good at SQL and R:Base a long time ago, but haven't really caught on to FileMakers relational stuff very well yet :)
Rats. Don't seem to find an edit button :)
Anyway, I managed to create the table, then I did an import, and now I have a copy.
Now to figure out the relation stuff...
UPDATE: And now I found the edit option :)
No no no :) nothing of the sort. You're making it hard on yourself.
Just to go manage databases, then the relationships window. At the bottom left, there is a plus sign button, click that and choose your original table. It will create a second instance of the same table. Then join the fields from table1 to instance2.
There's your join. Delete the table you created; it's not needed.
LOL! I'm on it :)
BTW... The thing has 151 fields. I guess there's no way to automate that (link them all at once) is there?
Just wait until I get to the part where I have to create a multi-level, multi-banded report with several sub-summaries and a grand summary :)
I've actually done this (quite a bit) but it's been about 5 years and I'll be damned if I can figure it out again. I know about the necessary multi-level sort, but the reports are just coming out all wrong.
But that's further down the road. I may get that one on my own yet.
techlarry, no, you don't link every field. you link the fields that you need to do the relationship you are trying to do.
For instance if you are trying to have a search field called first name that when you type in it it will filter the portal,
then in the first table you create a global field and you link that field to the second table instance's first name field.
then, when you type in the global field the name to be searched, the related table with find all records with that name
Ok, I think I'm getting there :)
So, if I needed a Portal with, say 10 fields displayed, I would only link those 10 fields.
And later, if I decided I needed a portal with 12 different fields, I can either add them to the current link, or even create yet another table, link THOSE 12 fields to the original, and specify that table during the creation of the portal.
techlarry, no - you only link the fields that you are going to "query". You can show however many fields you want in a portal. Only relate the fields that you will be looking up info it.
For example - you are going to have a field where you enter the invoice ID and another for the date.
You only need to link these two fields to 2 global fields in the main table.
Then, in the portal you can put however many fields you want.
Ok, I made a wrong turn somewhere :) We'll deal with just 5 fields...
- I created a global variable in the main table (Table) matching each. gCaseID, gCustomer Name, gCTD, gDate Resolved, gResolving Agent.
- I duplicated the table in the relationship as indicated.
- I creatd "=" relationships between CaseID, Customer Name, CTD, Date Resolved and Resolving Agent.
- I went to my form and added a portal.
- When the field selector showed up, I added gCaseID, gCustomer Name, gCTD, gDate Resolved, gResolving Agent.
When I view the form, the portal is empty. There is a box of dark lines, and the scroll bar, but there is no data.
I must have missed something :)
now this depends. using = on all will only pull up records where ALL fields match. I think an OR will prove better but I may be wrong.