web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / how to fetch record co...
Finance | Project Operations, Human Resources, ...
Answered

how to fetch record correctly from a temporary table based on report parameter/ Container

(0) ShareShare
ReportReport
Posted on by Apratim 166

I have a temporary table in which I am entering data at two different periods, which is working fine. My third table is the calculation based on the two data table of those two periods, I am also using another temporary table for the third table. I have passed the required report parameter and the first temporary table in a method call for this. 

public void comparsionTableMthod(ItemId itemidCode, ItemGroupId itemGroup, TPO_ComparisionReprtTemp temp)
    {
        container storageItem ;
        storageItem= conNull();
        storageItem= str2con(itemidCode);
        int i;

        ItemId _itemidCodeThird;
        ItemGroupId _itemGroup;


        if(itemidCode)
        {
           
            ttsbegin;
             
            calculationTable.clear();
            for(i=1;i<=conLen(storageItem);i  ) 
            {
                while select ComparisionMonth,ItemId,sum(Qty),sum(Value), sum(QtyTwo),sum(ValueTwo) from temp
                group by ComparisionMonth,  ItemId  where temp.ItemId== conPeek(storageItem,i)
              
           {
           }
        }
    }

The report parameter is multiselect report  parameter, The problem I am facing here is, the while select is only working for the first item in the container/passed parameter, it selects all the record correctly for the first item, when I assigned hardcoded value, then it is also working for any hard coded itemId,   But I need in a generalize way, so it would be helpful if anyone here can suggest me how to approach it.

I have the same question (0)
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 237,681 Most Valuable Professional on at
    RE: how to fetch record correctly from a temporary table based on report parameter/ Container

    First of all, let me simplify your code, especially by throwing away all the things that aren't used or meaningful:

    public void comparsionTableMthod(ItemId _itemId, ItemGroupId _itemGroup, TPO_ComparisionReprtTemp _temp)
{
	if (!_itemId)
	{
		return;
	}

	container storageItem = str2con(_itemId);

	ttsbegin;

	// Note that you're wrong if you expect this to delete records from calculationTable
	calculationTable.clear();
	
	for (int i = 1; i <= conLen(storageItem); i  ) 
	{
		while select ComparisionMonth, ItemId, sum(Qty), sum(Value), sum(QtyTwo), sum(ValueTwo) from _temp
			group by ComparisionMonth, ItemId
			where _temp.ItemId == conPeek(storageItem, i)
	   {
	   }
	}
	
	ttscommit;
}

    What exactly do you get in the _itemId parameter? The type is ItemId, therefore it should be a single item ID, while your code assumes that it's actually a list of item IDs that can be parsed to a container. One of these things is wrong.

  • Apratim Profile Picture
    Apratim 166 on at
    RE: how to fetch record correctly from a temporary table based on report parameter/ Container

    Hi Martin. 

    Thanks for the reply.

    I am resending my code snippet again. Since you asked, I am getting comma separated list of  itemids in , ItemidCode. I both type, str /Edt  ItemId, I m getting comma separated list of itemId.

    public void comparsionTableMthod(ItemId itemidCode, ItemGroupId itemGroup, TM_ComparisionReprtTemp temp)
    {
    
        container storageItem ;
        storageItem= conNull();
        storageItem= str2con(itemidCode);
        int i;

    //    ItemId _itemidCodeThird;
    //    ItemGroupId _itemGroup;


        if(itemidCode)
        {
           
            ttsbegin;
             
            calculationTable.clear();
            for(i=1;i<=conLen(storageItem);i  )
            {
             //   _itemidCodeThird= conPeek(storageItem,i);
                while select ComparisionMonth,ItemId,sum(Qty),sum(Value), sum(QtyTwo),sum(ValueTwo) from temp
                group by ComparisionMonth,  ItemId  where temp.ItemId==conPeek(storageItem,i)
              
           {
           

                calculationTable.clear();
           
                calculationTable.ItemIdThird              = temp.ItemId;
                calculationTable.CategoryThird=InventTable::find(temp.ItemId).itemName();
           
                calculationTable.ComparisionMonth=temp.ComparisionMonth;
            
                if(temp.Qty!=0)
                  calculationTable.QtyThird= (temp.QtyTwo-temp.Qty)/temp.Qty;
                if(temp.Value!=0)
                calculationTable.ValueThird=(temp.ValueTwo-temp.Value)/temp.Value;
                 
                calculationTable.insert();

            
            }
       
    

        }
        ttscommit;
        //method
    }

    Thanks.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 237,681 Most Valuable Professional on at
    RE: how to fetch record correctly from a temporary table based on report parameter/ Container

    All right, so the data type and name of the first parameter is misleading. Let me continue with my code, because you've just resend your original code without my fixes. We we'll have this:

    public void comparsionTableMethod(str _itemIdList, ItemGroupId _itemGroup, TPO_ComparisionReprtTemp _temp)
{
	if (!_itemIdList)
	{
		return;
	}

	container itemIdContainer = str2con(_itemIdList);

	ttsbegin;
	
	for (int i = 1; i <= conLen(itemIdContainer); i  ) 
	{
		while select ComparisionMonth, ItemId, sum(Qty), sum(Value), sum(QtyTwo), sum(ValueTwo) from _temp
			group by ComparisionMonth, ItemId
			where _temp.ItemId == conPeek(itemIdContainer, i)
	   {
	   }
	}
	
	ttscommit;
}

    It'll be benefitial to change it a bit and add information useful for debugging:

    public void comparsionTableMethod(str _itemIdList, ItemGroupId _itemGroup, TPO_ComparisionReprtTemp _temp)
{
	if (!_itemIdList)
	{
		return;
	}

	container itemIdContainer = str2con(_itemIdList);

	for (int i = 1; i <= conLen(itemIdContainer); i  ) 
	{
		ItemId itemId = conPeek(itemIdContainer, i);
		info(itemId);

		info(InventTable::exist(itemId) ? "Valid item" : "Invalid item";
		
		select firstOnly RecId from _temp
			where _temp.ItemId == itemId;
			
		info(_temp.RecId ? "Exists in _temp" : "Not in _temp");
	}
}

    Please run it with your input and tell us what result you got.

  • Apratim Profile Picture
    Apratim 166 on at
    RE: how to fetch record correctly from a temporary table based on report parameter/ Container

    Thanks a lot Martin.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 683 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 563 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 398 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans