I am trying to add our Customer Alternate ID to our Purchase Order Report and it is not appearing. Any help appreciated!
Best answer by Michaelh
@cschwark83 - We have similar business cases at my company where I need to send CUSTOMER SPECIFIC data to our VENDORS through PURCHASE ORDERS so they can put special markings on the boxes etc.
What we want to do is access the INItemXRef table, which is shown when we use the CTRL+ALT+CLICK function on the cross-reference grid on the STOCK ITEM screen.
Just like when we enter a row manually on the STOCK ITEM, we need to isolate the row’s “uniqueness” in our joins.
INItemXRef.InventoryID
When you join on this, you are now seeing the records for only your item
INItemXRef.AlternateType
When you join on this, you will only see records of type = CustomerPartNumber.
This is the part where I growl at our Acumatica developers for giving us insufficient documentation. They will says it’s NVAR(4) in the schema, but they don’t GIVE you the codes. To get the code, make a Trash GI and set a condition for the value you want to see:
SAVE, EXPORT as XML, find the value in the xml file. In my XML export I get this line (NEAR THE BOTTOM OF THE FILE): <GIWhere LineNbr="1" IsActive="1" DataFieldName="INItemXRef.alternateType" Condition="E" IsExpression="1" Value1="0CPN" Operation="A" />
Now we know that Customer Part Number is called 0CPN
If you have one customer part number per item, that’s it for joins. BUT…...
IF your business case has multiple customers per item, you will want to bind on INItemXRef.BAccountID. My PO’s do NOT see a BAccountID, as I only have 1 Customer Part Number per item. If you have linked a customer to your PO, you need to find the BAccountID and bind on it.
Below are the joins you want:
Generic Inquiry (use left join not inner, bad image sorry):
On the stock item screen make sure it is listed as a ‘Vendor Inventory ID’ on the Vendors screen, and that the PO is for that item and vendor. Also, once it’s added as a vendor inventory ID, it should also appear in the cross reference tab.
Ah, I see. Are you familiar with the report writer? You can modify the PO report to show other fields in Acumatica relatively easily. If you are not familiar with this I’d recommend reaching out to your VAR, as they can likely assist you!
@cschwark83 - We have similar business cases at my company where I need to send CUSTOMER SPECIFIC data to our VENDORS through PURCHASE ORDERS so they can put special markings on the boxes etc.
What we want to do is access the INItemXRef table, which is shown when we use the CTRL+ALT+CLICK function on the cross-reference grid on the STOCK ITEM screen.
Just like when we enter a row manually on the STOCK ITEM, we need to isolate the row’s “uniqueness” in our joins.
INItemXRef.InventoryID
When you join on this, you are now seeing the records for only your item
INItemXRef.AlternateType
When you join on this, you will only see records of type = CustomerPartNumber.
This is the part where I growl at our Acumatica developers for giving us insufficient documentation. They will says it’s NVAR(4) in the schema, but they don’t GIVE you the codes. To get the code, make a Trash GI and set a condition for the value you want to see:
SAVE, EXPORT as XML, find the value in the xml file. In my XML export I get this line (NEAR THE BOTTOM OF THE FILE): <GIWhere LineNbr="1" IsActive="1" DataFieldName="INItemXRef.alternateType" Condition="E" IsExpression="1" Value1="0CPN" Operation="A" />
Now we know that Customer Part Number is called 0CPN
If you have one customer part number per item, that’s it for joins. BUT…...
IF your business case has multiple customers per item, you will want to bind on INItemXRef.BAccountID. My PO’s do NOT see a BAccountID, as I only have 1 Customer Part Number per item. If you have linked a customer to your PO, you need to find the BAccountID and bind on it.
Below are the joins you want:
Generic Inquiry (use left join not inner, bad image sorry):
+1
+15
+2