Find and relationship question

[emerywang]

Hi. Is there a way to do a find that displays the last x number of records? For example, I only want to find only the last 12 records in a database (records 29-40 in a 40 record file, for example).

Also, I need to copy the contents of one field in each of these 12 found records to 12 corresponding fields in a single record of another database. So for example, say there is a field called "name" in the original database. In the second database, I create 12 fields called "name1, name2, name3, etc. Then, I need to import the names from the 12 found records into these 12 fields. So the value from the "name" field in first found record of the original database would be copied into the "name1" field in the second database, the second found record's name would be copied into the "name2" field, etc.

Any help would be appreciated.

Thanks!

-Emery

[Ugo DI LUCA]

Hi Emery,

This is how I'd handle this.

1. In File A, create any valid relationship from File A (where fields 1 to 12 are) to File B (call it Relationship#1) and add :

g_IDGrabbed (global, text)

Relationship#2 (FileA:g_IDGrabbed::File B:Record_ID)

2. In File B, create a layout that has only the Record_ID field on it. Call it "CopyAllLayout", and add 2 fields in your file (on another layout) :

- g_RecordIDsGrabbed (global, text)

- g_RecordStart (global, number)

3. Still In File B, create a script 'SortAndExtractLastTwelve':

Show All records

Sort [by Creation Date]

SetField[g_RecordStart, Status(CurrentRecordsCount)-12]

GoToRecord[by field - g_RecordStart]

Omit Next [ ] enter in the box 10,000 and check "No Dialog"

Show Omitted

Go To Layout ["CopyAllLayout"]

CopyAllRecords

GoToLayout[where 'g_RecordIDsGrabbed' is]

Paste[select-g_RecordIDsGrabbed]

4. In File A, create a script 'GrabNamesFromIDs'

Set Field [g_IDGrabbed, MiddleWords(Relationship#1::g_RecordIDsGrabbed,1,1)]

SetField[field1,::Relationship#2:NameField]

Set Field [g_IDGrabbed, MiddleWords(Relationship#1::g_RecordIDsGrabbed,2,1)]

SetField[field2,::Relationship#2:NameField]

Set Field [g_IDGrabbed, MiddleWords(Relationship#1::g_RecordIDsGrabbed,3,1)]

SetField[field3,::Relationship#2:NameField]

.....

Set Field [g_IDGrabbed, MiddleWords(Relationship#1::g_RecordIDsGrabbed,12,1)]

SetField[field12,::Relationship#2:NameField]

So that now, being in a given record in File A, your script would look something like :

PerformScript [external:FileB:'SortAndExtractLastTwelve']

PerformScript ['GrabNamesFromIDs']

may be startng with a sub-script checking for emptyness in fields 1 to 12 before the script is launched...

HTH.

PS: Not tested though.

[emerywang]

Wow, thanks, HTH. I'll have to study what you wrote, and see if I can implement it.

Thanks again.

-Emery