Query::insert_recordset() giving an error about inserting multiple records in custom table

(0) Share

Report

Posted on by RayBizee

I am new to the R3 method Query::insert_recordset so I researched how this is used but alas, my understanding is unfruitful thus far.

I have two custom tables. One called AltLoadDetailsData (holds custom data about load information). AltLoadDetailsDataFiltered is simply a duplicated version of the other table that will simply act as a temporary location to store a filtered or subset of the AltLoadDetailsData table.

I have a simple form that has options to filter the original table data using a few options. There are checkboxes that indicate which filters to use and if checked, it adds a range to the query that uses the populated value from the form.

Below the optional filter criteria checkboxes, I have a simple OK command button that will populate the filtered table and then refresh the grid below that using the new query. It thought this was a wonderful opportunity to use the new feature in R3 to bulk insert into a table based on a query and a map. However, I have tried each example I found with no luck. I either get the error that there is a mismatch on fields I am mapping or the error stating that I cannot insert multiple records. I debug and the query object and the qdbs object show the correct query but when I execute the actual Query::insert_recordset() it goes straight to the exception without giving me a clue as to what is wrong.

Below is the code that is in my clicked() event on the OK command button on the form:

void clicked()
{
    Query                       query;
    QueryRun                    queryRun;
    QueryBuildDataSource        qbds;
    QueryBuildRange             qbr;
    QueryBuildFieldList         qbdsFldList;
    Map                         targetToSourceMap = new Map(Types::String, Types::Container);
    ALTLoadDetailsDataFiltered  altLoadFiltered;
    int                         dataSourceUniqueId;

    // Clear the temp table to be used for the grid.
    delete_from altLoadFiltered;

    query =  new Query();

    qbds = query.addDataSource(tableNum(ALTLoadDetailsData));

    if ( Warehouse.checked() )
    {
        qbr = qbds.addRange( fieldNum(ALTLoadDetailsData,Warehouse));
        qbr.value(SysQuery::value(FilterOptions_Warehouse.valueStr()));
    }

    if ( LoadStatus.checked() )
    {
        qbr = qbds.addRange( fieldNum(ALTLoadDetailsData,LoadStatus));
        qbr.value(SysQuery::value(FilterOptions_LoadStatus.valueStr()));
    }

    if ( Direction.checked() )
    {
        qbr = qbds.addRange( fieldNum(ALTLoadDetailsData,Direction));
        qbr.value(SysQuery::value(FilterOptions_Direction.valueStr()));
    }

    if ( Site.checked() )
    {
        qbr = qbds.addRange(fieldNum(ALTLoadDetailsData,Site));
        qbr.value(SysQuery::value(FilterOptions_Site.valueStr()));
    }

    //I was using this variable for all entries in the mapping below but I've seen examples both ways.
    //  I'm not sure which one is accurate to use.
    //dataSourceUniqueId = qbds.uniqueId();
    
    qbds.fields().clearFieldList();
    qbdsFldList = qbds.fields();
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, AccountNumber));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, BillingGroupId));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, CarrierService));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, CustomerFreightCharge));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, DelayedShipmentDays));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, DeliveryTerms));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, Direction));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, FreightBill));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, FreightBillInvoice));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, FreightRevenue));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, LoadId));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, LoadShippedConfirmationDateTime));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, LoadStatus));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, LoadTemplateId));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, LoadWeight));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, Mode));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, OrderNumber));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, ProductWeightTBD));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, ProNumber));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, Route));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, ScheduledLoadShippingDateTime));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, ShipFromPostalCode));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, ShipmentId));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, ShippingCarrier));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, ShipToPostalCode));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, Site));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, Warehouse));
    
    qbdsFldList.dynamic(QueryFieldListDynamic::No);

    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, AccountNumber),                   [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, AccountNumber)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, BillingGroupId),                  [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, BillingGroupId)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, CarrierService),                  [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, CarrierService)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, CustomerFreightCharge),           [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, CustomerFreightCharge)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, DelayedShipmentDays),             [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, DelayedShipmentDays)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, DeliveryTerms),                   [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, DeliveryTerms)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, Direction),                       [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, Direction)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, FreightBill),                     [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, FreightBill)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, FreightBillInvoice),              [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, FreightBillInvoice)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, FreightRevenue),                  [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, FreightRevenue)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, LoadId),                          [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, LoadId)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, LoadShippedConfirmationDateTime), [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, LoadShippedConfirmationDateTime)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, LoadStatus),                      [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, LoadStatus)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, LoadTemplateId),                  [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, LoadTemplateId)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, LoadWeight),                      [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, LoadWeight)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, Mode),                            [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, Mode)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, OrderNumber),                     [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, OrderNumber)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, ProductWeightTBD),                [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, ProductWeightTBD)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, ProNumber),                       [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, ProNumber)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, Route),                           [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, Route)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, ScheduledLoadShippingDateTime),   [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, ScheduledLoadShippingDateTime)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, ShipFromPostalCode),              [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, ShipFromPostalCode)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, ShipmentId),                      [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, ShipmentId)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, ShippingCarrier),                 [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, ShippingCarrier)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, ShipToPostalCode),                [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, ShipToPostalCode)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, Site),                            [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, Site)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, Warehouse),                       [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, Warehouse)]);

    ttsBegin;

        Query::insert_recordset(altLoadFiltered, targetToSourceMap, query);

    ttsCommit;

    //Refresh the form data source for the grid
    ALTLoadDetailsDataFiltered_ds.research();
    ALTLoadDetailsDataFiltered_ds.refresh();
}

