NULLing a user-defined field

[former_member445524]

Hello, all

How to assign NULL to a Numeric(10) user-defined field via DI API?

I have tried:

Documents.UserFields.Fields.Item( "U_GTPurchOrdr" ).

  Value = Null;         // ("Illegal value entered")
  Value = DBNull.Value; // ("Illegal value entered")
  Value = "";           // (Zero instead of null is assigned)


  ValidValue = "";     // (Illegal value entered)
  ValidValue = "NULL"; // (Illegal value entered)
  ValidValue = "''";   // (Illegal value entered)

The SDK claims that the Value field "sets or returns the field SQL query value." and the ValidValue field "sets or returns the actual value of the field (instead of the SQL query value)," which doesn't make much sense...

I am using Business One 8.82.067.

[maik_delly · Accepted solution]

Hi Anon,

just for completeness :

In SBO 9.0 you have a method for fields called "SetNullValue" which is doing exactly what you want.

regards,

Maik

[Former Member · Accepted solution]

Hi Maik,

Which I'm Learning by the way, else I woud have suggested...

Regards,

Eric

[pedro_magueija]

Hi Anton,

Try not setting the property at all.

if( my logic here = true )

{

    set the Value = MyValue

}

// else don't set the value by doing nothing

---

Best regards,

Pedro Magueija

[former_member445524]

Pedro, I can't do as you advice because that field already contains a number which I must eraze.

[pedro_magueija]

Hi Anton,

I see, well couple of things you can try:

Use xml to remove the value and update (this requires that you manipulate the xml);

if that doesn't work either you can try to set it to a value that would be recognized as no value (e.g.: edit text hide zero values if an option is checked so setting the value to 0 would "hide" it in the forms).

Or maybe someone has another idea.

Any way hope this can help.

Good luck.

---

Best regards,

Pedro Magueija

[Johan_H]

Hi Anton,

This is a very basic "flaw" in B1 (both UI API and DI API). Once a property has been set, you cannot null it, you can only set it to a "neutral" value like 0 or ''.

In case of userfields, the simplest option is direct sql. N.B. only with userfields

(Also see this thread)

If you are adamant about using the DI API,perhaps the options that Pedro suggested work. I have not tested these myself. One option that I know works in some scenarios is saving the entire record in memory, removing it, and adding it back as a "new" record, where you then not set the offending property, as per Pedro's advice.

Regards,

Johan

[Former Member]

Hi Anton,

To complete Pedro's answer, since the Value property of the object is not nullable, as soon as the object is updated the null value is lost.

The only solution to do it is by doing direct SQL update (even by using the oRecordset object, which works), which is outside of the scope of allowed things for the SDK.

If really needed, have to ask for an update to SAP...

By the way, why wanting to deal with null values?

Regards,

Eric

[former_member445524]

Pedro,

Updating a draft of A/R Invoice via XML seems non-trivial. In the following code

request.GetByKey( docEntry );
request.UserFields.Fields.Item( "U_GTPurchOrdr" ).Value = -111; // HACK
string asXml = request.GetAsXML();
asXml = asXml.Replace( "-111", "" );
Documents newRequest = (Documents)DI.Cmp.GetBusinessObjectFromXML(asXml,0);
if ( newRequest.Update() != 0 )
{  errMsg = DI.Cmp.GetLastErrorDescription();
   goto Error;
}

the Update() method returns an error: "[OINV.DpmStatus][line: 1] , 'Field cannot be updated (ODBC -1029)".

Using a "neutral" value isn't convenient either because this is a standard document's user-defined field, used in several reports.

[former_member445524]

Johan,

Have you reported this "flaw" to B1 support? If not, then I surely will.

I prefer DI API because I want this update to be registered in the "History", so maybe the ugly combination of DI update to a neutral value and a direct SQL update to null will work for me.

Your third option of removing the whole record and then adding it anew is overhead for my case, because I'll have to keep track of whatever other documents refer to the one being thus re-created.

[former_member445524]

Eric,

I think direct SQL updates are allowed with user-defined fields.

As for why I want a null value, that UDF references a Purchase Order (PO), so keeping it empty (which is null) when there is no PO seems correct. Users should be surprized to see a zero for empty reference, and we'll have to add special handling of this field in our addons and SQL code.

[Former Member]

Hi Anton,

SAP is not very clear on this one... Depending on documents, this is allowed or forbidden. I remind a not so old thread (http://scn.sap.com/thread/154221) speaking about this.

Having said this, it doesn't help you to achieve what is asked, so sometimes (and I am the first to do it), you have to "cross the line", but by doing it on purpose and as less as possible.

Regards,

Eric

[pedro_magueija]

Hi Anton,

That's not the way to do it. Here is a sample.

	Documents doc = b1.Company.GetBusinessObject(BoObjectTypes.oOrders);
	doc.GetByKey(1);

	b1.Company.XMLAsString = true;
	b1.Company.XmlExportType = BoXmlExportTypes.xet_ExportImportMode;
	string xml = doc.GetAsXML();

xml = xml.Replace("<U_test>123</U_test>", "<U_test></U_test>");

doc.UpdateFromXML(xml);

	int result = doc.Update();
	if(result!=0)
	throw new Exception(b1.Company.GetLastErrorDescription());

---

Best regards,

Pedro Magueija

[pedro_magueija]

By the way the update call is unecessary:

	Documents doc = b1.Company.GetBusinessObject(BoObjectTypes.oOrders);
	doc.GetByKey(1);

	b1.Company.XMLAsString = true;
	b1.Company.XmlExportType = BoXmlExportTypes.xet_ExportImportMode;
	string xml = doc.GetAsXML();

xml = xml.Replace("<U_test>123</U_test>", "<U_test></U_test>");

int result= doc.UpdateFromXML(xml);

	int result = doc.Update();
	if(result!=0)
	throw new Exception(b1.Company.GetLastErrorDescription());

[former_member445524]

Hello, Pedro.

Thanks for the code sample. It works, but sets the user-defined field to 0 (zero) instead null.

EDIT: Yes, including the correction.

[pedro_magueija]

Hmm... that's odd. In my database the field became NULL. That U_Test is a numeric field in my db. Is that the same on yours? Or do you mean the value of the Items("U_Test").Value?

---

Best regards,

Pedro Magueija

[former_member445524]

Pedro,

Yes, after a successful update via XML I get a zero (0) in that field in the database. A direct update via the Recordset works though...