function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion

I'm getting 0 records returned from a query when I know 1 exists

I'm working on being able to translate a part number (stored as a string) from one extenal system to another.  I have a custom table setup each record will have both part numbers and would like the user to be able to enter either number and have SF provide the other.  If a number is not found in the table, query an external system (using HTTP callout) to retrieve it then save it in the table so future requests for the same number don't have to callout to the external system (essentially add records on the fly).

Here's the flow:  user enters a part number from system A.  This field is a string.  Apex queries the custom table to see if it is saved.

List<Part__c> p = new List<Part__c>();
p = [Select Return__c, Regional_Scrap__c, Return_Factory__c, Factory_Approval__c, Name, Description__c, SAP_Number__c From Part__c Where Name = :li.Part_Number__c];

First time part number is used, the query will return 0 records [p.size()=0].  Apex will then use the HTTP callout to query the external system to get the other part number.  Callout is working fine.  Response gets parsed into a List<String> and the field value set.

HTTPResponse res = http.send(req);
bodyval = res.getBody();
list<string> dfresults = bodyval.split('\t', 10);
li.SAP_Number__c = dfresults[4];

The other number looks fine after the parsing and setting the field value.  Since the initial query returned no records (as expected) I then copy save the data into the table (so future need doesn't result in another callout to the external system)

Part__c Prt = new Part__c();
Prt.Name = li.Part_Number__c;
Prt.SAP_Number__c = li.SAP_Number__c;
insert Prt;

This is working.  I can see the new part in the table (on the tab using a list view).

NOW, i want to translate this number back the other way.  So I provide the SAP Number and query the Part table expecting to find the record that was just saved.

p = [Select Return__c, Factory_Approval__c, Regional_Scrap__c, Return_Factory__c, Description__c, Name, SAP_Number__c From Part__c Where SAP_Number__c = :li.SAP_Number__c];

Unfortunately this query ALSO returns 0 records so the loop continues and since "It didn't find the record" it adds it a second time and now I have 2 records.  it will not create any additional records.  If I repeat the second part and ask for hte SAP number, it will return the second record that was created.  I can even delete the intial record (created from the HTTP callout) and will not create a second record.

So my question is, any idea why the query after the initial record is created returns 0???



Bhawani SharmaBhawani Sharma
Even you have been executed the insert statement, but probably the changes have not been committed in database. Is there any way you can break this code in 2 separate requests? That means, insert record in request 1 and fetch in other?
Thanks for your response Bhawani.  They already occur in 2 separate transactions.  All of the code is enclosed in a single save method which gets executed from a button on the VF page.  First time through part is not found in SF as expected so the code to add it executes.  Method finishes successfully.  I can then go look at the Parts tab and actually see the new part has been added to the object as expected.  

User then edits the list and hits the button to trigger the save method again.  This time SF checks for the SAP Number (whereas user entered the Part Number the first time through and SF found added the SAP Number).  Again, the part is in the table with both the part number AND the SAP Number but searching for it via SAP Number returns 0 records so SF adds it a second time.  For some reason the SAP Number on the record is not matching the SAP number that was saved in the object.

After it has been added this way (via SAP Number) SF will find that record the next time through and not create an additional record.
Bhawani SharmaBhawani Sharma
Before executing the second transaction, is there any way for you to try querying that record using developer console? Is there any possibility you can some trigger or WF rule which are modifying SAP NUmber on record? Regards, Bhavi Sharma Certified Salesforce Consultant +91-9928130589 LinkedIn | Twitter | Blog | Community
Thanks for your thoughts.

Here's the newly added part (using Part Number) in the List View showing Part Number, SAP Number and Description
User-added image
I'm not on speaking terms with the developer's console but in Eclipse I can get the record by Part Number:

User-added image

But by SAP Number...  No Body's Home!:

User-added image

Bizarre?  Yes?