Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

Custom/Sequential Ids

eriku40433679
Level 3
Level 3

Hi all,

We have a system where we populate the account number field with the CRM id. We then use the account number as a foreign key to other schemas in campaign. My problem begins in scenarios where when we load entries that will never be loaded in CRM, so I must make up some unique account number for them, prefixed with some unique identifier like temp001020 or something. Account Number remains a varchar field.

I've tried to setup a sequence in the database and added a calculated field in the enrichment with 'temp'+NEXTVAL('mySequenceName') but I'm getting a "You're not authorized to use SQL expressions" error, even though I have the named right.

How can I go about setting this up?

Thank you,

--Erik

1 Accepted Solution
pierrec70731455
Correct answer by
Level 5
Level 5

Hi Erik,

you could create mutilple sequences in SQL scripts like this :

SQL script sequence.png

And by defining an SQL function to get the next value of your sequences (Adding additional SQL functions😞

<?xml version="1.0" encoding='ISO-8859-1' ?>

<!-- ===========================================================================

  Additional SQL functions for Adobe Campaign

  ========================================================================== -->

<package

  namespace   = "nms"

  name        = "package-funclist-nextSeqVal"

  label       = "nextSeqVal function"

  buildVersion= "6.1"

  buildNumber = "8721">

  <entities schema="xtk:funcList">

    <funcList name="sequenceFunctions" namespace="crf">

      <group name="numeric" label="Numerical">

        <function name="nextSeqVal" type="long" args="(&lt;SequenceName&gt;)" help="get next value of specified sequence ex.: nextSeqVal('negativePersonID')"

                  minArgs="1" maxArgs="1" display="Get next value of sequence '$1'">

          <providerPart provider="PostgreSQL,MSSQL" body="nextval($1)"/>

        </function>

      </group>

    </funcList>

  </entities>

</package>

You can then use the sequence of your choice :

Update Data with Sequence.png

Kind regards,

Pierre

View solution in original post

4 Replies
wodnicki
Community Advisor
Community Advisor

Hi,

Create a counter under Admin/Platforms/Counters, then set the schema attribute's default value to use the counter, e.g. <attribute ... default="CounterValue('name of counter you made')">. Delete the sequence you made.

Thanks,

-Jon

eriku40433679
Level 3
Level 3

Hi Jon,

Thanks for responding, I've thought about that approach as my backup. Apologies, I missed a detail in my requirements. The prefix text needs to be flexible. If I define it in the schema, I wouldn't be able to change it on the fly. temp may be brand1tempxxx, brand2tempxxx, etc..

It's a start though. I could have the default set to tempxxx so they're added to the table that way on the insert step then filter for the temps generated immediately after and prepend the brand name. Any other way I can go about this?

Thanks,

--Erik

pierrec70731455
Correct answer by
Level 5
Level 5

Hi Erik,

you could create mutilple sequences in SQL scripts like this :

SQL script sequence.png

And by defining an SQL function to get the next value of your sequences (Adding additional SQL functions😞

<?xml version="1.0" encoding='ISO-8859-1' ?>

<!-- ===========================================================================

  Additional SQL functions for Adobe Campaign

  ========================================================================== -->

<package

  namespace   = "nms"

  name        = "package-funclist-nextSeqVal"

  label       = "nextSeqVal function"

  buildVersion= "6.1"

  buildNumber = "8721">

  <entities schema="xtk:funcList">

    <funcList name="sequenceFunctions" namespace="crf">

      <group name="numeric" label="Numerical">

        <function name="nextSeqVal" type="long" args="(&lt;SequenceName&gt;)" help="get next value of specified sequence ex.: nextSeqVal('negativePersonID')"

                  minArgs="1" maxArgs="1" display="Get next value of sequence '$1'">

          <providerPart provider="PostgreSQL,MSSQL" body="nextval($1)"/>

        </function>

      </group>

    </funcList>

  </entities>

</package>

You can then use the sequence of your choice :

Update Data with Sequence.png

Kind regards,

Pierre

View solution in original post

eriku40433679
Level 3
Level 3

Hi Pierre,

This is excellent! Thank you Pierre. I didn't think of using a custom function for this. I was expecting the platform to have made it a bit more accessible because of the availability of counters. This is perfect.

Cheers,

--Erik