Import found set from table in same db

[AndyCan]

Hi everyone,

This is a quick and probably simple question to answer but i haven't been able to find anything on Google, so here goes...

How can i copy an active record from one table to another table within the same database? The other table is a shadow table via ODBC, but to all intensive purposes is just another table for this question.

I'm thinking it has to do with executing a find set using the current record ID, changing to the shadow table layout and then importing, but it's the first time i've tried something like this.

Can someone guide me through this please? By the way, the functionality will go in a script run by a button on the current active record.

Thanks!

[TheTominator]

I'd recommend that you do the sequence manually first. Keep track of what you did and then use that as a guideline for what script steps are required. You will quickly discover if there are any logical or technical pitfalls with the concept.

If you are carrying out the Find operation on the current record ID solely to make the current found set contain only the current record, there is an simpler way to do it.

Show All Records

Omit Record

Show Omitted

The current found set will now be only one record, and it will be the record you were browsing.

[AndyCan]

Show All Records

Omit Record

Show Omitted

Thanks Tom,

That little trick was just what i needed. Then i just go to the other layout and run the import. Works great

Andy

[LaRetta]

The current found set will now be only one record, and it will be the record you were browsing.

The second part is always true but the first isn't necessarily. There is possibility (although not likely) that, if multi-user, another user will create a new record in that nano-second when you have your record Omitted. Then when you 'Show Omitted' you won't have just one record but two (or more).

The prettiest solution I've seen was by JT (-Queue-) with:

Freeze Window

Show All Records

Omit Record

Show Omitted Only

Loop

Exit Loop If [ Get ( FoundCount ) = 1

Go To Record/Request/Page [ Last ]

Omit Record

End Loop

The loop omits any new records and only exits the loop if only one record is found ... the record wanted. :smile2:

Edited March 27, 2010 by Guest
Added End Loop

[TheTominator]

The prettiest solution I've seen was by JT (-Queue-) with:

Freeze Window

Show All Records

Omit Record

Show Omitted Only

Loop

Exit Loop If [ Get ( FoundCount ) = 1

Go To Record/Request/Page [ Last ]

Omit Record

The loop omits any new records and only exits the loop if only one record is found ... the record wanted.

What if you are using FM 10 which preserves the sort order when adding new records? If the new record is now sorted to come before the current one, then going to the last record will omit the wrong one, right?

Maybe it needs to be tweaked to add unsort...

Freeze Window

Unsort Records

Show All Records

Omit Record

Show Omitted Only

Loop

Exit Loop If [ Get ( FoundCount ) = 1

Go To Record/Request/Page [ Last ]

Omit Record

Edited March 27, 2010 by Guest
Added proposed remedy

[bcooney]

Got to find this in the documentation, but it is my understanding that the sort order is not updated during a script.

Edit: Found it. KB 6981:

Scripting Behavior

A common script sequence is to loop over a set of records and perform some action on each record. If this action modifies a field which is part of the current sort order, this could result in reordering the affected record which in turn could cause very unexpected behavior for the script. Some records might be skipped and other records might be processed more than once.

Therefore, the reordering of records does NOT occur while a script is running or paused. Records modified during script execution are only reordered once the script execution is completed.

Edited March 27, 2010 by Guest

[TheTominator]

Ah... Good to know. Thank you bcooney.

[LaRetta]

What if you are using FM 10 which preserves the sort order when adding new records? If the new record is now sorted to come before the current one, then going to the last record will omit the wrong one, right?

Maybe it needs to be tweaked to add unsort...

I forgot that I wanted to respond here; I've been a bit behind in everything.

I really appreciate you thinking of these things because it all is very important!

Two steps in this script will nix the possibility of sort interfering (in addition to what Barbara added). If the current set is sorted when the script starts, Show Omitted Only turns them to unsorted automatically, as does Show All Records (if they all weren't shown when it is fired). And if unsorted then the records automatically fall into natural sort order of creation.

I have not tested this behavior in versions 10 or 11 yet but if the behavior changed on these two script-steps then it would be a total nightmare for everyone.

Edited April 2, 2010 by Guest
Added sentence