Here is a picture of the form I created.

Below is the actual error that is currently occurring.

I have completed a full SQL database synchronization, a full CIL and have compiled all related code to this.

I cannot see why the code is trying to insert Warehouse in the first field instead of AccountNumber to begin with.

If someone can point out what I am doing wrong, I will be grateful. I'm hoping it's something simple I've been missing.

Thanks,

Ray

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (0)

Mea_ 60,284 on at

Like (0)

Report

Hi Ray Breakall,

Did you try to remove next code to see if it will resolve your issue

if ( Warehouse.checked() )

{

qbr = qbds.addRange( fieldNum(ALTLoadDetailsData,Warehouse));

qbr.value(SysQuery::value(FilterOptions_Warehouse.valueStr()));

}

Was this reply helpful? Yes No
RayBizee 36 on at

Like (0)

Report

Yes, I have tried without. Actually, I am running the code without checking any boxes as well and when I debug, it bypasses that code.

Was this reply helpful? Yes No
RayBizee 36 on at

Like (0)

Report

I set the code back to inserting each line of the query results into the temp table and that works without issue but it's just not as efficient as this new method. If I can get any help, I'd like to fix this. Thanks everyone for your time!

Was this reply helpful? Yes No
Douglas Noel 3,905 on at

Like (0)

Report

Hi Ray,

one question: will the result be the same

if you replace everything starting from line

--------

qbds.fields.clearFieldlist()

up to line

qbdsfldlist.dynamic(queryfieldlistdynamic::no)

--------

with a block only containing

qbds.addSelectionField(.......)

instead of the qbsFldList.addField(....)

commands?

regards Douglas

Was this reply helpful? Yes No
RayBizee 36 on at

Like (0)

Report

I will try this tomorrow and see. I believe I did this in one of the iterations of trial and error but can't be 100% sure. I'll let you know if that helps. I was thinking the selection fields were used if you had an aggregate but it could be a strangely required combination of those two.

Was this reply helpful? Yes No

RayBizee

36 on at

Like (0)

Report

Douglas, I tried what you said and I got the same error about inserting Warehouse more than once. Below is the updated code (to make sure I have what you asked me to do) and the error.

