Evaluating calculation in related table if no related record?

[Justin Close]

I ran into a FM behavior today that just doesn't make sense to me.  Here is some basic test structure:  two tables, related based on the ID (one-to-one), and a calculation field in the related table.  (This is a contrived example, but it is mimicing an actual situation I ran into today.  It is intended to be highly simplified to demonstrate the behavior. )

 

TableA:  ID, Constant1

TableB:  A_ID_fk, Text_calc

     Text_calc = If ( TableA::Constant1 ; "Yes" ; "No" )

 

TableA::ID = TableB::A_ID_fk

 

Conside a layout based in Table A, showing the ID from A, and the Text_Calc field from B. 

 

The odd behavior is that, even if there are no related records in B (or frankly, no records at all), the calculation field from B still evaluates and is displayed on the layout as "No".  It doesn't quite make sense to me that a field that should only exist in a record, is returning a result when there is no record.

 

Can anyone explain the logic behind evaluating this calculation in this case? 

 

Thanks,

J

[LaRetta]

Hi Justin, 

 

Table B must hold a value which might match Table A's ID.  You cannot use a calculation for the child key which checks table A to see if it matches; it is not logical.  Table B should hold a key which is same data type as table A and hold a value same as table A or not.  Then another calculation can check that relationship as:

 

not IsEmpty ( Table A::ID ) ... to know the answer is no.  No need to make it text "No"  ... leave it producing 0.  Then change the number format to boolean and type Yes and No into the boolean for display.

 

But please ... please use real names for the tables because it gives us necessary perspective.  The calculation is always saying NO because you can't use the key to check if it is related.

[Justin Close]

The example wasn't really set up to be logical, just demonstrate the issue.  The calculation in TableB pointing back at TableA is perhaps a bit of distraction.  In itself the calculation doesn't have much to do with my question.  The calculation for that field could have been:

    If ( A_ID_fk ; "Fred" ; "Bob")

The question is about why this calculation even evaluates and displays "Bob" when there are no related records in TableB  (if you are on a layout in based on TableA, that does have TableA records displayed, and showing the "Text_Calc" field from TableB).  If there are no records, and thus no fields, to operate in, why is the calculation engine firing off and returnin a result? 

 

That's how it works, apparently, and I probably just need to accept it and get over it, but it's just...odd. 

 

 

-- J

[doughemi]

 The argument in an If() is a Boolean statement which returns either 1 or 0 (true or false).  If A_ID_fk is non-numeric text, it evaluates to 0, and thus the false result ("Bob") is returned by the If() statement.  If it is empty (as it would be if it were a reference to a non-related table), it is also evaluated as false.  If it were alphanumeric, the numeric part only would be returned, and evaluated as being 0 or not-zero.

 

It's not odd; it's logic.

 

To summarize, an argument which is empty or 0 is false; anything else is true.

[Justin Close]

Doug,

   The logic isn't confusing, it is why/how the statement is being evaluated at all. 

 

Well...darn it.  Now I can't seem to get the problem to recreate, at least not when I build it from scratch.  It does appear that the showing of a field from a related does NOT show the calculation result prematurely.  There must have been something else going on in the area I was working in previously, because I spent a number of hours analyzing and fixing a bug just because of this 'bug' that I was (apparently) hallucinating about.  Grrr...

 

--  J

[Fitch]

Justin, the droid you are looking for is in the lower left corner of the calculation dialog box:

 

"Do not evaluate if all referenced fields are empty"

 

The checkbox is on by default.

[LaRetta]

    If ( A_ID_fk ; "Fred" ; "Bob")

The question is about why this calculation even evaluates and displays "Bob" when there are no related records in TableB  (if you are on a layout in based on TableA

 

There is nothing illogical here.  If you ask a calculation to provide a result, it will do so.  It does not judge whether your request is logical.  

 

If there is NO related record (no A_ID_fk) then it cannot evaluate to true so the result is the remaining value of Bob.  It is the kind of test we perform often when we want to see if there are related records.  :-)

 

But you were using this calculation as the KEY.  That cannot happen; a key cannot use itself to check itself.

[LaRetta]

BTW ...

 

If ( A_ID_fk ; "Fred" ; "Bob")

 

Why do you not have the table occurrence in front of the field?  This suggests that the field exists in the table you are presently in, i.e., Table A and that you are not looking to Table B at all.  If you were indeed looking to the related table, it would look like:

 

If ( Table B::A_ID_fk ; ...

 

Providing 'contrived' situation instead of the actual one, just has everyone running in circles trying to figure out what you have and what you really want.  It is always best to provide a simple file with your question.  In this way, we can tell you the principle behind why it isn't working.

 

"and I probably just need to accept it and get over it, but it's just...odd."

 

Don't do that.  If you do, you will never truly understand the principle behind it.  This is an opportunity ... take advantage of it!  We're here to help you!  :-)