Solved

Evaluate Not Null

  • 20 February 2024
  • 7 replies
  • 69 views

Userlevel 2
Badge

Hello everyone,

What is the proper syntax in the Generic Inquiry builder to evaluate if the value of a field is not null?

 

For example if I want to evalute if Inventory Item Item ID is not null...

The following does not evalute correctly:

[InventoryItem.InventoryID]<>Null

 

The following does evaluate correctly, but looking for a more direct way:

=IIF(Isnull([InventoryItem.InventoryID],0)<>0,...)

 

Thanks in advance!

Luke

icon

Best answer by darylbowman 21 February 2024, 15:09

View original

7 replies

Userlevel 6
Badge +5

You could try inventoryID>’’    -   that’s 2 single quotes.  I believe I’ve used this before.

Userlevel 7
Badge +8

InventoryID is a required field from a database level as it is the primary key.  Could you share more on your reasoning for this formula as it might not be needed? 

 

Your first formula is correct FIELD <> Null is a valid expression so once we know more about use case it will be easier to determine why you are not seeing the result you expect.  

Similarly for a generic inquiry you can use the conditions tab to indicate FIELD | IS NOT EMPTY

Userlevel 2
Badge

I used InventoryID as a simple example.  My actual use case is a multi-level Kit Specification report where I am left joining KitSpecifcations on KitComponents.  I am using the null check to determine what level the component is on.

Here is an example evaluating using <>Null vs IsNull

 

Badge +11

Null is not the same as blank, although if one formula works as ‘null’, it seems like they both should. Try this:

[InventoryItem.InventoryID]<>''

Userlevel 2
Badge

Thanks @darylbowman.  Agree that null is not the same as blank, but that does work in this context. 

Badge +11

It may be that IsNull checks for null or blank, whereas, an equality comparision to null will only check for null.

Badge +11

@lukef55 If this is resolved, would you mark it solved by checking the box on the best answer?

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved