4 Replies Latest reply on Jan 6, 2013 11:34 AM by Dekade

    Join Table Field Summaries

    Dekade

      Title

      Join Table Field Summaries

      Post

           In a join table I am having trouble getting a field, that has varying values, to return how many times it is present in the join database.

           There may be 100 records in the join table. Each join table has it's own ID of course. However there is another field in the join table that returns a related table ID number. It is called " _fk_TopicID". I need to know out of the 100 join table records how many of them contain (for example) the value of 'T013' located in the join table " _fk_TopicID" field. Then on other records the value may be 'T002'. So, how many times does 'T002' occur in the join table for the " _fk_TopicID" field?

           I thought it was a summary that would be used but that does not seem to work; (at least in the manner that I tried to get it to work).

           Dekade

        • 1. Re: Join Table Field Summaries
          philmodjunk

               A summary field can be used but only if you can do a find and sort that groups your records in the join table by the value of _fk_TopicID.

               IF you need this same count in other contexts than that, there are other approaches--such as a self join to a new occurrence of the join table that matches records by this field or, if you have FMP 12, there are ways to use Execute SQL to produce such a count.

          • 2. Re: Join Table Field Summaries
            Dekade

                 What I want to do is have a field that constantly shows the result that I want without doing a find. I thought far enough to know that a find or a summary list report could provide the answers. But, I am wanting a field that I can place into an existing portal along side each respective _fk_TopicId so that I can view the count progression as time and compositions evolve.

            So, that said, I have FM Pro 10 Adv. Can you give me some help on a self join or something that can get the process working? OR simply tell me that the effort is to great to mess around with. I would like to learn a way though rather than trash the thought.

                 Seems like a process that many people could use in many different ways. Then again, maybe not. Seems like it could be something for FM to develop into a more user friendly approach.

                 Incidentally, PhilModJunk, your input and guidance has provided me great success in my last post of having one portal fill another portal with data. It is all up and running very nicely. Took some time; but, got 'er done. Thanks a lot for your help.

            • 3. Re: Join Table Field Summaries
              philmodjunk

                   I think I recall that the join table was called something link compos_topic.

                   compos_topic::_fk_TopicID = compos_TopicSameTopic::_fk_TopicID

                   compos_TopicSameTopic would be a new occurrence of compos_Topic.

                   Then, on a layout or portal based on compos_Topic, you can use Count ( compos_TopicSameTopic::_fk_TopicID ) defined to evaluate from the context of compos_Topic to show the current count of join table records with the same topic ID.

              • 4. Re: Join Table Field Summaries
                Dekade

                     PhilModJunk,

                HOT DIGGITY DOG!!!!

                     I got it to work the first time through. Can't believe I'm comin' back on this FM stuff.

                     Thanks a big bunch!! Now I'll work on getting the results to display on the given layout where I want them to.

                     Dekade