1 Reply Latest reply on Mar 21, 2012 6:27 AM by philmodjunk

    <Index Missing>

    pd_1

      Title

      Post

      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. 

        • 1. Re: <Index Missing>
          philmodjunk

          This part of your post is not clear:

          I went to relationships and established connection between CalcTBL::TotalNumberofOrders and MySQLTableName::extra_stock and MySQLTableName::to_order.

          Relationships should match pairs of fields of the same type of data. You appear to have three fields in this relationship. Exactly how did you set up this relationship? What field matches to what field? Do they match with the = operator? (A fileMaker relationship is set up much like the "join" part of a SQL expression. You can think of them as expressions starting with Select *, defining a "join", but not having a "where" or "orderby" clause.)

          In field type I chose "Calculation" and in option using Sum function I added 2 fields (extra_stock and to_order) from MySQL together.

          Can you post the exact calculation used? It looks like you may be confusing the fields to be used to match records with the fields to be used to compute a value in your calculation.

          ...Inventory Tab in filemaker is a tab showing product details

          And that tab control is located on a layout. On what table is the layout based?