Find Unique Records

[bdam]

I would like to be able to find and GTRR all unique occurrences within a table. If there are multiple records based on a criteria it would only show the first instance.

Currently, this can be achieved by a looping script that omits records but that's a CPU intensive way of doing things. If FileMaker implemented this I'm hoping they could use the indexes to make it much quicker.

[Søren Dyhr]

Perhaps I havn't understood the premises, but where is the looping in this:

Enter Find Mode [  ] 

Set Field [ pling::aField; "!" ] 

Omit Record 

Perform Find [  ] 

--sd

[bdam]

That omits every record that is duplicated, not just the duplicates.

Say there are three records with the same ID. What I want to do is show only the first record. The omit duplicates step would omit all three. Does that make sense?

I found a better way to script it. Add the ID field to a variable and do a pattern count on it. You don't have to sort first with this method which helps cut the time down. It works well when my criteria is only one field. Not sure how it would scale to use multiple fields as the criteria.

[Søren Dyhr]

Ah! you mean first occurences only - well Ugo's method does it!

--sd

pling.zip

[bdam]

Thanks for the solution Søren. I'm sure it works but it looks kind of crazy doesn't it? I mean would you want to inherit a large database with that structure repeated everywhere?

For the size of my particular problem, I'll take the simplicity of the script over several additional TOs and fields. If FileMaker would include a simple find function they could easily avoid such convoluted solutions.

[Søren Dyhr]

Since I tend to anchor bouy my solutions isn't there anything in it that makes it frightening for me. However If you really wish to de-dupe fast, should you consider the "...group by" feature in the exports dialog, only thing it requires is the data is sorted... take a look at the scripting above, it doesn't really candidate as looping - does it?

--sd

plang.zip

[comment]

I thought the request was to FIND them - not to DELETE the rest.

A 'Fast Summary' type of loop would be much quicker than going record-by-record. Sure, it would be nice to have a 'select distinct' type of command - but if you find yourself needing this often, you are most likely missing a table in your schema.

[Søren Dyhr]

I thought the request was to FIND them - not to DELETE the rest.

Correct, but then make an extra field say auto-enter 1 and make the field to single out upon, a match-field instead and the field to update the auto-enter. This makes us get rid of the deletion.

Very much to our luck is it only the first occurrence of the match field despite the number of them, which gets chosen by the import process.

--sd

plangMod.zip

[bdam]

>I thought the request was to FIND them - not to DELETE the rest.

Exactly.

>you are most likely missing a table in your schema.

See, that's what I thought as well; I just can't get my mind around what's missing. Permit me to include a sample file that illustrates the conundrum.

Example.zip

[comment]

You might want to check your implementation, as it ADDS 5 records to the total count on each run.

Implementation issues aside: while I agree that it is a fast method, there are other considerations when working with existing data, such as:

- order of creation;

- continuity of serial ID numbers;

- creation and modification entries/logs;

and probably a few more that skip my mind at the moment.

[comment]

I am not sure what exactly you want to achieve here. Your structure allows one contributor to contribute two or more articles to a project. These articles can have conflicting status. What then should be the status assumed by the contributor in the context of the selected project?

[bdam]

It doesn't matter much which article's status is shown; it can be any article from the project. The problem is that the contributor always relates to their first project; not the project I came from via the Project layout.

[comment]

Yes, that is so - but so what? Again, it's not clear what your purpose is here. You do get a unique set of contributors related to the project you came from.

[bdam]

Sorry for not being clear.

I am trying to to list all the contributors of a particular project and their status on that project. Status as it applies to a contributor can be defined at the status of their first article in that project.

When you are in the contributors layout you cannot see Contributor 1's status on Project Two.

Edited April 9, 2008 by Guest

[comment]

Well, that is a rather clunky arrangement (what makes the first article in the project so special?).

But possible to achieve by setting a global gProjectID field in the Contributors table to the current project, before GTRR. Then you can use this to filter the articles.

[bdam]

There's a good idea, thanks! I should have thought of the global.

I agree wholeheartedly it's a clunky way of doing things and I don't like it. However, for one dictionary project we have there are over 700 contributors. For this project it's not feasible to manage the status of the 5000+ articles individually. For other projects with fewer articles they will. It a piece of flexibility that's not easy to accommodate.

Additionally, there are other things I want to achieve that require relating a single contributor record to the articles in a particular project. One example is to create a value list that can be transformed to suit various documents we need to create.

[comment]

For this project it's not feasible to manage the status of the 5000+ articles individually.

Perhaps your structure should be: Projects, Contributors, Contracts (a join of Projects and Contributors) and Articles (a child of Contracts). Then the status of each contributor in a specific project can be written to their contract record (where a combination of ContributorID and ProjectID must be unique).

[bdam]

Exactly! I had tried doing a join table between various tables but got nowhere. Hanging the articles off the join table is the key. Thanks for the great idea; I'll have to think it through a bit to make sure it doesn't break some of the other stuff we're doing but that is the better schema.

