Value List Calculation?

[adyf]

Hi,

Is it possible to create a calculation field or number field that will display the number of entries in a value list? Thanks in advance.

[LaRetta]

Do you want to know the number of unique entries in a field which has a value list attached?

Or do you want to know how many entries are listed in a Custom Value List? You might try:

ValueCount ( ValueListItems ( Get ( FileName ) ; "yourVLname" ) )

The calculation should be number and unstored.

Edited May 12, 2009 by Guest
Added sentence about unstored

[adyf]

Thanks for the response LaRetta. I should have mentioned that this is a work project and I only have access to Filemaker 6 as the company won't support it any longer. Therefore I cannot use the ValueCount function as it does not appear in FM 6. Is there another formula to use?

[LaRetta]

Try:

PatternCount ( ValueListItems ( Status ( CurrentFileName ) , "YourVLName" ) , "¶" ) + 1

Wow, going back to vs. 6 is strange! We are so spoiled now!

[adyf]

Just to be clear when I enter current filename, do I enter it exactly as it is i.e with spaces between words and .fp5 file extension? the filename is highlighted in blue and says 'this constant is not a recognised status flag'

[LaRetta]

PatternCount ( ValueListItems ( Status ( CurrentFileName ) , "YourVLName" ) , "¶" ) + 1

The only thing you need to adjust at all is what is within quotes specifying your value list name. Leave everything else exactly as is.

Why am I not suggesting you enter your fp5 file name? Because that will mean hardcoding your file name within the calculation. And if you EVER change that file name, your calculation will break.

By specifying 'generically' to use the 'existing current file name' it will never break for you. So Status ( CurrentFileName ) (and in current versions

Get ( FileName ) is always preferred. Spaces are ignored in FileMaker calculations and I learned to use them because it makes the code more readable.

So copy and paste this calculation and then just change what is between the quotes and you should be good to go!

[adyf]

I have entered it as below and unfortunately still get the same error:

PatternCount ( ValueListItems ( Status ( assessmentplanscopyold ) , "DriverName" ) , "¶" ) + 1

[LaRetta]

PatternCount ( ValueListItems ( Status ( assessmentplanscopyold ) , "DriverName" ) , "¶" ) + 1

No.

PatternCount ( ValueListItems ( Status ( CurrentFileName ) , "DriverName" ) , "¶" ) + 1

Don't put your file name in there at all. Copy paste EXACTLY what I give you. :wink2:

[adyf]

Oops sorry obviously can't read instructions!

Entered it correctly this time and it returns a value of 1 when it should be 28.

There may be another way of achieving what I'm trying to do. I'm trying to do a staff head count. All of the staff are on a value list therefore I thought the easiest way was to count the number of entries on the value list but doesn't seem to work. However, I have 280 records in the database and each record has a persons name and each individual name has several records. Can a calculation be done that counts the number of unique names? i.e I have 28 staff and 280 records. Also can the calculation show 28 at all times and not just work on the found set?

Thanks for your help so far.

[LaRetta]

"Can a calculation be done that counts the number of unique names? "

Yes, and that is what the calculation does. I would suspect a few things:

1) Your value list is not based upon 'all values' from the table you are talking about and the field with the name.

2) Your name field is accidentally set as number instead of text.

3) Your value list is based upon a related value and that relationship is n:1.

4) You did not make the calculation unstored (see Storage Options and check 'Do not store')

Let us know if it still isn't working and we may need to see your zipped, empty file to pinpoint the problem. :wink2:

[adyf]

Points 2 and 4 are ok, although I did have the calculation field set as a number because it is a number value I am after. Set it to text and it still gives a value of 1.

Points 1 and 3 are maybe getting out of my comfort zone for now. I am relatively new to FM although too far in to change to MS Access which my company would like me to do hence being stuck with FM 6. Apologise if my lack of knowledge is frustrating.

[LaRetta]

No, your calculation SHOULD be number. Point 2 I mention was asking about your name field itself - make sure it is text.

Points 1 and 3 simply refer to the settings in your value list itself.

1) Open File > Define > Value Lists.

2) Select your DriverName value list and edit.

3) Does it say use values from field?

4) Click the specify button next to it.

5) At top, does it say all values or only related values?

Past this, if you wish to pin down the issue, I do not think it benenficial to keep posting back and forth particularly since you are unfamiliar with FileMaker. If you wish to post your file, please do so. Otherwise, I suggest that you 'get out of your comfort zone' and put on your thinking cap and begin reading FM Help to understand basics such as value list settings. :wink2:

[adyf]

PatternCount ( ValueListItems ( Status ( CurrentFileName ) , "DriverName" ) , "¶" ) + 1

I have just tried the above again and put a space in between Driver and name and voila!! Therefore the space must be necessary.

All I need to do now is workout how the calculation you gave me achieves this. Many thanks for your help and persistence.

[LaRetta]

Therefore the space must be necessary.

Ha ha ... the space would only be necessary if the space exists in your actual value list name. You must match that value list name EXACTLY within those quotes!! I copied exactly as you had it listed when you gave me the value list name.

I'm pleased it's working now. :laugh2:

[LaRetta]

All I need to do now is workout how the calculation you gave me achieves this.

ValueListItems() function:

[color:blue]Returns a list of the values in the specified value list, separated by carriage returns.

PatternCount() function:

[color:blue]Returns the number of occurrences of the search string (pattern) in the specified text.

So, starting always from the inside-most portion of a calculation (if wrapped by another function), you would:

1) Get the result from ValueListItems() which would give you a list of unique entries from the specified value list with carriage return between each. It is then wrapped by PatternCount() so next ...

2) PatternCount() will count how many carriage returns. Since the list does not have an ending carriage return, we need to add a 1 to properly count the number of lines (and that's why when your value list name was incorrect, it was producing only a 1).

In newer versions of FileMaker, we have ValueCount() which simply counts how many values are in a value list; much simpler. :wink2:

[adyf]

Sorry my fault, but a valuable lesson learned in the importance of accuracy. :

Thanks for the explanation on the formula, I'm beginning to understand a little more. When I was looking at the functions available in the calculation window, I wasn't aware that two or more functions could be combined which is how my problem was solved.

I'd love to have the depth of FM knowledge that you have, fancy swapping brains?

Edited May 13, 2009 by Guest

[LaRetta]

fancy swapping brains?

No, you seem like too nice a person. :

It was also a lesson for me because probably the number one reason this calculation fails is that the value list name entered between the quotes does not match EXACTLY and FileMaker doesn't give us an error message (because it is within quotes meaning explicit text). I should have added that to the things for you to check.

Yes, combining functions (by wrapping a function around another) adds great power and depth and will keep you interested in learning FileMaker forever.