In MYSQL I have a table of Customers and a table of Addresses. I have imported these tables via ESS as shadow tables on my FM server.
A customer may have more than one address. The address table has a 'type' field contain values such as 'Billing', 'Shipping', etc.
Let's say I want to show only the billing address for a customer.
In native FileMaker I would simply define a constant field 'Billing' in the Customers table (or a number field where '1' represents billing, '2' is shipping, etc). I would use that constant field, plus the ID field to relate the Customer to the Billing Address.
However when using ESS I cannot create the constant field in the same way.
So... what is the best way to join the tables to show only the billing addresses for each customer?
Should I ask the MySQL admin to add constant fields for me, or perhaps use some kind of global calc field in FileMaker that can be used as a constant? Or is there a better way?
Thanks for any direction,