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.
FileMaker does not determine field types. You specify field types in Manage | Database | Fields. You'll need to select number as your field type in invoiceitem table in order for them to sort into the correct order. (I'm assuming that you use numbers to specify the order here.)