Create a temp table on field modified

Hello,

I have a two datasource form in which one datasource is a platform to trigger the other datasource that will be temp. The form is used for the company’s unique pricing of finished goods. I have a selection of 3 fields - Quote number, Customer account and Item number. These could be customer or quote specific pricing for our products. The first two must be selected before the item is selected. When that item is selected I want the price breaks to be populated into the temp table/datasource and displayed on the form. The price breaks Tmp table will be checking if a pricing exists already for that combination, if not it checks to see if it exists for just the item, if not then it creates a blank record for it. These are checked through history which is written at the time of close after a calculation has been completed.

When I select my item it doesn’t seem to run my insertRecords method that is used to create my table for the breaks. The method is passed a bunch of different values and it will fill out the temp table based on those values. I am also calling the linkPhysicalTableInstance method on the data source that has table type TmpDB.

Question:

Does each datasource need to be temp if multiple users are going to be accessing this and creating multiple prices for multiple items?

If you used a single table shared by all users, you would have to distinguish the data either by user ID, or more likely by session ID (because a single user can have multiple concurrent sessions). It would also requires data cleanup procedures (abandoned sessions). It seems to me that what you need is really temporary data and you would just makes a lot of problems for yourself if you used a persistent table.

I can’t comment on why your method isn’t called, because I know nothing about your code.

By the way, how many records do you expect? Are you sure that TempDB is the right choice?

If I remember it correctly (I’m not sure…), you can’t successfully use linkPhysicalTableInstance() after running the form. But it’s not a problem - you can simply write records to the cureent form buffer.

Thank you Martin,

The amount of records is dynamic based on how many Pending Costing Versions are set for that item.

The logic is…

Select Quote # (if needed, most of the time will be empty), Customer Account (if customer specific pricing is being calculated), and finally item number.

When Item number is selected find all costing versions set for that item. This is from the InventItemPriceSim table.

Say there are costs for a 1 MFT, 2.5 MFT, and 5 MFT costing version calculated on item price tab of the released product. (We are a wire company).

It will create a new record for each of those “breaks”, similar to how it looks below.

pastedimage1572979049135v1.png

After these are created they will fill in the rest of the grid and hit a calculate button that totals each record and saves it into a history table. This History table is then used to access the breaks to be put into Sales quotations. This works as it should but we cannot get to this point because of the rework of the record creation logic so that multiple users can use this price tool at the same time.

I can provide code if needed.

You say that linkPhysicalTableInstance() may not be needed for this because it is supposed to be used in the init() of the form? Should I just use an event handler on itemid_onModified() and insert records into the TempDB table like you would a normal table (table.insert())?

Yes, I know it will be dynamic. My point is that creating many tiny TempDB table isn’t ideal; InMemory table is likely a more appropriate option in your case.

Yes, data is inserted to temporary tables in the same way as to normal tables, e.g. by insert(). If you didn’t do that, no wonder you didn’t see any data.

Try inserting a record in this simple way:

myDatasource.ItemNumber = 'Item1';
myDatasource.CostingVersion = '1000 ft';
myDatasource.Cost = 560.98;
myDatasource.insert();

You don’t need anything like linkPhysicalTableInstance() if you work directly with the buffer you already have in the form.

I was able to fill in the grid before trying to make it generate through TempDB, InMemory didnt work either. Maybe there is a more effective way of allowing multiple users to use the same form. Could I just add a new field to the Header datasource and to the line datasource for session id where it is a guid?

I’m sorry, but I can’t help you with your problem unless you give me more information. That you tried something and it didn’t work might mean anything.

Using a persistent table is definitely much more work and risk, therefore you should fix your current problem rather than replacing it with several more complicated ones.

Okay. So I have a class called MJMPriceToolEH which holds all the event handlers on that form. The form contains two data sources MJMPriceTool and MJMPriceToolPriceBreak. These data sources had been generating, calculating, and storing properly before changing MJMPriceToolPriceBreak into a Temp table. However, ran into an issue where only one person could use it at a time.

Do I need to make both datasources temp tables or inMemory?

