Solved

IIF() function giving unexpected results

  • 5 April 2022
  • 4 replies
  • 369 views

Userlevel 4
Badge

I have a function in a report that is not working as expected. 

 

It should only show an inventory code if the type is “Finished Good”:

=IIf(([InventoryItem.ItemType]='Finished Good'),[InventoryItem.InventoryID],'') 

This is only returning blanks for all Item types, but if I show the value in the ItemType field, it is displaying “Finished Good”.

 

I’m wondering if the underlying data in this field is actually something other than what displays on the screen, like maybe FG or something.  But I have no access to the DB to find out.

icon

Best answer by Freeman Helmuth 5 April 2022, 12:06

View original

4 replies

Userlevel 5
Badge +2

@MarciaW You should be checking against the value stored in database instead of the one displayed in the screen. Try the following:

=IIf(([InventoryItem.ItemType]='F'),[InventoryItem.InventoryID],'')

 

Userlevel 7
Badge +17

@MarciaW  Try this!! 

=IIf(([InventoryItem.ItemType]='F'),[InventoryItem.InventoryID],'') 

Userlevel 4
Badge

To add to the above answers, anytime you want to filter or use a formula on a dropdown in Acumatica(not to be confused with a selector) you have to examine the dropdown to get the actual field values.

  1. Click Customization>Inspect Element(Or press Ctrl+Alt)

     

  2. Click on the dropdown you wish to examine

     

  3. Click on “Dropdown Values” to see the ID values of the dropdown

     

Userlevel 4
Badge

Thank you all very much, this is super helpful!  I don’t have access to our database as we are using the SAAS model, so at the moment I use inquiry screens to investigate data.

Really very much appreciated!

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