SAP Business One Query

[Former Member]

Hi Experts,

Hopefully someone can help...

I require a report with selection criteria.

I wish to run a report that looks at the Item Master Data Length, Width and Height Fields.

Basically when I run the report I would like to have 4 selection criteria boxes:

1. Item Group

2. Length

3. Width

4. Height

When I enter the relevant data in one or more then a list of items in that length or greater will appear. I only want to show items that are in stock.

Example: Item Group = Timber

                Length = 2 metres

Results show anything 2m or longer.

Is this possible?

I appreciate your help...

P

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query:

SELECT T0.[ItemCode], T0.[ItemName], T0.[SHeight1], T0.[SLength1], T0.[SWidth1], T0.[SWeight1] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod WHERE T1.[ItmsGrpNam] = [%0] and T0.[SLength1] = [%1]

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi Nagarajan,

Almost what I am looking for...

I require the data to be pulled from the purchasing Length, Width and Height and I also need search criteria for width and height.

Thank you for your help...

P

[kothandaraman_nagarajan · Accepted solution]

Try this:

SELECT T0.[ItemCode], T0.[ItemName], T0.[BHeight1], T0.[BLength1], T0.[BWidth1], T0.[SWeight1] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod WHERE T1.[ItmsGrpNam] = [%0] and T0.[BLength1] = [%1] and T0.[BHeight1] = [%2] and T0.[BWidth1] = [%3]

[Former Member · Accepted solution]

Hi,

This doesn't appear to work.

For example,

I have a piece of timber in item group 'Treated Timber'

Purchasing Length = 1500mm

Purchasing Width = 125

Purchasing Height = 10

However when I select the relevant criteria no results are found.

Ideally I would like it to show anything in stock that is equal to the criteria and also greater than the criteria i.e. if there was another piece of timber the same width and height however the length is 2000mm I would still like that to appear....

I don't think my explanation is great - apologies.

Thanks again

[kothandaraman_nagarajan · Accepted solution]

Try this:

SELECT T0.[ItemCode], T0.[ItemName], T0.[BHeight1], T0.[BLength1], T0.[BWidth1], T0.[BWeight1],t0.onhand

FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod

WHERE T1.[ItmsGrpNam] = [%0] and T0.[BLength1] = [%1] and T0.[BHeight1] = [%2] and T0.[BWidth1] = [%3] and  (t0.onhand >=  [%1] or  t0.onhand >= [%2])

[Former Member · Accepted solution]

Hi Nagarajan,

This is still the same. No items are appearing when the query is run.

Thanks

[kothandaraman_nagarajan · Accepted solution]

Share your item master data with on hand, lengh, width & height.

[Former Member · Accepted solution]

Apologies I don't understand what you are asking me to do?

I have the following piece of timber

Item Group: Treated Timber

BLength     : 1500mm

BWidth      :  125mm

BHeight     :  10mm

On Hand:      1326

However when I select the required criteria, nothing appears. I would like anything that is equal to or greater than the above criteria.

Example:

Anything that is >=1500m long, >=125mm wide, and >=10mm high that is in the Treated Timber item group.

Thanks

[kothandaraman_nagarajan · Accepted solution]

Yes same thing tested. Please refer below screen shot:

[Former Member · Accepted solution]

Thanks Nagarajan,

I really appreciate your help.

Using the answers you have given me I have been able to create the following query:

SELECT T0.[ItemCode], T0.[ItemName], T0.[OnHand], T1.[ItmsGrpNam] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod WHERE T1.[ItmsGrpNam] = [%0] and  T0.[BHeight1] >= [%1] and  T0.[BWidth1] >=[%2] and  T0.[BLength1] >= [%3]

This pulls through everything I require, however it also pulls through everything with a 0 stock.

If you could help in advising me where to include the ' Where T0.[OnHand] >= '1' that would be great.

Thanks

[kothandaraman_nagarajan · Accepted solution]

Try this:

SELECT T0.[ItemCode], T0.[ItemName], T0.[OnHand], T1.[ItmsGrpNam] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod WHERE T1.[ItmsGrpNam] = [%0] and  T0.[BHeight1] >= [%1] and  T0.[BWidth1] >=[%2] and  T0.[BLength1] >= [%3] and  T0.[OnHand]  > 0

[Former Member]

Thank you for your help. I know have everything I was looking for.

Much appreciated.

Thanks

[javier_facessantos]

Hello Paul

Try by running this query:

SELECT T1.[ItmsGrpNam],T0.[SLength1],T0.[SLen1Unit],  T0.[SWidth1],T0.[SWdth1Unit],  T0.[SHeight1], T0.[SHght1Unit] FROM [dbo].[OITM]  T0 INNER JOIN OITB T1 ON T0.[ItmsGrpCod] = T1.[ItmsGrpCod] WHERE T0.[SLength1] >'2' AND T1.[ItmsGrpNam] = 'Timber'

Let me know if that is ok or not

Regards

[Former Member]

Hi,

Thanks for the reply... I think there may be a misunderstanding....

I would like a search criteria box so I can select the relevant item group, select the relevant length, width etc.

Therefore the example I gave above.....anything belonging to item group timber that is more than 2m long should appear in the list. However if I was to leave all selection criteria empty then everything in stock would appear.

Hope this clears it up...

Thanks again.