2 Replies Latest reply on Aug 10, 2012 10:05 AM by StevenTempelman

    FMP 12 Calculation Help



      FMP 12 Calculation Help




      I am stumped. I have a value list of accommodation types that has 14 different text values - for example "Hostel" "Lodge" "Resort" etc. that I use in the field "accommodation type"

      Now, I am going to be doing regular exports from this database but need to have this field exported as a numeric value - for example "Hostel" would be type id 1 and I need to have a field - call it "typed" that would have the numeral 1 in it if that particular entry had Hostel as the accommodation type the numeral 2 for Lodge, numeral 3 for resort, etc. 

      I can think of a sloppy way of doing this by setting up 15 new calculation fields. 1 field for each of the accommodation types. For example field 1 would be "if accomtype = hostel then put numeral 1 in field 1 else leave it blank" and for field 2 "if accomtype = lodge then put numeral 2 in field 2 else leave it blank" and so no and field 15 would just have "field 1 & field 2 & field 3" etc. with the result that field 15 would contain the accommodation type's numeric code, there has got to be a better/cleaner way to do this. 

        • 1. Re: FMP 12 Calculation Help

          Hi Steve,

          Ideally you should create a table with an auto-enter serial number called TypeID and one record per accommodation type (with the ID field and text field holding the Type description).  This table would then be used to create a value list based upon all values from field accommodation type, using the TypeID in the left pane and the type description in the right pane.  Below specify 'show only second field.'

          Then place your accommodation type field on your layout, attach the Type value list and assign it as a pop-up.  In this way, User selects and it INSERTS the numeric ID but displays the type name.

          When ready to export, you WILL be exporting the number. This is the *BEST approach.  :^)

          The other option will require hard-coding the values but will only require one calculation (result is number).  But if you add 'types' they must be added in the calc.

          Case (
          Type = "Hostel" ; 1 ;
          Type = "Lodge" ; 2 ;
          ... list all types ending with last type
          Type = "Suite" ; 8

          * there are many reasons you should use IDs instead of the actual field value, main reason being you can change the text but the ID remains the same.  Otherwise if you change the text, it will break any relationships and you will have to find all those values and manually change them to the new value.  Using ID also takes up less space in your solution.


          UPDATE:  Added bold for clarity

          • 2. Re: FMP 12 Calculation Help

            Thanks very much - great ideas. I abandoned the table idea a too much work for now (and I also need to be able to export the alphanumeric part sometimes too) but used the case approach and it worked great!