Anyway. The class MJMPriceToolEH contains the method I use to insert records and return the MJMPriceToolPriceBreak table. Because our requirement says it can be generated different ways I pass in a few variables to help get the correct info.

    public static MJMPriceToolPriceBreak insertRecords(Common _pricing, 
        InventItemPriceSim _inventItemPriceSim, 
        MJMPriceTool _priceTool, 
        CostingVersion _costVersion,
        boolean _historyPricingExist = false,
        boolean _standardPricingExist = false)
    {
        MJMPriceToolHistory history;
        InventItemPriceSim inventItemPriceSim = _inventItemPriceSim;
        MJMPriceTool priceTool = _priceTool;
        CostingVersion version = _costVersion;
        boolean priceExistInHistory = _historyPricingExist;
        boolean standardExistInHistory = _standardPricingExist;
        MJMPriceToolPriceBreak priceToolPriceBreaks;
        InventTable inventTable;
        UnitOfMeasure uom;
        UnitOfMeasureConversion uomConvert;
        MJMCommissionPercentages commissions;
        MJMClassCode classCode;
        MJMProfitTable profits;
        MJMPrimaryIndustry primaryIndustry;

        select * from inventTable
            where inventTable.ItemId == priceTool.ItemNum;
        select * from uom
            where inventTable.BOMUnitId == uom.Symbol;
        select * from uomConvert
            where uomConvert.FromUnitOfMeasure == uom.RecId;
        select * from classCode
            where classCode.Code == inventTable.MJMClassCode;
        select * from profits
            where profits.ClassCode == classCode.Code
            && profits.CostingVersion == priceToolPriceBreaks.CostingVersion;
        select * from commissions
            where commissions.Code == inventTable.MJMCode;

        UnitOfMeasureConverter_Product lbQtyConverter = UnitOfMeasureConverter_Product::construct();
        lbQtyConverter.parmProduct(inventTable::find(priceTool.ItemNum).Product);
        lbQtyConverter.parmFromUnitOfMeasure(uom.RecId);
        lbQtyConverter.parmToUnitOfMeasure(UnitOfMeasure::unitOfMeasureIdBySymbol("lb"));
        lbQtyConverter.parmApplyRounding(NoYes::No);
        lbQtyConverter.parmRoundAbsoluteValue(NoYes::Yes);

        UnitOfMeasureConverter_Product kgQtyConverter = UnitOfMeasureConverter_Product::construct();
        kgQtyConverter.parmProduct(inventTable::find(priceTool.ItemNum).Product);
        kgQtyConverter.parmFromUnitOfMeasure(uom.RecId);
        kgQtyConverter.parmToUnitOfMeasure(UnitOfMeasure::unitOfMeasureIdBySymbol("kg"));
        kgQtyConverter.parmApplyRounding(NoYes::No);
        kgQtyConverter.parmRoundAbsoluteValue(NoYes::Yes);

        int bool;
        if(priceExistInHistory == true && standardExistInHistory == true)
        {
            bool = 1;
        }else if(priceExistInHistory == false && standardExistInHistory == true)
        {
            bool = 2;
        }else
        {
            bool = 3;
        }
                
        switch(bool)
        {
            //Price Exists in History with combination of Customer, Quote and Item in Price Tool Header.
            case 1:
                history = _pricing as MJMPriceToolHistory;

                priceToolPriceBreaks.Adjuster = history.Adjuster;
                priceToolPriceBreaks.ApprovalDate = history.ApprovalDate;
                priceToolPriceBreaks.ApprovedBy = history.ApprovedBy;
                priceToolPriceBreaks.CostingVersion = history.CostingVersion;
                priceToolPriceBreaks.DrawingSetup = history.DrawingSetup;
                priceToolPriceBreaks.DrawLock = history.DrawLock;
                priceToolPriceBreaks.ExtLock = history.ExtLock;
                priceToolPriceBreaks.Extruded = history.Extruded;
                priceToolPriceBreaks.ExcessMaterial = history.ExcessMaterial;
                priceToolPriceBreaks.ULCSA = history.ULCSA;
                priceToolPriceBreaks.ULCSALock = history.ULCSALock;
                priceToolPriceBreaks.Toll = history.Toll;
                priceToolPriceBreaks.TollLock = history.TollLock;
                priceToolPriceBreaks.Misc = history.Misc;
                priceToolPriceBreaks.MiscLock = history.MiscLock;
                priceToolPriceBreaks.SubPercent = history.SubPercent;
                priceToolPriceBreaks.SubAdjust = history.SubAdjust;
                priceToolPriceBreaks.TargetPrice = history.TargetPrice;
                priceToolPriceBreaks.Notes = history.Notes;
                
                //Fields from ClassCode, ProfitTable and SGATable
                //And written to PriceBreak
                select * from classCode
                    where classCode.Code == inventTable.MJMClassCode;
                priceToolPriceBreaks.SGAPercent = classCode.SGAPercentage;
                priceToolPriceBreaks.SGAOverride = history.SGAOverride;
                
                select * from primaryIndustry
                        where inventTable.MJMPrimaryIndustry == primaryIndustry.Code;

                select * from profits
                        where profits.ClassCode == classCode.Code
                        && version.VersionId == profits.CostingVersion
                        && profits.PrimaryIndustry == primaryIndustry.Description;
                if(!profits)
                {
                    select * from profits
                        where profits.ClassCode == classCode.Code
                        && profits.CostingVersion == version.versionId
                        && profits.PrimaryIndustry == "";
                    priceToolPriceBreaks.Profit = profits.ProfitPercentage;
                }
                else
                {
                    priceToolPriceBreaks.Profit = profits.ProfitPercentage;
                }
                priceToolPriceBreaks.ProfitOverride = history.ProfitOverride;

                select * from commissions
                    where inventTable.MJMCode == commissions.Code;
                priceToolPriceBreaks.Commission = commissions.PriceToolFactor;
                priceToolPriceBreaks.CommissionOverride = history.CommissionOverride;
                priceToolPriceBreaks.SlowPay = history.SlowPay;
                priceToolPriceBreaks.IsSlowPay = history.IsSlowPay;
                priceToolPriceBreaks.IsInternational = history.IsInternational;
                priceToolPriceBreaks.International = history.International;
                priceToolPriceBreaks.QtyMft = history.Qty;
                priceToolPriceBreaks.QtyMtr = history.QtyMtr;
                priceToolPriceBreaks.QtyLb = history.QtyLb;
                priceToolPriceBreaks.QtyKg = history.QtyKg;
                priceToolPriceBreaks.QtyEa = history.QtyEa;
                priceToolPriceBreaks.CostPerUOM = inventItemPriceSim.Price + (inventItemPriceSim.Markup/inventItemPriceSim.PriceQty);

                //priceToolPriceBreaks.insert();
                break;
            //Price Exists in History with only Item pricing.
            case 2:
                history = _pricing as MJMPriceToolHistory;

                priceToolPriceBreaks.CustomerAccount = priceTool.CustomerAccount;
                priceToolPriceBreaks.QuoteNum = priceTool.QuoteNum;
                priceToolPriceBreaks.Adjuster = history.Adjuster;
                priceToolPriceBreaks.ApprovalDate = history.ApprovalDate;
                priceToolPriceBreaks.ApprovedBy = history.ApprovedBy;
                priceToolPriceBreaks.CostingVersion = history.CostingVersion;
                priceToolPriceBreaks.DrawingSetup = history.DrawingSetup;
                priceToolPriceBreaks.DrawLock = history.DrawLock;
                priceToolPriceBreaks.ExtLock = history.ExtLock;
                priceToolPriceBreaks.Extruded = history.Extruded;
                priceToolPriceBreaks.ExcessMaterial = history.ExcessMaterial;
                priceToolPriceBreaks.ULCSA = history.ULCSA;
                priceToolPriceBreaks.ULCSALock = history.ULCSALock;
                priceToolPriceBreaks.Toll = history.Toll;
                priceToolPriceBreaks.TollLock = history.TollLock;
                priceToolPriceBreaks.Misc = history.Misc;
                priceToolPriceBreaks.MiscLock = history.MiscLock;
                priceToolPriceBreaks.SubPercent = history.SubPercent;
                priceToolPriceBreaks.SubAdjust = history.SubAdjust;
                priceToolPriceBreaks.TargetPrice = history.TargetPrice;
                priceToolPriceBreaks.Notes = history.Notes;

                //Fields from ClassCode, ProfitTable and SGATable
                //And written to PriceBreak
                select * from classCode
                    where classCode.Code == inventTable.MJMClassCode;
                priceToolPriceBreaks.SGAPercent = classCode.SGAPercentage;
                priceToolPriceBreaks.SGAOverride = history.SGAOverride;
                
                select * from primaryIndustry
                        where inventTable.MJMPrimaryIndustry == primaryIndustry.Code;

                select * from profits
                        where profits.ClassCode == classCode.Code
                        && version.VersionId == profits.CostingVersion
                        && profits.PrimaryIndustry == primaryIndustry.Description;
                if(!profits)
                {
                    select * from profits
                        where profits.ClassCode == classCode.Code
                        && profits.CostingVersion == version.versionId
                        && profits.PrimaryIndustry == "";
                    priceToolPriceBreaks.Profit = profits.ProfitPercentage;
                }
                else
                {
                    priceToolPriceBreaks.Profit = profits.ProfitPercentage;
                }
                priceToolPriceBreaks.ProfitOverride = history.ProfitOverride;

                select * from commissions
                    where inventTable.MJMCode == commissions.Code;
                priceToolPriceBreaks.Commission = commissions.PriceToolFactor;
                priceToolPriceBreaks.CommissionOverride = history.CommissionOverride;
                priceToolPriceBreaks.SlowPay = history.SlowPay;
                priceToolPriceBreaks.IsSlowPay = history.IsSlowPay;
                priceToolPriceBreaks.IsInternational = history.IsInternational;
                priceToolPriceBreaks.International = history.International;
                priceToolPriceBreaks.QtyMft = history.Qty;
                priceToolPriceBreaks.QtyMtr = history.QtyMtr;
                priceToolPriceBreaks.QtyLb = history.QtyLb;
                priceToolPriceBreaks.QtyKg = history.QtyKg;
                priceToolPriceBreaks.QtyEa = history.QtyEa;
                priceToolPriceBreaks.CostPerUOM = inventItemPriceSim.Price + (inventItemPriceSim.Markup/inventItemPriceSim.PriceQty);
                //priceToolPriceBreaks.insert();
                break;
            //Price Does not exist in History. Create new price breaks.
            case 3:

                priceToolPriceBreaks.CustomerAccount = priceTool.CustomerAccount;
                priceToolPriceBreaks.QuoteNum = priceTool.QuoteNum;
                priceToolPriceBreaks.CostingVersion = inventItemPriceSim.VersionId;

                //Fields from ClassCode, ProfitTable and SGATable
                //And written to PriceBreak
                select * from classCode
                    where classCode.Code == inventTable.MJMClassCode;
                priceToolPriceBreaks.SGAPercent = classCode.SGAPercentage;
                
                select * from primaryIndustry
                        where inventTable.MJMPrimaryIndustry == primaryIndustry.Code;

                select * from profits
                        where profits.ClassCode == classCode.Code
                        && version.VersionId == profits.CostingVersion
                        && profits.PrimaryIndustry == primaryIndustry.Description;
                if(!profits)
                {
                    select * from profits
                        where profits.ClassCode == classCode.Code
                        && profits.CostingVersion == version.versionId
                        && profits.PrimaryIndustry == "";
                    priceToolPriceBreaks.Profit = profits.ProfitPercentage;
                }
                else
                {
                    priceToolPriceBreaks.Profit = profits.ProfitPercentage;
                }

                select * from commissions
                    where inventTable.MJMCode == commissions.Code;
                priceToolPriceBreaks.Commission = commissions.PriceToolFactor;
                priceToolPriceBreaks.QtyMft = version.MJMChargesQty;
                priceToolPriceBreaks.QtyMtr = version.MJMChargesQty * 304.8;
                priceToolPriceBreaks.QtyLb = lbQtyConverter.convertValue(version.MJMChargesQty);
                priceToolPriceBreaks.QtyKg = kgQtyConverter.convertValue(version.MJMChargesQty);
                priceToolPriceBreaks.QtyEa = version.MJMChargesQty;
                priceToolPriceBreaks.CostPerUOM = inventItemPriceSim.Price + (inventItemPriceSim.Markup/inventItemPriceSim.PriceQty);
                //priceToolPriceBreaks.insert();
                break;
        }

        return priceToolPriceBreaks;
    }

