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?
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?
PatternCount ( yourField ; "Available" )
will return 1 if field contains "Available" , else 0.
Opps made an error need to change the 0 to 1 and Stephen's is much simpler but needs may vary.
YourValueListField = "Available"; 1;
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)
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).
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".
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.
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)
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.