Bacically, I want to copy a record and its related records from two
tables, wbTransactions and wbPostings to two others, Transactions and
Postings though the master record of the second couple, in table
Transactions, will have a couple of extra fields, namely the date of the
transfer and the name of the user who has made the transfer.
The transfer should be considered /one/ transaction which means that
either everything is transferred successfully or nothing is transferred
at all. And the user in question will have access to the two first
tables [except for primary keys] and only read access to the two other
tables. The transfer, of course, will be done within a script is to run
with full access privileges.
I have found a description of transactions and how they can be performed
in FileMaker Pro on:
<
http://my.advisor.com/articles.nsf/aid/17403>.
The purpose of this is to create an Accounting Program where the user
inserts a transaction consisting of a number of postings into the waste
book. He will then push a button which will commence a script that will
check for validity [the postings should balance such that debit and
credits will settle each other].
The script will then create a record in Transactions with the same
primary key as the one automatically created in wbTransactions. It will
commit the record and go to a layout where wbTransactions is the master
with two portals based on Postings and wbPostings. With Go To
Object[portalWbPostings] it will traverse Go to Portal Row[Next] and
transfer the rows's contents to new records in Postings, placed in
portalPostings.
Of course, this should be done in a loop and of course if it is possible
to get a list of the fields from the sytem, the transfer from field to
field should be done in an inner loop.
Through Set Field it seems possible to give new values to the fields in
Postings {Set Field[Postings::field1;wbPostings::field1} But how do I
focus on two rows in two different tables, in two different portals, at
the same time? When going to the next row in wbPostings how do I at the
same time go to the next [yet to be created] row in Postings without
loosing the focus on wbPostings?
After I have transferred the wbPostings to Postings, I will have to
commit the changes using the method described in the aforementioned
link. I will then have to go to a master-detail layout with only the
waste book tables and delete the actual wbTransactions record -
cascading the deletion of the related wbPostings records. Transactions
committed should never be changed and transaction numbers never reused -
no problems with that.
So my sole problem is how synchronize the row number in wbPostings and
Postings.
Of course, all of this could easily have been done in SQL. I think
FileMaker should make it possible to have en Execute SQL based on
internal tables, not just tables specified in an ODBC Data Source. It
simply isn't smart to have all of your database tables placed in an
Oracle or MySQL database rather than in the FM database itself, unless
they have to reside externally for other reasons.
--
Per Erik Rønne
http://www.RQNNE.dk