6 Replies Latest reply on Feb 19, 2015 8:33 AM by philmodjunk

    Counting the number of different values from a related field

    mchancevet@gmail.com

      Title

      Counting the number of different values from a related field

      Post

       Hello,

      I have a layout that lists quote line items and displays the parent quote document information in the header. The layout is accessed via a script that incorporates a find such that only records related to any one quote (parent) should be found. 

      If quote line items are deleted the search could potentially result in showing all quote line item records for all quotes. This is problematic and as an additional fail safe I wish to disengage (hide) several buttons and controls from the layout if the found set includes records from more than one parent quote. 

      I can't conceive of the calculation that would allow for this hide function or for a script to check on this status.

      Thank you,

      Morgan

       

       

       

        • 1. Re: Counting the number of different values from a related field
          philmodjunk

          If a scripted find fails to find records, you get an error code returned that your script can test for and display an error message when detected. You can use set Error Capture [on] to supress FileMaker's built in error message so that it does not interrupt your script so that your script can handle the error. Such a failed find will produce a found set of zero so you will still not have any records from other quotes showing on the layout. Your script, having detected the error code or empty found set can take the user back to a different layout so that this layout is not even accessible to the user if the find fails to pull up records for the current Quote.

          And if you want a script to hide buttons or other layout objects, in FileMaker 13, you can set up Hide Object When expressions that refer to the value of a global variable. Your script can set the values of such variables and then use either Refresh Window or Refresh Object to make them disappear.

          • 2. Re: Counting the number of different values from a related field
            mchancevet@gmail.com

            Hi Phil,

            Thank you for your answer. I've made some changes to the script as above and incorporated a dialogue with a troubleshooting tip. I think this will resolve the problem.

            I would like to learn a little more, to pick your brain on this theme:

            From the context of a parent record (quotes) I could do a count or other function (list, patterncount, etc) that interrogates a field/s in the related set of child records (quote line items).

            From the context of a set of child records (found set or all records) can you interrogate the set of parent records (say, List (Quote::UUID) ) given that only one record in the found set of child records (quote line items) is active at any one time.

            To refer back to the example in my initial enquiry, Is there a way to achieve count(Quote::UUID) from the context of a set of quote line item records.

            Hopefully that makes sense.

            Thanks for your help.

            Morgan

            • 3. Re: Counting the number of different values from a related field
              philmodjunk

              Do you mean that you have this relationship?

              Quote::__pkQuoteID = QuoteLineItems::_fkQuoteID

              And you want to know the number of related quote records that link to at least one QuoteLineItem record in a found set of LineItem records that are not all from the same Quote?

              Hmm, possible--especially in FMP 13, but not at all a straight forward thing to do...

              Define a "list of" summary field that lists the _fkQuoteID field. This summary field produces a return separated list of all the ID's for your current found set of line item records.

              Define a calculation field, cIDList, that simply copies the value of the new summary field. We need a field that we can use as the match field in a relationship and a summary field can't be used for that so we need this calculation field.

              Set up a relationship to a new occurrence of Quotes like this:

              QuoteLineItems::cIDList = Quotes|FoundSet::__pkQuoteID

              Then Count ( Quotes|FoundSet::__pkQuoteID ) will return the desired count.

              PS. I deliberately avoided referring to any field named UUID as it is not clear by context whether that field has been set up as the primary key (__pk) or the foreign key ( _fk ) field.

              • 4. Re: Counting the number of different values from a related field
                mchancevet@gmail.com

                Hi Phil,

                Thanks once again. I once asked an Australian FM representative if you were an FM employee. and then indicated I thought you ought to be.

                One item from above is unclear to me.

                Quotes|FoundSet::__pkQuoteID.   

                What does the |FoundSet:: signify I have not seen this notation before.

                Cheers,

                Morgan

                • 5. Re: Counting the number of different values from a related field
                  mchancevet@gmail.com

                  Ah I see it is the new table occurrence of Quotes.

                  Is this how it works?

                  Table|OccurenceName::

                  Morgan

                  • 6. Re: Counting the number of different values from a related field
                    philmodjunk

                    Yep, it's just my personal naming convention for the new table occurrence. I'd say that the entire text was the occurrence name with the text after the | supplying additional info about the intended purpose of that occurrence so that when I am setting up a script step or calculation, I can more easily select the correct table occurrence.

                    and then indicated I thought you ought to be.

                    Late last year, I nearly became one, but then, at the last moment, the opening for which I had been interviewed was filled internally. That was a disappointment in the short term as I saw that opportunity as an ultimate dream job, but it freed me up to accept a new job from another employer which has proved to be an extremely positive experience in terms of better pay/benefits, working environment and opportunities for professional growth...