cancel
Showing results for 
Search instead for 
Did you mean: 

Formula field that looks at more than one value - Item Group number

Former Member
0 Kudos

Hi Experts,

I hope you're all well.

I'm hoping you can help me with something I'm doing with my PLD please.

I am setting up my Sales Quotation PLD only prints out Items which belong to a certain Item Group, for example only Items belonging to Item Group 100. I made a formula field which says F_XXX==100 which works perfectly.

Now what I want to do is set up a formula field that doesn't only look at Item Group 100, but which also looks at Item Groups 137, 151, 175, 187, 189.... do I have to set up a seperate Formula field for each of these Items Groups or is there a way to make a formula field which can look at all of these Item Groups together please?

Many thanks for any help.

Caroline

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Caroline,

I stopped using PLDs a long time as they were very restrictive when it came to formulas (Crystal is much better), but from memory, you cannot reference multiple conditions in a single formula. You cannot say IF x = 100 OR 137 OR etc.

You might be able to do it using lots of formulas but it would be messy and complicated.

What about using a different approach though, do the complicated part on the Quotation itself using a User Defined Field and a Formatted Search (the SQL query for the FMS gives you much more flexibility than PLD formulas) and then use a simple formula on the PLD to check the value of the UDF? Something like the following:

- Create a User Defined Field at line level on the Quotation e.g. U_DisplayItem, that has Y and N valid values and you could default it to N (make it mandatory so it assigns an N value to all previous documents).

- Then assign a formatted search to the UDF that auto-refreshes after exiting the Item No. column (display saved values). The formatted search query would select a value of Y if the item belongs to your list of item groups. Something like the following:

SELECT 'Y' 
FROM OITM o
WHERE o.ItemCode = $[$38.1.0]
AND o.ItmsGrpCod IN (100,137,151,...)

- Then on the PLD just use a single formula to show the item when U_DisplayItem = 'Y'.

Something like the above should work. I used to find it was always easier in the long run to do all the hard work before you get to the PLD stage if at all possible.

Hope this helps.

Regards,

Andrew.