I am fairly new to FM script writing, and am writing a script to

(i) Import some records from an Excel spreadsheet into a table as new records in that table

(ii) Set two fields in the imported records to specific values (but not change the field values in any records that existed prior to the import)

Here is my problem. After I use the Import Records script step, I was hoping that the Found Set of records in my table would be only the imported records, and then I could step through the Found Set and use Set Field on my two fields. (Such a Found Set is what results from using the File -> Import Records -> File command). However, it seems that after using the Import Records script step, then the Found Set is all records in the table, not just the imported records.

What script steps should I add to get the Found Set to be equal to the records that I have just imported?

Note that the table includes a housekeeping field with a timestamp of the field creation time. So I was thinking that I could somehow use that to find records that have just been imported.

Thank you!

Hi Pip

I wouldn't use the time stamp as a filter for your find as it relies on the system clock not being changed ever and so might prove unreliable.

You say that two of the fields need values setting in them. If that means that all other records already have values in them then could you not just find all records that have no values in one (or both) of those fields?

If that is not a possibility what about using the unique id field of the record?

What you can do then is include the following steps (pseudocode) in your script

Show all records

Unsort

Go to record last

Set Variable [$lastrecord; Value:youruniqueidfield]

do your import routine

enter find mode

find for all records where unique id > $lastrecord

That would give you a found set of those records which weren't in the file before the import. Beware that if this is a multi user system it might break if anyone else added a record during the running of this script

HTH

Phil

ps if you do not have a uniqe id field and do not know how to create one in an existing file get back to me

Phil:

Thanks, that will work. I do have an ID field in the record, so I will put it to good use.

Philip

then the Found Set is all records in the table, not just the imported records

I can't copy here, the import is the found set as well, it's only all records in the table if the recieving base before the import was emptied completly.

I've just confirmed this by a tiny test - it must be because your scripting takes you to a layout of another TO, the selection is only tied to the layout/TO where the actual import is done. It's kind of the backward approach of this:

http://www.sumware.net/robfm/savingfoundsets.php

...so if no found set attribution is made in the other TO, will newly created record start to list in this layout just after the older records.

--sd

But Phil, Imports makes found sets - no need for the extra measures unless the scripting takes you to a new TO of the same table!

--sd

Hi Soren

I too thought that an import resulted in a found set of the imported records but since Pip seemed quite sure of his findings I thought it might be because he was importing from excel. I have only ever imported from FM and so have never had to use the work around that I gave him

Regards

Phil

Then would he need to teach me how to make such a spreadsheet, all my excel imports won't let me see more than the just imported as a found set.

--sd

Soren:

You are correct, I do have a "Go to Layout" step at the top of my script. The reason is that the layout where this script is called is not linked to the target table for the imported records. So I jump to layout that is linked to an (unrelated) TO of the target table.

Could I ask you to explain one item in more detail?

You wrote:

it's only all records in the table if the recieving base before the import was emptied completly

What is a "receiving base" and how do I set it so that it is not empty?

Philip

Soren, Phil:

I think that I have now solved the problem by taking some clues from your postings. I created a new layout that is linked to a TO of the target table that is related to the originating layout's TO (in the past I had used an unrelated layout/TO of the target table) and now the found set is exactly the imported records.

I still do not understand why this should have fixed the problem, but I guess that enlightenment may come with time.

Thank you for your help.

Philip

Good catch Soren

Phil

What is a "receiving base" and how do I set it so that it is not empty?

1) Rightmost part of the importmapping window!

2) Put some records in it!

:qwery:

--sd