Title
Performing a Case calculation to return a range of numbers
Post
I am using a Case calculation in a Calculation field type and it is not working correctly. When a particular Centre is selected from a drop down list, I want to return a number of Centre ID depending on centre selected. My calculation is as follows:
Case ( Centre="Armidale Minimbah"; 25;
Centre="Ballina"; 1;
Centre="Barrack Heights"; 3;
Centre="Bega Bandara"; 2;
Centre="Bellambi Point"; 4;
Centre="Berkeley Noogaleek"; 29;
Centre="Brewarrina"; 5;
Centre="Broken Hill"; 6;
Centre="Corrimal"; 8;
Centre="Corrimal East KU"; 22;
Centre="Doonside Ngallu Wal"; 27;
Centre="Dubbo West"; 12;
Centre="Eden"; 13;
Centre="Griffith Dorothy Waide"; 11;
Centre="Gunnedah Winanga-Li"; 40;
Centre="Jervis Bay"; 16;
Centre="Kempsey Dalaigur"; 10;
Centre="Koonawarra"; 21;
Centre="Lightning Ridge"; 23;
Centre="Lismore Jarjum"; 15;
Centre="Minto Waranwarin"; 37;
Centre="Moruya Northside"; 30;
Centre="Mount Druitt Yenu Allowah"; 41;
Centre="Narrabri West"; 26;
Centre="Nowra East Clipper Road"; 7;
Centre="Nowra East Cullunghutti"; 9;
Centre="Queanbeyan South"; 31;
Centre="Sanctuary Point"; 32;
Centre="Sanctuary Point The Basin"; 35;
Centre="Toronto Nikinpa"; 28;
Centre="Tweed Heads South"; 36;
Centre="Wagga Wagga Kingfisher"; 19;
Centre="Walgett Koolyangarra"; 20;
Centre="Wallaga Lake Little Yuin"; 24;
Centre="Warrawong Community"; 38;
Centre="Warrawong South Coast"; 33;
Centre="Wilcannia Barlu Kurli"; 39;
Centre="Wollongong Kids Uni North"; 17;
Centre="Wollongong Kids Uni South"; 18;
Centre="Wollongong South Coast Workers"; 34;
Centre="Wreck Bay Gudjahgahmiamia"; 14;
"Other")
The following centres return the correct number:
Armidale Minimbah, Ballina, Barrack Heights, Bega Bandara, Bellambi Point, Doonside Ngallu Wal, Moruya Northside, Warrawong Community, Warrawong South Coast, Wilcannia Barlu Kurli, Wollongong Kids Uni North, Wollongong Kids Uni South, Wollongong South Coast Workers, Wreck Bay Gudjahgahmiamia.
The remaining centres return "other" upon selecting them. I can't figure out why the others are not returning their assigned number! Have double checked the spelling and can't see any problems.
Any help would be gratefully accepted!
Penny
With a related table of Centre names and ID's, you can set up a use values from field value list where the ID is specified for the first field and the name is specified for the second. This allows the user to pick a centre by name but the value list will enter the ID.
More sophisticated approaches allow you to enter all or part of the centre's name and the system "looks up" the ID and enters it for you.
You might check out the Adventures in FileMaking series. Adventures 1 and 2. They are free to download.
Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)
#1 will show you how to create the use values from field value list and #2 shows how to set this up in several different ways where you can enter the name and still link records by ID.
Caulkins Consulting, Home of Adventures In FileMaking