Inventory List GI including Sales Price, Avg Leadtime, Last Cost, Vendor Cost, Last PO, and Last PR Receipt
I have created a GI displaying all inventory item data along with Sales Price, Avg Leadtime, Last Cost, Vendor Cost, Last PO, and Last PR Receipt for each item.
Problem is that in March of 2017 we changed Numbering Sequence for PO Numbers from POxxxxxx to P0xxxxxx ( Changed the O to a zero) This causes the POLine.OrderNbr column in the GI not to showing the latest Purchase Order. I want to display all items regardless if it has a associated PO or not, so I can't filter the old numbering POxxxxxx as a condition for the GI. I am trying to filter out the old PO's by row level in the results page of the GI. I have tried multiple ways but haven't found the right solution yet. Need ideas of row level syntax for the POLine.OrderNbr.
=[POLine.OrderNbr],(IIF([POLine.OrderDate]> @MonthEnd-12), ( ("N/A"))
Thank you Shawn for the input.
Because I do not want Purchase Orders to show that begin with PO(Capitalized Letter O) and just Purchases Orders beginning with P0 (Zero)
would I use the following:
The above syntax gives me the following:
Incorrect syntax near ')'
Incorrect syntax near the keyword 'ORDER'
Thank you Shawn for the input. This will work but the results only include items with Purchase Orders against them. Because of this, is why I headed down the road of trying to put the formula in the data field row under the results grid.
Am I wrong in assuming that if I can come up with the right function in the data field results row that all items will show up regardless of if a purchase order has been issued against the item or not?
I know it's asking a bit but would you mind sharing how you defined the join for each of the table relationships? I find this to be where my customers have the most challenge in building GI's. Once they know the DAC and column that contains the data they want to include by using "Customization - Inspect Element", they run into challenges with how to define the table joins. Having that contained here in your conversation string will make it easier for people to get the whole picture on how you achieved your results.
Thank you in advance for sharing.
That's quite an ambitious list of joins Dwilk. Thanks for sharing.
I'm especially interested in the join from InventoryItem to ARTran. Is this returning the data that you're expecting?
The way I interpret it is that you're assuming every Item will only be sold one time.
Interested in joining a Local Acumatica User Group? Click here for more info