Expand my Community achievements bar.

Adobe Campaign User Groups are live now. Join our Adobe Campaign User Groups and connect with your local leaders!
SOLVED

How to start autopk from 1 in a schema?

Avatar

Level 2

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.

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi @saurabh99 

 

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

 

Create a Schema with "AutoPK="true" " 

AkshayAnand_0-1696935446679.png

 

 

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

AkshayAnand_1-1696935446656.png

 

 

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.)

AkshayAnand_2-1696935446681.png

 

 

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>

AkshayAnand_0-1697616699904.png

You can also refer to this solved post.

 

Hope this might help.

Regards

Akshay

View solution in original post

3 Replies

Avatar

Community Advisor

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

LakshmiPravallika_0-1696933711971.png

Regards,

Pravallika.

 

 

Avatar

Correct answer by
Community Advisor

Hi @saurabh99 

 

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

 

Create a Schema with "AutoPK="true" " 

AkshayAnand_0-1696935446679.png

 

 

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

AkshayAnand_1-1696935446656.png

 

 

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.)

AkshayAnand_2-1696935446681.png

 

 

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>

AkshayAnand_0-1697616699904.png

You can also refer to this solved post.

 

Hope this might help.

Regards

Akshay

Avatar

Administrator

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!



Sukrity Wadhwa