Line loop on Grid excluding first four lines

[lsauser]

Hi all,

I have come up with an interesting dilemma and for the life of me I cannot figure out a clear explanation as to why it is happening.

I am running a loop over the lines on a grid and checking whether the PickedQty > 0, if it is then the line is included in a stock transfer document. This is working perfectly fine if include any lines from the fifth one onwards, however, if I include any one of the first four lines OR if I include any of the first four lines in addition to the fifth line onwards then I still get a success message but no transfer document is made.

When I try any of the first four rows I get the processing line x / x status message so I know the condition is correct for that part of the code to trigger.

try
{

    // Define a flag to track if any rows with PickedQty > 0 are found
    bool rowsFound = false;

    Grid grid = (Grid)form.Items.Item("BOY_1").Specific;
    int rowsCount = grid.Rows.Count;

    // Iterate over the grid rows to check for PickedQty > 0
    for (int i = 0; i < rowsCount; i++)
    {
        int qty = Convert.ToInt32(grid.DataTable.GetValue("PickedQty", i));
        
        if (qty > 0)
        {
            rowsFound = true;
            break; // Exit the loop as soon as a row with PickedQty > 0 is found
        }
    }

    // If no rows with PickedQty > 0 are found, alert the user and exit
    if (!rowsFound)
    {
        application.StatusBar.SetText("No rows with Picked Quantity greater than 0 are selected.", SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Error);
        return; // Exit the method
    }

    // Proceed with creating the stock transfer document
    SAPbobsCOM.StockTransfer transfer = (SAPbobsCOM.StockTransfer)company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oStockTransfer);
    transfer.DocDate = DateTime.Now;
    transfer.FromWarehouse = "2WM";
    transfer.ToWarehouse = "2WM";

    // Iterate over all rows in the grid and process those with PickedQty > 0
    for (int i = 0; i < rowsCount; i++)
    {
        string ItemCode = grid.DataTable.GetValue("ItemCode", i).ToString();
        string Warehouse = grid.DataTable.GetValue("WhsCode", i).ToString();
        int qty = Convert.ToInt32(grid.DataTable.GetValue("PickedQty", i));

        // Process only rows with PickedQty > 0
        if (qty > 0)
        {
            application.StatusBar.SetText("Processing line " + (i + 1) + "/" + rowsCount, SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Success);
            transfer.Lines.ItemCode = ItemCode;
            transfer.Lines.FromWarehouseCode = "2WM";
            transfer.Lines.WarehouseCode = Warehouse;
            transfer.Lines.Quantity = qty;
            transfer.Lines.Add();
        }
    }

    // Check if any lines were added to the transfer document
    if (transfer.Lines.Count > 0)
    {
        transfer.Add();
        application.StatusBar.SetText("All items successfully transferred", SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Success);
    }
    else
    {
        application.StatusBar.SetText("No items with Picked Quantity greater than 0 to transfer", SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Error);
    }

    // Release the COM object
    System.Runtime.InteropServices.Marshal.ReleaseComObject(transfer);
}
catch (Exception ex)
{
    // Handle the exception gracefully
    application.StatusBar.SetText("Error: " + ex.Message, SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Error);
}

Hopefully someone else can see some reason I am missing as to why it is doing this, i'm stumped. Happy to provide further context if required.

Kind Regards,

Nick

[BattleshipCobra · Accepted solution]

Oh baby baby this is right in my wheelhouse.  A B1UP grid loop of an SQL Report UF??

I have done something very similar, not identical but my code will hopefully give you a couple of good ideas on how to troubleshoot your UF.

The point of my code is to create a "bill of lading" document by linking several sales orders through the "Referenced Document" system.  Then I use this to do a grouped printout later in Crystal.

I run the first part off of a Sales Order from a function button into an SQL Report Universal Function like this (pretty standard HANA SQL):

