when you say 'it breaks down' - what exactly happens ? and FYI, that isn't a 'script' as such it's just a calculation :)
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
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....
@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.
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?
Example of the .xlsx file being imported
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.
(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
You should not need to change your calculation like this to get the needed results.
Which column in your screen shot has this auto-enter calculation?
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?
It's the "Outlet Group Column"
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.