Skip to main content

Notifications

Announcements

No record found.

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

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,832 Super User 2024 Season 1 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,869 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,832 Super User 2024 Season 1 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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 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