Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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 149

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.

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

    Thanks a lot Martin.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,379 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 149 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 230,379 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.

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

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,661 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,379 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans