How to list only lowest price for each item?

[NeuroPulse]

Greetings,

Trying to list just the records with the lowest price for each item and having much difficulty.

Example records:

ItemID, Price

1 $1

1 $2

1 $3

2 $1

2 $2

2 $3

I would want it to just list:

ItemID, Price

1 $1

2 $1

omitting the others.

Thanks

[mr_vodka]

Sort your records by ItemID and then price ascending order. You can then use a method to omit duplicate records. Advance Search on these forums for +omit +duplicate or +delete +duplicate

[comment]

Or use a summary report.

MinPtrice.fp7.zip

[NeuroPulse]

Oops. I misread your post the first time.

Thanks.

Edited September 4, 2008 by Guest

[NeuroPulse]

That is excellent. Thanks. I did not know about summary reports.

But I need to be able to do this in table view so I can further manipulate the records from the results.

[comment]

Then this won't work for you, and you need to either follow John's advice* or add an Items table where each item has a unique record.

---

(*) You are not supposed to remove all duplicates, but to omit all duplicates except one in each group. This is not a trivial task - that's why John suggested you look at some previous threads discussing various techniques to accomplish it.

[NeuroPulse]

Thanks. I misread his post.

"(*) You are not supposed to remove all duplicates, but to omit all duplicates except one in each group. This is not a trivial task - that's why John suggested you look at some previous threads discussing various techniques to accomplish it."

[NeuroPulse]

Hi. Did a search and found out about the help file entry called "Identifying duplicate values using a self-join relationship".

Trying to use this to do want I want. Questions about it:

1- It says, "The primary record is the first matching record according to the sort order defined in the relationship."

Can you explain what that means? How do you define a sort order in a relationship?

2 - In

If(Counter = table1::Counter, "Unique", "Duplicate")

what am I supposed to put for table1? Is that duplicate table created in the self relationship?

Thanks

Here is the help page:

Identifying duplicate values using a self-join relationship

This procedure identifies "extra" instances of duplicated records. You specify the criteria that determine which is the primary record.

This procedure uses a self-join relationship and a calculation field referencing the relationship to determine which records are duplicates.

To find duplicate records except the first instance:

1. If you plan to delete the duplicate records that you find, make a backup copy of the file.

For more information, see Saving and copying files.

2. Identify a field that determines a unique entity in your file.

For example, in a Contacts database, the Last Name field is probably not a good choice, because you might have several people with the same last name. Social Security Number is a better choice. You can also create a calculation field (returning a text result) that combines data in several fields to make a unique identifier. An example formula is First Name & Last Name & Phone Number.

3. Define a self-join relationship.

Use your chosen identifying field as the match field in both tables in the relationship. For more information, see About self-joining relationships.

The primary record is the first matching record according to the sort order defined in the relationship.

4. Define two fields:

Counter, a text field with an auto-entered serial number (select Serial number and accept the default values for Next and Increment by).

Check Duplicates, a calculation field with a text result, with the formula:

If(Counter = table1::Counter, "Unique", "Duplicate")

5. Choose Records menu > Show All Records.

6. Click the new Counter field, choose Records menu > Replace Field Contents, and Replace with serial numbers. Again, accept the default values. Select Update serial number in Entry Options, and click Replace.

This will assign a serial number to all existing records in your database. Serial numbers will automatically be entered in new records.

7. Perform a find for Duplicate in the Check Duplicates field.

The first record in any series of duplicates now holds the value "Unique" in the Check Duplicates field, and all duplicate records within the same series are marked "Duplicate".

[mr_vodka]

1- It says, "The primary record is the first matching record according to the sort order defined in the relationship."

Can you explain what that means? How do you define a sort order in a relationship?

The record that will remain as the unique record instead of the duplicate will be the first record that matches your sort criteria.

You can define a sort order in a relationship by the option for sort when defining the relationship.

2 - In

If(Counter = table1::Counter, "Unique", "Duplicate")

what am I supposed to put for table1? Is that duplicate table created in the self relationship?

Yes.

Here is the basic concept of this technique...

You need the following:

1. Unique RECORD_ID ( a properly setup table should already have this)

2. A Calculation that makes up your criteria for what you consider to be unique such as "First Name & Last Name & Phone Number".

In your case, you dont need this extra calc field since the only criteria that you are checking to be unique is ItemID.

3. A Calculation that marks the record ( FM Help uses "Unique" and "Duplicate" ) If(RECORD_ID =

SelfJoin::RECORD_ID, "Unique", "Duplicate")

4. A Self Join relationship keyed on ItemID.

The basic concept is that the step #3 calculation is looking at the related records that is keyed on your ItemID. So lets say there are 3 records with the same ItemID and their unique RECORD_IDs are 1, 5, 99.

For Record 1, it should return "Unique" as the first related record is 1 as well (pending there is no sort order). 1 = 1 so "Unique"

For Record 5, it will return "Duplicate" as the first related record

is still 1. 5 <> 1 so "Duplicate"

For Record 99, it will return "Duplicate" as the first related record

is still 1. 99 <> 1 so "Duplicate"

So now when you do a find, for "Unique" you will get only one of each ItemID record.

[Fitch]

If you have a products table where each record has a unique ID, you could make a relationship to your SoldItems, and sort the relationship by price.

Then all you have to do is make a list layout in your products table, and put the related fields on the layout.

The sample file shows the related prices and the lowest on one layout just for illustration.

products.fp7.zip

Edited September 4, 2008 by Guest
Attached sample file

[NeuroPulse]

I think I understand! -)

What makes this work is that when, for a given field value, a corresponding value is looked up on another table, if there is more than one record with the given value, then the first record found is used.

This technique is taking advantage of that, even though it does not happen that way for the purpose of doing this. It just has to pick a record and it picks the first match.

Is that right?

[comment]

Roughly, yes. Or, if you like: a reference to a related field returns data from the first related record, according to the sort order of the relationship.

[NeuroPulse]

Hi,

This seems like it might be much faster than the self-joining whose calculations for a hundred thousand records is taking a very long time.

Is there a way to create an item table from the itemIDs in the database? There are thousands of different items.

Thanks

Edited September 4, 2008 by Guest

[Fitch]

Easy way to create the item table: export a summary report.

[NeuroPulse]

It works. Thanks to everyone for the help.