Cross file relationships, lookups not finding field type in client
I have an application in which I have split the Client app and the database tables into seperate files, let's call the front end (client) and back end (data) for now.
The back end that I have inherited is also grouped into different files. For our example let's say we have a products tables and an invoices table, in seperate files. In the products table I have a column call productList order, so that I might determine here the order that I want the item to appear on an invoice. (don't worry about the logic, it's just an example).
So I have
product.sortOrder (in product.fp7) .... invoiceItem.~prdSortOrder (in invoices.fp7 lookup from product table)
It seems filemaker isn't able to determine the correct field type because when I try and sort invoiceItem on ~prdSortOrder it won't do it.
Further, when I use this column in the front end it won't let me format it, and doesn't recognise that it's a number.