on 03-10-2015 12:05 PM
Hi, everybody,
I have a form with 2 folders. Each folder contains a grid, which is populated from a different query.
Folder1 (PC Invoices) lists all Unreconciled Invoices. (It uses mainly OINV)
Folder2 (Journal Entries) lists all Journal Entries relating to the invoices listed in Folder 1.(It uses OJDT joined to OINV etc)
This is executed on pressing the button in Folder1
In Folder1, there is a column of combo box, so that the user can select BP_Codes.
(My aim is to transfer the selected invoices to the new BP_Codes)
I want Folder2 to list only those Journal Entries for the invoices in Folder1 which have a value in the BP_Code column,
i.e if the user does not select a BP_Code from the combo, the related Journal should not appear in Folder2
Can anybody give me an idea how to achieve this?
Thanks
Leon Lai
Hi Leon,
Add this code on after item pressed of "Folder2"
Dim sInvEntries as string = "-1"
For iRow as integer = 0 to oGrid1.DataTable.Rows.Count
IF oGrid1.DataTable.GetValue("BPCode",iRow).ToString().Trim() <> "" THEN
Dim InvDocEntry as string = oGrid1.DataTable.GetValue("InvDocEntry",0)
sInvEntries &= ", " & InvDocEntry
END IF
Next
Dim sSQL as string = "Build your query to get your JE data based on Invoice entries contains in the sInvEntries"
oGrid2.DataTable.ExecuteQuery(sSQL)
Regards
Edy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Edy,
I am working on your suggestion.
When I click the Generate J.Entry button, the list called sInvEntries is correctly generated (I used MessageBox).
For ex, if I select the first 3 rows, I get this sequence:
-1, 12060,12053,12112
By analogy with SQL, I used the IN function to select those items whose DocEntry are in the list.
But it does not work!
Questions:
(a) Why do you use -1 in your list?
(b) How to write the ExecuteQuery properly?
----------------------------
Here is my code:
Dim sInvEntries As String = "-1"
For iRow As Integer = 0 To oGrid.DataTable.Rows.Count - 1
If oGrid.DataTable.GetValue("BP_Code", iRow).ToString().Trim() <> "" Then
Dim InvDocEntry As String = oGrid.DataTable.GetValue("DocEntry", iRow)
sInvEntries &= ", " & InvDocEntry
End If
Next
SBO_Application.MessageBox(sInvEntries)
oItem2 = oForm.Items.Add("GridJE", SAPbouiCOM.BoFormItemTypes.it_GRID)
'==============================================
'//Set the grid dimentions and position
'==============================================
oItem2.Left = 20
oItem2.Top = 45
oItem2.Width = 800
oItem2.Height = 450
oGrid2 = oItem2.Specific
oItem2.FromPane = 2
oItem2.ToPane = 2
oForm.DataSources.DataTables.Add("JENTRY")
oForm.DataSources.DataTables.Item(0).ExecuteQuery _
("Select T0.DocEntry from oinv T0 where T0.DocEntry IN sInvEntries ") '//??????
oForm.State = SAPbouiCOM.BoFormStateEnum.fs_Maximized
oForm.PaneLevel = 2
oForm.Items.Item("Folder2").Specific.Select()
End If
---
Thanks
Leon
Hi Ankit,
I still get the same error:
Invalid query tree [300-33]. The SQL error is 8180
Normally, in SQL the code is something like:
SELECT T0.DocEntry from OINV T0 Where T0.DocEntry IN (12060, 120153,12112)
But sInvEntries gives this string:
-1,12060,120153,12112
Maybe I should modify the code so that it reads
('12060','120153','12112') ???
It seems either that
(a) IN is not the appropriate function to use
or
(b) SQL cannot pick up and use a variable declared in VB.NET
Thanks
Leon
Hi Edy,
I copied and pasted your formula.
I still get the same error:
Invalid query tree [300-33]. The SQL error is 8180
Why do you use {0} ?
Thanks
Leon
---------------------------------
Sorry Edy
My assistant has made changes to my program for testing.
When I reinstated the code, it works perfectly.
I am closing, but still would like to know why you used {0} ?
Best Regards
Leon Lai
Hi Leon,
Would address 2 of your questions :
1. why -1, it is just to simplify my code. If I hadn't put -1 as the first value, my SInvEntries would result in ",12060,12053,12112"
Note the comma in front. I would then have to remove the first comma before inserting into the query string. And since -1 is never in table, I am save to include it.
2. {0} is a place holder when using the string.format method. In my code, this place holder will be replaced with SInvEntries. Google up string.format to know more of this method.
Regards
Edy
Hi Leon,
You need to Loop through the Grid Rows and Get all Row's BP Codes which are not empty.
Then according to the Selected Customers, you can display the data.
Sample to Try:
for (int k = 0; k <= oGrid.Rows.Count - 1; k++)
{
string BPCode = oGrid.DataTable.GetValue("BPCode", k).ToString();
if (BPCode != "")
{
// Keep this BPCode Value in a List or However you want to keep.
}
}
Finally 'Select From Table Where CardCode in (Values in your List or wherever you have kept the same)'
Hope that helps.
Thanks & Regards
Ankit Chauhan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Ankit,
I am trying your suggestion, but I'm stuck.
To simplify my problem, let's use this simple query to generate data to populate the grid in the Journal Entry folder:
Select T0.CardCode from OCRD T0
This will return ALL CardCodes from OCRD (a long list).
How can I use your idea to return only those CardCodes that have been earmarked in the PC Invoices Folder?
I don't quite understand your remark:
// Keep this BPCode Value in a List or However you want to keep.
How to keep the BPCode Value in a List?
-----
I have tried using this code, but it's probably a mess:
Dim k As Integer
For k = 0 To oGrid.Rows.Count - 1
Dim Code As String = oGrid.DataTable.GetValue("BP_Code", k).ToString
If Code <> "" Then
oItem2 = oForm.Items.Add("GridJE", SAPbouiCOM.BoFormItemTypes.it_GRID)
'==============================================
'//Set the grid dimentions and position
'==============================================
oItem2.Left = 20
oItem2.Top = 45
oItem2.Width = 800
oItem2.Height = 450
oGrid2 = oItem2.Specific
oItem2.FromPane = 2
oItem2.ToPane = 2
oForm.DataSources.DataTables.Add("JENTRY")
oForm.DataSources.DataTables.Item(0).ExecuteQuery _
("Select T0.CardCode from ocrd T0 where T0.CardCode = Code") '// Error here!
oGrid2.DataTable = oForm.DataSources.DataTables.Item("JENTRY")
oForm.State = SAPbouiCOM.BoFormStateEnum.fs_Maximized
oForm.PaneLevel = 2
oForm.Items.Item("Folder2").Specific.Select()
End If
Next
Best Regards,
Leon Lai
Hi Leon,
You need to keep the Selected CardCodes value somewhere like as follows:
List<Int16> li = new List<Int16>();
for (int k = 0; k <= oGrid.Rows.Count - 1; k++)
{
Int16 DocEntry = Convert.ToInt16(oGrid.DataTable.GetValue("DocEntry", k));
if (DocEntry <> 0)
{
li.Add(DocEntry );
}
}
Hope it helps.
Thanks & Regards
Ankit Chauhan
Hi,
Here is the working sample:
List<string> Customer = new List<string>();
for (int k = 0; k <= oGrid.Rows.Count - 1; k++)
{
string IsCustomer = oGrid.DataTable.GetValue("Customer", k).ToString();
Customer.Add(IsCustomer );
}
StringBuilder st = new StringBuilder();
bool bt = false;
try
{
for (int i = 0; i < Customer.Count; i++)
{
if (bt)
{
st.Append(" OR CardCode =");
}
st.Append("'");
st.Append(Customer[i]);
st.Append("'");
bt = true;
}
string New = st.ToString();
string Query = "Select * From OCRD Where CardCode = " + New + " ";
}
catch (Exception ex)
{
Program.oMainSAPDI.ShowMessage(ex.Message, BoStatusBarMessageType.smt_Error);
}
Hope it helps.
Thanks & Regards
Ankit Chauhan
Hi Ankit,
I converted your C# code into VB.NET using a converter.
Dim li As List<Int16> = New List<Int16>()
Dim k As Integer
For k = 0 To oGrid.Rows.Count - 1 Step k + 1
Dim DocEnTry As Int16 = Convert.ToInt16(oGrid.DataTable.GetValue("DocEntry",k))
If DocEnTry <> 0 Then
li.Add(DocEnTry)
End If
Next
----
Problem:
It seems that List does not exist in VB,NET
Please check if the converted code is OK, and amend it if needed
Thanks
Leon
Hi,
In VB .NET
Dim Customer As New List(Of String)()
For k As Integer = 0 To oGrid.Rows.Count - 1
Dim IsCustomer As String = oGrid.DataTable.GetValue("Customer", k).ToString()
Customer.Add(IsCustomer)
Next
Dim st As New StringBuilder()
Dim bt As Boolean = False
Try
For i As Integer = 0 To Customer.Count - 1
If bt Then
st.Append(" OR CardCode =")
End If
st.Append("'")
st.Append(Customer(i))
st.Append("'")
bt = True
Next
Dim [New] As String = st.ToString()
Dim Query As String = "Select * From OCRD Where CardCode = " & [New] & " "
Catch ex As Exception
Program.oMainSAPDI.ShowMessage(ex.Message, BoStatusBarMessageType.smt_Error)
End Try
Hope it helps.
Thanks & Regards
Ankit Chauhan
Hi Ankit,
Unfortunately, Stringbuilder does not exist in VB.NET, although it exists in C#
Using Intellisense, I find that the only words containing "String" are:
String, StringFormat, StringSplitOption.
Well, don't bother.
I used Edy's method to generate the same string without using Stringbuilder.
If you could tell me what to do next, it would be great. See my reply to Edy
Best Regards
Leon
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.