Generating Sequential AutoPK for Data Schema

[AnkitaV3131]

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

[AkshayAnand · Accepted solution]

Hi @AnkitaV3131 

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>

Hope this might help.

Regards

Akshay

[AnkitaV3131]

Hi Akshay,

Thanks a lot. This method worked.

[sergio_NYC]

Great post!  Efficient and concise.  Well done!