Recently I started working on Filemaker Pro Advnced, so I am very new to this app, however I have quite advanced experience in SQL database and its query language.
I have been given a task to create an inventory calculation in flilemaker. The file I am working on is connected to MySQL database via ODBC. I have full access to all tables and fields in MySQL database through FileMaker.
As far I could figure out I have created table in filemaker using "Manage Database" window that I called "CalcTBL". In this table I have a filed "TotalNumberofOrders". I want this filed to calculate by adding value of 2 fileds from MySQL table - extra_stock and to_order. So I went to relashionships and established connection between CalcTBL::TotalNumberofOrders and MySQLTableName::extra_stock and MySQLTableName::to_order. In field type I chose "Calculation" and in option using Sum function I added 2 fields (extra_stock and to_order) from MySQL together.
Inventory Tab in filemaker is a tab showing product details. Let's say I am right now in product "G-Star Black T-shirt" that reflects everything what is in MySQL table under this product's "ROW_ID". When I place a filed in Inventory Tab and make this field to display data from CalcTBL::TotalNumberofOrders it will show <Index Missing> message.
I would imagine it will automatically get the index from the current product displayed in inventory tab (currently showing "G-Star Black T-shirt"), but as I can see it doesn't. So where is the catch? How can I make this working correctly, so the filed CalcTBL::TotalNumberofOrders will always calculate according to displayed product? I am aware that in my calculation I need to refer to current product "ROW_ID" which is the index in MySQL table.