Perhaps the calculation field's result type is number when it should be text?
Jeeez, yes, that was it! :)
On a "related" note, I have two tables, student and student2. They both have the same structure and data except for one row, which I'm trying to display (limit) via a relationship.
If I define a relationship where firstname <> firstname and lastname<>lastname, then regardless of which table in the layout I choose to view the data, it's not correct. If I pick the side where there are two records not in the first table, then I see both records regardless. If I choose the point of view the table with all the students, I see just one record repeated.
I was thinking this would be sort of like an "inner join". Therefore, even in a non-portal situation, if I create a relationship between two tables, I would only see matching records and not the others.
Clearly I'm missing something.
I can't quite picture the relationship that you defined. <> normally stands for "does not equal" and that makes no sense here. Did you mean:
Table1::FirstName = Table2::FIrstName AND
Table1::LastName = Table2::LastName
If so, keep in mind that you would appear to have duplicates here and your are describing a layout desing where you apparently only display data from the first such matching record from the related table.
Thanks for your reply....Let me clarify.
Say you have two tables, ColorA and ColorB with a single "color" field.
ColorA has this data:
Color B has this data:
In SQL, I think what the FMP relationship graph gives me is this:
-- inner join
select colorB.color from colorB, ColorA where colorB.color = ColorA.color
(which in SQL only shows one row)
I set up the relationship as shown in the graph below and set the layout in ColorB to use ColorA as a reference.
Note that FileMaker shows three record positions and then also (one of the three) the matching color?
I was thinking it would only show the one matching record so I'm missing something.
I guess the relationship graph is really not exactly like an "inner join" since it includes blank records for non-matches.
Look forward to hearing back.
P.S. If I switched the = to <>, I was thinking the relationships graph would give me something like this:
ExecuteSQL ( "select * from ColorA where color not in (select color from ColorB)" ; "" ; "" )
Perhaps I really don't understand the limitations of the relationships graph.
A relationship in the graph is more like this:
Select * From ColorA Left Outer Join Color ON ColorA.Color = ColorB.Color
No WHERE, NO Order By. Where is analogous to performing a Find. Sort Records similar to Order By.
Thanks Phil. That makes sense.
Appreciate the reply. Perfect as always. :)