I haven't been able to work out the logic for the following search/find in my head, any suggestions?

Here's an example of a simple table

FIELD1 FIELD2

Red 2

Red 4

Green 4

Green 1

Green 5

Purple 1

Purple 4

What I want to be able to do is, find a single occurence for each value in FIELD1, but make sure that the single record found is the one with the lowest value in FIELD2.

For example, from the previous table, the filter would find this subset:

Red 2

Green 1

Purple 1

any ideas?

One way to do it involves adding a new relationship and a new calculated field. You would search for "1" in the new calculated field.

You would need to have a field "record_id" which is a unique identifier, e.g. an auto-generated serial number.

Add a new Table Occurrence with a new relationship.

Original Table on left hand side

New table "LowestColor" on right hand side

Field1 = Field1

Sort the right hand side by Field2 ascending

Create a new calculated field

IsLowestColor =

Case(

record_id = LowestColor::record_id; 1;

0

)

Search for 1 in IsLowestColor.

You haven't said how you want to results displayed nor the purpose of wanting this information. It would help to know whether you want the lowest 'red' value displayed when you are viewing red records or do you want to go to the lowest value? It also matters whether this is a one-time thing or an on-going process.

If you want to display the lowest red on red records and lowest green when viewing green records and this is on-going need then I would:

1) Create another table occurrence of your table (this is called a self-join). Simply click the ++ in the graph after selecting your table and name it.

2) Now join Main Table::Color = SelfJoin::color

3) Sort the selfJoin ascending on number. Display the selfJoin fields directly on your main layout (whatever information you want to display).

If you want to go to this 'lowest-this-color' record, script would be simple:

Go To Related Record [ from table self-join ; using CURRENT layout ] and do NOT check anything down in the 'Show Only Related Records' section unless you want to isolate all of one color in your found set.

Beautiful!

Works like a charm, Thanks!