How to start autopk from 1 in a schema?

[saurabh99]

I want to customize the autopk in our schema and start counting from p_1 like p_1,p_2,p_3,p_4,p_5.... so on and my Adobe Campaign Classic is v8.

If there is any way to do this, please let me know.

[LakshmiPravallika]

Hi @saurabh99 ,

It is possible to add the customized key also to the Schema having autopk and it is not possible to delete the existing @id column in the Schema for autopk generation.

You can follow the below steps to add the customized autopk to your schema

1) If your sequence is not incrementing starting from Value 1 , Please follow below steps and modify your SQL Code after you add autopk="true" and Update DB Structure

drop sequence {pksequenceValueinPreviewTab};

create sequence {pksequenceValueinPreviewTab};
alter table sqltablename alter sqlcolumnnameofId set default nextval('pksequenceinPreviewTab' );

insert into sqlTableName( sEmail ) values ('lakshmi@test.com');

2) Add the below attribute in the edit tab in your autopk schema

<element autopk="true" label="TestingAutopk" name="TestingAutopk">

<attribute desc="formatted key" expr="'p_'+@id" label="formatted PK" name="fid"
sqlname="iTestingAutopkFormattedId" type="long"/>

<key internal="true" name="id">
<keyfield xpath="@id"/>
<keyfield xpath="@fid"/>
</key>

</element>

3) After saving it, Please Update your DB Structure and then try inserting few records using Workflow.

4) You will be getting Data as below format into your schema

Regards,

Pravallika.

[AkshayAnand · Accepted solution]

Hi @saurabh99 

You can try the below approach to make an auto incremental Key for a custom schema :-

Create a Schema with "AutoPK="true" " 

Do Update Database structure by following the path -- Tools >> Advanced >> Update database structure.

Once the DB has been committed logout and login.

Now create a workflow to alter the sequence that has been automatically create at the time of DB update. (You can edit the same at the time of DB update also in the SQL window.)

In the SQL activity, insert the following code to create your custom auto incremental key by 1

drop sequence auto_cussequenceTest_seq; --(dropping the Sequence of the custom schema created at the time of DB update, you can find it in the preview tab of the schema);

create sequence auto_cussequenceTest_seq; --(Creating the Sequence for the custome schema)
alter table CusSequenceTest alter isequenceTest set default nextval('auto_cussequenceTest_seq' ); --(setting up of nextVal for incremental value)
INSERT INTO CusSequenceTest (isequenceTest ) VALUES (0); --(defining the first 0 value for the primary key, hence the records now will have sequence as 1,2,3... )

insert into CusSequenceTest( sEmail ) values ('testingAkkitest3.com'); --(Testing by inserting a record using SQL sript)

You can also test the same by inserting the record using JS.

Once you have your sequence, you can create a custom Key as below :-

<attribute desc="customKey" expr="'p_'+@calculatedField" label="customKey" name="customKey" type="long"/>

<key internal="true" name="id">
<keyfield xpath="@customKey"/>
</key>

You can also refer to this solved post.

Hope this might help.

Regards

Akshay

[Sukrity_Wadhwa]

Hi @saurabh99,

Were you able to resolve this query with the help of the given solutions or do you still need more help here? Do let us know. In case the given solutions were helpful, then kindly choose the one that helped you the most as the 'Correct Reply'.
Thanks!