I have two databases. They have been set up with a relation based on a unique serial number. When I type in the serial number or serial numbers (separated by a carriage return) into the number field in database "A", it gives me a list of colors from database "B". Some of the colors for each serial number may be the same. Currently, I am getting a list of colors that are duplicates (ie: Blue, Blue, Red, Blue, Yellow). I want a field that simplifies/consolidates the list of text to just one of each (Blue, Red, Yellow). How do I go about this?

Edited February 9, 20232 yr by mhemans

8 minutes ago, mhemans said:

I want a field that simplifies/consolidates the list to just one of each (Blue, Red, Yellow).

I guess you want a calculation field (result is Text) that does something like:

UniqueValues ( List ( B::Color ) )

A better solution might be to have a table where each color has a unique record. But we would need to know more about what your database does. 

---
P.S.  Please do not use abstract names like "A" and "B" - they are difficult to follow. Give your tables names that represent the entities they track.
Also, a field that contains a return-separated list should be a Text field.

 

 

8 minutes ago, comment said:

I guess you want a calculation field (result is Text) that does something like:

UniqueValues ( List ( B::Color ) )

A better solution might be to have a table where each color has a unique record. But we would need to know more about what your database does. 

---
P.S.  Please do not use abstract names like "A" and "B" - they are difficult to follow. Give your tables names that represent the entities they track.
Also, a field that contains a return-separated list should be a Text field.

 

 

I appreciate the answer and suggestion. That did the trick.