Using GTRR with calculations and related fields
When trying to use Go To Related Record, I'm getting the FMP error message: "This operation cannot be performed because one or more of the relationships between these tables are invalid." The relationship is between a Products table and itself. The purpose is to constrict the found set to only the products that need to be reordered (when the level in stock has fallen below the minimum par level). I highly suspect that this is happening because on one end of the relationship I am using an unstored calculation. But I'm wondering if anyone knows of a possible workaround.
Note on naming conventions:
(TOG management method is "Anchor Buoy". TO names listed with upper case 3-letter abbreviation, i.e. PRD, 2 underscores + table name. Unidirectional relationships show base table as lower case abbreviation and buoy table as upper case abbreviation, indicating base table. So TO names indicate relationships like this: abbreviation_ABBREVIATION__TableName, to indicate direction of relationship. Calculation field names prefixed by zc...)
The relationship is based on 2 fields:
Field Name / Type / Options/Comments
zcConstant1 / Calculation / Indexed, from PRD__Products, =1
zcFlagReorder / Calculation / Unstored, from PRD__Products, = If (zcTotalQuantityInStock ≤ prd_PAR__ParLevelsByProductIDDept::ParMin; 1; 0)
Fields used in GTRR is as follows:
Table: PRD__Products = Table: prd_PRD__ProductsByReorderFlag
zcConstant1 = zcFlagReorder
Script step details:
Go to Related Record [Show only related records; From table: "prd_PRD__ProdctsByReorderFlag"; Using layout; <Current Layout>]
Since zcConstant1 is always 1, whenever zcFlagReorder = 1 it should Go To Related Record and only show me Products that need reordering. zcFlagReorder contains a 1 for each Product record that is under par and a 0 for each product record that isn't. I can't store the results of zcFlagReorder, because it references a related field. I have to track the product reorder levels in a seperate "ParLevels" table, because I am dealing with multiple par levels for multiple departments. If I can't use GTRR because of the way my fields are set up, how can I script it so it will generate a found set of records where zcConstant1 = zcFlagReorder, or where the stock level is greater than the minimum par (reorder level)???