SELECT
     'N' AS "Link"
    ,T0."DocEntry"
    ,$[ORDR.DocEntry] AS "BaseDocEntry"
    ,CASE (SELECT COUNT(*) FROM RDR21 S0 WHERE S0."DocEntry" = $[ORDR.DocEntry]) WHEN 0 THEN 'True' ELSE 'False' END AS "FirstRow"
    ,T0."DocNum"
    ,CASE T0."DocStatus" WHEN 'O' THEN 'Open' ELSE 'Closed' END AS "Status"
    ,T0."NumAtCard"
    ,T0."DocDate"
    ,T0."DocDueDate"
    ,CASE WHEN T1."DocEntry" IS NOT NULL THEN 'Y' ELSE 'N' END AS "Linked"
    ,T0."ShipToCode"
    ,T0."Address2"
    ,X."ItemCode"
    ,X."ItemName"
    ,X."Quantity"

FROM
    ORDR T0
    LEFT JOIN RDR21 T1 ON T0."DocEntry" = T1."RefDocEntr" AND T1."DocEntry" = $[ORDR.DocEntry] AND T0."ObjType" = T1."ObjectType" AND T0."ObjType" = T1."RefObjType" AND T1."Remark" = 'Shipping Link'

    INNER JOIN (
        SELECT
             D0."DocEntry"
            ,D0."ItemCode"
            ,D0."Dscription" AS "ItemName"
            ,D0."Quantity"
       FROM RDR1 D0
       WHERE D0."VisOrder" = 0
    ) X ON T0."DocEntry" = X."DocEntry"

WHERE 
    T0."CardCode" = $[$4.0.0]
    AND
    T0."ShipToCode" = $[$40.0.0]
    AND
    T0."DocNum" > $[$8.0.0]
    AND
    (T0."DocStatus" = 'O' OR T1."DocEntry" IS NOT NULL)

ORDER BY T0."DocNum"

The code indicates other sales orders going to the same address and shows if they are already linked through the "Referenced Document" system or not.

Then there is a checkbox in the first column which I use as an indicator for the Dynamic Syntax to pick up later.

I used the Item Placement Tool to make a button called "Link Shipments" and then a B1 Validation to run this dynamic syntax which loops the SQL Report grid, looks for shipments that have a checkbox and that are not already linked (this is C#):

// Variables
string docLink,isLinked,DocEntry,DocNum,BaseDocEntry,cMessErr;
int nResult;
bool FirstRow;

// Assuming you have a grid object named "oGrid" in your SAP Business One form
Grid oGrid = (Grid)application.Forms.ActiveForm.Items.Item("BOY_1").Specific;

// Get the number of rows in the grid
int rowCount = oGrid.Rows.Count;

BaseDocEntry = oGrid.DataTable.GetValue("BaseDocEntry", 0).ToString();
FirstRow = Convert.ToBoolean(oGrid.DataTable.GetValue("FirstRow", 0).ToString());
SAPbobsCOM.Documents oOrder = (SAPbobsCOM.Documents)company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oOrders);
oOrder.GetByKey(Convert.ToInt32(BaseDocEntry));

// Loop through each row in the grid
for (int i = 0; i < rowCount; i++)
{

    // Get the value in a specific column and row
    docLink = oGrid.DataTable.GetValue("Link", i).ToString();
    isLinked = oGrid.DataTable.GetValue("Linked", i).ToString();

    if ( docLink == "Y" & isLinked == "N" ) {

        DocEntry = oGrid.DataTable.GetValue("DocEntry", i).ToString();
        DocNum = oGrid.DataTable.GetValue("DocNum", i).ToString();

        if ( FirstRow == false ) {

            oOrder.DocumentReferences.Add();

        }

        application.StatusBar.SetText("Linking Row " + (i+1) + ": " + DocNum, BoMessageTime.bmt_Medium, BoStatusBarMessageType.smt_Warning);

        oOrder.DocumentReferences.ReferencedObjectType = SAPbobsCOM.ReferencedObjectTypeEnum.rot_SalesOrder; 
        oOrder.DocumentReferences.ReferencedDocEntry = Convert.ToInt32(DocEntry);
        oOrder.DocumentReferences.Remark = "Shipping Link";

        FirstRow = false;

    }

}

