Find Unique records

[eddyb2]

Hi all

I need to run a find script that only finds unique records in the database.

The only way I can see to do this is in reverse - find all duplicates using "!" then omit found set.

Is there an easier way?

Thanks

Ed

[LaRetta]

Hi Ed,

The only way I can see to do this is in reverse - find all duplicates using "!" then omit found set.

This wouldn't work anyway ... you will end up with BOTH records which are identical. Can you explain a bit about the purpose and context? Tell me a bit about your table structure, the type of data (is it contacts, invoices etc) and can you briefly describe your tables?

There are many ways to achieve what you wish and the best way will depend upon 1) what you are doing, 2) is this a one-time or ongoing need, 3) what do you want to do with the unique found set and so forth.

LaRetta :wink2:

[eddyb2]

Hi LaRetta,

Thanks for your reply.

Here is a basic overview of the what I am trying to do. There is only 1 table involved.

This table is called HistoricData

This is to archive the amount of stock sold each day

(i have a script set to delete anything older than 1 year each night, so the table only ever holds 365 days of data for each stock code)

Each evening the days data is added as a new record per stock. This is done by importing a text file from another program (I use the Troi File Plugin to get exactly what I want here)

So lets say we only have 3 stocks, their codes are 0001, 0002 and 0003

I have been selling 0001 and 0002 for 2 years so there is a historic record in there for these stocks (for the last 365 days an import has occured for these stock codes). 0003 is a new stock.

So when the overnight import runs it will import that days data for the 0003 stock.

Now, this record is unique, in the whole table there is only 1 record for 0003 (dated today)

I need a way so that each night I run a script to show only the unique (new) stocks - where there is only 1 record in the table for a stock code.

Then - I have a script that loops through the found records and adds a "Y" to a field called Unique

I will use this unique field later on that night for more scripts...

Hope that makes sense!

Many thanks

[LaRetta]

Then - I have a script that loops through the found records and adds a "Y" to a field called Unique

I will use this unique field later on that night for more scripts...

Hope that makes sense!

Well, I don't understand why it needs to be that complex but, without seeing your file, I couldn't even make a recommendation. But I CAN get you a unique set easily ... use another table. Do you not have a Product table?

[eddyb2]

Yes, that table is called "base"

It details each stock code

[LaRetta]

It really seems that you are using far too much scripting which can be handled by proper relationship instead.

Have you considered also adding the new products to Base (your product table) and using THAT table for your scripting later, instead of flagging a record as unique within a mass of archive data? You can import directly to your Base table at same time you import to Archive. Your import into Base would be accomplished simply by setting the validation tab on your Stock Code to 'unique' and 'validate data always' and uncheck 'allow user override.'

Flagging records as 'unique', as you've found out, isn't a great idea. What kind of scripting takes place later on that requires using the archive table instead of the Base (Product) table? You've explained what you are doing, Ed, but I still can't envision why. I wish to help and not be critical but your process seems so twisted that I can't logically even envision what should happen. I suspect you are twisted as well and that is why so much scripting and flagging is required. Would you consider posting your file as well (or at least sending it to me back-channel)?

Sometimes when one is heading down a convoluted path, they can't see it clearly AND they must keep twisting their solution to make it work when, if shown a different perspective, they can get back on an elegant, simple path.

LaRetta

[LaRetta]

Whoa. Why is this post different from this one? For instance, I didn't know you wanted unique (as in there is only ONE of the codes in the table) instead of unique, as in ONE EACH of every code. Comment's method would certainly work if you only want codes with one record. But what if Code 0003 comes in twice into the Archive in one import?

If you don't get help on a post, keep going back to the post because those prior posts adds information to what I'm trying to give you.

I know, not pretty but with what I've bene given it is what I need to do.

No it is not. What you need to do is change what you have. I suggest you hire someone to assist you in getting your structure straight. In your prior post, your tables are not normalized, the naming is not consistent with the purpose ... you need assistance with it, Ed.

And no, I'm not saying it to get hired to fix it; I'm not currently available. :laugh2:

Edited January 18, 2009 by Guest

[eddyb2]

To be honest I had forgotten all about that post. This is an old problem of mine that I am revisiting to try and make not only a cleaner solution but one that runs quicker.

I will revisit that post and try to work out from there what it is I am doing. If i remember right the topic turned into a discussion whther a custom finction worked corretly or not - I may have to revisit that custom function.

Thanks for your help on this anyway though

Ed

[LaRetta]

That custom function didn't work. The issue is your structure and process. You are scripting to horse things instead of letting a proper relationship run most of it for you. Enough said ... you do as you wish. I've no doubt it takes a long time to run and must be handled at night. And it shouldn't!!

We will try to assist as best we can nonetheless. :wink2:

Edited January 18, 2009 by Guest

[comment]

Just a small note:

IMHO, finding the opposite of what you need and then switching to omitted is a rather elegant method. I often use it just to simplify the find criteria - but in this case, it's unavoidable since there is no corresponding operator to ! that would have the opposite effect.

Other than that, I agree with LaRetta: it sounds like an extremely clunky process.