You can force a relationship with a constant. I use the NUMBER 1. I call it my LINK_FLD. It defaults to a TEXT "1".
Not exactly sure what you mean.
Even if I create a relationship and use the "X" to join them, I still don't see all the records in the lookup table.
What I'm going for is to display the recent projects for "This Vendor", but also, "All Projects".
A SQL would simply be "Select bid_Pk, pid_description from bids".
It would be nice if I could just do a SQL statement to a 'result set" that also allowed me to figure out what link the person clicked.
Anyway, if you could elaborate what you were getting at above, that would be appreciated. :)
The X relationship should show all record from the related table in an unfiltered portal to the now related table. Is that how you tried to use the Cartesian join relationship? And a GTRR from a button in the portal row can be used to make that portal record the current record on another layout which can be in a new window if desired.
And a value list should also work, but again, there's no detail here on how you attempted to use that option.
It can be very helpful to NOT say "it didn't work" but instead to describe exactly what failed results were produced by that effort. HOW it failed often clues us in as to what needs to be done differently.
(Another option that might also work is to open a small modal window to a layout based on the other table showing the records in List View.)
I must have mis-created the "X" relationship (yes, that's what I tried as per my posting above). When I tried it just now, it worked. I think it's that I needed to click "Change" in the upper part of the relationship dialog and I must have clicked Add or something. Sorry 'bout that.
Thanks Phil. I did try to specify that I tried the "X" relationship in my posting above.
Appreciate your replies as always. :)
Note, TKn, Texas' method also works, it's how we did this before a Cartesian join was an option and sometimes even today it's a better option as it set's up a relationship that supports "allow creation" where the Cartesian Join does not.
When I said the "X" relationship didn't work, I was implying, but not explicitly stating, that the results were the same as the non-X relationship. As I stated above, I had clicked the wrong button as the relationship dialog has opportunities for error entry errors as there are three rows of buttons. In the future, I'll post a screenshot (a suggestion I've made for you as well) to clarify things.
What I was trying to accomplish was to create a portal showing all of the jobs ever done regardless of the client and DB relationships. Using a portal, I can add a button (GTTR) to go to each individual job. I don't think a value list would work in this case.
Regarding using a constant to force a relationship, the "How" was not specified. How do you use a constant to "force a relationship"? I followed up on that posting, but didn't hear back.
I will describe my method. I create a field LINK_FLD on each table that I want to link. I set it as TEXT and to have a default value of "1".
I add the field to a convenient layout merely to populate existing records with the "1". After doing that new records will have the "1" when created. I create the relationship by connecting the LINK_FLD from both tables. While in Manage Databases I turn on UNABLE TO MODIFY.
I remove the field from the layouts. The records are mutually viewable.
A value list could be set up, but it wouldn't use the portal. A "use values from field" value list in a global field could be used with a script triggered script that either uses the value in a scripted find or a GTRR to pull up the record on the other layout.
Thanks to TKnTexas for your reply. Very interesting.
Phil - you definitely should write a FMP Tips & Tricks book. You know everything about this product. A real FMP MVP. I would bet that you've helped FMP become as popular as it is since you single handedly (others help too, of course) make this forum an incredible resource.
Thanks again for all your great tips and help!!! :)
Well I don't know everything, I learn new stuff about FileMaker all the time and right here in the forum much of the time. I have started a kind of "ebook" where I can publish one "chapter" at a time as individual FileMaker files. Ironically, those first chapters helped get me the new job I now have (with much better pay and benefits) and has in turn limited my time to add more chapters to the series so I have been "stuck" in #3 for a long time now (and need to update my knowledge of FMP 14 to see if the first two now need revision anywhere...)
Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)
Sounds great Phil. Congratulations on the new job.
We just up graded from FM11 to FM14 so I'm sure I will be around again asking questions.