Join tables using calculations

[jburchill]

Hello all, new to all this.  When creating relationships between tables.  Can you create joins off of calculations?  I have an org number and I need to substring the field to be substring(org,9,4) on each table that needs to be joined.  I keep getting an error with really no error message.

So is it even possible to do this?  Or do I need to add fields in the database to do this? Or even in FM to do this.

[jburchill]

here is the calculations:

SUBSTRING(i_ORGS,9,4) join to substring(i_ORGANIZATION_ID,9,4)

when on the grid tab, each return the correct data.  Just don't know why I can't use this as a join

[bus_pass_man]

Can you create joins off of calculations?

Yes and in one of the sample FM models there's an example of one.  It will be awfully inefficient but needs must I imagine.

I keep getting an error with really no error message.

Have you tried clicking on view details?  In some places, such as the relationship editor preview UI and the data grid, when something goes wrong there is a message to that affect with a link which says 'view details'.  If you click it you get the error message details.