Define a range for a field in Adobe Campaign Schema

[dipendu_g]

Hello All,

Is there a way to define a range for a byte / integer field while defining them, in the ACC Schema.

We need byte field which should hold only values between 1 and 25. Can that range be defined itself in the schema ?

Regards,

DG

[AkshayAnand · Accepted solution]

Hi @dipendu_g 

I guess we can't define the range in schema, instead you can have a check while loading data into the schema, like greater than/equal to 1  and less than/equal to 25. By this you will only have values from 1 to 25.

Regards

A

[aggabhi]

Hi @dipendu_g ,

Range is defined in adobe campaign by deciding type of datatype you are choosing for particular field. Every dataype has maximum limit of particular values and based on our requirement we have to define the same.

The following data types are supported in schemas:

• string: character string. Examples: a first name, a town, etc.

  The size can be specified via the length attribute (optional, default value “255”).

• boolean: Boolean field. Example of possible values: true/false, 0/1, yes/no, etc.

• byte, short, long: integers (1 byte, 2 bytes, 4 bytes). Examples: an age, an account number, a number of points, etc.

• double: double-precision floating point number. Examples: a price, a rate, etc.

• date, datetime: dates and dates + times. Examples: a birth date, a purchase date, etc.

• datetimenotz: date + time without time zone data.

• timespan: durations. Example: seniority.

• memo: long text fields (multiple lines). Examples: a description, a comment, etc.

• uuid: “uniqueidentifier” fields to support a GUID (supported in Microsoft SQL Server only).

Here you can define your datatype as byte only and not short or long because 1 Bye can hold upto numbers till 255. So that should be good to you.

[_Manoj_Kumar_]

Hello @dipendu_g 

Here is what you can do.

Create an enumeration in the schema like this:

<enumeration basetype="byte" name="range">
    <value label="1" name="1" value="1"/>
	<value label="2" name="2" value="2"/>
	<value label="3" name="3" value="3"/>
	<value label="4" name="4" value="4"/>
	<value label="5" name="5" value="5"/>
	.
	.
	.
	<value label="25" name="25" value="25"/>    
  </enumeration>

Then you can define your attribute like this:

 <attribute desc="Range Field" enum="range" label="Range Field" length="1"
               name="rangeField" type="byte"/>

[david--garcia]

You can also add a constraint directly into the database, use a sql activity, I wouldnt recommend it unless you have SQL experience or know how to revert back the changes (perhas you can engage with your instance's DBA) and really test it in staging (also if your instance is hosted by Adobe then this is a no go), the sql syntax can change depending on your engine but it should be around the following lines.

ALTER TABLE <yourtable>
ADD CONSTRAINT <constraintName> CHECK (
   yourcolumn >= 1 AND yourcolumn <= 25 --Inclusive
)

[dipendu_g]

Hi @david--garcia, @_Manoj_Kumar_ , @aggabhi , @AkshayAnand ,

Thank you all for your responses.

@david--garcia, Our's is Adobe hosted environment hence, cant go ahead with the SQL changes.

@_Manoj_Kumar_ The enum option was good, but in some cases our range goes upto 100

Planning to keep the check done at the time of data insertion, rather than controlling the same via schema/db

Regards,

DG