The portal records should go away in this situation as they are "owned" by the original record--not the new one you've just created by duplicating it.
The trick is to duplicate your portal's records first, assigning them the new ID number. Then you duplicate the parent record and give the parent record the same new ID number.
You can set it up this way to start:
Define a global text field, gNewNumb and place it on your layout where you see the parent record you might want to duplicate. You'll enter a new number in this field and then trigger a script to duplicate the current record and it's related portal records. I'll call your two tables "Main" and "PortalRecs" for this example. You'll need to substitute your own names where appropriate
If [Not IsEmpty(gNewNumb) /* make sure a number was first entered in this field */]
#duplicate any portal records
IF [count (PortalRecs::MDO) > 0 /* there are portal records to duplicate */]
Go to Related Record [ From table: “PortalRecs”; Using layout: “PortalRecs” (PortalRecs) ] [ Show only related records ]
Go to Record/Request/Page [ First ]
Set Field [ PortalRecs::MDO; Main::gNewNumb ]
Go to Record/Request/Page [ First ]
Exit Loop If [ Get ( FoundCount ) = 0 /* All records have been duplicated */ ]
#Duplicate Parent Record
Go to Layout [ original layout ]
Set Field [Main::MDO ; Main::gNewNumb ]
Note: The brackets and parameters for Go To Related record will look different in your script editor as I copied that portion of this script from a database design report which formats this line a bit differently.
Also: Go To Related Records is a very useful tool, but which is very poorly documented. To learn more about GTRR, click the following link:
The Complete Go To Related Record
I use a variant of that script that works well.
Since my parent table uses an autoenter serialID, I:
trap the parent recordID, $oldID
create a new record,
trap the new ID, $newID
go back to the record to be copied (find IDfield=$oldID),
and use your loop to copy the portal records and setfield their ID# to $newID
Same concept as yours, but keeps autoentered serial#s clean.
Thanks, guys. I'll give it another try and will post the results soon.
That's exactly how the real script from which I adapted this does it. Since I couldn't tell if the MDO number was human generated (copied from a label?) or auto-generated, I used a sufficiently generic approach for it to work either way. I'd definitely use your approach if at all possible as asking humans to type in key values is a recipe for trouble.
If the MDO number is "human entered", I'd modify such a database to use an auto-entered serial number for my relationships and keep the MDO as simply a label so users can use it to find records and such.
The MDO number is a manually generated barcode. I print batches of 100 or 200 at the time, the user grabs the barcode and scans it minimizing human errors. I've been thinking about autogenerate MDO numbers but since I'm fairly new to the world of databases, I've decided to do one thing at the time.
One thing at a time is definitely a good idea. :smileywink:
If you haven't already figured this out, you can place your cursor in the global field and then use the barcode scanner to enter the scanned MDO number. You can even program most scanners to append a "postamble" onto the end of the scan that appends an enter, return or tab character to the end of the scan to exit the scanned field. Then you can use the OnObjectExit script trigger for this field to trigger your duplicate records script.
First, I had to create a new forum user ID because the one I had said that the email couldn't be found. I tried resetting the password and that didn't work either.
Anyway, the only thing I changed on the script was "Match found set" in order to get everything on the new record, other than that, it works perfectly!
These forums are incredibly awesome!!!
I don't know if I'll ever get access to the other account, and this is a dumb question, Is it possible you can mark your answer as "Solved?"
Howdy EL Berraco,
Typically only the original poster can mark a thread as solved. But perhaps Phil can do it as Community Leader?
Well, it looks like I got my forum account back . I do have a question, though, the way I have MDO_ID is, "MDO ID text Indexed, Auto-enter calculation replaces existing value, requiered value, Unique, range, Maximum, Allow Override, Message" I get the meesage every time I run the script because one of the options didn't apply, why is this happening? Both tables have a relationship as MDO(Main)=MDO_ID(Projects) Allow creation of records and delete records on Projects table are checked.
Also, the more I look at the database the more I want to come up with ato-generated MDO numbers.
What would be the best way to create an Auto-serial number keeping in mind that I don't want to have missing MDO numbers, i.e, user a creates new record. Filemaker assigns MDO-00001999 for this new record. User b creates new record and gets MDO-00002000. User a decides to delete MDO-00001999, can I still use that number?
As far as creating labels for them, we have a Zebra printer that can be installed in a center location and print the labels from either Filemaker or the Zebra utilities.
Thanks again, guys!!
...can I still use that number?
Not something I'd recommend, though I understand that for auditing purposes, sometimes you have to do this. If you put a Unique Value validation on the field to help protect yourself, you would be able to type in the serial number of a deleted record.
A better approach: Put your Label serial number in a separate table and use a hidden serial number (where you won't care about gaps in the sequence) to link to this "label" record. If you delete the item's record, it's "label" record will remain and you can select it to re-use. (I'd also use this internal, hidden serial number for all your other links.)
I have a few extra options here, but marking another member's post as "solved" is not one of them.
What kind of relationship would I need between the new label table and the other ones?
You'll have to look at all your tables to make sure it works for you but you should only need one new table with one new relationship in most cases. You may not have realized this, but you can reference data in tables "more than one table occurrence away" in most cases if you aren't using the X operator and the in between table (your main table in this case) always has a record to "bridge the gap".
In your case, it wouldn't make sense to have other related tables without a matching main table, so this shouldn't be a problem.
Simply add one new table with two fields:
MDO Serial (auto-entered serial with MDO prefix) //or you might use a calculation to combine a simple serial number field with your text and leading zeroes.
Main::InternalSerial = LabelTable::InternalSerial (enable "allow creation..." for LabelTable records.)
The main catch is you have to trigger the creation of the MOD serial number with a script like:
Set Field [LabelTable::Internalserial; Main::internalSerial]