5 Replies Latest reply on Jul 9, 2012 11:08 AM by philmodjunk

    Some Calculations are not performed when record created via Script

    MikeBradshaw

      Title

      Some Calculations are not performed when record created via Script

      Post

      Hi,

      I hope someone can point me in the right direction I have a script that transfers the line items from a quote to an order. For the most part this works, however where i am struggling is to do with currencies.

      I have a base currency that the company operates in, a currency that was used for the quote and a currency that is used to purchase the items (by me). All 3 of which could be different.

      I have a relationship established between the main order table and the line items, with the main order table and the preferences, product tables.

      The script that does the transfer of the line items, firstly creates a new main record for the order and populates this with the main info from the estimate. It then reads the entries for the estimates line items into variables and then creates the necessary new records in the Order Line Item table and uses the Set Field command to write the value from the variable into the relevant field. All seems OK, however if I try to write the Currency Field over to the new record I just get a blank field. If I do a look up I get a blank field and if I try to use a Calculation which basically copies the value from the Preferences (my currency) the Main Order table Order Currency, or the Products Table Supplier Currency I also get a blank field.

       

      I thought that the calculations and lookup would be performed when the record is created (or the key changed).

      I have no idea why this is not working as I do not have issues with Lookups or calculated values anywhere else.

      I am usung FMP11 on a Mac.

      I hope this is enough info. 

        • 1. Re: Some Calculations are not performed when record created via Script
          davidanders

          Fields are Number Type with correct number format.

          Field text is not set to white.   Field font size not set to huge or tiny.  Field font is not a weird font.

          • 2. Re: Some Calculations are not performed when record created via Script
            MikeBradshaw

            Thanks David, but none of the above. In testing I have used a plain form with no fancy (or weird) fonts, colour etc.

            What I have found since the original post is that if I use a Lookup then the lookup does not perform when the record is originally created, but if I change the value of the relationship key and change it back again I get the desired result.

            The script does the following in this order:

            Sets variables with data from the estimates record

            Creates New Record in the Order table

            Copies the Order ID to a variable

            Set Fields in the Order table with the data from the estimate table (via the variables set already)

            Finds Estimate Line Items that need to be transferred to the order

            Sets up a loop that will then copy data from the Estimate Line Item record to variables

            Create a New record in the Order Line Items

            Populate new record with variables set from the Estimate Line Items.

            Loop until all records are copied across

             

            The fields with lookups do not perform their lookups when the record is created in this way. Is this normal behaviour?

            Mike

            • 3. Re: Some Calculations are not performed when record created via Script
              philmodjunk

              What I have found since the original post is that if I use a Lookup then the lookup does not perform when the record is originally created, but if I change the value of the relationship key and change it back again I get the desired result.

              That's a key bit of info that confirms that you have a valid relationship and that you cn trigger a relookup of the data. For fields that use the looked up value setting, any change made to the value in that field will trigger a relookup for all fields with looked up value settings based on relationships that use this field. I believe that this will also occur for auto-entered calculations, but would have to do a quick test to be sure.

              Using variables to copy the data over should copy should also trip this same relookup--note that it's the same thing, it's changing the value of the relationship key from blank to the value from the variable. So the unanswered question is why this isn't happening in your script.  Perhaps the relationship key is copied over and then your script copies over the values from the original record into the looked up value fields to overwrite the value's that were looked up when the relationship key was copied over?

              • 4. Re: Some Calculations are not performed when record created via Script
                MikeBradshaw

                Thanks Phil, I did move the Set Field command related to the id_order to be the last field written to, so that any other values will have already been written to, however I still get the same result.

                I did another test which had the Lookup write a totally fictious value to the field if there was no match (I thought this may also show the lookup had been executed) using the "if no exact match, then use' option. I got the fictious value, however when I manually enter the key filed and change the value and then change it back I get the desired result.

                I even tried doing a Recover on the database (Just in case). Nothing was wrong so this made no difference.

                I tried one last thing - I put some Show Dialog commands to show me whether the id_order and other fields were in place when they were meant to be. It looks like the problem is to do with the commitment of records.

                The Script created the Order record using data from the Estimate and then it created the Order Line Items from the Estimate Line Items. At the end of the script was the Commit Records command.

                I inserted a Commit Records after the main order page record had been populated but before the Line Items were created and now the rates lookup. I thinkt he relationship was valid but the Lookup was actually looking up no value.

                Anyway, I seem to have fixed it.

                Thanks for the help.

                • 5. Re: Some Calculations are not performed when record created via Script
                  philmodjunk

                  Thanks Phil, I did move the Set Field command related to the id_order to be the last field written to, so that any other values will have already been written to, however I still get the same result.

                  If they have auto-enter settings and you want the auto-enter to happen, you shouldn't need to copy them at all.

                  Nothing was wrong so this made no difference.

                  Recover rebuilds the indexes among many other tasks and problems with the index won't be discovered and reported--recover just rebuilds them all. Index issues could affect this part of the process. You didn't need to do that here as you've found the problem (And I agree that the commit record was the issue), but for future reference, it's always a good idea to test the recovered copy even if no problems were reported by the recover process.