Sage 100

I want to create a custom ALE in inventory inquiry to see Qty on SO and Qty on Backorder.  The customer only uses warehouse 000.  So the primary table is CI_Item and the link table is IM_ItemWarehouse.  Is this possible?  If so, what should the key expression be?  I tried CI_Item+"000" and only 0.00 appears in the lookup for Qty on So and Qty on Backorder. 

It is possible, I don't recall if you can enter a literal string but you can try it.  At the very least, you can always use ItemCode+DefaultWarehouseCode.

I want to create a custom ALE in inventory inquiry to see Qty on SO and Qty on Backorder.  The customer only uses warehouse 000.  So the primary table is CI_Item and the link table is IM_ItemWarehouse.  Is this possible?  If so, what should the key expression be?  I tried CI_Item+"000" and only 0.00 appears in the lookup for Qty on So and Qty on Backorder. 

I want to create a custom ALE in inventory inquiry to see Qty on SO and Qty on Backorder.  The customer only uses warehouse 000.  So the primary table is CI_Item and the link table is IM_ItemWarehouse.  Is this possible?  If so, what should the key expression be?  I tried CI_Item+"000" and only 0.00 appears in the lookup for Qty on So and Qty on Backorder. 

Not to steal ANY of @David Speck II posts (they are truly amazing) but I want to give a shout out to @David Overholt for an oldie but still very goodie for his trail blazing work back in the day..

I'm trying to figure out the easiest way to lookup | Sage 100 (90minds.com)

Not to steal ANY of @David Speck II posts (they are truly amazing) but I want to give a shout out to @David Overholt for an oldie but still very goodie for his trail blazing work back in the day..

I'm trying to figure out the easiest way to lookup | Sage 100 (90minds.com)

I want to create a custom ALE in inventory inquiry to see Qty on SO and Qty on Backorder.  The customer only uses warehouse 000.  So the primary table is CI_Item and the link table is IM_ItemWarehouse.  Is this possible?  If so, what should the key expression be?  I tried CI_Item+"000" and only 0.00 appears in the lookup for Qty on So and Qty on Backorder. 

Not to steal ANY of @David Speck II posts (they are truly amazing) but I want to give a shout out to @David Overholt for an oldie but still very goodie for his trail blazing work back in the day..

I'm trying to figure out the easiest way to lookup | Sage 100 (90minds.com)

I want to create a custom ALE in inventory inquiry to see Qty on SO and Qty on Backorder.  The customer only uses warehouse 000.  So the primary table is CI_Item and the link table is IM_ItemWarehouse.  Is this possible?  If so, what should the key expression be?  I tried CI_Item+"000" and only 0.00 appears in the lookup for Qty on So and Qty on Backorder. 

I just created the below 'link' via VI Export Job Maint (PO_VendorPurchaseAddress for IM_ItemVendor), but the new tables' fields are all showing as 'not on file' in my ALE (below).  Is that because I would need to also include PrimaryPurchaseAddressCode in my Key Expression? (although that field doesn't exist in IM_ItemVendor).  So it seems like it's not feasible to pull in Vendor Purchase Address/contact info into the 'Multiple Vendor Lookup' in Item Maint.  Or am I missing a step?

Not to steal ANY of @David Speck II posts (they are truly amazing) but I want to give a shout out to @David Overholt for an oldie but still very goodie for his trail blazing work back in the day..

I'm trying to figure out the easiest way to lookup | Sage 100 (90minds.com)

I want to create a custom ALE in inventory inquiry to see Qty on SO and Qty on Backorder.  The customer only uses warehouse 000.  So the primary table is CI_Item and the link table is IM_ItemWarehouse.  Is this possible?  If so, what should the key expression be?  I tried CI_Item+"000" and only 0.00 appears in the lookup for Qty on So and Qty on Backorder. 

I am pretty sure you need all the primary key fields for the table you are linking to.  So, linking to IM_ItemWarehouse would require either ItemCode+DefaultWarehouseCode or ItemCode+"000"... to return a unique result.

I just created the below 'link' via VI Export Job Maint (PO_VendorPurchaseAddress for IM_ItemVendor), but the new tables' fields are all showing as 'not on file' in my ALE (below).  Is that because I would need to also include PrimaryPurchaseAddressCode in my Key Expression? (although that field doesn't exist in IM_ItemVendor).  So it seems like it's not feasible to pull in Vendor Purchase Address/contact info into the 'Multiple Vendor Lookup' in Item Maint.  Or am I missing a step?

Not to steal ANY of @David Speck II posts (they are truly amazing) but I want to give a shout out to @David Overholt for an oldie but still very goodie for his trail blazing work back in the day..

I'm trying to figure out the easiest way to lookup | Sage 100 (90minds.com)

I want to create a custom ALE in inventory inquiry to see Qty on SO and Qty on Backorder.  The customer only uses warehouse 000.  So the primary table is CI_Item and the link table is IM_ItemWarehouse.  Is this possible?  If so, what should the key expression be?  I tried CI_Item+"000" and only 0.00 appears in the lookup for Qty on So and Qty on Backorder. 

