13 Replies Latest reply on Feb 13, 2014 7:49 AM by flxbrown

    Script breaks down

    flxbrown

      Title

      Script breaks down

      Post

           Hi,

           I'm having a problem with a basic script to insert a value in a field based on an evaluation of two other field. The script I've written looks like this:

           Case ( 

           Dimmer Schedule::Rack Type = 48 and Dimmer Schedule::Slot≤ 3; "1" ; 

           Dimmer Schedule::Rack Type = 48 and Dimmer Schedule::Slot = 4; "A" ;

           Dimmer Schedule::Rack Type = 48 and Dimmer Schedule::Slot≤ 7 ; "2";

           Dimmer Schedule::Rack Type = 48 and Dimmer Schedule::Slot = 8; "B" ;

           Dimmer Schedule::Rack Type = 48 and Dimmer Schedule::Slot≤ 11 ; "3";

           Dimmer Schedule::Rack Type = 48 and Dimmer Schedule::Slot = 12; "C" ;

           Dimmer Schedule::Rack Type = 48 and Dimmer Schedule::Slot≤ 15 ; "4";

           Dimmer Schedule::Rack Type = 48 and Dimmer Schedule::Slot = 16; "D" ;

           Dimmer Schedule::Rack Type = 48 and Dimmer Schedule::Slot≤ 19 ; "5";

           Dimmer Schedule::Rack Type = 48 and Dimmer Schedule::Slot = 20; "E" ;

           Dimmer Schedule::Rack Type = 48 and Dimmer Schedule::Slot≤ 23 ; "6";

           Dimmer Schedule::Rack Type = 48 and Dimmer Schedule::Slot = 24; "F" ;

           "" )

           Pretty straight forward. Right now it breaks down after 16 records and returns "1" from record 17 onwards (i.e. the first two records of Dimmer Schedule::Rack Type = 48 and Dimmer Schedule::Slot≤ 11 ; "3"; are captured, then it stops.) It does - rightly - not return any value to records that are not Rack Type = 48

           Right now I'm testing it with only importing 144 records, with 48 matching the Rack Type = 48 criteria.

           I've tried running the script as a auto enter calculation and as a set field script after import. Same result.

           Any ideas? What obvious things have I missed?

            

           Thanks!

            

           Felix

        • 1. Re: Script breaks down
          symbister

               Hi

               when you say 'it breaks down' - what exactly happens ? and FYI, that isn't a 'script' as such it's just a calculation :)

                

          • 2. Re: Script breaks down
            flxbrown
            Hi, Thanks for getting involved. When I say break down, I mean that the calculation only runs for a limited amount of records, then stops. This calculation is the specified auto enter calculation for a field. When importing records it only returns the correct value for the first 17 records. After that it returns the value 1, seemingly arbitrarily. I'll post a screen shot of what the finished import looks like when i get back my computer. Felix
            • 3. Re: Script breaks down
              symbister

                   I think you'll need to be more specific in your case statement, for example if Dimmer Schedule::Slot is equal to 4, how does the case statement 'know' which result you want : "A' or "2"

                   I suggest that you carefully define each element to, for example:

                   Dimmer Schedule::Rack Type = 48 and Dimmer Schedule::Slot > 1 and Dimmer Schedule::Slot < 4 ; "1"

                   and also you might like to consider using a Let Statement, for example:

                    

                   Let ([RT = Dimmer Schedule::Rack Type; DSS = Dimmer Schedule::Slot];
                   (Case (RT = 48 and DSS >= 1 and DSS < 4 ; "1";
                   RT = 48 and DSS = 4 ; "A";
                   etc....
                    

                    

                    

              • 4. Re: Script breaks down
                philmodjunk

                     @Symbister, Case selects the result for the first expression to evaluate as true. WHen Slot = 4, "A" will be the result, provided that the rack type is 48.

                     I don't see any errors in your Case Statement, I'd take a careful look at the data being imported to see why you are getting the results that you see.

                • 5. Re: Script breaks down
                  flxbrown

                       Hi,

                        

                       Thanks for the answers.

                       @symbister - Interestingly, this is how I wrote the calculation to begin with and that produced worse results. It's not not that the calculation produces incorrect returns, per se, it's that it stops producing returns after a certain amount of records. It seems that the more ands you include the less likely Filemaker is to carry through the calculation on all records

                       @PhilModJunk - The data imported is a test .xlsx. It only contains the fields and data I need to see if this method works. I'll post a picture of that too.

                  I'm going to try a an If step to take Rack Type = 48 out of each case line and see if that helps.

                  Is there any previous history of processing power (for example) vs. effective number of script steps that you know of, maybe?

                       Thanks,

                        

                       Felix

                  • 6. Re: Script breaks down
                    flxbrown

                         Example of the .xlsx file being imported

                    • 7. Re: Script breaks down
                      philmodjunk

                           To repeat what you have already been told: You do not have a script and thus there are no script steps. Your case function looks correctly written and is far from the most complex one every created in FileMaker.

                           And your auto-enter calculation evaluates record by record so this is not a case where a larger number of records consumes more system resources to do this task than a smaller number.

                           I suggest the following two tests:

                           Temporarily convert your field to a field of Type calculation. You won't lose the calculation, the auto-enter expression will appear as the calculation in the calculation version of the field. You just select the field in Manage | Database | Fields, change the field type to "calculation" and click "change". You should then see the expected value appear in every record. If you don't, you need to take a closer look at your data. You may have some additional invisible data in one of the fields that is keeping it from evaluating as expected. Some of the possible details won't show in the screen shots that you've posted, you'd have to click into the Rackt type and slot fields in a record where you aren't getting the expected result.

                           If the change to calculation field type works, you might just choose to leave it as a calculation field. If you want to keep it auto-entered, you can change it back and then try the next test:

                           Import the data again into a clone of your file, but make sure that the option to enable auto-enter options that appears in a small dialog just before the import is started is correctly selected. If this option is not selected no auto-enter calcs evaluate during the import.

                      • 8. Re: Script breaks down
                        flxbrown

                             Update...

                             (Sorry for being a pain)

                             So amending the calculation to this:

                             If ( Rack Type = 48 ; 
                             Case ( 
                             Slot≤ 3; "1" ; 
                             Slot = 4; "A" ;
                             Slot≤ 7 ; "2";
                             Slot = 8; "B" ;
                             Slot≤ 11 ; "3";
                             Slot = 12; "C" ;
                             Slot≤ 15 ; "4";
                             Slot = 16; "D" ;
                             Slot≤ 19 ; "5";
                             Slot = 20; "E" ;
                             Slot≤ 23 ; "6";
                             "F" ) ; 
                             "96" )
                              
                             leads to the attached result. It seems that reducing the number of operators reduces the fault, but I'm still not getting a expected result.
                              
                             Huh?
                              
                             Felix
                        • 9. Re: Script breaks down
                          philmodjunk

                               You should not need to change your calculation  like this to get the needed results.

                          • 10. Re: Script breaks down
                            philmodjunk

                                 Which column in your screen shot has this auto-enter calculation?

                            • 11. Re: Script breaks down
                              flxbrown

                                   Thanks PhilModJunk,

                                   I know this is a very simple case function - that's why I'm baffled at why it doesn't work.

                                   Each attached example is freshly imported.

                                   Changes in the case function cause a change in the amount of correctly returned records. Invariably the correctly returned records are at the first ones and, then, after a certain number, they become incorrect. 

                                   I do not change the data in the .xlsl file between, so if there where errors in that, you'd expect them to track through, no?

                                   Felix

                                    

                              • 12. Re: Script breaks down
                                flxbrown

                                      

                                     It's the "Outlet Group Column"

                                      

                                     Thanks

                                • 13. Re: Script breaks down
                                  flxbrown

                                       Hi both,

                                       I found the problem - you were right, PhilModJunk, it was a gremling in the excel file. 

                                       The slot numbers hadn't been entered in manually. I used a fill series command, which led to a lot of decimals, that I had unthinkingly edited out.

                                       True, whole, numbers return correct results.

                                        

                                       Thanks for your time and effort.

                                        

                                       Felix