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

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.

Quick and easy answer is to use GetNthRecordUnique custom function.

Have you tried it?

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

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

2. What is the purpose of getting this list?

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

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.

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

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

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

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?

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

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

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

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.

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

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.

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

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?

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.