cancel
Showing results for 
Search instead for 
Did you mean: 

How to populate Folder 2 based on conditions in Folder 1?

leon_laikan
Participant
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

edy_simon
Active Contributor
0 Kudos

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

leon_laikan
Participant
0 Kudos

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

ANKIT_CHAUHAN
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Leon,

Use it as follows:

Dim sSQL as string = "Select T0.DocEntry from oinv T0 where T0.DocEntry IN (sInvEntries) "


oForm.DataSources.DataTables.Item(0).ExecuteQuery(sSQL)




Hope it helps.



Thanks & Regards


Ankit Chauhan

leon_laikan
Participant
0 Kudos

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

edy_simon
Active Contributor
0 Kudos

Hi Leon

oForm.DataSources.DataTables.Item(0).ExecuteQuery _

                 (string.format("Select T0.DocEntry from oinv T0 where T0.DocEntry IN ({0})",sInvEntries ))



leon_laikan
Participant
0 Kudos

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

Your code works perfectly!

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

edy_simon
Active Contributor
0 Kudos

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

leon_laikan
Participant
0 Kudos

Thanks again Edy for your enlightening explanation.

Everything is now very clear to me.

Best Regards,

Leon

Answers (1)

Answers (1)

ANKIT_CHAUHAN
Product and Topic Expert
Product and Topic Expert
0 Kudos

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

leon_laikan
Participant
0 Kudos

Hi Ankit,

Thanks a lot for your reply.

Your suggestion makes good sense.

I'll try it to-morrow morning when I'm back in office and will let you know if I have problems.

Best Regards,

Leon Lai

leon_laikan
Participant
0 Kudos

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

ANKIT_CHAUHAN
Product and Topic Expert
Product and Topic Expert
0 Kudos

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

ANKIT_CHAUHAN
Product and Topic Expert
Product and Topic Expert
0 Kudos

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

leon_laikan
Participant
0 Kudos

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

ANKIT_CHAUHAN
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Leon,

You need to use this as follows:

Dim list As New List(Of Integer)


Hope it helps.

Thanks & Regards

Ankit Chauhan

ANKIT_CHAUHAN
Product and Topic Expert
Product and Topic Expert
0 Kudos

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

leon_laikan
Participant
0 Kudos

Hi

Now I have a similar problem with:

Dim st as StringBuilder = New StringBuilder()

StringBuilder does not exist in VB.NET (I am programming in VB.NET)

----

Kindly advise

Thanks

Leon

ANKIT_CHAUHAN
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Leon,

Can you try this ? The problem is that I don't have VB .Net Project.

Dim builder As New StringBuilder



Hope it helps.



Thanks & Regards


Ankit Chauhan

leon_laikan
Participant
0 Kudos

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