Thanks.  Also wondering if this flows into BIE.  At first glance it doesn't appear so but I don't do much BIE stuff.

I am pretty sure you need all the primary key fields for the table you are linking to.  So, linking to IM_ItemWarehouse would require either ItemCode+DefaultWarehouseCode or ItemCode+"000"... to return a unique result.

I just created the below 'link' via VI Export Job Maint (PO_VendorPurchaseAddress for IM_ItemVendor), but the new tables' fields are all showing as 'not on file' in my ALE (below).  Is that because I would need to also include PrimaryPurchaseAddressCode in my Key Expression? (although that field doesn't exist in IM_ItemVendor).  So it seems like it's not feasible to pull in Vendor Purchase Address/contact info into the 'Multiple Vendor Lookup' in Item Maint.  Or am I missing a step?

Not to steal ANY of @David Speck II posts (they are truly amazing) but I want to give a shout out to @David Overholt for an oldie but still very goodie for his trail blazing work back in the day..

I'm trying to figure out the easiest way to lookup | Sage 100 (90minds.com)

I want to create a custom ALE in inventory inquiry to see Qty on SO and Qty on Backorder.  The customer only uses warehouse 000.  So the primary table is CI_Item and the link table is IM_ItemWarehouse.  Is this possible?  If so, what should the key expression be?  I tried CI_Item+"000" and only 0.00 appears in the lookup for Qty on So and Qty on Backorder. 

I just created the below 'link' via VI Export Job Maint (PO_VendorPurchaseAddress for IM_ItemVendor), but the new tables' fields are all showing as 'not on file' in my ALE (below).  Is that because I would need to also include PrimaryPurchaseAddressCode in my Key Expression? (although that field doesn't exist in IM_ItemVendor).  So it seems like it's not feasible to pull in Vendor Purchase Address/contact info into the 'Multiple Vendor Lookup' in Item Maint.  Or am I missing a step?

Not to steal ANY of @David Speck II posts (they are truly amazing) but I want to give a shout out to @David Overholt for an oldie but still very goodie for his trail blazing work back in the day..

I'm trying to figure out the easiest way to lookup | Sage 100 (90minds.com)

I want to create a custom ALE in inventory inquiry to see Qty on SO and Qty on Backorder.  The customer only uses warehouse 000.  So the primary table is CI_Item and the link table is IM_ItemWarehouse.  Is this possible?  If so, what should the key expression be?  I tried CI_Item+"000" and only 0.00 appears in the lookup for Qty on So and Qty on Backorder. 

You do need to have all of the primary key fields in the target table available in the primary table to link to the target table. 

You could use a sourced UDF in IM_ItemVendor to pull the PrimaryPurchaseAddressCode from the VendorNo source but this can potentially get stale since it won't be updated automatically when a user changes a vendor's primary purchase address.

Instead, you can make use of the FNREAD$ (for string fields) and FNREAD (for numeric fields) functions in a new calculated field.  For string fields/values, clear the value in the Mask field, it will warn you that it is invalid, just click OK.  Use the following calculation to return the PurchaseName field from the PO_VendorPurchaseAddress table based on the item's vendor's primary address code.  For the additional fields needed from PO_VendorPurchaseAddress, add a new calculated field for each and replace the "PurchaseName" near the end with the field you want returned, don't include the"$" suffix for string fields in this argument.

FNREAD$(STR(dat.APDivisionNo$) + PAD(STR(dat.VendorNo$), VendorNo.Length, "R", CHR(0)) + STR(FNREAD$(STR(dat.APDivisionNo$) + STR(dat.VendorNo$), "AP_Vendor", "1", 0, "PrimaryPurchaseAddressCode")), "PO_VendorPurchaseAddress", "1", 0, "PurchaseName")

I just created the below 'link' via VI Export Job Maint (PO_VendorPurchaseAddress for IM_ItemVendor), but the new tables' fields are all showing as 'not on file' in my ALE (below).  Is that because I would need to also include PrimaryPurchaseAddressCode in my Key Expression? (although that field doesn't exist in IM_ItemVendor).  So it seems like it's not feasible to pull in Vendor Purchase Address/contact info into the 'Multiple Vendor Lookup' in Item Maint.  Or am I missing a step?

Not to steal ANY of @David Speck II posts (they are truly amazing) but I want to give a shout out to @David Overholt for an oldie but still very goodie for his trail blazing work back in the day..

I'm trying to figure out the easiest way to lookup | Sage 100 (90minds.com)

I want to create a custom ALE in inventory inquiry to see Qty on SO and Qty on Backorder.  The customer only uses warehouse 000.  So the primary table is CI_Item and the link table is IM_ItemWarehouse.  Is this possible?  If so, what should the key expression be?  I tried CI_Item+"000" and only 0.00 appears in the lookup for Qty on So and Qty on Backorder.