I would keep the autoenter serial in place and write a script for those that you want to duplicate.
I would also question thoroughly why you would want multiple records with the same ID#. It sort of defeats the concept of having an ID# doesn't it? Why do you want to do this?
When you want a new record with the next higher number, use: New Record/Request
When you want a duplicate record with the same ID# (again, why?):
run the script:
SetVariable [$ID ; Value: IDField ]
SetField [IDField ; $ID ]
You don't have to answer the "Why?" question to me...but you may have to answer it to your database design when it stops working the way you want it to...so think it through thoroughly.
Hi Ninja, already have an auto serial number for relating this table to other tables, so this isn't at issue. The field value I want to increment when creating a new record is the serial number (not ID). I use this serial number to group records together by this field. For instance, a commission payment may come in from a company such as Legal & General, this commission payment may be split between 6 financial advisers. By using the serial number, I can group the split together to produce a total which should match up with the gross commission payment to the main company for reconciliation and accounting purposes.
So I really don't wish to use the autoenter serial. Also, when the payment comes in from Legal & General, I will create the first record with all details relating to the payment such as the payment date and the period that the commission payment covers. I will then duplicate this 5 times to give me the 6 way split so that each adviser is listed with their own payment details and the payment.
Could you consider the solution for this again on the above basis. I cannot see how the autoenter serial would work.
already have an auto serial number for relating this table to other tables, so this isn't at issue. The field value I want to increment when creating a new record is the serial number (not ID).
Got it. Well done.
So the serial number (not ID) is used to group similar records. What else is it used for? Is there another table that links to/references this number (Payments Table?)
If so, perhaps it would be best for you to work from a layout based on that OTHER table and work through a portal. This would have all of your Serial numbers not only be the same (based on the key from the Payment Table), but would take care of linking it to the place you want it linked.
Your gross commission payment would be from the reference of the other table and simply be Sum(Commissions:: pymt)
Sorry if I'm slow to understand.
Wonder if it would be possible to create a script to create the new blank record, which would look at the previous serial number for a completed record, andthe would increment it by one. I would like the options to either do this or not do this and would prefer to use a button when I need to carry out the auto increment.
Could you give me some idea of syntax. Don't worry about summarising these records grouped by the serial number, I've already got it sorted with reporting.
ps - thanks for your help!
Will you be doing this in a multi-user set up (file is shared over network) or single-user (only one person has file open at a time. Scripts that assign serial numbers instead of relying on an auto-entered serial number have to be designed with care to make sure that issues are avoided if two different users don't try to assign a serial number to the same record or the same serial number to different records...
Hi PhilModJunk, this is for a single user setup only.
One way to do this, assuming that you want the newID to be incremented by one from the max serial# (as opposed to the record you are currently on)...
Self Join your table and run the following script:
SetField [IDNumber ; Max (JoinTable::IDNumber) + 1 ]
At the risk of being an annoying, resounding gong...the above script snippet simply replaces the autoenterID...
For a duplicate record with the same ID (I still can't figure out why you want this...but that's OK) simply run the three step script I posted up above.
Put these two scripts on buttons labelled "Duplicate Record" and "New Record" or as appropriate.
Note that if there is a "-" preceeding the number in your ID it will treat as a negative, so switch to "- 1 ] "