I've created a database that has a line item entry containing a field for Type. This field is populated using a value list that I've created. My ultimate goal is have to a calculated field that will contain only 1 occurrence of each type of item on my line item list, but the List function is resulting in strange behavior.

I have a total of 10 line items encompassing 5 different types. Using the list function my calculated field displays some values as repeated and other values only once, leaving a total of 7 values. Obviously this is less than my total number of line items, but there are also repeated values in my list.

Any idea what's going on?

Edited April 2, 201015 yr by Guest

When you define a value list based it on the contents of a field, for all practical purposes it will always deduplicate the list for you and sort it in to alphabetical order.

List(table::field) doesn't do that deduplication or sorting.

It's behaving perfectly normally. What are you aiming to achieve?

James

My goal is have to a calculated field that will contain only 1 occurrence of each type of item on my line item list

So;

Type 1

Type 1

Type 1

Type 2

Type 2

Type 3

into a list that looks like;

Type 1

Type 2

Type 3

Use the UniqueValues custom function on Brian Dunning's web site. You'll need FMP Advanced to install the cf.

Why not use the design function:

ValueListItems ( Get ( FileName ) ; "YourValueList" ) ?

ValueListItems ( Get ( FileName ) ; "YourValueList" )

Since you already have a value list of Type, James Gill, then it makes sense to use it here (as both jamesducker and RodSierra pointed out). But be sure to make the calculation unstored (in Options, Storage).

If you didn't already have a value list (or you don't want vl behavior of sorting) then using a custom function as Vaughan pointed out would be the way to go.

I'll try this but I don't think that this function is going to work for what I'm trying to accomplish. It looks as if this function is going to list every item of a value list across a specific file. I want this function to list the contents of a value list in content to what has been selected as part of a line item order.

fake edit: I just tried the function and it does list all values across the database and not only those that are part of my line item order.

Brian Dunnings Custom functions seems to work better, although for some reason it's only returning one value at a time. Thoughts?

It looks as if this function is going to list every item of a value list across a specific file.

I don't think you understand the suggestion made here (or that it has been made clearly enough)

In order to list only unique values from the child items, you need to define a SECOND value list - using values from field LineItems::Type, show only related values starting from Orders. Then use the ValueListItems() function with this value list.

Another option (IMHO, preferable) is to use the EXISTING value list in this manner =

FilterValues ( ValueListItems ( Get (FileName) ; "Values" ) ; List ( LineItems::Type ) )

Since value list items are indexed, both of these will be much faster than a custom sorting function.

I'll try this but I don't think that this function is going to work for what I'm trying to accomplish. It looks as if this function is going to list every item of a value list across a specific file. I want this function to list the contents of a value list in content to what has been selected as part of a line item order.

What does that mean? Perhaps you mean, as Comment suggests, that you want only values from a particular order. That's easy enough and he tells you how.

But that isn't quite what you're saying. You're adding qualifiers: "what has been selected" and "as PART of a line item order." So how is anything supposed to figure out which part?