cancel
Showing results for 
Search instead for 
Did you mean: 

How to make Columnwise total using Query in the picklist window

Former Member
0 Kudos
72

Hi Experts,

I want to make the column wise total for the values in the field(Picked) in the Pick list window through query.

How to do it?

Regards,

Magesh.

Accepted Solutions (1)

Accepted Solutions (1)

former_member583013
Active Contributor
0 Kudos

This Query will sum pick qty and you will have to specify the Pick No.

SELECT SUM(PICKQTTY) WHERE ABSENTRY = '[%0]'

Do you want to do it by Pick Id or On the Release Screen or where exactly do you want to Run this Query

Suda

Former Member
0 Kudos

Hi Suda,

yes you are correct ,but i want to get the value that is the sum(pickqtty) in the remarks window automatically without choosing the absentry.. but it should be for the current AbsEntry..How to get it directly..

Regards,

Magesh.

former_member583013
Active Contributor
0 Kudos

Please use the following Query, You will need to Press Shift+F2 to fire this query.

Only after you select your pick quantity and hit Update will the Query be able to get the Picked Qty from the database. If I set the Auto refresh on Status then it does not work.

SELECT SUM(T0.PickQtty) FROM [dbo].[PKL1] T0

WHERE T0.AbsEntry = $[$6.2]

Save the Query and Attach the same to the Remarks Field.

Suda

Former Member
0 Kudos

Hi Suda,

Thanks it is working.

Regards,

Magesh.

Answers (2)

Answers (2)

former_member186095
Active Contributor
0 Kudos

Hello,

By using F1 help file, I could replicate your problem and the query is as follows:

select distinct t0.itemcode, t0.quantity from rdr1 t0 inner join ordr t1 on t1.docentry = t0.docentry

where t1.docnum = '1500004'

compute sum(t0.quantity)

But unfortunately, compute sum(..) can't be used in query generator of SAP B1

Rgds,

former_member583013
Active Contributor
0 Kudos

Could you tell what are the columns you are looking to have the Query. If you have started with the query then paste the same here so that I can take a look.

FYI, The table is PLK1 and the field is PickQtty

You can SELECT the fields you want and then Group by all the Fields and SUM(PickQtty)

Suda

Former Member
0 Kudos

Hi Suda,

I have the following values in the Picked fields:

Picked

10

20

30

-


60

-


i want to get the column total 60.. If iam clicking the Pickall button the values will get posted in the Picked field.. i want the query to total the value of picked field..

How to do it?

Regards,

Magesh.