4 Replies Latest reply on Jan 11, 2011 3:04 PM by PJSpark

    Get a record from a function result



      Get a record from a function result


      Happy New Year!

      I've created a report with two summary fields that return a minimum and a maximum value. What I would like to include is the date or other information that the minimum or maximum value occurred. So I was wondering if there was a way of finding out the record number that got the result, so I can retrieve values from related fields of the same record. 


        • 1. Re: Get a record from a function result

          First, you'd need a relationship that matches the minimum and maxiumum values to the original value fields in your table. What should happen if there are more than one such record with the minimum or maximum value? Do you want to match to the first or last record?

          Here's an example, just for the maximum, but you can apply the same technique to get to the minimum value record also. It will only match to the first such record--so changes would be needed to match to the last matching record.

          You can't use a summary field in a relationship, so we need a calculation field to capture this value:

          Define cMaxValue by just entering the name of your summary field as it's sole calcuation term. This will return the maxium value for the current found set. (If you need max values of sub groups of the found set, you can use the getSummary function here.)

          Define a new relationship as:

          YourTable::cMaxValue = YourTableMaxValue::ValueField

          Create YourTableMaxValue by selecting YourTable in Manage | Database | Relationships by clicking it, then click the button with two green plus signs to make a new table occurrence you can rename to this name and then link as shown.

          Now, on your layout, you can refer to fields from YourTableMaxValue to show other data from the first such record to match the maximum value. You can also define calculation fields that refer to these fields via YourTableMaxValue::fieldname format in their expressions.

          • 2. Re: Get a record from a function result


            I wasn't able to get this to work as FM isn't allowing me to create a relationship based on a field that references a summary field. It keeps reporting <Index Missing>. The field has to be indexable.

            This is what I did:

            MaxSub: summary field that returns the max value of an amount

            created MaxValue: calc field = MaxSub

            made a new table occurance of table that has these fields: Table1::MaxValue=Table1a::MaxValue

            Thanks again

            • 3. Re: Get a record from a function result

              Take another look at my example. The relationship should be

              MaxValue = Value


              MaxValue = MaxSub

              • 4. Re: Get a record from a function result


                Thanks for that correction. It works perfectly now and I was able to even make it return the last record when the value matches by adding a descending sort to the relationship.

                Once again, thanks for your quick support!