Custom/Sequential Ids

[#1]

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

[#2 · Accepted solution]

Hi Erik,

you could create mutilple sequences in SQL scripts like this :

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 :

Kind regards,

Pierre

[#3]

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

[#4]

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

[#5]

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