I call this insertRecords method in the event of the itemId field changing. I set the event version of MJMPriceToolPriceBreak called priceBreak equal to the result of insertRecords then insert priceBreak.

while select * from inventItemPriceSim1
    where inventItemPriceSim1.ItemId == itemNum
    && inventItemPriceSim1.PriceType == CostingVersionPriceType::Cost
    join costingVersion
        where costingVersion.VersionId == inventItemPriceSim1.VersionId
        && costingVersion.CostingType == CostingVersionCostingType::Normal
{
    MJMPriceToolHistory pricing;
    select * from pricing
        where pricing.ItemId == itemNum
        && priceTool.CustomerAccount == pricing.CustomerAccount
        && priceTool.QuoteNum == pricing.QuoteNum
        && pricing.CostingVersion == costingVersion.VersionId;
    if(!pricing) //Checks if pricing exists for combination of item customer and quote. "Does not exist clause"
    {
        MJMPriceToolHistory pricingStd;
        select * from pricingStd
            where pricingStd.ItemId == itemNum
            && pricingStd.CustomerAccount == ""
            && pricingStd.QuoteNum == ""
            && pricingStd.CostingVersion == costingVersion.VersionId;
        if(!pricingStd) //Checks if pricing exists for just item but not customer or quote. If no create new records.
        {
            priceBreak = MJMPriceToolEH::insertRecords(pricingStd, inventItemPriceSim1, priceTool, costingVersion, false, false);
            priceBreak.insert();
            i++;
        }else //Pricing does exist in history for item but does not exist for customer specific.
        {
            priceBreak = MJMPriceToolEH::insertRecords(pricingStd, inventItemPriceSim1, priceTool, costingVersion, false, true);
            priceBreak.insert();
            i++;
            if(priceBreak.CommissionOverride)
            {
                FormRealControl commission = design.controlName(formControlStr(MJMPriceTool, MJMPriceToolPriceBreak_Commission));
                commission.allowEdit(true);
            }
            if(priceBreak.ProfitOverride)
            {
                FormRealControl profit = design.controlName(formControlStr(MJMPriceTool, MJMPriceToolPriceBreak_Profit));
                profit.allowEdit(true);
            }
            if(priceBreak.SGAOverride)
            {
                FormRealControl SGA = design.controlName(formControlStr(MJMPriceTool, MJMPriceToolPriceBreak_SGAPercent));
                SGA.allowEdit(true);
            }
        }
    }else //Exists in History using combination of Customer, Item, and quote
    {
        priceBreak = MJMPriceToolEH::insertRecords(pricing, inventItemPriceSim1, priceTool, costingVersion, true, true);
        priceBreak.insert();
        i++;
        if(priceBreak.CommissionOverride)
        {
            FormRealControl commission = design.controlName(formControlStr(MJMPriceTool, MJMPriceToolPriceBreak_Commission));
            commission.allowEdit(true);
        }
        if(priceBreak.ProfitOverride)
        {
            FormRealControl profit = design.controlName(formControlStr(MJMPriceTool, MJMPriceToolPriceBreak_Profit));
            profit.allowEdit(true);
        }
        if(priceBreak.SGAOverride)
       {
            FormRealControl SGA = design.controlName(formControlStr(MJMPriceTool, MJMPriceToolPriceBreak_SGAPercent));
            SGA.allowEdit(true);
        }
    }
}