Thanks again Comment.

[Søren Dyhr]

You might want to check your implementation, as it ADDS 5 records to the total count on each run.

Yikes you're right, the found set when matching are all that does ... what a crappy idea!!!

But it's then interesting to see that esoteric scripting wishes once again, is due to an inadequate relational structure.

Perhaps it should be named the missing table syndrome :

--sd

[comment]

esoteric scripting wishes once again, is due to an inadequate relational structure.

Interesting observation - by now I have forgotten how this started.

[bdam]

While I will agree that in my case the problem is solved by using a more adept table structure I don't think the ability to find all unique records in a table is esoteric by any means.

If there's any syndrome that needs naming it's the requirement of complex Table Occurances and script structures to get around limitations in FileMaker. I'm not trying to harp on FM but at the same time let's not harp on users who look as some solutions and say that there should be an easier way.

[comment]

I don't think anyone was trying to harp on you, or making a personal remark of any kind. Finding unique records would be a nice feature to have, but not really required with a proper structure.

You may recall that early on I said that "if you find yourself needing this often, you are most likely missing a table in your schema." That was an easy guess, because in a relational database finding unique records is merely a substitute to showing the missing parent entity. Creating the missing entity is quite easy, and does not require complex relationships or scripts. These complications are only required if you decide to use a "virtual parent table" instead of a real one, on a regular basis.

Exceptions do occur, but exceptions are by definition esoteric.

[LaRetta]

... because in a relational database finding unique records is merely a substitute to showing the missing parent entity.

How aptly stated and so true. Sometimes that's okay but if you find yourself wanting it more than a few times it's worth adding for sure!

[bdam]

Sorry if I sounded defensive, I see that I did. I didn't feel harped on in any particular way. You are completely correct about my structure being deficient. In fact, I should have seen the structure problem myself; I just got caught up with the join table being in the wrong place.

I was trying to speak in broad generals in response to Søren's comments. I have long felt a tendency or group perception that as long as a problem is solvable, no matter how convoluted the solution is, then there's no reason to improve it.

Edited April 17, 2008 by Guest

[Søren Dyhr]

I have long felt a tendency or group perception that as long as a problem is solvable, no matter how convoluted the solution is, then there's no reason to improve it.

Ha ha! Just bear in mind what kind of tool this is, just like everything else under the Apple umbrella, is the development not propelled by technology demands but instead a wish to bend your perception of what's required. It's a strategy kept very stubbornly, filemaker isn't a database when you look strictly at it, but instead a content management system.

There are numerous of bells and whistles in filemaker where the developer shrugs and says to himself "why on earth..." and other core database topics which seems ignored by some kind of infinite wisdom beyond the developers ability to grasp. Quite a lot of those are regarding event trigging and the speed of the internal algorithms.

Somehow do Filemaker manage to keep us in suspense, what the next upgrade will bring us ... but it's pretty sure it isn't where we expected them to come, such as tightening up the coding behind the internal algorithms.

--sd

[bdam]

the development not propelled by technology demands but instead a wish to bend your perception of what's required

Well put Søren. I guess I have an adverse reaction to having to bend to FileMaker's will.

The trigger example is perfect. FM must be aware that people want this feature. There are plugins that prove that implementation isn't impossible or even that difficult. Yet, will we ever get it? Who knows, it's a complete black box from what I can tell.

Edited April 17, 2008 by Guest

[Søren Dyhr]

Adversivity or direct hostility - I won't go that far. I originally switched from 4D, and the time it took get adjusted to the new mindset is not something I'm longing for again. Every new tool have a learning curve and unfortunately are we supposed to be worth what we're paid

... and here would I rather live with the sometimes Byzantine circumventions you have to shake out of your sleeves with filemaker than, start on a fresh with an entirely new tool where it, no doubt have a much steeper learning curve.

My background is as roadie/soundengineer and hardly what usually would pass as academic. But I've noticed that the most accomplished musicians often find creativity in playing with "...a handcap" - I can't really tell if you academic degrees makes you favour and actually makes you use linear ways of thinking. But I know that what my approach is, it questioning things via Islands of rules, which often too gets a whirl in the blender too.

If you are drilled in an useful acedemic direction applicable to development of databases, would I say you should use these qualifications to pick another tool more in sync with your mindset. This tool's utilization is flooded with people from artsy trades such as musicians, photographers etc.

--sd

[bdam]

Again, well said Søren. You guessed it right, my training is more with RDMSs like MySQL and Oracle.

That being said, I actually like FileMaker (the program) a great deal. Well that, and I inherited a huge set of databases originally design in FM 3. So switching now would mean doing an unthinkable amount of work for one man. We're talking thousands of layouts and reports.

Filemaker is great, and I generally love working in it. There are just times when it seems like a simple change would make certain things much simpler and cleaner to implement and support. And over the last set or releases I haven't seen much of that low hanging fruit addressed. New features are great but streamlining complex workarounds would help a lot of us and the people we pass these projects to someday.