9 Replies Latest reply on Feb 17, 2016 12:09 PM by MichaelHart

    Assigning Numeric Values to Words


      Using FMP-13, I want to assign numeric values (either 0 or 1) to a list of words in value-list so I can calculate if an inventory item is available or not AND determine why it is not available. The value-list of the assigned numeric-values would be: "Sold" = 0, "NFS" = 0, "Consigned" = 0, "Available" = 1.

      I've been accomplishing this by just using a "0" or "1" as the input values and doing a simple "If (test ; resultOne ; resultTwo) but would like to use a value-list to be more descriptive than just a simple "yes" or 'no" but still be able to calculate a "yes" or "no" summation based on all the words in the value-list. I hope that makes sense.

        • 1. Re: Assigning Numeric Values to Words


          Not sure what your structure is and your business model. Is the value list attached to the field that is storing the 1 or 0 or are there two fields one to hold the alpha and a second to hold the value?  Have you considered using an auto-enter calculation base on the case function?


          Case (

          YourValueListField = "Sold"; 0;

          YourValueListField = "NFS"; 0;

          YourValueListField = "Consigned"; 0;

          YourValueListField = "Available"; 0;

          your default value) / default could be 0, 1 or  "" but if none of the above options were picked you might want a default of "" or empty.


          If it's two fields you would add the case function to the one storing the 1 and 0.


          Would you ever want to know how many Sold, NFS,  or Consigned you have?

          • 2. Re: Assigning Numeric Values to Words
            Stephen Huston

            PatternCount ( yourField ; "Available" )

            will return 1 if field contains "Available" , else 0.

            • 3. Re: Assigning Numeric Values to Words

              Opps  made an error need to change the 0 to 1 and Stephen's is much simpler but needs may vary.

              YourValueListField = "Available"; 1;

              • 4. Re: Assigning Numeric Values to Words

                If the field value is restricted to value list, it can be straightforward

                yourField = "Available"

                Using PatternCount() is safer way, but if the field is not restricted, the result can be 2 or more

                (Yes, I know 2 or more is same as 1 in boolean context)

                • 5. Re: Assigning Numeric Values to Words

                  Imho you are mixing 2 different concepts in the same field: Status and Availability.


                  Status can be : empty, sold, consigned or nfs. Availability becomes IsEmpty(Status).

                  • 6. Re: Assigning Numeric Values to Words

                    Thanks for these good suggestions. I haven't got time to implement them this weekend but I will try using PatternCount which, never occured to me and I've never used before.

                    I agree that status and availability are overlapping and somewhat redundant concepts however, I have a seperate "Status" field which includes "framed, to-be-framed, at-framers, archived, lost, stolen, damaged, mine, & unknown". I have another field for "Out and Returned" dates.

                    My "Availabilty" field is for a layout that customers can view but not give them too much information like number of available prints. I just want a field that will tell them if a particular print is available for them to purchase or not and that will indicate why it's unavailable such as it's Not For Sale or it's on consignment or sold or out-on-approval, etc. which, will indicate if it might become available to them in the future.

                    I have a seperate field called "Count Available".


                    Thanks again.

                    • 7. Re: Assigning Numeric Values to Words

                      I tried MaxEh's suggestion but couldn't get the syntax right so... I ended-up using Stephen's suggestion of:

                           PatternCount ( Impressions::Availability ; "Available" )

                      to get a "1" or "0"... then I created an New field in which I used:

                           If ( Availability = 1 ; "Yes" ; "No" ).

                      I can now use the Availability Copy - field to display "yes" or "no" to the layout my customers will see and I can view the details in another layout in the original "Availability" field, which seems to fit my purpose.

                      Much simpler than I was trying to make it.

                      Thanks for the help guys.

                      • 8. Re: Assigning Numeric Values to Words

                        It can be even more simple - you don't need the "Availability copy" field.


                        A Field that is 0 or 1 can be displayed formatted as Boolean (in layout mode, select field, inspector -> data tab -> data formatting section -> Boolean, then show non zeros as Yes, show zeroes as No)

                        • 9. Re: Assigning Numeric Values to Words

                          Awesome! I had to change the Calculation Result to "number" before it worked but, now I can do, as you suggested, and use Status: to garner the details I need, on another layout. I think that does work better, all the way around.