cancel
Showing results for 
Search instead for 
Did you mean: 

Filter Table data on Query Result

Former Member
0 Kudos

Dear All

When I execute the following query (in Query Generator) I got 6411 records. But when I want to filer this table, in the drop down list in 'ITEM' column I found only 500 records (i.e. up to the recods starting with 'K' though there are records starting with 'W' in 'ITEM' field).

I am using SAP B1, PL 14.

Can any one suggest how to get all the records in the drop down list of filter window?

My query is as follows:

SELECT distinct D.ITEMCODE + ' - ' + D.DSCRIPTION 'ITEM',

DATENAME(M,H.DOCDATE) + ' - ' + DATENAME(YYYY,H.DOCDATE) 'MONTH',

D.PRICE

FROM PCH1 D INNER JOIN OPCH H ON H.DOCENTRY= D.DOCENTRY

WHERE H.DOCTYPE='I' AND D.ITEMCODE + ' - ' + D.DSCRIPTION IS NOT NULL

ORDER BY 1, 2

Thnaks & with regards

Aloke

Edited by: ALOKE BANDYOPADHYAY on Jul 15, 2011 4:51 PM

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Dear Aloke,

500 lines are a hard limit for filtered table data on Query Result. You may try in the newest version to see if this has any changed.

Thanks,

Gordon

Former Member
0 Kudos

Dear Gordon

What you indicate by newest version? Is it SAP B1 8.81 or any Patch level higher than 14 of SAP B1 8.8 which I am using now?

Please clarify.

Thanks

Aloke

Former Member
0 Kudos

Dear Gordon

I have placed the following problem also in forum since one month without receiving any positive answer. So I am requesting you personally can you help me to solve the problem:

Following is the code I am using for revaluation of items which is giveing "Internal Error 5002':

Dim lMaterialRevaluation As SAPbobsCOM.MaterialRevaluation

lMaterialRevaluation = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oMaterialRevaluation)

For li As Integer = 0 To ls_Lines.Length - 1

If ls_Lines(li).Split(vbTab).Length 5 Then

My.Computer.FileSystem.WriteAllText(ls_OutFileName, lnl_SrlNo & "--> Can't Read in the data in Line " & li + 1, True)

My.Computer.FileSystem.WriteAllText(ls_OutFileName, vbNewLine, True)

lnl_SrlNo += 1

Continue For

End If

ls_ItemCode = ls_Lines(li).Split(vbTab)(0).Trim

ls_WareHouseCode = ls_Lines(li).Split(vbTab)(1).Trim

ld_Price = ls_Lines(li).Split(vbTab)(2).Trim

li_IncAccount = ls_Lines(li).Split(vbTab)(3).Trim

li_DecAccount = ls_Lines(li).Split(vbTab)(4).Trim

lMaterialRevaluation.Lines.SetCurrentLine(li)

lMaterialRevaluation.Lines.ItemCode = ls_ItemCode

lMaterialRevaluation.Lines.WarehouseCode = ls_WareHouseCode

lMaterialRevaluation.Lines.Price = ld_Price

lMaterialRevaluation.Lines.RevaluationIncrementAccount = li_IncAccount

lMaterialRevaluation.Lines.RevaluationDecrementAccount = li_DecAccount

lMaterialRevaluation.Lines.Add()

Next

Dim li_Ret As Integer

li_Ret = lMaterialRevaluation.Add

If li_Ret = 0 Then

MsgBox("Material Revaluation Completed")

System.Runtime.InteropServices.Marshal.ReleaseComObject(lMaterialRevaluation)

Else

oCompany.GetLastError(lErrCode, sErrMsg)

MsgBox(sErrMsg & ", Material Revaluation failed")

End If

Thanks & with regards

Aloke

Former Member
0 Kudos

Hi ALok........

Filter data range is Limited to 500 as I can also see in SAP 8.81 PL05......

But you can manage the same in Where Clause so that when you select any data it would filter and when you select nothing it would show everything........

Regards,

Rahul

Former Member
0 Kudos

I refer to the latest 8.81 and do not say it is changed. For you other posting, I have answered in your original thread.

Former Member
0 Kudos

Dear Gordon

Thanks for the pains you have taken to help me to solve my problem.

Thanking you once again

Aloke