3 Replies Latest reply on Sep 7, 2013 1:06 PM by philmodjunk

    table with portal duplication



      table with portal duplication


           I have a table which is used to manage offers, orders, transport documentation, invoices. Documents which are sharing most of data.

           The table has a related portal with line items.

           The logical path would be to be asked for offer, followed by order, ...... therefore from an existing record, e.g offer OF025/2013, I would like to generate the order OR012/2013.

           name and numbering of the single category is done automatically based on the category name and selecting highest+1.

           when user is creating a new document from an existing one he's been asked to select which category is the new one. All fields and related table are correctly copied.

           Issue is with the name and numbering. I'm not able to assign the correct category in the new record and obviously numbering is failing too.

           In attachment you'll see script for duplicating the document.

           Thanks in advance for your help







        • 1. Re: table with portal duplication

                    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.

          • 2. Re: table with portal duplication

                 Thanks Phil,

                 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




            • 3. Re: table with portal duplication

                   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.