When I debug through this priceToolPriceBreaks (the return value) in insertRecords is filled in as I expect with real values. priceBreak is storing the same values and it gets inserted with recId 2928. For my specific test I would see it happen 3 times. Each recId is the same. I was expecting 2928, 2929, 2930 as the recId of the inserted record. When viewing the form it generates extra lines with no information that was actually seen to be inserted. When looking in the Database with SQL the inserted record is non existent. I am not researching the datasource as I was before it was InMemory.

A good idea would be simplifying your code for debugging purpose. It would make it easier for everybody, including yourself, to understand the code and spot bugs there.

I’ll try to ignore almost all the code, because most of it doesn’t look relevant to the problem in question.

I see that at line 24, for instance, you’re calling priceBreak = MJMPriceToolEH::insertRecords(…). Whatever the method returns will replace what was in priceBreak variable before, therefore you’ll lose everything except of the very last value. That’s looks like a significant bug.

Then if I look into insertRecords(), I see you’re returning priceToolPriceBreaks buffer, but you never insert any data to it. When the form tries to get data, it’ll find none, because nothing was inserted there. That’s another big problem.

So… one important step is to insert record to the table. The other is inserting records to the same buffer, instead of creating a new temporary buffer for every record (and losing the previous ones). Does it make sense?

I have made the corrections you suggested with the insert in insertRecords(). I have also changed up the logic on the table buffer to not overwrite but append.

