Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Answered

Caching/refresh issue while inserting data into table

Posted on by

Hi Experts, 

I have the following code which when executed 1st time inserts the data into the table, and 2nd attempt does nothing which is expected as records are already there in the table.

However for 3rd & consecutive attempts on random sequence #(sometime 3rd attempt, 4th , 7th attempt ,etc)  it will insert data into table again . Which is very strange it should always execute like 2nd attempt ,i.e. insert nothing in the table as data is already there.

Please suggest a fix so that code is consistent .

  for(i = 10;i <=conLen(line2);i   )
        {
            charHolder1 = conPeek(line2,i);

            if((charHolder1 == "" && i>= 10 && i != 123))
            {
                j = i-1;
                charholder3   =  conPeek(line2,j);
                combinedValue =  conPeek(line2,j)   " "  conPeek(line3,i);
            }
            else
            {
                combinedValue = conPeek(line2,i)   " "  conPeek(line3,i);
            }


            charholder2 = combinedValue;

            mytable.clear();
            mytable.reread();

            select  mytable where mytable.Category == charholder1 && mytable.SubCategory == charholder2;
            if (mytable.RecId == 0)
            {
               mytable1.clear();
               mytable1.reread();
               select  forUpdate mytable1 where mytable1.Category == charholder3 && mytable1.SubCategory == charholder2;
                     if (mytable1.RecId == 0)   // Condition for controlling inserts
                        {
                                mytable1.Category = charHolder1;
                                if(charholder1 == "" && i != 123)
                                {
                                    mytable1.Category = charHolder3;
                                }

                                mytable1.SubCategory = charholder2;
                                if (i == 10 || i == conLen(line2))
                                {
                                    if (i == 10)
                                        mytable1.Cattype = "Hourly Pay Rate";
                                    else
                                        mytable1.Cattype = "Total Earning Amount";
                                }

                                else if (i >= 11 && i <= 46)
                                {
                                    mytable1.Cattype = "Category1";
                                }
                                else if ( i >=47 && i<= 114)
                                {
                                    mytable1.Cattype = "Category2";
                                }
                                else if (i >= 115 && i < conLen(line2))
                                {
                                    mytable1.Cattype = "Category3";
                                }
                            ttsBegin;
                                mytable1.insert();
                            ttsCommit;
                        }


            }


          }

  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,083 on at
    RE: Caching/refresh issue while inserting data into table

    Hi Mav,

    One more thing how you can improve and simplify your code is to move assignment for category to separate variable before exists check and after you need to check if record exists only once by category stored in variable.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 225,490 Super User on at
    RE: Caching/refresh issue while inserting data into table

    1) I can't suggest a solution, exactly because all I know is the number. Your code doesn't explain the meaning of the value. That's the problem.

    4) No, you must be wrong. If you have a unique index (synchronized to database) and you'll try to insert duplicate values, you'll get an exception. Either your index isn't correct or what you consider unique the same values are actually different values. Don't forget that some characters look similar or even the same and some characters aren't visible at all.

    5) I rather mean using exist() methods like this:

    if (MyTable::exist(charholder1, charholder2)
    {
    	if (MyTable1::exist(charholder3, charholder2)
    	{
    		// do stuff
    	}
    }

    By the way, there is no point in calling clear() on empty buffers.

  • Mav Profile Picture
    Mav on at
    RE: Caching/refresh issue while inserting data into table

    Thanks for quick responses.

    @Martin

    1>Please suggest on how to do this "Also, magic numbers such as 47 should always be avoided."

    2>Does the whole code runs inside a transaction?

    Yes

    3>What's the value of CacheLookup property of the table?

    None

    4>Don't you want to add a unique index with Category and SubCategory fields?

    I want this , please check response for 5> for further details

    5>Try Using exist()?

    wrote this method in my table & then it started working REVISON , just noticed it does not work , i was running the job via batch & after few execution it did inserted rows in the table :-(.

    public boolean existsRecId(str 100  _char1,str 100 _char2, str 100 _char3 )
    {
        Mytable  mytable,mytable1;
        mytable.clear();
        mytable1.clear();
        select firstonly RecId from mytable
          where mytable.Category == _char1 && mytable.SubCategory == _char2;
    
          if (mytable.RecId == 0)
            {
                 select firstonly RecId from mytable1
                    where mytable1.Category == _char3 && mytable1.SubCategory == _char2;
    
                    if(mytable1.recid == 0)
                    {
                        return false;
                    }
                    else
                        return true;
            }
          else
              return true;
    
    }

    Note When trying to add index of not allowing duplicates for subcategory field of myTable along with cachelookup value "none in TTS" , the solution does not work & exhibits the same issue

    i.e. inserts 1st attempt into table, avoid insertion in 2nd attempt & 3rd or consecutive random attempts again inserts into the table.

    @Gunjan & Niklaos

    Yes i have debugged it several time the value of charholder1 & charholder 2 shows up as apple & fruit while debugging.

    For 2nd attemp it finds the apple & fruit  in myTable & skips inserting the record which is expected .

    However for random attempts after 2nd attempt even when debgigger coninue to shows up apple & fruit for charholder1 & charholder2, it for some unknown reason is unable to find a recid in mytable unlike attempt2 & hence initiates the record insertion code block.

  • nmaenpaa Profile Picture
    nmaenpaa 101,148 on at
    RE: Caching/refresh issue while inserting data into table

    Just debug it and you will most likely understand it. For us it's very hard to understand what you are trying to do, or what the code will actually do.

  • Martin Dráb Profile Picture
    Martin Dráb 225,490 Super User on at
    RE: Caching/refresh issue while inserting data into table

    A good practice is using exist() method for such a check. It won't change the behavior, but it'll simplify your code, make clear what's the goal of the piece of code, it prevents duplication (and cost and risk when maintaining code) and if implemented correctly, it also improves performance (you're fetching all fields when you only need RecId). You also don't need to select the record for update, because you never update it.

  • Gunjan Bhattachayya Profile Picture
    Gunjan Bhattachayya 35,417 on at
    RE: Caching/refresh issue while inserting data into table

    Hi Mav,

    the record insertion is going to depend on charHolder1 and charHolder2 values.

    Have you debugged this to check the values being passed?

  • Martin Dráb Profile Picture
    Martin Dráb 225,490 Super User on at
    RE: Caching/refresh issue while inserting data into table

    First of all, let me format your code a bit, so it's easier for us to read. It's still pretty cryptic though - consider using meaningful variable names and splitting your code to several small methods with intent-revealing names. Also, magic numbers such as 47 should always be avoided.

    for (i = 10; i <= conLen(line2); i  )
    {
    	charHolder1 = conPeek(line2,i);
    
    	if (charHolder1 == "" && i >= 10 && i != 123)
    	{
    		j = i-1;
    		charholder3   =  conPeek(line2, j);
    		combinedValue =  conPeek(line2, j)   " "  conPeek(line3,i);
    	}
    	else
    	{
    		combinedValue = conPeek(line2, i)   " "  conPeek(line3,i);
    	}
    
    	charholder2 = combinedValue;
    
    	mytable.clear();
    	mytable.reread();
    
    	select mytable
    		where mytable.Category == charholder1
    		   && mytable.SubCategory == charholder2;
    		   
    	if (mytable.RecId == 0)
    	{
    		mytable1.clear();
    		mytable1.reread();
    	   
    		select forUpdate mytable1
    			where mytable1.Category == charholder3
    			   && mytable1.SubCategory == charholder2;
    			   
    		if (mytable1.RecId == 0)   // Condition for controlling inserts
    		{
    			mytable1.Category = charHolder1;
    			if(charholder1 == "" && i != 123)
    			{
    				mytable1.Category = charHolder3;
    			}
    
    			mytable1.SubCategory = charholder2;
    			if (i == 10 || i == conLen(line2))
    			{
    				if (i == 10)
    					mytable1.Cattype = "Hourly Pay Rate";
    				else
    					mytable1.Cattype = "Total Earning Amount";
    			}
    
    			else if (i >= 11 && i <= 46)
    			{
    				mytable1.Cattype = "Category1";
    			}
    			else if ( i >= 47 && i <= 114)
    			{
    				mytable1.Cattype = "Category2";
    			}
    			else if (i >= 115 && i < conLen(line2))
    			{
    				mytable1.Cattype = "Category3";
    			}
    			
    			ttsBegin;
    			mytable1.insert();
    			ttsCommit;
    		}
    	}
    }

    Does the whole code runs inside a transaction?

    What's the value of CacheLookup property of the table?

    Don't you want to add a unique index with Category and SubCategory fields? It'll either stops the execution immediately when the problem occurs, or it'll reveal that the value aren't actually the same.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,696 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,490 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans