How to Apply Iff Function in Generic Inquiry

  • 26 October 2022
  • 4 replies

Userlevel 4

Hi All,

I have created a new generic inquiry, I  joined  tables and I want to show project ID in one column. But currently I only can show 2 columns, because project ID appears in 2 screens ( Invoice & Memos) and (Payment & Application) 

I have tried  to use iff function for ar invoice, if project id is null, it’s will show project ID in payment, but it’s not work.  

Pls help me if you have any idea to show project ID in one column.

Thanks in advanced.





Best answer by hthuy24 27 October 2022, 10:06

View original

4 replies

Badge +18

I’d like to see the other available columns. Without seeing, I am guessing.

Here is my guess. Your GI likely shows a document type. Payments are showing Payment in Document type column and Invoices are showing Invoice in document type column.

If I am correct so far, I think you can add a new “Project” column to your GI with code something like this:

If Document type = ‘Payment’, [ARPayment.ProjectID], [ArInvoice.projectID].

However, this above example may be too simple if there are many document types showing. You may need to use  OR like this:

If Document Type = ‘Payment’ or Document Type = ‘Prepayment’ or Document Type = ‘Credit Memo’, [ARPayment.ProjectID],  ... etc.

I hope this helps you.

Userlevel 4

Thanks Laura so much

You get my point but I have a lot of types document so I dont know how to use iff for ar invoice screen  and I try to link another table that is PM Project (PM3010PL) . Below is the relationship I used

It’s work for all type but I dont know why type credit memo is doubled like this, it should be show one line “CV-21-A008” 

Thanks in advanced 

Userlevel 4


Thanks all, I have already solved my problem, I use function NullIff ([arinvoice.projectid], [arpayment.projectid]), and I join 2 tables arinvoice and arpayment with ar register.

Userlevel 7

Thank you for sharing your solution with the community @hthuy24 !


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