By using this website, you agree to our Terms of Use (click here)
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.
Something like:
=[POLine.OrderNbr],(IIF([POLine.OrderDate]> @MonthEnd-12), ( ("N/A"))
=InStr([POLine.OrderNbr], P0*)
Instead of looking for anything that contains 'PO', look for rows where left([POLine.OrderNbr],1) = 'P'.
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:
=Left([POLine.OrderNbr],2)='P0'
The above syntax gives me the following:
Incorrect syntax near ')'
Incorrect syntax near the keyword 'ORDER'
Create a new Parameter and set the default value to 'P0'. Then on the Conditions tab, on a new line, select the parameter as the data field, and in the Value 1 column, put in the formula =Left([POLine.OrderNbr],2).
See if that doesn't work.
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?
Using Shawn's solution, you just need a 2nd condition to allow for [POLine.OrderNbr] having a null value - both conditions should be surrounded in braces (so they are a pair) and change the AND to OR - that should include rows where no purchase order exists
Thx! It's working now. Attached are screen shots if any one wants to use as reference.
I highly recommend you bracket the OR conditions, even though there are currently only 2 conditions at the moment, it's a good practice. If in the future further conditions are added and whoever adds them doesn't notice the OR, the query will give the wrong result.
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.
Here you go.
Please note that in the results grid below the column with Caption "Last 2 Yr Sales" is showing inflated QTY's. I am working on getting the qty's to display correctly so the table relations might change in regards to the ARTran / Inv ID / ARInvoice, etc.
You might try adding DocType to your list of fields on the join between ARTran and ARInvoice.
Thx for the input. I thought I had gone back and done that.
Thanks for providing the link info. I think it helps folks when they come looking for answers and review past discussions.
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.
Online Members
