10 Replies Latest reply on May 2, 2013 10:59 PM by philmodjunk

    Calculations break when Creating New Records from Related Table

    ClaireCollin

      Title

      Calculations break when Creating New Records from Related Table

      Post

           I used the script below to create new records from one table [Production] into another [NEW].

           Go to Layout ["Weeks" (NEW)]
           Show All Records
           Delete All Records [No Dialog]
           Go to Layout ["Production" (Production)]
           Sort Records [Restore;no dialog]
           Go to Record/Request/Page [First]
           Loop
              If [Production::Mark ≠ "x"]
                  Set Field [NEW::Unique; Production::Unique]
              End If
              Go to Record/Request/Page [Next; Exit after last]
           End Loop

           The tables are related by
           NEW::SKU<>Production::SKU
           NEW::Unique<>Production::Unique

           This works and brings in the correct number of records but the calculations that were working when I imported records no longer work when I use a script to create the records instead. Is there a way to have the script copy over the field information when it creates the new record rather needing to use calculations?

           I can use calculations to fill in the Name and Week fields but the calculations to fill in Shipping and Availability, which are based on the week, only fill in information for the first week. Any thoughts would be helpful.  I have tried changing the relationships as well as the calculations themselves but nothing I've tried seems to work.

        • 1. Re: Calculations break when Creating New Records from Related Table
          philmodjunk

               We'd need to know more about your calculations.

               Is this the actual relationship that you have?

               NEW::SKU = Production::SKU AND
               NEW::Unique = Production::Unique

               Why do you need to match by both pairs of fields?

               

                    but the calculations to fill in Shipping and Availability, which are based on the week, only fill in information for the first week.

               This appears to indicate that your calculation is working correctly but uses a wrong relationship to access the data from the related table. If there is more than one related record, a calculation that refers to a field from the related table will reference the "first" related record. But you can always add a new relationship linking to that table with different match fields if you add a new occurrence of the related table in Manage | Database | Relationships.

               If "Table Occurrence" is a new term, see this tutorial on the topic: Tutorial: What are Table Occurrences?

          • 2. Re: Calculations break when Creating New Records from Related Table
            ClaireCollin

                 I have uploaded my relationship map.  This set up allows me to create records in the Production Table from Supply and Demand.  However, my calculations in the NEW table no longer work. For Instance, when records were imported to Production rather than created I used the following calculation to pull in availability information:
                 If ( Name=Production::Name ; If ( Week1=Production::Week_Year ; Production::Available Totals ; "" ) ; "" )

                 I used multiple table occurances of NEW to make this happen for multiple weeks, and a similar process to pull information into other fields.  I am puzzled as to why when the records are created in Production rather than imported these calculations break.

                 Should I be using multiple occurances of Production now instead of NEW?

            • 3. Re: Calculations break when Creating New Records from Related Table
              philmodjunk

                   Why are Production::Name and Production::Week_Year fields with global storage specified?

                   From the context of one of the New table occurrences, your relationship will either match to all records in production or none of the records in production.

                   Why do you match by Name and not by SKU? Names are less likely to match values reliably than would matching by SKU.

                   and your calculation:

                   If ( Name=Production::Name ; If ( Week1=Production::Week_Year ; Production::Available Totals ; "" ) ; "" )

                   Is this an auto-enter calculation specified in field options? or is it for a field of type calculation?

                   What Table Occurrence Context is selected for this field in the drop down in the top of the Specify Calculations dialog? (New, New 2, New 3?)

                   And given that you have match fields that match by Name and Week, I don't see the purpose of using the if functions like this.

              • 4. Re: Calculations break when Creating New Records from Related Table
                ClaireCollin

                     I appreciate all of your help.  I know my database is complicated.

                     Production::Name and Production::Week_Year are related to the NEW tables.  I believe I had these relationship in order for the calculations to work properly originally when Production was using imported records.  The Availability and other information is specific to the Name by each week, there are 52 weeks for each Name and each week may have unique information for that name.
                     Do I no longer need this relationship? Should I just match by Name?

                     I use Name because that is what needs to be visible and is unique same as the SKUs.  Why would Name not match as reliably as SKU?

                     The calculations are a field type and the drop down specifies the table occurance of NEW depending on the week.

                     What would you suggest to grab information from Production and place it in the appropriate field in NEW since this is not working? I have tried Look-Ups and those don't seem to be wokring either.

                • 5. Re: Calculations break when Creating New Records from Related Table
                  philmodjunk

                       The way the relationship lines connect to Production::Name and Production::Week_Year tell me that you have specified global storage for these two fields in field options. That means that no matter what record is "current" in production, you'll see the same values in these two fields. Global fields do not store information specific to any one record in the table where they are defined. Thus any record in NEW either matches to All the records in Products or None of the records.

                       You need to match your records in NEW to non global fields in Products in order to link records in NEW to a specific record or group of records in Products.

                  • 6. Re: Calculations break when Creating New Records from Related Table
                    ClaireCollin

                         None of my fields are specified as global storage.  Unless this changed because of the script, but I dont think so because these fields do not have the same value for each record.

                    • 7. Re: Calculations break when Creating New Records from Related Table
                      philmodjunk

                           Sorry about assuming global storage, a quick check reveals that several other storage options can all produce the same indicator in your relationships diagram:

                           Global Storage

                           Unstored Calculation

                           Indexing turned Off

                           All produce the indicators shown circled in red in my capture of your screen capture as shown here below. All, however, will prevent your calculations in NEW from working correctly when they reference data in Production and this needs to be changed before they will work.

                      • 8. Re: Calculations break when Creating New Records from Related Table
                        ClaireCollin

                             FMP will not allow me to store those fields because they reference related fields.  

                             For example:
                             Name is a calculated field that pulls from Supply and Demand based on SKU.

                             This is the error message I get:
                             The calculation “Name” cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage.

                             Since it does not reference a summary field, an unstored cacluation field, or a field with global storage; I assume that because Supply and Demand are related to Production by SKU it will not allow me to store this field.

                             Is there a way to force storage?

                        • 9. Re: Calculations break when Creating New Records from Related Table
                          ClaireCollin

                               After doing some more hunting in the forums I decided to use the auto entry function instead of a calculation field and this seems to have everything working again.  Thank you for all your help

                          • 10. Re: Calculations break when Creating New Records from Related Table
                            philmodjunk

                                 Careful, auto-entered calculations will not update when data referenced by that calculation in another table changes. You may need to use a script performed by a script trigger to keep the values correctly updated.