In those changes, I can now see the correct information coming through to the tmp table buffer from the insertRecords() into priceBreak but shows the last record that was inserted and a blank record. I expect 3 records there and none should be blank. See images.

pastedimage1573162617796v1.png

I am expecting to see Costing Version 1 (first inserted into the buffer), Costing Version 2.5 (second inserted into the buffer) and the one that shows currently (version 5). Getting closer but am unsure of why it isn’t showing the other two. See code below.

else //Exists in History using combination of Customer, Item, and quote
{
    if(i != 0)
    {
        priceBreak_ds.create();
    }
    priceBreak = priceBreak_ds.cursor();
    priceBreak.setTmpData(MJMPriceToolEH::insertRecords(pricing, inventItemPriceSim1, priceTool, costingVersion, true, true) as MJMPriceToolPriceBreak);
    priceBreak.insert();
    Info(strFmt("PriceBreak %1", priceBreak.ItemId));
    i++;
    if(priceBreak.CommissionOverride)
    {
        FormRealControl commission = design.controlName(formControlStr(MJMPriceTool, MJMPriceToolPriceBreak_Commission));
        commission.allowEdit(true);
    }
    if(priceBreak.ProfitOverride)
    {
        FormRealControl profit = design.controlName(formControlStr(MJMPriceTool, MJMPriceToolPriceBreak_Profit));
        profit.allowEdit(true);
    }
    if(priceBreak.SGAOverride)
    {
        FormRealControl SGA = design.controlName(formControlStr(MJMPriceTool, MJMPriceToolPriceBreak_SGAPercent));
        SGA.allowEdit(true);
    }
}
priceBreak_ds.research();
int bool;
        if(priceExistInHistory == true && standardExistInHistory == true)
        {
            bool = 1;
        }else if(priceExistInHistory == false && standardExistInHistory == true)
        {
            bool = 2;
        }else
        {
            bool = 3;
        }
        ttsbegin;        
        switch(bool)
        {
            //Price Exists in History with combination of Customer, Quote and Item in Price Tool Header.
            case 1:
                history = _pricing as MJMPriceToolHistory;

                priceToolPriceBreaks.ItemId = history.ItemId;
                priceToolPriceBreaks.CustomerAccount = history.CustomerAccount;
                priceToolPriceBreaks.QuoteNum = history.QuoteNum;
                priceToolPriceBreaks.TotalMft = history.TotalMft;
                priceToolPriceBreaks.TotalMtr = history.TotalMtr;
                priceToolPriceBreaks.TotalLb = history.TotalLb;
                priceToolPriceBreaks.TotalKg = history.TotalKg;
                priceToolPriceBreaks.TotalEa = history.TotalEa;
                priceToolPriceBreaks.Adjuster = history.Adjuster;
                priceToolPriceBreaks.ApprovalDate = history.ApprovalDate;
                priceToolPriceBreaks.ApprovedBy = history.ApprovedBy;
                priceToolPriceBreaks.CostingVersion = history.CostingVersion;
                priceToolPriceBreaks.DrawingSetup = history.DrawingSetup;
                priceToolPriceBreaks.DrawLock = history.DrawLock;
                priceToolPriceBreaks.ExtLock = history.ExtLock;
                priceToolPriceBreaks.Extruded = history.Extruded;
                priceToolPriceBreaks.ExcessMaterial = history.ExcessMaterial;
                priceToolPriceBreaks.ULCSA = history.ULCSA;
                priceToolPriceBreaks.ULCSALock = history.ULCSALock;
                priceToolPriceBreaks.Toll = history.Toll;
                priceToolPriceBreaks.TollLock = history.TollLock;
                priceToolPriceBreaks.Misc = history.Misc;
                priceToolPriceBreaks.MiscLock = history.MiscLock;
                priceToolPriceBreaks.SubPercent = history.SubPercent;
                priceToolPriceBreaks.SubAdjust = history.SubAdjust;
                priceToolPriceBreaks.TargetPrice = history.TargetPrice;
                priceToolPriceBreaks.Notes = history.Notes;
                
                //Fields from ClassCode, ProfitTable and SGATable
                //And written to PriceBreak
                select * from classCode
                    where classCode.Code == inventTable.MJMClassCode;
                if(history.SGAOverride == NoYes::Yes)
                {
                    priceToolPriceBreaks.SGAPercent = history.SGAPercent;
                }else
                {
                    priceToolPriceBreaks.SGAPercent = classCode.SGAPercentage;
                }
                priceToolPriceBreaks.SGAOverride = history.SGAOverride;
                
                select * from primaryIndustry
                        where inventTable.MJMPrimaryIndustry == primaryIndustry.Code;
                if(history.ProfitOverride == NoYes::Yes)
                {
                    priceToolPriceBreaks.Profit = history.Profit;
                }else
                {
                    select * from profits
                        where profits.ClassCode == classCode.Code
                        && version.VersionId == profits.CostingVersion
                        && profits.PrimaryIndustry == primaryIndustry.Description;
                    if(!profits)
                    {
                        select * from profits
                            where profits.ClassCode == classCode.Code
                            && profits.CostingVersion == version.versionId
                            && profits.PrimaryIndustry == "";
                        priceToolPriceBreaks.Profit = profits.ProfitPercentage;
                    }
                    else
                    {
                        priceToolPriceBreaks.Profit = profits.ProfitPercentage;
                    }
                }
                priceToolPriceBreaks.ProfitOverride = history.ProfitOverride;

                select * from commissions
                    where inventTable.MJMCode == commissions.Code;
                if(history.CommissionOverride == NoYes::Yes)
                {
                    priceToolPriceBreaks.Commission = history.Commission;
                }else
                {
                    priceToolPriceBreaks.Commission = commissions.PriceToolFactor;
                }
                priceToolPriceBreaks.CommissionOverride = history.CommissionOverride;
                priceToolPriceBreaks.SlowPay = history.SlowPay;
                priceToolPriceBreaks.IsSlowPay = history.IsSlowPay;
                priceToolPriceBreaks.IsInternational = history.IsInternational;
                priceToolPriceBreaks.International = history.International;
                priceToolPriceBreaks.QtyMft = history.Qty;
                priceToolPriceBreaks.QtyMtr = history.QtyMtr;
                priceToolPriceBreaks.QtyLb = history.QtyLb;
                priceToolPriceBreaks.QtyKg = history.QtyKg;
                priceToolPriceBreaks.QtyEa = history.QtyEa;
                priceToolPriceBreaks.CostPerUOM = inventItemPriceSim.Price + (inventItemPriceSim.Markup/inventItemPriceSim.PriceQty);

                priceToolPriceBreaks.insert();
                break;

Why do you call priceBreak_ds.create() and priceBreak.insert() in the first code block? I think this is what creates the empty record.

Oh shoot, that is write now not insert.

You didn’t tell me what are you trying to achieve by that, so I can’t address it.

Simply trust me and remove both create() and write().

I removed the create and write. Should the cursor still be there?

When testing with cursor still there… You are correct that those do not need to be there as it is creating the empty record. However, I am still only getting the last inserted record of cost version 5.

pastedimage1573218967946v1.png

Why would I be losing the first two records inserted into the table?

I don’t you know yet.

If you’re sure that data is in the table (I don’t know how you tested this assumption), then the problem must be in the form. For example, there may be a filter or a join.

But if you merely think that all three records are in the table but you didn’t test it, you should take a look. Otherwise you may be solving a wrong problem.