Getting Set Of Unique Values From A Field

[Wickerman]

I'm trying to turn a "flat" (single table) database into a parent-child database. Let's say the original database hold records of Book titles, with a field for Publishers. I want to create a parent table to hold the Publishers, each of which will publish many (child record) books.

I figure as a first step I can just duplicate the Book Table as new Publisher Table, delete all the irrelevant fields and then throw out all the duplicates (assuming all publishers are unique). Then after creating the relationship between the tables via new key fields, I can populate the foreign key field in the Book table by matching up the Publisher names and pulling in the Publisher primary key.

My simple question is how to throw out all those duplicate values in the Publishers table. I figure I can create a self-join, matching the Publisher field to itself and use a Count calculation to make a field that indicates how many instances of each Publisher there are, then write some sort of looping script that deletes records until that count = 1 . . . but I have a suspicion there's a much simpler way of doing this? For instance, it occurs to me that the index of the Publisher field would contain the list of unique values I'm looking for -- but I'm not certain how to "get at" those values and turn them into records?

Thanks for any suggestions.

[omegagoh]

Dear Wickerman,

Usually, I will define a Value List referring to the Field concerned and use function, ValueListItems to obtain the unique values (then use a Script to split the values into records).

However, you may proceed from where you are now. You are close to it!

Steps:

1. Consider the self-join Relation you had created. Let's call the Table Occurrence on the left, PUBR and the right, pubr_PUBR_Publisher.

2. Define a number Field, Publisher No (Number, Auto-Enter, Serial number, next number: 1, increment by: 1) in the Publisher table.

3. Define a calculation field, First Occurrence = If( pubr_PUBR_Publisher::Publisher No = Publisher No; 1; 0)

4. Add both fields to a list/table layout (base table: Publisher).

5. Show all Records and sort by Publisher.

6. Go to first Record then go to Field, Publisher No.

7. Records > Replace Field Contents (Replace with serial numbers, Initial value: 1, Increment by: 1, Update serial number in Entry Options).

8. Ta'la, all the Records with Field Value 0 in First Occurrence are the duplicates which you may delete now.

Warning: Ensure that the Relationship to Book for delete Related Records is uncheck, else you could accientally delete all the Books!

Regards,

Omega Goh

Speedy Scaleable Solutions (Singapore)

I'm trying to turn a "flat" (single table) database into a parent-child database. Let's say the original database hold records of Book titles, with a field for Publishers. I want to create a parent table to hold the Publishers, each of which will publish many (child record) books.

I figure as a first step I can just duplicate the Book Table as new Publisher Table, delete all the irrelevant fields and then throw out all the duplicates (assuming all publishers are unique). Then after creating the relationship between the tables via new key fields, I can populate the foreign key field in the Book table by matching up the Publisher names and pulling in the Publisher primary key.

My simple question is how to throw out all those duplicate values in the Publishers table. I figure I can create a self-join, matching the Publisher field to itself and use a Count calculation to make a field that indicates how many instances of each Publisher there are, then write some sort of looping script that deletes records until that count = 1 . . . but I have a suspicion there's a much simpler way of doing this? For instance, it occurs to me that the index of the Publisher field would contain the list of unique values I'm looking for -- but I'm not certain how to "get at" those values and turn them into records?

Thanks for any suggestions.

[comment]

I have a suspicion there's a much simpler way of doing this?

Define the Publisher field in the Publishers table to validate as 'Unique, validate always'. Then import the records from the Books table.

[omegagoh]

Dear Comment,

You win. That's indeed a much better alternative.

Hehe! I was using a similar self-join for showing both detail and summarized info from the same table (FMP8). Hence, too used to it.

Dear Wickerman,

Further to Comment's approach, you would need to empty Publisher table, change Field definition and re-import from Book.

Same warning as before: Ensure that the Relationship to Book for delete Related Records is uncheck, else you could accientally delete all the Books!

Happy exploring FMP!

Regards,

Omega Goh

Define the Publisher field in the Publishers table to validate as 'Unique, validate always'. Then import the records from the Books table.

[Wickerman]

Comment wins the simplicity prize, but I thank you both for taking the time to reply -- and the longer solution is nevertheless helpful to me as a conceptual exercise.