Make campaign ID (primary key) start at least from 10000 | Community
Skip to main content
salvdangelo
salvdangelo
Level 5
September 19, 2017

Make campaign ID (primary key) start at least from 10000

  • September 19, 2017
  • 2 replies
  • 16749 views

Hi,

I would like to make sure that all campaign (operation) IDs are over 10.000 (since we import historical campaigns from a different source having IDs from 1 to about 9600).
Could you tell me step by step, I should I do this ?

I extended the "operation" schema with a custom one, if may help.
Someone suggested me to use a sequence for the primary key, but I never used it.

Thanks for your help,

Salvatore

florentlb​

Amit_Kumar​

Adhiyan​

Vipul Raghav​

Adobe Campaign​

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

    2 replies

    Jean-Serge_Biro
    Jean-Serge_Biro
    Level 10
    September 19, 2017

    Hi Salvatore,

    Please may you explain a bit more why you must force the ID values in your import job, instead of letting Adobe Campaign import system increment automatically the primary key value of the targeted standard objects?

    And will you import the data with Adobe Campaign tools (workflow activity or manually with Import model treatment)? Or with the help of an external ETL (Extract / Transform / Load) tool ?

    Some standard Adobe Campaign tables (schemas) use autopk=true so it increments automatically the primary key value (most of times internal=true ID generated but can be other fields as well).

    The incremental counter is managed through sequences schemas, see xtkNewId in documentation, and these sequences values are shared for several tables/objects.

    For instance, the folders (nodes) of the Adobe Campaign tree-list use such xtkNewId.
    And even with an empty new instance, the values must start over 1001.

    So you can import the historical campaigns (operations/deliveries) with standard AC tools, and let the system set the ID values accordingly.
    If you want to keep the external ID you can store it in an additional field in same schemas (see schemas extension), but not force the default primary key values.

    In case of you really need to force standard/default primary key values using sequences (autopk=true), then you must claim for range with GetNewIds procedure, but you won't obtain a range in small values, but after the current max value.

    Hope this helps.

    Regards
    J-Serge

      Jean-Serge_Biro
      Jean-Serge_Biro
      Level 10
      September 19, 2017

      Please have also a look on xtkNewId (and GetNewIds):

      https://docs.campaign.adobe.com/doc/AC/en/browseAC.html

      https://docs.campaign.adobe.com/doc/AC/en/PRO_Database_maintenance_Application_objects.html

      Adhiyan
      Adobe Employee
      AdhiyanAdobe Employee
      Adobe Employee
      September 20, 2017

      Hi Salvatore,

      I have a working theory on how to achieve this.

      In a very simple way , without extending your schema and chaning anything else, you can simply run an ALTER Table command to have your sequence which generates IDs to restart with 10000.

      The command would have to run on the DB and the syntax would be something like :

      ALTER sequence <Sequence name> MINVALUE 0 MAXVALUE 2147000000 RESTART WITH 10000 CACHE 1000 CYCLE;

      This will reset the Sequence to restart with 10,000 and will start assigning IDs incrementally after 10,000.

      Please bear in mind though , that this change will have an effect across all schemas which uses this sequence and the change will not only be limited to the operation schema.

      If you want to make the change effective only in this schema you will have to implement a couple of more complex changes which include

      1>. Create a new sequence on DB and also create a custom function for generating IDs for this sequence. Make sure to have the "restart with" parameter set at 10000 or after creating the Sequence , use the ALTER command to reset the sequence to start with 10,000.

      2>. Go to your operation schema extend it and make sure that autoPk="true" and pkSequence="<new_sequence>" is set.

      Your operation schema would then start using this new sequence with IDs starting from 10,000.

      Regards,

      Adhiyan

        salvdangelo
        salvdangeloAuthor
        Level 5
        September 20, 2017

        Hi Adhiyan,


        Thank you very much, I was searching something like that acting (if possibile) directly on the SQL tables

        Just to be clear <Sequence name> must be equal to <new_sequence> ?
        And could you tell me where in Adobe Campaign a should define the new sequence and the function that generates the IDs for the new sequence ?


        Jean-Serge Biron Thanks a lot!
        However this is the scenario :

        As a requirement we have imported historical campaigns from an outer data source. We will create our campaigns in AC and we'll export both historical campaign and new custom AC campaigns through a Salesforce connector in the same 'SF Campaign' table having a unique primary key valid for both types of campaigns. Unluckily we cannot ask any modifications on the SF entity.

        Jean-Serge_Biro
        Jean-Serge_Biro
        Level 10
        September 20, 2017

        Hi Salvatore & Adhiyan,

        Actually there are 2 main cases:

        1. sequence managed by the RDBMS (database) itself

        2. sequence managed by Adobe Campaign (autopk=true)

        Of course I used to use both depending on context.

        But you must take care that you must not alter the sequence value targeted with instruction given by Adhiyan (Oracle syntax, but similar ones in other RDBMS) for sequences managed by Adobe Campaign. So please have a look in your targeted schema, if the column to update is autopk=true, then use GetNewIds stored procedure (or equivalent Javascript function). And have a look on the stored procedure, you will understand;) But keep in mind that Adobe Campaign internal sequences share most of times between several objects.


        Regards
        JS

          Terms & ConditionsAccessibility statement

          Loading footer...