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.
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.
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.
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?