13 Replies Latest reply on Feb 27, 2015 11:00 AM by ChrisJohnston

    why only one?

    ChrisJohnston

      Title

      why only one?

      Post

      After putting forth the effort to learn FileMaker I heard this answer or advice many times. “FileMaker will only see the first record”. The question came in the form of why my portal only shows the correct data on the first record. It also came in other forms but for me that was the most common. I have only come to accept this. I know it so well now and always can remind myself. Recently I had the good fortune to use this to my advantage via the concepts taught in this doc from FileMaker. This was great because I used what always becomes a challenge, to my advantage, because of the type of solution I was building. But the other day I reminded myself that I know that is what it does, but I STILL DON’T KNOW OR UNDERSTAND WHY? At that point I said that, if I want to really build my knowledge with this powerful application that is helping me build great software, I should try to understand why. I am asking if anybody can point me to information that explains this concept. Or can explain it themselves. I feel that then I will just not have a habit respecting the way FileMaker does, I will have knowledge of the way FileMaker/Databases work. Thanks

        • 1. Re: why only one?
          FentonJones

          If I see that a portal shows the values of the 1st line, in all the rows, then the relationship is close, but incorrect. So I look, and fix the relationship, and each row is the correct values.

          • 2. Re: why only one?
            FentonJones

            If I see that a portal shows the values of the 1st line, in all the rows, then the relationship is close, but incorrect. So I look, and fix the relationship, and each row is the correct values.

            • 3. Re: why only one?
              philmodjunk

              Let's say that I have this relationship:

              Invoice::__pkInvoiceID = LineItems::_fkInvoiceID

              I have many related records in LineITems for any given record in Invoice. If I put a portal to LineItems on my Invoice based layout, I don't see "only one", I see all related records.

              But what if I drag a LineItem field out of that portal directly onto the layout?

              From which LineItem record out of the several listed in the portal should the data shown in this field come from? The field can only display one value so the FileMaker programmers had to set up a simple rule that produces consistent and predictable results. They chose to select the "first" related record. In an unsorted, unfiltered portal, the record in the first portal row is the "first" related record. I put "first" in quotes because this will be the oldest record out of the set of records shown in the portal unless you open up relationship details and specify a sort order for the portal's table occurrence. If you do that, which every related record sorts out as the first record in this sorted order is the "first" related record. (And this sort order will put that record first in the unsorted, unfiltered portal also.

              Now think of a calculation defined in Invoices to evaluate from the context of the Invoice Layout's Table Occurrence:

              NumberField + LineItems::amount

              Do you see that the same issue arises as when we put a field from a related table on our layout and do not use a portal? Once again, the expression can only refer to a single record from the possible set of many related records. And the programmers set this up to follow the same rule as with our layout example. The expression will refer to the first related record in order to supply a value to add to the Number Field from Invoice.

              Caulkins Consulting, Home of Adventures In FileMaking

              • 4. Re: why only one?
                ChrisJohnston

                I see! Your explanation is great as usual Phil, and as Fenton said "fix the relationship" great guys!!!! I will hold this near and continue to learn from both of you guys expertise! Thanks

                • 5. Re: why only one?
                  ChrisJohnston

                  Yes! I have wondered what the Sort records checkbox does on the Edit Relationship window. So this means you can set up criteria for what you relationship sees as record one. Correct?

                  • 6. Re: why only one?
                    philmodjunk

                    Yes. Like any sort or search, it takes FileMaker time (and it's most likely a O*NLogN type of function where N is the number of records) to sort the records so use with care.

                    • 7. Re: why only one?
                      ChrisJohnston

                      Wow! That is to much power! Thanks

                      • 8. Re: why only one?
                        FentonJones

                        The "sort" can be one of two things. You can specify a "sort" for the relationship itself. That has been around for many many years (FileMaker 3?). Then later FileMaker added the "sort" the "portal" itself. They do much the same thing. However, the "sort the portal" applies to ONLY that portal on that layout". It's up to you which to use. In some cases you might want to sort the relationship itself, but usually you can just sort the portal. A sorted relationship returns sorted records in scripts and calculations.

                        • 9. Re: why only one?
                          ChrisJohnston

                          That is good to know, I could see if you are not careful not knowing why your relationship is returning unexpected results. Overall more good news though! Exciting!!! Thanks

                          • 10. Re: why only one?
                            philmodjunk

                            And calculations will ignore any portal sorting--that's one of the reasons why I described an unsorted unfiltered portal in my original post.

                            • 11. Re: why only one?
                              ChrisJohnston

                              Keep it coming! This has been a knowledge packed day! And it's only 2:00 PM! Thanks

                              • 12. Re: why only one?
                                philmodjunk

                                Well it's 11:00 here and I need to get back to the job that sends me real deposits in my checking account...

                                • 13. Re: why only one?
                                  ChrisJohnston

                                  Hilarious!