void clicked()
{
    Query                       query;
    QueryRun                    queryRun;
    QueryBuildDataSource        qbds;
    QueryBuildRange             qbr;
    QueryBuildFieldList         qbdsFldList;
    Map                         targetToSourceMap = new Map(Types::String, Types::Container);
    ALTLoadDetailsData          altLoadData;
    ALTLoadDetailsDataFiltered  altLoadFiltered;
    int                         dataSourceUniqueId;

    // Clear the temp table to be used for the grid.
    delete_from altLoadFiltered;

    query =  new Query();

    qbds = query.addDataSource(tableNum(ALTLoadDetailsData));

    if ( Warehouse.checked() )
    {
        qbr = qbds.addRange( fieldNum(ALTLoadDetailsData,Warehouse));
        qbr.value(SysQuery::value(FilterOptions_Warehouse.valueStr()));
    }

    if ( LoadStatus.checked() )
    {
        qbr = qbds.addRange( fieldNum(ALTLoadDetailsData,LoadStatus));
        qbr.value(SysQuery::value(FilterOptions_LoadStatus.valueStr()));
    }

    if ( Direction.checked() )
    {
        qbr = qbds.addRange( fieldNum(ALTLoadDetailsData,Direction));
        qbr.value(SysQuery::value(FilterOptions_Direction.valueStr()));
    }

    if ( Site.checked() )
    {
        qbr = qbds.addRange(fieldNum(ALTLoadDetailsData,Site));
        qbr.value(SysQuery::value(FilterOptions_Site.valueStr()));
    }

    if ( DateRange.checked() )
    {
        qbr = qbds.addRange(fieldNum(ALTLoadDetailsData, loadShippedConfirmationDateTime));
        qbr.value(SysQuery::range(DateTimeUtil::newDateTime(FromDate.DateValue(),0), DateTimeUtil::newDateTime(ToDate.DateValue(),0)));
    }
    
    if ( DeliveryTerms.checked() )
    {
        qbr = qbds.addRange(fieldNum(ALTLoadDetailsData,DeliveryTerms));
        qbr.value(SysQuery::value(FilterOptions_DeliveryTerms.valueStr()));
    }
    
    //dataSourceUniqueId = qbds.uniqueId();
    /*
    qbds.fields().clearFieldList();
    qbdsFldList = qbds.fields();
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, AccountNumber));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, BillingGroupId));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, CarrierService));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, CustomerFreightCharge));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, DelayedShipmentDays));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, DeliveryTerms));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, Direction));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, FreightBill));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, FreightBillInvoice));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, FreightRevenue));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, LoadId));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, LoadShippedConfirmationDateTime));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, LoadStatus));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, LoadTemplateId));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, LoadWeight));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, Mode));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, OrderNumber));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, ProductWeightTBD));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, ProNumber));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, Route));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, ScheduledLoadShippingDateTime));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, ShipFromPostalCode));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, ShipmentId));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, ShippingCarrier));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, ShipToPostalCode));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, Site));
    qbdsFldList.addField(fieldNum(ALTLoadDetailsData, Warehouse));

    qbdsFldList.dynamic(QueryFieldListDynamic::No);
    */

    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, AccountNumber));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, BillingGroupId));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, CarrierService));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, CustomerFreightCharge));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, DelayedShipmentDays));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, DeliveryTerms));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, Direction));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, FreightBill));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, FreightBillInvoice));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, FreightRevenue));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, LoadId));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, LoadShippedConfirmationDateTime));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, LoadStatus));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, LoadTemplateId));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, LoadWeight));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, Mode));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, OrderNumber));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, ProductWeightTBD));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, ProNumber));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, Route));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, ScheduledLoadShippingDateTime));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, ShipFromPostalCode));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, ShipmentId));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, ShippingCarrier));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, ShipToPostalCode));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, Site));
    qbds.addSelectionField(fieldNum(ALTLoadDetailsData, Warehouse));

    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, AccountNumber),                   [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, AccountNumber)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, BillingGroupId),                  [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, BillingGroupId)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, CarrierService),                  [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, CarrierService)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, CustomerFreightCharge),           [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, CustomerFreightCharge)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, DelayedShipmentDays),             [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, DelayedShipmentDays)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, DeliveryTerms),                   [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, DeliveryTerms)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, Direction),                       [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, Direction)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, FreightBill),                     [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, FreightBill)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, FreightBillInvoice),              [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, FreightBillInvoice)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, FreightRevenue),                  [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, FreightRevenue)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, LoadId),                          [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, LoadId)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, LoadShippedConfirmationDateTime), [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, LoadShippedConfirmationDateTime)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, LoadStatus),                      [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, LoadStatus)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, LoadTemplateId),                  [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, LoadTemplateId)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, LoadWeight),                      [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, LoadWeight)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, Mode),                            [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, Mode)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, OrderNumber),                     [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, OrderNumber)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, ProductWeightTBD),                [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, ProductWeightTBD)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, ProNumber),                       [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, ProNumber)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, Route),                           [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, Route)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, ScheduledLoadShippingDateTime),   [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, ScheduledLoadShippingDateTime)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, ShipFromPostalCode),              [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, ShipFromPostalCode)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, ShipmentId),                      [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, ShipmentId)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, ShippingCarrier),                 [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, ShippingCarrier)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, ShipToPostalCode),                [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, ShipToPostalCode)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, Site),                            [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, Site)]);
    targetToSourceMap.insert(fieldStr(ALTLoadDetailsDataFiltered, Warehouse),                       [qbds.uniqueId(), fieldStr(ALTLoadDetailsData, Warehouse)]);

    ttsBegin;

        Query::insert_recordset(altLoadFiltered, targetToSourceMap, query);

    ttsCommit;

    
    /*
    // Run Query
    queryRun = new QueryRun(query);

    // Retrieves the next record from the query.
    while(queryRun.next())
    {
        altLoadData = queryRun.get(tableNum(ALTLoadDetailsData));
        altLoadFiltered.AccountNumber = altLoadData.AccountNumber;
        altLoadFiltered.BillingGroupId = altLoadData.BillingGroupId;
        altLoadFiltered.CarrierService = altLoadData.CarrierService;
        altLoadFiltered.CustomerFreightCharge = altLoadData.CustomerFreightCharge;
        altLoadFiltered.DelayedShipmentDays = altLoadData.DelayedShipmentDays;
        altLoadFiltered.DeliveryTerms = altLoadData.DeliveryTerms;
        altLoadFiltered.Direction = altLoadData.Direction;
        altLoadFiltered.FreightBill = altLoadData.FreightBill;
        altLoadFiltered.FreightBillInvoice = altLoadData.FreightBillInvoice;
        altLoadFiltered.FreightRevenue = altLoadData.FreightRevenue;
        altLoadFiltered.LoadId = altLoadData.LoadId;
        altLoadFiltered.LoadShippedConfirmationDateTime = altLoadData.LoadShippedConfirmationDateTime;
        altLoadFiltered.LoadStatus = altLoadData.LoadStatus;
        altLoadFiltered.LoadTemplateId = altLoadData.LoadTemplateId;
        altLoadFiltered.LoadWeight = altLoadData.LoadWeight;
        altLoadFiltered.Mode = altLoadData.Mode;
        altLoadFiltered.OrderNumber = altLoadData.OrderNumber;
        altLoadFiltered.ProductWeightTBD = altLoadData.ProductWeightTBD;
        altLoadFiltered.ProNumber = altLoadData.ProNumber;
        altLoadFiltered.Route = altLoadData.Route;
        altLoadFiltered.ScheduledLoadShippingDateTime = altLoadData.ScheduledLoadShippingDateTime;
        altLoadFiltered.ShipFromPostalCode = altLoadData.ShipFromPostalCode;
        altLoadFiltered.ShipmentId = altLoadData.ShipmentId;
        altLoadFiltered.ShippingCarrier = altLoadData.ShippingCarrier;
        altLoadFiltered.ShipToPostalCode = altLoadData.ShipToPostalCode;
        altLoadFiltered.Site = altLoadData.Site;
        altLoadFiltered.Warehouse = altLoadData.Warehouse;

        altLoadFiltered.insert();

        // Show AccountNum
        //info("Account Number: " + altLoadFiltered.AccountNumber);
    }
    */

    ALTLoadDetailsDataFiltered_ds.research();
    ALTLoadDetailsDataFiltered_ds.refresh();

}

