therefore from an existing record, e.g offer OF025/2013, I would like to generate the order OR012/2013.
I strongly recommend that you NOT use those values as primary keys in your relationships. If you need them (or your client insists on them), set them up as normal data fields in oodf_IN, but do NOT use them as match fields in relationships--use an auto-entered serial number instead.
You really haven't explained your numbering system and your script only shows how the portal records are duplicated--not how that value is generated. It sounds like you have one 3 digit number series with leading zeros for Offers and and another for Orders followed by the year. I would guess that you start over at 1 with each new year.
If I am correct about how your numbering is supposed to work, I recommend the following approach. Add two more tables to your system:
oodf_IN::__pkOodfID = OfferSerials::_kfOodfID
oodf_IN::__pkOodfID = OrderSerials::_kfOodfID
Enable "allow creation of records via this relationship..." for both OrderSerials and OfferSerials.
00df_IN::__pkOodfID should be a field of type number set to auto-enter a serial number.
Define the following fields in OrderSerials:
_kfOodfID : Number field
OrderID : auto-entered serial number
Define similar fields in OfferSerials.
Now, from the oodf_IN layout, you can generate the serial number portion of your ID's "on demand". The following single step script will generate the next number in the Order series if such a related record does not exist. (IF one does, the record already has a serial number and no change in values takes place.):
Set FIeld [OrderSerials::_kfOodfID ; oodf_IN::__pkOodfID ]
Then the following calculation, either an unstored calculation field or a text fields set with a set field step following the step shown above:
"OR" & Right ( "00" & OrderSerials::OrderID ) & "/" & Year ( Get ( CurrentDate ) )
The final part of the process is a script that is run once a year to reset the serial numbers back to 1 at the start of each new year.
I'm going to try your solution.
The primary key is a serial number and I'm not using the category name, which is needed for the fiscal authority in Italy as we need to have a unique progressive number on each document.
pls find the attachment for the name and serial generation. lookup is to the table occurrence oodf 2
The method you describe can be made to work if your self join matches records by category (Offer or Order )., but it is vulnerable to producing duplicate values if two or more users try generating new records at the same time. Using the method I've specified does not have that issue.
BTW, I'm not a lawyer and certainly know nothing about Italy's legal requirements, but a simple serial number would generate a "unique progressive number" for each document if you can class both offers and orders as the same type of document.