Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / notexists join not wor...
Finance | Project Operations, Human Resources, ...
Answered

notexists join not working with tempTable

(0) ShareShare
ReportReport
Posted on by Faqruddin 1,881

Hello,

I have a records in tempTable and I'm trying return a records from temTable if there is doesn't exist a match in the second table(CustTable). It works below code but the all values starting from  603 in below code always return twice where first two values 11102206","12104348" already exists in  first & second table and its not returns value as expected. But I have issue values keep repeating. I'm expecting single output("603","604","605","606","607") but its not works as expected. Please advise.

static void readJSON(Args _args)
{
mapEnumerator mapEnumerator;
Map data;
List testList;
str _jsonStr;
container test;
ListEnumerator listEnumerator;
int containerList;
Map map1;
int lengthIterate;
CustTable custable;
TempMirnahCust tmpMirnahCust;
str _conpkrsult;

;
_jsonStr = '{"branchList":["11102206","12104348","603","604","605","606","607"]}';
data = RetailCommonWebAPI::getMapFromJsonString(_jsonStr);
mapEnumerator = data.getEnumerator();
while (mapEnumerator.moveNext())
{
switch (mapEnumerator.currentKey())
{
case "branchList" :
test = mapEnumerator.currentValue();
testList =List::create(test);
listEnumerator = testList.getEnumerator();
while (listEnumerator.moveNext())
{
test = listEnumerator.current();

 _conpkrsult = conpeek(test, 1);
      
    tmpMirnahCust.MirnahCode = _conpkrsult;
    
    tmpMirnahCust.insert();
   
        while select tmpMirnahCust where  tmpMirnahCust.dataAreaId == custable.dataAreaId
            notexists join custable
            where custable.MirnahCode == tmpMirnahCust.MirnahCode
        {
             
            print tmpMirnahCust.MirnahCode;      
             pause;
    }
  

}
break;
}
}
}

  • Verified answer
    GirishS Profile Picture
    GirishS 27,823 Moderator on at
    RE: notexists join not working with tempTable

    Hi Faqru,

    Try the below code.

    Instead of adding a while select statement - Check if the customers exist in the cust table by adding a select statement - If exist add it to the container.

    So outside the while loop print the container using con2str function.

    static void readJSON(Args _args)
{
mapEnumerator mapEnumerator;
Map data;
List testList;
str _jsonStr;
container test;
ListEnumerator listEnumerator;
int containerList;
Map map1;
int lengthIterate;
CustTable custable;
TempMirnahCust tmpMirnahCust;
str _conpkrsult;

;
_jsonStr = '{"branchList":["11102206","12104348","603","604","605","606","607"]}';
data = RetailCommonWebAPI::getMapFromJsonString(_jsonStr);
mapEnumerator = data.getEnumerator();
while (mapEnumerator.moveNext())
{
switch (mapEnumerator.currentKey())
{
case "branchList" :
test = mapEnumerator.currentValue();
testList =List::create(test);
listEnumerator = testList.getEnumerator();
while (listEnumerator.moveNext())
{
test = listEnumerator.current();

 _conpkrsult = conpeek(test, 1);
      
    tmpMirnahCust.MirnahCode = _conpkrsult;
    
    tmpMirnahCust.insert();
    custTable.clear();
    select * from custTable
        where custTable.MirnahCode == tmpMirnahCust.MirnahCode;
        if(!custTable)
        {
            // create new container and add it to the container.
            container custTableNot;
            custTableNot  = tmpMirnahCust.MirnahCode;
        }
        
  

}
break;
}
}
info(strfmt("%1", con2str(custTableNot)));
}

  • Faqruddin Profile Picture
    Faqruddin 1,881 on at
    RE: notexists join not working with tempTable

    Hello Girish,

    Actually I have api which returns customers and I'm trying to read those customers which not exists in custtable. I try loop outside of list and map enumarator but its doesn't print anything. Could you please past full code from above.

    Thank you

  • GirishS Profile Picture
    GirishS 27,823 Moderator on at
    RE: notexists join not working with tempTable

    Hi Faqru Shaik,

    What is the functionality you are trying to achieve from the above code?

    Also if you want to print the customer other then custTable, You need to add the while loop outside of the list and map enumerator.

    TempMirnahCust tmpMirnahCustBuffer;
while select tmpMirnahCustBuffer where tmpMirnahCustBuffer.dataAreaId == custable.dataAreaId
    notexists join custable
    where custable.MirnahCode == tmpMirnahCustBuffer.MirnahCode
{
    print tmpMirnahCustBuffer.MirnahCode;      
    pause;
}

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,233 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,923 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans