Data Extension API decimal type definition

Question

I'm trying to set decimal type for one of the fields in data extension table using soap, using MaxLength attribute with value of 18,6, call to the API executes with no error. When I go to check what has been set up value is defined as decimal with total length with 18 but number of decimal points as 'undefined', I have tried to use FieldLength instead MaxLength with value of 18,6 but I'm getting following error :

ErrorCode: 310006

StatusCode: Error

StatusMessage: Error occurred while saving Custom Object products (fbfd00f6-a64f-e411-b6d4-b4b52f5cc744): Incorrect syntax near ','. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

How do I set it up correctly using SOAP API?

Answer 1

I do not know if someone else encountered this issue but I solved it by addind the property 'Scale' in the props property of the DataExtension object. Example in php (should be the same for the others SDKs) : After initializing the DataExtension object :

 $yourdataExtension->columns[]=
                    array(
                        'Name'=>$name,
                        'IsRequired'=> false,
                        'IsPrimaryKey'=> false,
                        'FieldType'=>'Decimal',
                        'MaxLength'=>'18',
                        'Scale'=>'2',
                    )
                ;

Answer 2

Thanks for this answer. I have no reputation to vote it up yet, but in case someone wants to do it in AMPScript:

Set @de = CreateObject("DataExtension")

(...)

Set @deFields = CreateObject("DataExtensionField")
SetObjectProperty(@deFields,"Name","Decimal Field")
SetObjectProperty(@deFields,"FieldType","Decimal")
SetObjectProperty(@deFields,"MaxLength","18")
SetObjectProperty(@deFields,"Scale","2")
AddObjectArrayItem(@de,"Fields",@deFields)

Answer 3

This is NOT the answer, but this information is so hard to find I wanted to post it here as well. If you are doing a RetrieveRequest, you can specify which fields to pull with an array.

  $rr = new ExactTarget_RetrieveRequest();
  $rr->QueryAllAccounts = true;
  $rr->ObjectType = "DataExtensionField";   
  $rr->Properties =  array("Name","FieldType","IsPrimaryKey","MaxLength","Scale");
  $rr->ClientIDs->ID = $bu;