Display data from a temp table in a grid

(2) Share

Report

Posted on by JeanB

210

Hello everyone, I hope you're doing well.

I'm a beginner in programming and currently facing an issue with a grid that should display data from an InMemory table in a form.

The goal is to have a form with two fields: Start date and End date, plus a "Calculate" button.

When I click the button, I want the grid to display the sum of invoice line amounts per item, within the selected date range.

So far:

The form exists
The date fields and button are in place
The calculation runs, and I use info() messages to log each item and its total amount — so the logic seems to work

But... the grid remains empty.

When I check the table browser in another tab right after clicking the button, the InMemory table is still empty.
I’m not sure if the problem comes from the data not being inserted in the table, or if the grid isn’t displaying it properly.

I really don’t know what I’m missing.

Here’s what I’ve done:

A screenshot of the form with the info() messages

A screenshot of Visual Studio

And my current method:

[Form]
public class TESTJBB11 extends FormRun
{
    FormDateControl startDateCtrl;
    FormDateControl endDateCtrl;

    /// <summary>
    /// Initializes the form and binds the date controls.
    /// </summary>
    public void init()
    {
        super();

        // Link the form controls to class variables using their names from the designer
        startDateCtrl = element.design().controlName("StartDateControl");
        endDateCtrl = element.design().controlName("EndDateControl");
    }

    [Control("Button")]
    class CalculateButton
    {
        /// <summary>
        /// Calculates the total invoice line amount per item between two selected dates.
        /// The result is inserted into an InMemory table displayed by the form grid.
        /// </summary>
        public void clicked()
        {
            super();

            Map                      itemMap = new Map(Types::String, Types::Real); // To accumulate totals per ItemId
            CustInvoiceTrans         invoiceTrans;
            TmpSalesInvoiceSumJBBTEST tmpBuf;
            TransDate                startDate = startDateCtrl.dateValue();
            TransDate                endDate = endDateCtrl.dateValue();

            // Validate date fields
            if (!startDate || !endDate)
            {
                error("Please enter both a start and end date.");
                return;
            }

            // Access the form datasource for the InMemory temporary table
            FormDataSource ds = element.dataSource("TmpSalesInvoiceSumJBBTEST");

            // Clear any existing data from the datasource (grid)
            ds.executeQuery(); // Refresh buffer
            ds.delete();       // Delete all existing records from the view

            ttsbegin;

            // Select invoice lines between selected dates
            while select ItemId, LineAmount, InvoiceDate from invoiceTrans
                where invoiceTrans.InvoiceDate >= startDate
                   && invoiceTrans.InvoiceDate <= endDate
            {
                real existingSum = itemMap.exists(invoiceTrans.ItemId)
                                   ? itemMap.lookup(invoiceTrans.ItemId)
                                   : 0;

                itemMap.insert(invoiceTrans.ItemId, existingSum + invoiceTrans.LineAmount);
            }

            // Insert aggregated data into the datasource (so it will appear in the grid)
            MapEnumerator mapEnumerator = itemMap.getEnumerator();
            while (mapEnumerator.moveNext())
            {
                tmpBuf.clear();
                tmpBuf.ItemId = mapEnumerator.currentKey();
                tmpBuf.AmountSum = mapEnumerator.currentValue();

                ds.cursor().clear();
                ds.cursor().data(tmpBuf);
                ds.create();

                // Log inserted item for debugging
                info(strFmt("Inserted ItemId: %1, AmountSum: %2", tmpBuf.ItemId, tmpBuf.AmountSum));
            }

            ttscommit;

            // Refresh the grid to display the new data
            ds.executeQuery();
        }

    }

}

Thanks in advance for your help!

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

Martin Dráb 239,040 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!
Your solution is overcomplicated. Instead of writing records to tmpBuf and then trying the data source to the same temporary buffer, simply write records to the buffer created for the data source.

For example:

tmpSalesInvoiceSumJBBTEST.ItemId = 'item1' tmpSalesInvoiceSumJBBTEST.AmountSum = 42; tmpSalesInvoiceSumJBBTEST.insert();

The tmpSalesInvoiceSumJBBTEST is created automatically for the data source.

Notice that you forgot to call insert(), therefore your code didn't actually put any data to the table.

Also, your query is very inefficient. Instead of loading all lines and calculating the amount in the application, you can ask the database to do the calculation for you:

while select sum(LineAmount), ItemId, LineAmount from invoiceTrans group by ItemId where invoiceTrans.InvoiceDate >= startDate && invoiceTrans.InvoiceDate <= endDate

Then you can also get rid of the map and all the related logic.

There is no point calling executeQuery() before you insert the data.

The calls of ds.delete() and ds.create() make no sense in your case; remove them.

Never hard-code application element names as strings, such as dataSource("TmpSalesInvoiceSumJBBTEST"). In this case, use the existing variable tmpSalesInvoiceSumJBBTEST_ds.

Was this reply helpful? Yes No
JeanB 210 on at

Like (1)

Report
Copy link

Link copied!
Hello Martin, thank you a lot for your help, now that works !

Just one more question if you have time, when I click on validate, to display the data, I have to refresh the screen. Is there any option to check or a code line to add to refresh the grid automatically and display the data ?

Here is my method :

[Form] public class TESTJBB11 extends FormRun { FormDateControl startDateCtrl; FormDateControl endDateCtrl; /// <summary> /// Initializes the form and binds the date controls. /// </summary> public void init() { super(); // Link the form controls to class variables using their names from the designer startDateCtrl = element.design().controlName("StartDateControl"); endDateCtrl = element.design().controlName("EndDateControl"); } [Control("Button")] class CalculateButton { /// <summary> /// Calculates the total invoice line amount per item between two selected dates. /// The result is inserted into an InMemory table displayed by the form grid. /// </summary> public void clicked() { super(); TransDate startDate = startDateCtrl.dateValue(); TransDate endDate = endDateCtrl.dateValue(); if (!startDate || !endDate) { error("Please enter both a start and end date."); return; } // Clear existing records from the data source buffer delete_from TmpSalesInvoiceSumJBBTEST; // Use SQL grouping directly in the select statement CustInvoiceTrans invoiceTrans; while select sum(LineAmount), ItemId from invoiceTrans group by ItemId where invoiceTrans.InvoiceDate >= startDate && invoiceTrans.InvoiceDate <= endDate { TmpSalesInvoiceSumJBBTEST.ItemId = invoiceTrans.ItemId; TmpSalesInvoiceSumJBBTEST.AmountSum = invoiceTrans.LineAmount; TmpSalesInvoiceSumJBBTEST.insert(); info(strFmt("Inserted ItemId: %1, AmountSum: %2", TmpSalesInvoiceSumJBBTEST.ItemId, TmpSalesInvoiceSumJBBTEST.AmountSum)); } // No need to call executeQuery() — the table will refresh automatically } } }

Was this reply helpful? Yes No
Martin Dráb 239,040 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!

You'll need to call executeQuery() after inserting the data.

Throw away init() and the FormDateControl variables. Set the Auto Declaration property of StartDateControl and EndDateControl to Yes and then refer to them simply by name, e.g. StartDateControl.dateValue(). But the way, you again made the mistake of hard-coding the control names as strings. You'd lose the compile-time check of the name, cross-references and so on.

Was this reply helpful? Yes No
JeanB 210 on at

Like (0)

Report
Copy link

Link copied!

Hello, thank you a lot Martin ! I wish you a good week !

Was this reply helpful? Yes No