6 Replies Latest reply on Feb 8, 2012 4:19 PM by abbadonnergal

    Using GTRR with calculations and related fields

    abbadonnergal

      Title

      Using GTRR with calculations and related fields

      Post

      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)???

       

        • 1. Re: Using GTRR with calculations and related fields
          philmodjunk

          Your analysis of the issue is correct. the fact that zcFlagReorder is by definition an unstored calculation makes for an invalid relationship. You can see this if you try to add a portal to prd_PRD__ProductsByReorderFlag on your PRD__Products based layout.

          You have two options.

          Move the ParMin field into the products table so that all referenced fields are part of the same table or set up a system that copies the data into the products table to replace ParMin in the calculation. (Out of curiosity, why is it not in the products table?)

          Or

          Don't use a GTRR to pull up the records. You can perform a find, specfiying a 1 in the zcFlagReorder to find all records that are marked with this value for re-orders.

          • 2. Re: Using GTRR with calculations and related fields
            abbadonnergal

             Yeah, that's what I suspected. I guess there's no reason not to use a basic find for this. To satisfy your curiosity: The ParMin field isn't in the products table because a product can belong to multiple departments in my solution. So one department may need a stockpile of a particular product, while another department may only need a few cases of the same product. So the reorder levels are different, and the only way I could think of to deal with this is to have a ReorderLevels table related to Products and Departments.

            • 3. Re: Using GTRR with calculations and related fields
              philmodjunk

              For finds or GTRR, a local, indexed field will produce faster results. Such a field is possible to implement here if you use careful scripting. What you do is use script triggers on the layouts where your departments set the re-order levels so that a script kicks in and modifies the total reorder level in products any time they change a value.

              Hmmm, if each department sets their own re-order levels, might it make more sense to produce this list from the related table where these levels are set instead of products? Just brain storming here, so don't feel that's some kind of strong recommendation. My main thought here is that such an approach would allow you to identify the department that reached a re-order level as well as identifying the product to re-order...

              • 4. Re: Using GTRR with calculations and related fields
                abbadonnergal

                Thanks for the advice! A find seems to be fast enough for now without the field being indexed. I'm still using GTRR to get a found set of products sold by a particular vendor (set by a global "zgVendor" field), then I'm constricting the found set by the Reorder status and product satus (active products). This seems to work fine. Perhaps there will be something that bites me in the butt later on, which will encourage me to copy the reorder levels into the product table via script... The reorder levels are set in a related "ReorderLevels" table from the context of Products (via related fields in a Products layout) when the global vendor field is selected, so it updates the reorder level for the needed department without the user's needing to leave the Products layout.

                • 5. Re: Using GTRR with calculations and related fields
                  philmodjunk

                  I doubt you'll need to change that approach. Constrain Found set only works on the current found set so it usually is very fast even when unindexed fields are part of the criteria. I use that method when performing finds on tables with massive numbers of records. I perform find #1 specifying criteria only in indexed fields, then return to find mode specify criteria in unindexed fields and constrain the found set.

                  • 6. Re: Using GTRR with calculations and related fields
                    abbadonnergal

                     Great! I hadn't used that script step before, but it looked like it would work. Thankfully the name "Constrain Found Set" was indicative of what it accomplishes ;-)