11 Replies Latest reply on May 19, 2015 9:46 AM by MarkGores

    How to Display All Records in Non-Related Table

    MorkAfur

      Title

      How to Display All Records in Non-Related Table

      Post

      Quick Question -- Say you have a separate lookup table with something like all the current jobs that you've recently done.

      All I want to do is display all the records in that (unrelated) table on the main layout and then pick one of them and go to that record.

      Yet, FMP seems to demand that the tables are related.

      I've tried an "X" relationship, but that didn't work either.

      No dice with a value list.

      Suggestions?

      Thanks,

      -m

        • 1. Re: How to Display All Records in Non-Related Table
          TKnTexas

          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".  

          • 2. Re: How to Display All Records in Non-Related Table
            MorkAfur

            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. :)

            Thanks,

            - m

            • 3. Re: How to Display All Records in Non-Related Table
              philmodjunk

              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.)

              • 4. Re: How to Display All Records in Non-Related Table
                MorkAfur

                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. :)

                - m

                 

                 

                • 5. Re: How to Display All Records in Non-Related Table
                  philmodjunk

                  Yes, but you didn't describe what results you got when you tried that and, just as you have since found out, it should have worked so I wanted you to take another look at that approach. wink

                  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.

                  • 6. Re: How to Display All Records in Non-Related Table
                    MorkAfur

                    Hey Phil,

                    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.

                    Thanks,

                    -m

                    • 7. Re: How to Display All Records in Non-Related Table
                      TKnTexas

                      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.

                      • 8. Re: How to Display All Records in Non-Related Table
                        philmodjunk

                        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.

                        • 9. Re: How to Display All Records in Non-Related Table
                          MorkAfur

                          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!!! :)

                          - m

                          • 10. Re: How to Display All Records in Non-Related Table
                            philmodjunk

                            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.)

                            Caulkins Consulting, Home of Adventures In FileMaking

                            • 11. Re: How to Display All Records in Non-Related Table
                              MarkGores

                              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.