Show related records in text field

[madman411]

Hi everybody

I'm putting a "notes" field on my invoice screen that shows all notes entered that's related to the open invoice in one scrollable text field (the notes are created and modified in another window and in this one instance I would like to avoid a portal). 

It's 90% working, however it is displaying notes that are related to every Invoice in the system, not just the active invoice record:

Let ( [ 

ReturnSub = "\n" ;



header = "";
header = "";


aNotes = Quote ( GetValue ( Substitute ( GetFieldName ( Notes::id_Invoice ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;
bInvoices = Quote ( GetValue ( Substitute ( GetFieldName ( Invoices::id ) ; "::" ; ¶ ) ; 1 ) ) & " b" ;


aKeyPrime = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Notes::id_Invoice ) ; "::" ; ¶ ) ; 2 ) ) ;
aDateTime = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Notes::StampCreated_DateTime ) ; "::" ; ¶ ) ; 2 ) ) ;

aUser = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Notes::StampCreated_User ) ; "::" ; ¶ ) ; 2 ) ) ;
aNote = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Notes::Note ) ; "::" ; ¶ ) ; 2 ) ) ;

bKeyPrime = "b." & Quote ( GetValue ( Substitute ( GetFieldName ( Invoices::id ) ; "::" ; ¶ ) ; 2 ) ) ;


q = 
"SELECT " & aDateTime & " , " & aUser & " , " & aNote & "
FROM " & aNotes & " 
INNER JOIN " & bInvoices & " ON " & aKeyPrime & " = " & bKeyPrime & " 
ORDER BY " & aDateTime & " ASC " ;


result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; "" ) ] ;  

 

List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  ) )  )

Is someone with a better eye able to pinpoint where I made the mistake that's telling the calculation to display all related records? I was more than sure this would have eliminated this problem:

INNER JOIN " & bInvoices & " ON " & aKeyPrime & " = " & bKeyPrime & "

 

[eos]

Funnily enough, it was just stated in the other thread of yours that for a single-table query you can establish a relationship with a simple

WHERE TableA.primaryKey = TableB.foreignKey 

In your case, delete the INNER JOIN line and add e.g.

…
FROM " & aNotes & " 
WHERE " & aKeyPrime & " = ?" ;
result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; Invoices::id ) ] ; 
…

and see if this works reliably.

btw, is aKeyPrime a good name for a foreign key field?

Edited July 12, 2015 by eos

[Wim Decorte]

And again I say... a lot of old-style FM devs overthink the JOIN thing in SQL queries where it is not needed.  A simple WHERE clause like eos has will do just fine

[madman411]

Thank you, guys.

I am learning. I appreciate your critiques. 

[beverly]

Something that may help: if you build your "joins" like you build your relationships "on the graph", then they are far less complex. The advantage of eSQL is that you don't need to actually build on the graph. But the query would be the same:

TableA::field_pk = TableB::field_fk (as FM would show this in the Database Design Report)

becomes (in eSQL):

TableA.field_pk = TableB.field_fk.

The OUTER JOIN is helpful when you need all parents returned whether they have children or not. Otherwise just use JOIN or just the more simple equality in the WHERE.