Export and Import Same Records in a single operation

[Agentshevy]

I have come across this technique severally on this forum, but I can't figure out how this really works. Right now I am faced with a situation that requires similar technique and I can't seem to get it work.

I have Table A and Table B.

1. I want to script an import Process that will first export all records in Table A into Table B (kinda A Clone Copy of Table A) Thats just my basic objective

2. Then from there, I would want to be able to do similar Process but this time, I will only update the records in Table B with that of Table A using a match field.

3. I will also love to perform the process based on a found count. ie after performing a find of specific records

I think If I can be guided on achieving NO. 1, then I can similarly script the others..

 

[comment]

2 minutes ago, shevyshevy said:

I want to script an import Process that will first export all records in Table A into Table B (kinda A Clone Copy of Table A)

It's hard to understand what you are asking for. You cannot export to a table.You can import from a table. And you can only import records - so your comparison to a clone is confusing. If you want to re-create the same fields in the target table, you must import to a new table. You probably don't want to script this, as this would add a new table every time it's run.

[Agentshevy]

59 minutes ago, comment said:

You cannot export to a table.You can import from a table

My apologies.. I don't really know the approach to take, so I was making that assumption.. My main objective is to have a clone of Table A on Table B.

I have already duplicated the Table A as Table B, with All major fields except Calculations.. Record creation is only done on Table A, so I would want to script an Import process to that will import the records from Table A to Table B, usually I will be updating the records in Table B with a match field (the ID_Field)

That is my main objective...

[comment]

If you have already created the target table (B), then it's very simple to import into it. Select your file as the file to import from, and select A as the source table and B as the target table. Note that only found records of table A will be imported, and only found records of table B will be updated.

That said, I would question the need to maintain duplicate data.

[Agentshevy]

4 hours ago, comment said:

If you have already created the target table (B), then it's very simple to import into it. Select your file as the file to import from, and select A as the source table and B as the target table.

I will be ticking the box for "perform script without dialogue" on the import record script step because I will love to run the process with any visual dialogue. However, since my file will be hosted on the server, guess I wouldnt need to manually locating my file to select it?

 

4 hours ago, comment said:

That said, I would question the need to maintain duplicate data

The purpose of the technique is to have a decoy Recycle bin for our records. Clients get deleted from Table A when they have unscribed from the service, but sometimes, they do come back. So we wouldn't want the stress of having to re-enter their records over again. We can easily navigate to Table B, and copy the records back. I have already scripted that process.

We do a lot of analysis based on table A, so we have a lot of calculation fields in that table. Initially we were using status field to indicate Active and inactive Clients, but as a result of the overhead caused when performing finds, omitting of records etc, we opted for outright deletion of inactive records. Thats just it..

Thanks for your replies..

[webko]

In this instance, wouldn't it make more sense to only copy across the records that you then plan to delete?

Possible process:

Mark Inactive record in TableA (which it sounds like you might already do)
Find these records 
Move to TableB, Find All in TableB and then import from TableA: Update Matching, Add remaining data as new record
Then remove the records from TableA

Which can all be scripted quite easily...

[Agentshevy]

8 hours ago, webko said:

In this instance, wouldn't it make more sense to only copy across the records that you then plan to delete?

I totally agree with you. Funny enough, i never thought about this .. this would definitely reduce the number of records on Table B, which is very good as my database size is reduced. Thanks for pointing me in this direction.

8 hours ago, webko said:

Which can all be scripted quite easily...

Still battling with the scripting. Especially since I would be uploading my file on the server. On the Import records script step > selecting my file, how would i tell filemaker that my file is on the server and no more on the desktop?

because my file is currently on my desktop, but will be uploaded to the server after i am done..

[webko]

You will have to check the Import script after it has been uploaded to the server...

[Agentshevy]

On 6/22/2016 at 11:01 PM, webko said:

In this instance, wouldn't it make more sense to only copy across the records that you then plan to delete?

Possible process:

Mark Inactive record in TableA (which it sounds like you might already do)
Find these records 
Move to Table B, Find All in TableB and then import from TableA: Update Matching, Add remaining data as new record
Then remove the records from TableA

Which can all be scripted quite easily...

From your suggestion, this is what I have been able to do

Allow user Abort (off)
Set Error Capture (On)
Freeze window
Enter find mode
Go to Layout (Table A list)
Set Field (Table A list status::"INACTIVE")
PERFORM FIND
iF (Get(FoundCount) = 0)
Beep
End If
iF (Get(FoundCount) not equal to 0)
Go to Layout (Table B list view)
Show all Records
Import Records no dialogue (Source:Table A ; Target:Table B  )
Go to Layout (Table A list)
Go to Record/Request/Page (First)
    Loop
      Delete Record/Request (NO dialogue)
      Go to Record/Request/Page (Next;Exit after Last)
    End Loop
End If
Go to Layout (Original Layout)
Show All Records

 

Everything seems to work well EXCEPT the record deletion part on Table A.
All the marked inactive records are imported into Table B, but not all of them are been deleted in the last part of the script step (the loop side).

I don't what I am not doing right. Is there a simple way of deleting a foundset using a script step? Just as we can do from the main window.

 

Edited June 28, 2016 by shevyshevy
Typo

[doughemi]

After you delete a record, the next record becomes the current record.  Then your next script step says to go to the NEXT record, skipping the newly current record.

To get around this, you can make sure that the found set of records is indeed the entire set to be deleted, then use the Delete All Records script step instead of a loop. This step deletes only the current found set.

[bruceR]

 Is there a simple way to delete all records?

Yes

Delete all records [no dialog]

[Agentshevy]

Thanks a million for your replies..