Skip to Content
0

How can i create formatted search to get the alternative items

Dec 01, 2017 at 12:24 AM

102

avatar image
Former Member

I want to create formatted search to get the alternative items with name and quantity in every warehouse and link it with Production order UDF for every row to get the alternative item to every row in Production order

but when i create the query and link it with the UDF it get one value only

how can i get all values?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Johan Hakkesteegt Dec 01, 2017 at 07:27 AM
0

Hi,

The UDF will only store one value, but depending on your query it can show you multiple choices.

Is the problem that the UDF only stores one value, or that the query isn't returning multiple choices?

Regards,

Johan

Show 10 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi

Thanks alot for your answer

How can i create query to show multiple choices

as i need to create Query to get the alternative items with name and quantity in every warehouse and link it with Production order UDF for every row

Regards,

Mahmoud Ali

0

Hi Mahmoud Ali,

You said that you had created a query. Could you post it? it is easier to adapt a query than to write one from scratch.

Regards,

Johan

0
Former Member
Johan Hakkesteegt

Select distinct T4.OnHand,T0.ItemCode,t0.ItemName,T2.AltItem,T2.Match,T4.WhsCode,T4.OnHand ,t3.itemName alt_Name from OITM T0

INNER JOIN OALI T2 ON T0.[ItemCode] = T2.[OrigItem]

INNER JOIN OITM T3 ON t3.[ItemCode] = T2.[AltItem]

Inner JOIN OITW T4 ON t3.[ItemCode] = T4.[ItemCode]

Inner JOIN OWOR T5 ON t5.[ItemCode] = T0.[ItemCode]

where t5.ItemCode =$[WOR1.Itemcode]

0

Hi Mahmoud Ali,

Generally speaking you do not need to add the tables and fields of the form that you are adding the FMS to, to the query.

Please give this a try:

SELECT T4.OnHand
      ,T0.ItemCode
      ,t0.ItemName
      ,T2.AltItem
      ,T2.Match
      ,T4.WhsCode
      ,T4.OnHand
      ,t3.itemName AS alt_Name
FROM OITM T0
     INNER JOIN OALI T2 ON T0.[ItemCode] = T2.[OrigItem]
     INNER JOIN OITM T3 ON t3.[ItemCode] = T2.[AltItem]
     INNER JOIN OITW T4 ON t3.[ItemCode] = T4.[ItemCode] AND T4.WhsCode = $[WOR1.wareHouse]
WHERE T0.ItemCode = $[WOR1.Itemcode]

Regards,

Johan

0
Former Member
Johan Hakkesteegt

Hi Johan

It give me on value zero

i want to display in this UDF alternative items for this item in the production order with name and quantity in every warehouse

Regards,

Mahmoud Ali

0

Hi Mahmoud Ali,

Please confirm:

  • that the item you tested with has alternative items determined.
  • that the alternative items have the warehouse determined that was set in the production order for the item that you tested with.

Regards,

Johan

0
Show more comments