4 Replies Latest reply on Oct 24, 2014 5:16 PM by Stephen Huston

    Use a portal to view all records with same root number

    nancyf

      I'm trying to create a portal to view all proposals in the database with the same "root" number We may have one base proposal with the number 12345, then numerous addendums with 12345-1, 12345-2, etc. I've created a field using the Right function to narrow down the root number for each addendum, and then created a relationship based on the "rightfunction" field and the base. I can't seem to make the portal work...any suggestions.

        • 1. Re: Use a portal to view all records with same root number
          Stephen Huston

          You need to have the numbers in indexed fields, which means they need to be updated via a script trigger or Auto-Enter calculation (using Evaluate works well). That way they will update properly; stored calcs don't update as the base values change. Unstored calcs cannot be indexed, so a simple calc field is unreliable. You need a field of the matching field type (text or number as appropriate) which is indexed on both sides of the relationship.

          • 2. Re: Use a portal to view all records with same root number
            nancyf

            Understood.  I believe at least one field is Auto Enter, and typically I just click the box that let’s Filemaker index if needed.  I’ll check.

            • 3. Re: Use a portal to view all records with same root number
              erolst

              nancyf wrote:

              We may have one base proposal with the number 12345, then numerous addendums with 12345-1, 12345-2, etc.

              You should preferably use an auto-generated (meaningless) primary ID for the proposals, and a foreign proposalID for the addenda, to match those parents and children; you can use another mechanism to create the name variant (and e.g. sort by it, for which the field(s) in question don't have to be stored).

               

              This also protects the relationship from any future changes in your naming scheme, since your keys are just meaningless data, as opposed to your numbering scheme, which carries a certain amount of business information.

              Stephen Huston wrote:

              […] stored calcs don't update as the base values change […]

              I'd say that depends on the nature of those base values …

              Stephen Huston wrote:

              […] which is indexed on both sides of the relationship

              Really?

              • 4. Re: Use a portal to view all records with same root number
                Stephen Huston

                erolst wrote, in part:

                Stephen Huston wrote:

                […] stored calcs don't update as the base values change […]

                I'd say that depends on the nature of those base values …

                Stephen Huston wrote:

                […] which is indexed on both sides of the relationship

                Really?

                OK, the base values have a lot to do with it, but if a stored calc is evaluated and indexed before a change in the base value, the Stored calc value doesn't reliably update when the base value changes, unless triggered by something like an Evaluate function in an auto-enter calc.

                 

                And, yeah, I know that some special relationships work even where the parent value is based on a global field, but, though that has worked for through many recent versions, it's was originally treated as an undocumented/unintended functionality of FMP, and we've just come to expect it to be the exception. It's still a bad idea do try to make an unindexed value work as a key in other situations.