List unique records

[eddyb2]

Hi all,

I have 2 tables..

StockCodes

CreateFile

In CreateFile, I have a field called List.

At the moment, this is a calculated list that lists all records (stock codes) from the table StockCodes.

So the list shows

StockCode1

StockCode2

StockCode3

...

Does anyone know a way whereby this list will only show the unique records from the StockCodes table.

In other words the stockcodes table may show a stock item code in more than 1 record. I would like the list to only show the stock item codes that are listed in 1 record only in that table.

Can any of you experts out there advise on the best way to do this please?

Many thanks

Ed

[bcooney]

Quick and easy answer is to use GetNthRecordUnique custom function. However, I have a feeling that your database structure is flawed. If you describe your setup and functional goals in more detail, we can point you in the right direction.

[comment]

Quick and easy answer is to use GetNthRecordUnique custom function.

Have you tried it?

[eddyb2]

Thanks bcooney. I will take a look at the custom function and see if I can use that. I think I will be able to.

The structure is as follows:

Table: HistoricData

Field1: StockCode

Field2: Date

Field3: Amount

Filed4: RelationSetup

Table: CreateFile

Field1: Text (List field)

Field2: RelationSetup

I am sure this is not "proper" but it works...

The relationship is basically rubbish. All records from HistoricData relate to the 1 and only record in CreateFile by the 2 RelationSetup fields.

I can see experts cringing!!

Anyway, this provides the list in CreateFile - a list of all stockcodes from the related records (all of them) in HistoricData.

I want the list to show only the records that are unique - so where there is only one instance of the stock code in Historic Data.

For info, if it matters, the field called Text in the 2nd table is not just a list - it also has static text around the list so...

Here are the stock codes with only 1 record

Stock1

Stock2

Stock3

Sort them out...

Could I use the custom function using my "fake" relationship?

Thanks again - of course open to any suggestions on how to do this better!! Always eager to learn the "proper" way of doing things.

Ed

[comment]

1. How often does the data in the HistoricData table change?

2. What is the purpose of getting this list?

[eddyb2]

Its quite a strange scenario really.

The data in the historic table is submitted every Friday from Excel spreadsheets - something I can't get the users away from - this is using ODBC.

Now each Friday night I need to check if there have been any new stock items added to the historic table - i.e. it is the items first record.

If it is I create an output text file (the Field: Text mentioned in the previous post) which is submitted to an external system which then generates the old data I need.

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

Thanks

[comment]

I don't quite follow. You say "check if there have been any new stock items added to the historic table", but you generate a list of ALL items, old and new alike.

[eddyb2]

Thats my problem.

At the moment the best I can do with my limited knowledge is show a list of all stock codes. I need the list to just show unique stock codes- i.e. new ones - new ones are defined by only have 1 record

[David Jondreau]

So there can't be a stock code that only appears once, but hasn't been submitted before?

[eddyb2]

That's correct - it's impossible in this situation

[comment]

I am sorry, but I still don't get this. Unique is unique, and new is new. If I have a list:

A

B

A

C

A

then unique is:

A

B

C

If I now add to the list, so that it becomes:

A

B

A

C

A

D

D

A

then unique is now:

A

B

C

D

and new is:

D

So which one do you need?

[bcooney]

Yes, it worked for me...what are you suggesting?

[eddyb2]

Sorry, in your terms new is what I need, by unique I meant individual, one of a kind - confusion with lingo!!

Definitely new - i.e.

A

F

B

C

A

D

B

C

I would want this to report D & F in my list as they only appear once

[bcooney]

I'd just set a flag field that I've exported the record. Before an export, find for records where the flag is empty.

[comment]

Yes, it worked for me...what are you suggesting?

Can you post a demo? It doesn't work for me, and I don't think it can work the way it goes.

[eddyb2]

But I never export from the HistoricData Table

I only export 1 field from the CreateFile Table - the text field that makes up the list

[comment]

I would define a self-join relationship of the HistoricData table as:

HistoricData::StockCode = HistoricData 2::StockCode

and a calculation field cCount (result is Number) =

Count ( HistoricData 2::StockCode )

Do a find for 1 in this field.

[eddyb2]

Perfect! Knew someone would know a straight forward way once i made myself understood!!

Many thanks, very much appreciated, apologies for the confusion along the way.

Ed

[comment]

Come to think of it, there is a much simpler (and faster) way to find records that do not have duplicates enter Find mode, type ! (exclamation mark) into the StockCode field, check the Omit box and perform a find.

I should have come up with this at once, but I guess I got all confused by the term "unique".

I still don't get what role "old" and "new" play in this; perhaps you need only to constrain the found set instead of doing a full find?

[bcooney]

I swear I used it successfully, and yet I can't create a demo in which it works! I know in my final solution, I ended up not using it. Thank goodness.