Was this reply helpful? Yes No

Suggested answer

Marco iembo 105 on at

Like (0)

Report

Hi Ray,

add this line query.clearAllFields() below query = new Query();

It seems that qbds.fields().clearFieldList(); doesn't work well.

Let me know if this solve the problem

Kind Regard

Was this reply helpful? Yes No
Douglas Noel 3,905 on at

Like (0)

Report

Hi Ray,

yes, that's what I've meant. But I already expected the same result, should be generate the same sql command, but it was worth a shot to nothing.

But very interesting:

look at the following thread:

community.dynamics.com/.../161238

jerzy 8. Mar 2017 second problem

seems to be exactly the same problem what jerzy said. Duplicate field first/last, but only not in datasource init. One question: Did you already check LCS for some possible kind of kernel fixes, or tried a newer aos kernel?

regards Douglas

Was this reply helpful? Yes No
RayBizee 36 on at

Like (0)

Report

Marco, I tried clearing the fields using the query version and that didn't do anything different. The crazy part of this is that I don't have any joins, no group by, no aggregations or anything to disrupt.

Douglas, I don't have the very latest kernel but I have 6.3.6000.2026. The latest one is 6.3.6000.4444. I may try that as a last ditch effort. It would be nice if Microsoft offered some sound examples so we can put this more efficient code to good use.

I appreciate everyone's efforts to help resolve this.

Was this reply helpful? Yes No
RayBizee 36 on at

Like (0)

Report

I just upgraded to the latest kernel and that also did nothing to help. I cannot find any mention of this on LCS either. Is anyone from Microsoft out there to answer this?

Was this reply helpful? Yes No