3 Replies Latest reply on Mar 11, 2014 11:05 AM by philmodjunk

    Bring a field from one table to another and then calculate with it

    AlexNovum

      Title

      Bring a field from one table to another and then calculate with it

      Post

           I’m trying to figure something out, and no matter how hard I try, I keep getting no results.

           In table “Events” I have the field “Service Costs”

           I want to see the summary total of “Service Costs” on the table “Expenses”

           I also want to add the amount of the “Service Costs” field from “Events” to the amount of the “Reimbursements” field (also from “Events”) but all to shown in the “Expenses” table. 

           I’m really at a loss at how to do this, maybe someone can explain it slightly differently as I’ve read so many things and I’m still just as lost as ever. Thank you so so much

        • 1. Re: Bring a field from one table to another and then calculate with it
          philmodjunk

               Is there a relationship between events and Service Costs? If you have 3 events do you want to see the total service costs for each event rather than the grand total of all service costs in the service costs table?

               If you have a relationship such as:

               Events::__pkEventID = ServiceCosts::_fkEventID

               Then there are two simple options for showing the total service costs for a given event:

               1) Define a summary field in ServiceCosts that computes the total of the ServiceCosts field. If you place that field on your Events table, you'll see the total service costs for all service costs linked to that event record.

               2) Define a calculation field in Events with this calculation:

               Sum ( ServiceCosts::ServiceCosts)

               and it will compute the same total as 1).

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Bring a field from one table to another and then calculate with it
            AlexNovum

            There is no relationship set up, that's one thing I'm having a hard time understanding. Also I don't think there is a "one" or "many" side. I'm simply trying to see a number in another table.

            There is only one "Service Cost" field per record in "Events" and I have set up a field called "Service Total" in "Events" that works perfectly -- it's the summary of all the Service Costs through all the records. But I can't figure out how to do a relationship so that I can get "Service Total" to show in another table ("Expenses") and have it work in calculation with other fields.

            • 3. Re: Bring a field from one table to another and then calculate with it
              philmodjunk
                   

              I'm simply trying to see a number in another table.

              But it's not really that simple. Each table can store multiple records with a different value in that field in each record. You need a way to determine which record in Service Costs that you need in order to access the correct value.

                   You are using a relational database. Defining relationships between tables is a central part of designing a solution in it. If you don't know how to do this, you need to invest some time in some training resources such as this free one offered by Filemaker Inc.: https://itunes.apple.com/us/book/filemaker-training-series/id787527886?mt=11

                   

              There is only one "Service Cost" field per record in "Events"

              Then you don't actually need two tables if this is always the case. You can define the fields from Service cost as fields in Events.