4 Replies Latest reply on Apr 27, 2012 8:25 AM by JoaoNascimento

    Get(ActiveFieldContents) - To get a summary field total results

    JoaoNascimento

      Title

      Get(ActiveFieldContents) - To get a summary field total results

      Post

      Hello everyone,

      I have a table where i add the next fields:

      • RecordID (Number - Auto-enter)
      • Description (Text)
      • Value (Number)
      • TotalValue (Summary)
      After finish the value input i created the TotalValue as a Summary Field to give me the total of all the values.
      Until here everything went ok.

       

      Now i have a different table with customers information. On this second table i want to retrieve the TotalValue from the first table.
      I've already tried this Get(ActiveFieldContents) but with no result.

       

      In the Costumers information table, i'm trying to do a calculation on a single field between the TotalValue and another field from the Costumers Information table:
      ValueTable::TotalValue*Field1/1000

      I've alredy done the relationship with the two tables but even so i cant receive the result i'm after.
      Can you help me please?
      Thanks

        • 1. Re: Get(ActiveFieldContents) - To get a summary field total results
          philmodjunk

          Get (ActiveFieldContents) returns the value of the field that currently has your cursor in it. Thus, it's not the option to use here.

          A summary field can compute a number of different totals depending on how it is referenced. What total do you want for this calculation?

          The total of every record in the table? Every Record in the current found set? Or every record related to the current customer record?

          Also, please describe the relationship that links your records in ValueTable to Customers.

          • 2. Re: Get(ActiveFieldContents) - To get a summary field total results
            JoaoNascimento

            Let me try to explain better:

             

            The ValueTable is a fixed value table. It's a single table with a few rows. Example:

            Description Value
            Lawyer 1000€
            Maintenance 2000€
            Other 3000€
             
               

             

             

            Now this is a fixed value table. Imagine a few more like this and with a third Summary Field with the total of this records rows:

             

            Description Value ValueTotalSummary
            Lawyer 1000€ 6000€
            Maintenance 2000€ 6000€
            Other 3000€ 6000€

             

            This is like i have now in my database

             

            Now, in the Client Tables i have the some fields to identify the client and then i have two fields: FixedValue AND CalcValue.

            In the FixedValue i also have values that should always remain the same, and on the CalcValue is where i want to do a Calculation!

            So the calc that i want to do is:

             

            ValueTable::ValueTotalSummary*FixedValue / 1000  --> This is going to define the CalcValue on each client Records Row.

             

            I know that something is missing here. Even i trying to understand how can i create a relationship with the ValueTable without mess around the records.

             

            Above is the Relationships:

             

             

             

             

            Clients

                     

            ValueTable

            __pkClients

                     

            __pkValue

            Name

                     

            Description

            Address

                     

            Value

            Mobile

                     

            ValueTotal

            Notes

                       

            FixedValue

                       

            Calc Value

                       
                 

            CalcValueRelationship

                 
                 

            _fkClients

                 
                 

            _fkValue

                 

             

            • 3. Re: Get(ActiveFieldContents) - To get a summary field total results
              philmodjunk

              You are trying to explain here, but it still doesn't make sense.

              when you open manage | database | relationships, which of these relationships do you have?

              Clients::__pkClients---=----<Fixedvalue::_fkClients

              or do you have:

              Clients::_fkvalue>----=----Fixedvalue::__pkvalue

              To ask it another way, your sample data shows a total vale of 6000. Is this the total of all records in the fixed value table?

              and do you want that 6000 to be used in the calculation like this?

              6,000*FixedValue / 1000

              When you refer to a summary field from a related table, you do not get the total of all the records in the table nor do you get the total of all the records in the found set. Instead you get the total of all the records in the fixed value table that are linked to the current record in your client table.

              If you want to use the total of all records in the fixed value table, use a relationship defined like this:

              Clients::anyfield>-----X-----<Fixedvalue::anyfield

              The fields you match on do not matter when you use the cartesian join operator (x). It matches any record in clients to all the records in fixedvalue so with this relationship: ValueTable::ValueTotalSummary*FixedValue / 1000

              ValueTotalSummary will be the total of all records in the value table.

              • 4. Re: Get(ActiveFieldContents) - To get a summary field total results
                JoaoNascimento

                I've changed the relationship to make things easier.

                By now i have this:

                 

                Clients::__pkClients ---=---ValueTable::_fkClients

                 

                The scenario on my database is this:

                 

                Clients

                Name Address FixedValue CalcValue __pkClients (Auto-Enter)
                John Smith NewYork 0,3667   1
                Joanne Smith NewYork 0,6668   2

                 

                ValueTable

                Description Value ValueTotal (Summary Field)
                Lawyer 1000€ 6000€
                Other 5000€ 6000€

                On this table, when i created the Summary Field, to totalize all Value records, it created the total value for each record on ValueTotal field.

                NOW:

                On Clients Table, in the CalcValue field, and on each record i want to do the following calc:

                 

                Name Address FixedValue CalcValue __pkClients (Auto-Enter)
                John Smith NewYork 0,3667  6000*FixedValue / 1000 1
                Joanne Smith NewYork 0,6668  6000*FixedValue / 1000 2


                I Just tried to Change the relationship to: Clients::__pkClients---X---ValueTable::_fkClients and it works. Now the values are calculated automatically.

                Thanks!

                Sorry for the mess!