6 Replies Latest reply on Oct 13, 2008 1:04 PM by catwest

    Referencing fields in other records for calculations



      Referencing fields in other records for calculations


      I want to access the value of a field in another record of a table to use in a default value calculation.  Is there any way to access the value of Record ID#(Field Name) in a calculation?  Specifically, I want to access the the value of a field in the record having next lowest (or previously entered) unique ID number to use in a calculation [e.g., Field Value(N)=Field Value(N-1) + Another-Field Value(N)].  In EXCEL one can just click on the cell above the cell containing the formula to place the value of the designated cell in a calculation.  How does one do this in Filemaker Pro calculations for default entry values.  



        • 1. Re: Referencing fields in other records for calculations

          Wow, that is complicated, particularly since I'm just trying to access a field value in the same table.  It seems like there should be a GET_Field_Value (Record ID#,Field Name) function in Filemaker Pro.  Is there some reason that this would be hard to implement?  I can't imagine I'm the first person who has wanted to do this.


          Thank you for your suggestion, BTW.  I'll definitely try it out.



          • 2. Re: Referencing fields in other records for calculations

            I had been pulling out my hair trying to figure out the 'simple' thing of getting the value from a field in a related(child) table to automagically show up in the field of (Parent). 


            Here's the logic that worked for me:

            Assuming you have already created parent and child tables

            Assuming you have already created relationships between them


            --It is not necessary to create a relationship between the two fields you are trying to use the info from one to populate the other--


            Assuming you have followed the steps to Look up value, or look up calculation (for me, it was the parent that was looking up from the child, and it was a calculation which I had to specify with the "::" setup between tables)


            Create the relationships between the identifiers of that record. And Sort:

            For me it was Sort by: Group, Model, ID  Those had to have relationships.  


            The "Stock on Hand" field of the CHILD table record automatically populates the "Stock on Hand" field of the Parent Table Record in this way:


            Instead of clicking on the field I want populated (Stock on Hand) I click on the the ID field, then goto RECORDS,(drop down) go to "Re lookup field contents".  


            When I do that, I am told how many records I am currently browsing (5 in my case as I set up 5 test records in both parent and child) 


            Now, all 5 records automatically get the accurate total in their respective boxes!  Yeah!


            The part that is left out in the description of this was, for me, the part where I click on the ID and that populates the records.


            So, however you sort your records is how they will find the info.


            Hope that helps you! 


            • 3. Re: Referencing fields in other records for calculations



              You may want to have a look at the function GetNthRecord, which returns the content of a field from a specific record in the found set (when working on the same table).

              The found set and it's sort order are important here, so this technique may not work for your needs.


              If you are able to use it, getting the name from the previous record would look something like this:


              GetNthRecord(Name ; Get(RecordNumber) -1) 


              • 4. Re: Referencing fields in other records for calculations

                Yes! Make relations between tables that connect via "identifiers" whatever you use to identify your records: Then, in the field you want to automatically contain the calculation from another table's field, set it to 'look up value' and use either a value or calculation in "specify".  If a number, both must be a number. 


                Sort your relationships by "identifiers" not by 'calculation' 


                Next, enter calculation in the one table/field/record you want to have appear in the second table/field/record, automatically. 


                Now, go to the second table/field/record and either enter or click on the identifier you used to sort the relationships. 


                The value you are looking for, will automatically appear in all the records you are browsing, according to individual identifiers.



                • 5. Re: Referencing fields in other records for calculations

                  Also, (sorry, if I forgot to post this part), when you click on the identifier field, you must go to RECORDS->Re lookup field contents and click okay.  However many records you are browsing in that group, will each have their corresponding values/information automatically put in.


                  It's a beautiful thing once you see it actually works. 

                  • 6. Re: Referencing fields in other records for calculations

                    :robotwink:Remember to check and be sure that your descriptive fields, whatever you use to define each record, are consistent with the same fields in the records where the information you want to automatically import, reside. 


                    I had a problem of "inconsistent" results.  Some records worked, others did not. I found out that (especially since I copy/pasted my fields to make them consistent) I had several with extra blank spaces at the end, which counted as 'keystrokes' to the program and which made the fields not recognize each other.


                    Solved that by making sure there were NO unwanted blank spaces, especially at the ends of each descriptive/identifier field (that which is related in the parent/child relationship graphs on the tables)


                    I am using FMP 9


                    I hope this helps some of you to overcome the more 'simple but aggravating' issues that have plagued me.