nResult = oOrder.Update(); // returns error number during operation 0 is no error

if (nResult != 0)  {
    company.GetLastError(out nResult, out cMessErr);
    throw new Exception(cMessErr);
}

form.Items.Item("BOY_6").Click(); // Click Refresh button

application.StatusBar.SetText("Documents finished linking. (UF-024)", BoMessageTime.bmt_Medium, BoStatusBarMessageType.smt_Success);

As @Johan_H mentioned, an issue perhaps how you are doing it is the object already has a single line when you create it.  SO, if you declare the object, then define your properties and you add a row you technically do not need to do this, it will cause issues.  Not errors but it will usually just not work.  So you can see I start the code off with a "FirstRow" variable as "True" so I can avoid doing the add if it's the first row.  I had the same issue with my code, it would do every row except the first one properly until I did this.  I experimented, broke down the grid loop and then figured out that I didn't need to do the Add() off the first row.  Because there is already a row...

The second thing is that you should be wary of the TRY / CATCH stuff off the bat.  I use it but in your case you know your process is roughly correct.  You want to use the method I'm using for a better result (don't forget to define the variables):

nResult = oOrder.Update(); // returns error number during operation 0 is no error

if (nResult != 0)  {
    company.GetLastError(out nResult, out cMessErr);
    throw new Exception(cMessErr);
}

This gives you a clearer indication of what's wrong from my experience.

What I normally do to troubleshoot these type of builds is I just write out the steps I'm going to use manually without the loop.  So I would go like define the object, then pull the first row data into the properties then I would add then I would pull the second row into a new object and add, etc.  This way I know that I have a basic two / three row working step by step.  Then I work back to using the for() statements to recreate the same shape of the steps I need to do.  This is what got me working with the FirstRow Boolean process.  When I did the rows manually it didn't work, I was doing the add and it was just not doing it.  When I got rid of the add in my steps for the first row then it clicked into place and when I added the for() steps with the FirstRow it totally worked.

You can also break down each step and add MessageBox UI elements and StatusBar stuff too so you can see at each step if the data is going in the right place.

I plan on doing some videos on the B1UP Dynamic Syntax UF stuff.  I have a lot of examples now that I can share.

I hope this can help you, give it a try and if you are still stuck try and post your SQL Report code too, then I can have the full example to help troubleshoot.

Good luck!

Mike

[lsauser · Accepted solution]

Hi Mike,

Thank you so much for your in depth response to my issue. When i first started using B1UP several years ago I watched plenty of your videos on YouTube which helped with plenty of non-standard things I wanted to try so thank you for your contributions. Since then I've made plenty of left field reports/validations and solutions using the add-on and only recently just getting a bit more familiar with the SDK sides of it.

Using what you and @Johan_H have suggested I modified the code a bit to catch the problem and it turns out the first 4 rows just happen to be items that would have resulted in the quantity falling into the negative! Knowing this i just receipted some quantities of each item and now it works without any issues.

[BattleshipCobra · Accepted solution]

Beautiful, glad it worked out. Thanks for watching. I recently have been doing a ton of dynamic syntax work (mostly C#). It really allows you to really push the limits of functionality. I am hoping to publish a few videos with examples. Also thanks to @Johan_H!

[Johan_H]

Hi Nick,

I am not sure if this is related to this issue at all, but I am suprised that this code does not error out with a different error.

One of the base principals of the DI API, although admittedly not very intuitive, is that a transaction object starts off with one empty line, which you fill in. You then only add a new empty line, if you know there will be another one coming. As you end with adding a line, your app should be complaining about having missing data.

On the other hand, you are not checking for validation from the DI API.

//Add is a function that returns an integer.
//Only if this integer is 0, is the transaction
//actually successfull
transfer.Add();

You should check for the return value from the Add function, and if it is anything other than 0, you will need to use company.GetLastError(ErrCode, ErrMsg) to check the error information returned by the company object. Please note that you need to provide an integer and a string variable for this method to fill in, which you can then read.

Regards,

Johan