I've been using 2EmpowerFM's SQLRunner to insert, update, and delete FMP records via EPSqlExecute command. In SQL is there anything as straightforward as FMP's "duplicate" script step for duplicating records?
Documented in p17.
INSERT INTO table
SELECT ... FROM table WHERE ...
You can "duplicate" more than one record at once, with changing value if want.
Thanks user19752. The dilemma that poses involves fields with auto entries. FMP's duplicate command is really smart about that: prioritizing auto entries over data duplication. In other words for fields that have no auto entries the data is duplicated, and for fields with auto entries the auto entry terms prevail.
- "insert into ThisTable select * from ThisTable where KeyIndex=##" will duplicate the KeyIndex.
- "insert into ThisTable select [all fields except those that have auto entries] from ThisTable where KeyIndex=##" produces an error "The number of values in an INSERT...SELECT statement does not match the number of values in the target" which makes sense.
Of course it's entirely possible to work through exactly what to do with each field and nail the sql command with perfect precision, but the actual goal was to duplicate a record using SQL with the simple eloquence of FMP's native "Duplicate" command. Any hope of achieving that?
One of the underlying goals of this question is to develop a very simple reusable bit of code to copy all records in a portal from one master record into those of another. I do it frequently enough that it would valuable to tighten the steps.
BTW which page 17?
I hadn't used any SQL plug-ins, so without experience say it's not able easily.
If use auto entry by calculation, it can be aplyed in SQL, but becomes nothing "reusable". You need getting formulas from field definition.
The purpose duplicating potal records, "Inport records" may be the simplest. Agree you, this should be feature request.
page 17 mean the document PDF bundled in downloaded file of SQLRunner.
If you think about it, the goal is to get SQL commands -- which have their own internal world of logic -- to play nice with FMP's internal world of logic. I'm trying to get the best of both worlds here: The clever design of FMP's Duplicate Record command with SQL's facility of achieving the kind of multiple duplicates you pointed about above.
ODBC self import can use auto entry option, then need only making field list.
Another diference in import and duplicate is, imported records are commited immediately. (SQL INSERT may be same)
If you need to revert duplicated records ( and parent record ), you can't use this.
Retrieving data ...