4 Replies Latest reply on Nov 18, 2011 9:05 AM by LyndsayHowarth

    Simple Field Issue


      I have a field that calculates a number. The number changes based on a relationship between related records. So, it incrementally adds or subtracts based on all records of the same person. A total balance summary basically. I want it to show up as Total balance as a field in PHP, but it shows the value of the first record instead of last. This short 30 second video will explain EXACTLY what my issue is. What am I missing?


      Thanks very much.



        • 1. Re: Simple Field Issue



          Not sure if I am understanding what you are wanting to achieve exactly, but a couple of things popped into my head for you to try out:


          1. Presumably there is a relationship from somewhere to the refno table, and that is how you are determining which of the refno records you want to show the data from on the PHP page, you could try sorting the relationship to the refno table so that the 0 value record is not the first one returned.

          2. Maybe have a look at using Summary fields instead of a calculation so that the value will be the same for all records in the current found set.





          • 2. Re: Simple Field Issue



            I have not watched the video.  It looks you need to have master  and  details(transaction) tables and relate them based on refno.  The total balace can be in the master and this can be a summary field on the refno from detail.   




            • 3. Re: Simple Field Issue

              user12569 wrote:

              This short 30 second video will explain EXACTLY what my issue is.  What am I missing?


              Hello user12569,


              I'm afraid your 30 second video doesn't show us what the relationship between refno and trans is. Nor does it give us any clue what the logic of your "Balance" calculation is, why the sum of trans::Amount is zero, what f15 represents or what your purpose is in subtracting the trans::Amount total from it.


              That said, there are any number of ways to get the value of a field on the last record. Here are just a few:


              1. Create a cartesian self-join relationship linking refno to itself, specify a sort for the relationship (in descending order by creation date or serial number), then reference the first related record from the self-join table occurrence.
              2. Create a cartesian self-join as above but unsorted and create a calculation with the formula RightValues(List(refnoSelfJoin::Balance); 1)
              3. Create an unstored calc with the formula GetNthRecord(refno::Balance; Get(TotalRecordCount)) then show all records.
              4. Change refno::Balance to a global number field, then call a script that proceeds as follows:

              -  Show All Records

              -  Go to Record/Request [Last]

              -  Set Field [refno::Balance; refno::f15 - Sum(trans::Amount)]


              All these methods will work, but whether any of them will be ideal in your current situation is hard to say, since you haven't really said what you're doing or why.





              R J Cologon, Ph.D.

              FileMaker Certified Developer

              Author, FileMaker Pro 10 Bible

              NightWing Enterprises, Melbourne, Australia



              • 4. Re: Simple Field Issue

                In PHP, if $results is the found records... or the records in a related set... and they are unsorted...

                you can loop through the records with:

                foreach ($results as $result){

                $Total_balance=$result->getFied('Total balance');



                When you are done, $Total_balance should be the value you want.


                You might want to check with


                that the calculation result is being passed to PHP. As it is a calculation of related data it is therefore not indexed and therefore may not calculate on demand via php.


                - Lyndsay


                PS... the video was not informative in any way. There was not context nor interface to give us a clue and you didn't, as Ray pointed out, show us your Relationships. I say this only because you seem to have gone to the trouble of creating the video but whether it is video or written... the following is an example of what might have helped us:


                "I have a database which does X

                I am working on a PHP report and trying to get the result from the last record in a foundset (or /portal) of a calculation field which is based on a relationship. Instead, the first recordis returned.

                I have linked to a video showing field definitions and the Relationships graph"

                1 of 1 people found this helpful