5 Replies Latest reply on Jan 31, 2012 9:17 AM by philmodjunk

# calculation over a relationship possible: Hi all,  quick question I have:  2 tables relationship is...

### Title

calculation over a relationship possible: Hi all,  quick question I have:  2 tables relationship is snpk = snfk. Table one has the pk and I try to create a calculation field cfield in this take  that looks over the relationship whether  : case (  table2:: field1 = ”pop” and table2::field2=”rock”; 1; 0 ). This will be a check box that will show if the calculation is true as checked. .there are multiple record in table two with the same snfk. It does not work for me. when i am on layout based on table one and place table2:: field 1 it gives me only one valeuof I think of the first record that maches the relationship. Could explain why and help me to find solution? I hope I can do it without a table occurrence.  Thanks a lot

### Post

• ###### 1. Re: calculation over a relationship possible: Hi all,  quick question I have:  2 tables relationship is...

Table1::snpk = table2::snfk

With the exception of aggregate functions, a calculation that evaluates from the context of Table1 (such as a calculation field defined in table 1...) that refers to fields in table2 is only able to refer to the data in the "first" such related record.

There are several work arounds that can deal with this issue.

1) You are not limited to a single relationship between two tables. If you add a second occurrence of Table2 and a pair of calculation fields to table 1--one that returns "pop" and one that returns "rock", you can set up a relationship that only matches to records that match by these two values and the snpk-snfk pair:

Table1::snkp = table2PopRock::snfk AND
Table1::constPop = table2Poprock::field1 AND
table1::constRock = Table2Poprock::Field2

2) If you define a calculation field, cPopRock,  in table 2 as: Field1 & " " & Field2, you can use the List function from table 1 and check to see if any members of the list are "pop rock".

PatternCount ( List ( Table2::cPopRock ) ; "Pop rock" )

3) in FileMaker 11, you can set up a filtered portal that displays only records with field 1 = "pop" and field 2 = "rock" if you use this filter expression with the original relationahip I posted at the beginning of this post:

Table2::field1 = "rock" and Table2::field2 = "rock"

• ###### 2. Re: calculation over a relationship possible: Hi all,  quick question I have:  2 tables relationship is...

Hi Phil,

Thanks again for your input. Does option 2 and 3 are possilbe only with creating additional table occurrence or i can try to implemented as i have it right now?

Thanks for clarification cuz i got i little lost!

• ###### 3. Re: calculation over a relationship possible: Hi all,  quick question I have:  2 tables relationship is...

2) requires a new occurrence if you need to use the original relationship for other parts of your system

3) does not require an added occurrence and relationship. (That's one of the things that makes portal filters very useful in FileMaker 11.) Keep in mind though, that this option works best to display data, there are cases where you have to set up a calculation using your related data that won't work with the filtered portal.

• ###### 4. Re: calculation over a relationship possible: Hi all,  quick question I have:  2 tables relationship is...

Hi again,

i did not want to create new topic so i will post in here and it is related to what we discussed so far, and btw i implemented your second option withouth the new table occurence and seems to work fine and updates properly.

However, here is different calculation in table1 that has to get a date that associate with a record from a particualr record in table two. The weirdest part is it updates properly most of the time and sometimes will not update or it does not populate(not sure which one).

here is the formula:

Let(

@list = List(table2::cfield1_filed2);

GetNthRecord( table2::date; ValueCount(Left(@list;Position(@list; "pop rock"; 1; 1)))

)

)

Any idea why it does and it does not work.

• ###### 5. Re: calculation over a relationship possible: Hi all,  quick question I have:  2 tables relationship is...

I made a mistake in my last post. Option 2 does not require an added occurrence as it relies on the original relationship to produce a list of values from all related records.

I can see a potential issue with this clever calculation. If any of the related records returns a null value, list "closes the gap" and thus "pop rock" could be in a position that does not correspond with the "nth record" position for that related record.

Example:

Three related records:

popfield   rockfield
xxx          yyy
<empty> <empty>
Pop          Rock

list returns:

xxx yyy
Pop Rock

In most cases, I'd use a filtered portal to display this date unless it is needed in a calculation.