cancel
Showing results for 
Search instead for 
Did you mean: 

How can i create formatted search to get the alternative items

0 Kudos

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?

Accepted Solutions (1)

Accepted Solutions (1)

Johan_H
Active Contributor
0 Kudos

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

0 Kudos

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

Johan_H
Active Contributor
0 Kudos

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 Kudos

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]

Johan_H
Active Contributor
0 Kudos

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 Kudos

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

Johan_H
Active Contributor
0 Kudos

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

Hi Johan,

Its working fine , thanks alot for your kind co-operation

Regards,

Mahmoud Ali

Johan_H
Active Contributor
0 Kudos

Hi Mahmoud Ali,

I am glad that you got it to work. Please close this thread by clicking the 'accept' link below my answer.

Regards,

Johan

0 Kudos

Hi Johan

Last question

when i have a one alternative item only for the item it not display all the list just display
the item code in the UDF .. if the is more than one item it display correctly

how can i solve it

Regards,

Mahmoud Ali

Johan_H
Active Contributor

Hi Mahmoud Ali,

My guess is that that one item probably does not have the warehouse determined that was set in the production order for the item that you tested with. Please check this.

Regards,

Johan

Answers (0)