1 of 1 people found this helpful
A little more info about what you are trying to accomplish would be helpful. I don't believe that you can copy and paste a whole record, like you can a row in Excel, if that is what you are thinking.
Are these actually different tables, or just different layouts?
If they are different tables, do they have the same fields? ie- Would the data line up correctly if you were doing this in an Excel-style table view?
If so, then that begs the question: Why do you have 2 tables with identical fields/attributes? Sounds like you have some data modeling issues to straighten out.
That said, if you still want to go down this path, you would need to do it through a scripted process. Just a rough example, you would have a button that captures each field to a variable, then go to the desired layout, create a new record, then set each corresponding field using your variables.
Set Variable [$field1 ; Value: TableName::field1]
Set Variable [$field2 ; Value: TableName::field2]
etc, etc for each field.
(or you could do it by setting everything concatenated into one variable and parsing it out later, but that's up to you)
Go to Layout [Destination layout for 2nd table]
Set Field [DestinationTableName::field1; $field1]
Set Field [DestinationTableName::field2; $field2]
etc, etc for each field.
But, I would first take a hard look at your schema and figure out if this is actually how you should be doing things. It could save you a ton of re-work down the road.
You nailed all of my thought processes at once. The fields do line up exactly except the ID. The data should not be replicated accross multiple tables but that is a propblem for another day. The formula is what I need to get started so thank you for your help.
Leaving aside the usefulness of this endeavour: an import is always an option, or, if the field names are identical, you could
• relate both tables via a global, i.e. tableB::globalIDTableA = tableA::primaryKey
• put a list of the field names to process (i.e. all) into a variable (e.g. via SQL: http://www.databuzz.com.au/using-executesql-to-query-the-virtual-schemasystem-tables/)
• use a script and Set Field by Name to copy over the data via the relationship
[on layout of tableA ]
Set Variable [ $sourceTO ; Get ( LayoutTableName ) ] // assuming this is the TO of the relationship; otherwise hardcode
Set Variable [ $ID ; tableA::primaryKey ]
Go to Layout [ tableB ]
Set Variable [ $targetTO ; Get ( LayoutTableName ) ]
Set Variable [ $fieldList ; ExecuteSQL ( " SELECT Fieldname FROM FileMaker_Fields WHERE TableName= 'tableA' "; "" ; "" ) ]
Set Field [ tableB::globalIDTableA ; $ID ]
Exit Loop If [ Let ( $i = $i + 1 ; $i > ValueCount ( $fieldList ) ]
Set Variable [ $curFieldName ; GetValue ( $fieldList ; $i ) ]
Set Field by Name [ $targetTO & "::" & $curFieldName ; GetField ( $sourceTO & "::" & $curFieldName ) ]
Obviously the appeal of this method is directly related to the number of fields you need to copy …
IF you're on a Mac and applescript is a possibilty then there is a technique that allows you to copy a record; plus all of its related data to a new record.
See attached basic demo. This demo does not have a related record set.
It requires two layouts which contain no calc or auto enter fields; and which contain the required "match" fields to be in correct tab order on the source and target layouts.
Note that in this case the tables have a subset of fields in common.
If you look at creation order for the fields in the two table notice that they do not match.
The basis script is:
set targetLayout to "People2 Target"
set theData to (get data current record)
go to layout targetLayout
create new record with data theData
on error errmsg number errnum
display dialog errmsg
ASCcopyRecord.fmp12.zip 75.9 K
This saved me a ton of time. Now I can focus on cleaning up the data and setting the tables up correctly.
Thanks again. Great Forum!