5 Replies Latest reply on Jun 29, 2015 1:40 PM by tays01s

    Using 'Evaluate' across remote tables

    tays01s

      Title

      Using 'Evaluate' across remote tables

      Post

      I have several 1 to many relationships from Calc <

      - IO

      - Reqs_a

      - Reqs_b etc.

      Within IO I need to do various 'Evaluate' calcs using data found in the Reqs_a, b, etc tables. These tables are related to Calc by age & sex.

      Q: I'm wondering whether I should go for complex ESQL calcs in IO calculation fields or should I first Evaluate Req_a, b etc on Calc, then Evaluate the values on Calc, within IO? Or might there be a way to more intelligently use reletionships to do simple, nested evaluations in IO, without using ESQL or 'intermediate' Calc fields?

        • 1. Re: Using 'Evaluate' across remote tables
          philmodjunk

          I don't quite see why you need evaluate at all.

          RelatedTable::FieldA + RelatedTable::FieldB

          and

          Evaluated ( "RelatedTable::FieldA + RelatedTable::FieldB" )

          will produce identical results.

          • 2. Re: Using 'Evaluate' across remote tables
            tays01s

            I didn't supply enough info. On tables Req_a etc, each record has a different equation that needs processing through an Evaluation. So, for say age = 6y & sex = m on Calc table, I'd want say record '6' from 'Req_a' evaluated in 'field X' on the 'IO' table.

            I have wondered whether I should create IO::sex = Calc:: sex & IO::age = Calc::age and then relating IO to Req_a etc directly. However, I was wondering whether there was a more elegant method, just to save creating more fields.

            • 3. Re: Using 'Evaluate' across remote tables
              philmodjunk

              Still doesn't read like something that needs the evaluate function.

              age = 6y and sex = m

              Sounds like data that can be used to match to a specific record via a relationship. That, might be done with ExecuteSQL or via a table occurrence in the relationships graph, but neither would make use of evaluate().

              But I can't quite visualize what you mean by:

              I'd want say record '6' from 'Req_a' evaluated in 'field X' on the 'IO' table.

              and that may be why I still see no use for the Evaluate function here

              • 4. Re: Using 'Evaluate' across remote tables
                tays01s

                Sorry, the Evaluation would take place in the IO::EvaluateEq field, to evaluate a calc stored as text in Req_a::Equations field. That equation might be something like: 24*Calc::kg+2.4*Calc::cm+104. So a particular Calc::Age & Calc::Sex, would cause selection of a specific Req_a::Equation record. That text equation would then be evaluated in IO::EvaluateEq.

                • 5. Re: Using 'Evaluate' across remote tables
                  tays01s

                  I have tried using a calc field from the context of 'IO 2':

                  Evaluate(
                  If(
                  ExecuteSQL("SELECT Na FROM Reqs_EN WHERE Age_threshold>? AND Sex=? AND Std=?";"";"";Calc::Age;Calc::Sex;Calc::Std_EN)=".";"No Std";
                  ExecuteSQL("SELECT Na FROM Reqs_EN WHERE Age_threshold>? AND Sex=? AND Std=?";"";"";Calc::Age;Calc::Sex;Calc::Std_EN))
                  )

                  where field 'Na' is a text field (for the Evaluation). However, unless I narrow the search down to just Std vs Calc::Std_EN, I always get "?". Is there anything obvious missing from the syntax?