Solved

Is there a function similar to Vlookup on Generic Inquiries?

  • 15 November 2023
  • 7 replies
  • 110 views

Userlevel 3
Badge

We have a GI that provides PO Receipt Information. We would like the GI to exclude Returns and associated PO lines (hide them). Is there a function similar to VLookup that can be used? Below screen shot is one of the examples we are using. 

 

We tried the below formula but that is not working. 

 

 

I have attached a xml file of the GI. Any suggestions would be helpful, thank you!

icon

Best answer by Robert Sternberg 17 November 2023, 21:09

View original

7 replies

Userlevel 7
Badge +8

How about adding the condition OrigReceiptNbr IS EMPTY?  Would this accomplish your goal, if not could you elaborate on the issue you are trying to solve. 

 

Thanks!

 

 

Badge +10

If I’m understanding correctly, you need to add a filter condition on the Conditions tab of the GI like this:

POReceiptLine.OrigReceiptNbr   Does Not Equal   POReceiptLine.ReceiptNbr

(assuming this is accurate logic; the main thing is using the Conditions tab, not the Results tab)

Userlevel 3
Badge

Hi @darylbowman and @Robert Sternberg, thank you for sharing your ideas. However that is not what we are looking for. 

Below is an example: 

If the return contains the original receipt nbr and if the line nbr is the same - we would like to exclude the receipt line completely. 
That is if concatenation of (OriginalReceiptNbr, LineNbr) is the same as Concatenation of (ReceiptNbr,Linebr) then exclude. (In this case it needs to exclude the highlighted line in green)

Which is why we wanted to know if there is a function similar to Vlookup in acumatica GI. Can this be done in acumatica GIs? Thank you!

Userlevel 7
Badge +8

Not sure if I have an answer for you yet but I would like to read back your question and see if I understand correctly now. 

 

Goal: Exclude receipt lines that were returned. 

Use Case: This GI will display ‘receipts without returns’ and ‘returns’. (excluding ‘receipts with returns’)

 

Does this sound accurate for what you are trying to achieve? If so, we can probably use an aliased table to gather the required information. 

Userlevel 3
Badge

Hi @Robert Sternberg, yes you understood it correctly. Are you recommending adding another POReceiptLine table with a different alias? 

Userlevel 7
Badge +8

Yes that's correct.  Here are the steps explained, I also attached the working Generic Inquiry. 

 

Add Table POReceiptLine Aliased as POReceiptLineReturn
Add your relations so POReceiptLineReturn provides return details of a POReceiptLine
Set a condition to exclude any Receipts with Returns
You can check the results by disabling the condition above and enabling visibility for the ACUCOMMUNITY_ReturnReceiptNbr Field

 

Hope this helps!

Userlevel 3
Badge

Hi @Robert Sternberg, I appreciate you working on replicating this! I will review and get back to you, thank you again!

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