List - unique values

[TJ53]

Hi,

I use the "List" function to get values from a relationship. I would like to get a list of unique values from that list. Example:

From the following list:

A

B

C

A

D

Get the following result:

A

B

C

D

I've tried with the "Filter" function but can't get it to work properly. I think it must be simple but I'm stock right now ... any help would be much appreciated. Thanks a lot!

[comment]

You could define a value list based on the field (related values only). Then use the ValueListItems() function to get a list of unique items.

However, in most cases a better solution is to add a parent table, where each value would be unique. Then you can get your list by pointing the List() function at the parent table.

[TJ53]

Thanks a lot "comment". I have to go for the related value list option, since this is a for a list of item colors that can include pantone 1234, pantone 2334, etc.

In case anybody cares, I have attached an example showing "comment"'s suggestion.

list_unique_colors.fp7.zip

[comment]

I don't get your example. It looks like a standard invoicing solution, where each product is available in several colors. So let's say we have an invoice for two items: wouldn't each item be purchased in a specific color? What's the use of showing a combined list of all colors that these two items are available in?

[TJ53]

Well, this is actually for a "printing machine", I did the example as an "invoice solution" just to make it more clear ... but I see that I created confusion instead.

The issue here is that the guy who operates the machine needs to have a list of unique colors to be put into the machine.

[comment]

So do they invent the colors for each job? Or is there a table of colors to choose from? Because if you have such table, and you append a TO of it to the end of your chain, you will be able to get the unique list directly from there.

[TJ53]

They often use "Pantone" color codes. There can be thousands of them, and many of them are used only once.

[Ugo DI LUCA]

many of them are used only once.

Ok, but then *some* may be used more often. I'd consider Comment's suggestion, a thousand records is not something difficult to handle, and after all it's handy enough to have them recorded somewhere.

[comment]

Well, TJ53 said "thousands" (plural), not "a thousand". But I don't think the actual number matters. The question should be: do you need, from a business point-of-view, to track the colors or not?

Slightly off topic: this could be the perfect example for a "rigid" hierarchical structure, one that cannot be fitted into a recursive BOM data model, as discussed here:

http://www.fmforums.com/forum/showtopic.php?tid/192677/

Let's say the tables are:

Machines -< DailyRuns -< [RunJobs] >- Jobs -< [JobColors] >- Colors

And let's say the two join tables, RunJobs and JobColors, also contain quantities, e.g.

Job x:

• 60% Cyan

• 40% Magenta

Job y:

• 30% Cyan

• 10% Magenta

• 10% Yellow

Run z:

• 500 of Job x

• 350 of Job y

and the task is to calculate the required amount of each color for each run.

[Artone]

I think that I can explain this subject with clarity.

A Job order for a Press. As an invoice with Lines.

Item 1: Cyan, Magenta, Yellow, Black, 871 PTN

Item 2: Cyan, Magenta, Yellow, PTN 871, 877 PTN

Item 3: Magenta, Black, PTN 871, PTN 877, PTN 072

This is thus, cause in the same paper,we can print several

items. My , your and your neiborg visit cards. (item 1, 2 and 3)

In the part where the colors are indicated, it must appear to

print quantity of inks. The sum of all, WITHOUT REPEATING NONE

Cyan

Magenta

Yellow

Black

PTN 871

PTN 877

PTN 072

Total colors: 7

To know what pantone colors , this is a standard colors reference (thousands-Coaten, uncoated, metallic, pastel, etc..). So an Pantone 871(metallic) color is the same in China or in Rusia. I hope, you'll understand.

Edited May 15, 2008 by Guest

[TJ53]

I see your point "comment" and "Ugo", and I really appreciate your feedback. Actually I always try to stick to the relational model. In this particular case, I would create a colors table if this didn't involve using a value list for adding item colors to the portal, and using scripting for adding new colors to the colors table. My client just wants to "tab and enter data": no buttons, no value lists.

In other words, from what I understand, having a separate table for color names would involve:

1 - colors added to the "item colors" portal would have to be entered using a value list, since I guess each color record would have its own pk so the fk would have to be assigned to the "item color" record when created.

2 - new colors (used for the first time, that happens pretty often) would have to be added to the colors table through scripting, so a "new color" button would need to be added to the "item" layout

IMHO, please correct me if I'm wrong.

[comment]

You are mostly right, except for one point: selecting a color does not have to be done using a value list. The color could be selected from a portal or from a list view of the Colors table (in a scripted process).

[bikergeek]

Following is a custom function to take a list of values and return a list of all unique values from that input.

The initial value for the "iteration" parameter is the number of values in the "input" parameter.

/* uniqueValues ( input ; iteration ) */

Let (

[ value = LeftValues(input ; 1) ;

output = RightValues ( input ; ValueCount ( input ) - 1 ) ] ;

Case (

iteration = 0 ; input ;

PatternCount ( output ; ¶ & value ) = 0 ; uniqueValues ( output & value ; iteration - 1 ) ;

uniqueValues ( output ; iteration - 1 )

)

)

[TJ53]

That custom function looks good. Thank you all for your responses.

[David Jondreau]

Here's another CF, with tail recursion and one parameter:

UniqueValues(valueList) =

Let([

//grab value, count occurences

value = GetValue(valueList; 1);

valCountFilter = ValueCount(FilterValues(valueList; value));

//create next parameter

valCount = ValueCount(valueList);

newList = RightValues( valueList; valCount - 1);

//if last occurence of value, return it with pilcrow.

thisResult = Case(valCountFilter = 1 ; value & Case(valCount >1 ; "¶" ));

//create list of unique values and recurse

result = thisResult & Case(valCount > 0 ; UniqueValues( newList ) )

] ;

result

)

[comment]

This doesn't seem to be tail recursive?

[David Jondreau]

I thought tail recursion meant the call was in the parameter and not the result. But clearly, I'm mistaken.

Does tail recursion mean to pass the result as a parameter?

[David Jondreau]

I can't for the life of me figure out how to do this with tail and one parameter. I think I've got the tail part straightened out, but it takes two params. Oh well.

UniqueValuesTail(valueList; newList)

//newlist should be empty to start

Let([

//grab value, count occurences

value = GetValue(valueList;1);

valCountFilter = ValueCount(FilterValues(valueList; value));

//create next parameter

valCount = ValueCount(valueList);

valueList = RightValues( valueList; valCount - 1);

//if last occurence of value, return it with pilcrow.

thisResult = Case(valCountFilter = 1 ; value & Case(valCount >0 ; "¶" ));

//create list of unique values and recurse

newList = thisResult & newList] ;

Case(valCount > 0 ; UniqueValuesTail(valueList; newList ) ;newList)

)

Edited May 16, 2008 by Guest

[comment]

Does tail recursion mean to pass the result as a parameter?

I think tail recursion means you do not leave anything "on the ground" (i.e. in memory) when you move to the next iteration.