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"
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!
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.
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:
@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.
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.
Three related records:
In most cases, I'd use a filtered portal to display this date unless it is needed in a calculation.