Expand my Community achievements bar.

SOLVED

Generating Sequential AutoPK for Data Schema

Avatar

Level 2

Hi,

 

I am looking to create a sequential AutoPK for a specific data schema in campaign v8. Additionally, I would like to add the prefix 'P' to the AutoPK. Is there a method to generate the AutoPK in sequence with constant prefix?

 

 

Best regards,
Ankita

Topics

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

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi @AnkitaV3131 

 

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

 

Create a Schema with "AutoPK="true" " 

AkshayAnand_0-1697183538558.png

 

 

 

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

AkshayAnand_1-1697183538771.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-1697183538615.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_3-1697184365962.png

 

 

Hope this might help.

Regards

Akshay

View solution in original post

3 Replies

Avatar

Correct answer by
Community Advisor

Hi @AnkitaV3131 

 

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

 

Create a Schema with "AutoPK="true" " 

AkshayAnand_0-1697183538558.png

 

 

 

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

AkshayAnand_1-1697183538771.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-1697183538615.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_3-1697184365962.png

 

 

Hope this might help.

Regards

Akshay

Avatar

Level 2

Great post!  Efficient and concise.  Well done!