Custom/Sequential Ids

eriku40433679

05-06-2019

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

Accepted Solutions (1)

Accepted Solutions (1)

pierrec70731455

17-06-2019

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

Answers (3)

Answers (3)

wodnicki

MVP

12-06-2019

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

17-06-2019

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

eriku40433679

16-06-2019

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