Got a tough one here comparing fields in custom objects in flows. - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Steve ConnellySteve Connelly 

Got a tough one here comparing fields in custom objects in flows.

OK, I have four custom fields in Opportunities. Two for Product 1 and two for products 2
For prod1 the fields are a concatenation of the Opp account ID and the created date and the other is the Opp account ID and the close date.

The format looks like this: 0014100000a99z6AAA-2-28-2020

I have two custom objects, one for product 1 and one for product 2. They each have a custom field that concatenates the Account ID and the created date for the custom record. The format again for these looks like this: 0014100000a99z6AAA-2-28-2020

In the flow in "Get Records" elements, it looks for custom records where the acct/date field equals the acct/date field in the Opp

For product one, this works fine. the flow finds the records and away we go. But it does not find the records for product 2.

I have gone into my test records and pulled the field values and copy/pasted them below

Prod1    0014100000a99z6AAA-2-26-2020    Opp field    Works, match found
Prod1    0014100000a99z6AAA-2-26-2020    Cust Ob field    Works, match found

Prod2    0014100000a99z6AAA-2-26-2020    Opp field    Does not work, match not found
Prod2    0014100000a99z6AAA-2-26-2020    Cost Ob field    Does not work, match not found

The above are copied and pasted directly from my test records exactly as I find them. I can't find a reason why this works for prod 1and not for prod 2

I have tried swapping the get records so the opp product 1 tries to match with the custom prod 2 and that works. But trying to match prod 2 with the custom prod 1 fails.

So I have to think that the date field in the custom object for prod 2 is somehow outputting the date in a different enough format that it is causing the match to fail even though I can't see it in the results.

Here is the date field for prod 1 (works with prod 1 and prod 2 field from Opp):
User-added image

Prod 2 field (does not work with prod 1 or prod 2 field from Opp)

So, i am tearing my hair out here. Any suggestions on what is happeniong or how to resolve it?
Steve
 
Best Answer chosen by Steve Connelly
Amnon KruviAmnon Kruvi
Queries running from any form of trigger need to run on indexed fields. If your query isn't selective (uses enough indexed fields) and your object has over 200,000 records, you'll get this error - regardless of whether any results were found.

Since you're matching by this one single field, are you able to mark it as External ID in the field definition and see if it changes things?

All Answers

Steve ConnellySteve Connelly
Here is a debug that shows the comparrison work for product 1 and failing for product 2:
User-added image
Amnon KruviAmnon Kruvi
Hi Steve, 

I'll bite :) 

Obviously, everything you've described so far sounds perfectly correct. I'm wondering whether you have different sharing models on the two custom objects which may explain why the flow can't find the record?

I'd also try copying this value and running a query in the developer console. One with the Account and Date match field, so you can see if the values look correct and have values, and a second query that filters by the account and match field. If your query works, there should be no reason for the flow not to. 

It's possible that the value in the match field is corrupted in some way that isn't visible. If the second query fails, try manually setting the value in that field and try again. 

Of course, you're welcome to shoot me an email if this doesn't get you anywhere, and we'll try it together. 
Steve ConnellySteve Connelly
Hi sir! How are you and your family? We are well here.
So for the first part, Our org is pretty wide open and both objects should be visible and accessible from all users/profiles, and objects.

I did run a query to check how the field in question looks and that was fine. i also ran a query for one of the Id/Date field values that are not showing up in the flow and it does come up in the query.
User-added image

I should also mention, I originally set all this up for a report that I created using a reporting app called Apsona. We ended up deciding not to buy the product just yet so i an trying to create a few of these reports myself.

It should be noted that both the CDARS Match object field AND the ICS Match object field worked just fine in the multi-step reporting tool. So i am flummoxed on this one. It seems to me that this should all just work.

The goal here is to compare the amount in an Opp with the actuall change in business over the same period to determine how successful the opp was. To that end i created a new custon object that will pull data in from both the Opp and the two custom objects.

The custom fields as you know are used to identify the CDARS and ICS match records that correspond to the opp created and close dates.

With the new object it would just be a simple report showing the start amount, the end amount and the change for each product and compare that change to the original opp amount.

Until I got stuck with the ICS Account/Date field not working even though its counterpart for CDARS works fine.
Grrrrr.

I am bout ready to hang it up for the day and try again in the morning. I know it is quite late there so maybe we can touch base tomorrow or as you are available.

I will catch you later sir,
Steve
Steve ConnellySteve Connelly
One more screen shot. i added the account ID to the query so you can see the two components of the formula field:
User-added image
sakshi nagpalsakshi nagpal
Hi Steve,

 The field name that you are showing in developer console is Account_and_match_date_ICS__c.

 The field name that you are using in flow is Account_and_Close_Date_CDARS__c


 I think you are using wrong field

 Thanks,
Sakshi
Amnon KruviAmnon Kruvi
There are 2 separate objects, Sakshi. One has one field, the second has the other.

Steve, if your query works manually, then maybe the problem is with the get records step?

Could you show the debug log for trying to match the same product as the one you showed in your query (opportunity ID ending in MUAA2)?
sakshi nagpalsakshi nagpal
Thanks for pointing out Ammon.

 It still confuses me because, in developer console Steve should have queried Account_and_Close_Date_CDARS__c. As flow if failing for this field


 Check flow screenshot-:
User-added image

 However he queried Account_and_Match_Date_ICS__c.​ in developer console

 Flow is failing for Account_and_Close_Date_CDARS__c-:
User-added image

I feel there is some confusion about field names

 Thanks,
Sakshi

 
Steve ConnellySteve Connelly
My apologies for the confusion. The field that is not working is the CDARS fist have mixed them up in my missive. Prod 1 is CDARS and that works. P:rod 2 is ICS which does not work. Sc Steve Connelly Business Applications Administrator – Enterprise Applications Promontory Interfinancial Network, LLC 1300 N. 17th Street Suite 1800 Arlington, VA 22209 866.776.6426, ext. 3307 (Telephone) 703.292.3451 (Fax) sconnelly@promnetwork.com
Steve ConnellySteve Connelly
Lets try this again but not all jumbled up..

Maybe I built the query wrong. The field names in the Opp are as follows: 
* Account_and_Open_Date_CDARS__c
* Account_and_Close_Date_CDARS__c
* Account_and_Open_Date_ICS__c
* Account_and_Close_Date_ICS__c

These are trying to find a match in either the CDARS Match object or the ICS Match object. Here are those fields:
* Account_and_Match_Date_CDARS__c
* Account_and_Match_Date_ICS__c

So in the Flow, these two:
* Account_and_Open_Date_CDARS__c
* Account_and_Close_Date_CDARS__c

NOTE: these work fine look for a match here in the CDARS Macth object:
* Account_and_Match_Date_CDARS__c

And these two:
* Account_and_Open_Date_ICS__c
* Account_and_Close_Date_ICS__c

Look for a match here in the ICS Match Object:
* Account_and_Match_Date_ICS__c

NOTE: this is where it fails. It should also be noted that

This also works:
* Account_and_Open_Date_ICS__c
* Account_and_Close_Date_ICS__c

Can find matches here:
* Account_and_Match_Date_CDARS__c

But
* Account_and_Open_Date_CDARS__c
* Account_and_Close_Date_CDARS__c

Cannot find matches here:
* Account_and_Match_Date_ICS__c

This leads me to conclude that the problem resides with this field:
* Account_and_Match_Date_ICS__c

So that is the field I was querying in the developer console.
Steve ConnellySteve Connelly
NOTE: the match fields have exactly the same format  (or should any way) as the Opportunity fields. There should ever only be one CDARS or ICS match record that mateched an Opp field exactly as there is only one match ber day per participating account.

As well, since everything is the same format, the CDARS Opp fields should be able to match either the CDARS match field or the ICS match field. but they don't

The reverse should be true also. ICS Opp fields should be able to match with the CDARS Match field, and they can, as you would expect.
Amnon KruviAmnon Kruvi
Steve, have you tried running the debug log for the same opportunity you showed in the query screenshot? I just want to make sure that all the IDs actually do match and do exist.
Steve ConnellySteve Connelly
Not sure if this is the log you are looking for. This is what I get when I run the query in the Dev Console:

48.0 APEX_CODE,FINEST;APEX_PROFILING,INFO;CALLOUT,INFO;DB,INFO;NBA,INFO;SYSTEM,DEBUG;VALIDATION,INFO;VISUALFORCE,INFO;WAVE,INFO;WORKFLOW,INFO
08:32:50.0 (446629)|USER_INFO|[EXTERNAL]|00541000002c4kS|sconnelly@promnetwork.com.full|(GMT-04:00) Eastern Daylight Time (America/New_York)|GMT-04:00
08:32:50.0 (493414)|EXECUTION_STARTED
08:32:50.0 (500568)|CODE_UNIT_STARTED|[EXTERNAL]|Aura
08:32:50.0 (4008949)|CODE_UNIT_STARTED|[EXTERNAL]|01p4100000KRFlk|AccountTeamHelper.getAccountTeamList(Id)
08:32:50.0 (4426264)|HEAP_ALLOCATE|[79]|Bytes:3
08:32:50.0 (4497477)|HEAP_ALLOCATE|[84]|Bytes:152
08:32:50.0 (4524421)|HEAP_ALLOCATE|[399]|Bytes:408
08:32:50.0 (4552505)|HEAP_ALLOCATE|[412]|Bytes:408
08:32:50.0 (4585614)|HEAP_ALLOCATE|[520]|Bytes:48
08:32:50.0 (4627632)|HEAP_ALLOCATE|[139]|Bytes:6
08:32:50.0 (4650660)|HEAP_ALLOCATE|[EXTERNAL]|Bytes:1
08:32:50.0 (4673323)|METHOD_ENTRY|[1]|01p4100000KRFlk|AccountTeamHelper.AccountTeamHelper()
08:32:50.0 (4686363)|STATEMENT_EXECUTE|[1]
08:32:50.0 (4694163)|STATEMENT_EXECUTE|[1]
08:32:50.0 (4773963)|HEAP_ALLOCATE|[52]|Bytes:5
08:32:50.0 (4806581)|HEAP_ALLOCATE|[58]|Bytes:5
08:32:50.0 (4818819)|HEAP_ALLOCATE|[66]|Bytes:7
08:32:50.0 (4841767)|SYSTEM_MODE_ENTER|false
08:32:50.0 (4863318)|HEAP_ALLOCATE|[EXTERNAL]|Bytes:5
08:32:50.0 (4872318)|SYSTEM_MODE_EXIT|false
08:32:50.0 (4884158)|METHOD_EXIT|[1]|AccountTeamHelper
08:32:50.0 (4893430)|HEAP_ALLOCATE|[EXTERNAL]|Bytes:4
08:32:50.0 (4901494)|SYSTEM_MODE_ENTER|false
08:32:50.0 (4913446)|VARIABLE_SCOPE_BEGIN|[3]|recordId|Id|false|false
08:32:50.0 (5291666)|VARIABLE_ASSIGNMENT|[3]|recordId|"0014100000a9AMUAA2"
08:32:50.0 (5318196)|HEAP_ALLOCATE|[4]|Bytes:5
08:32:50.0 (5325960)|STATEMENT_EXECUTE|[3]
08:32:50.0 (5331281)|STATEMENT_EXECUTE|[4]
08:32:50.0 (5336274)|HEAP_ALLOCATE|[4]|Bytes:175
08:32:50.0 (5363930)|HEAP_ALLOCATE|[4]|Bytes:4
08:32:50.0 (5382144)|HEAP_ALLOCATE|[4]|Bytes:7
08:32:50.0 (7120996)|SOQL_EXECUTE_BEGIN|[4]|Aggregations:0|SELECT Id, TeamMemberRole, User.FirstName, User.LastName, User.SmallPhotoUrl, User.Id FROM AccountTeamMember WHERE AccountId = :tmpVar1 ORDER BY User.FirstName ASC NULLS FIRST
08:32:50.0 (180666288)|SOQL_EXECUTE_END|[4]|Rows:2
08:32:50.0 (180708366)|HEAP_ALLOCATE|[4]|Bytes:12
08:32:50.0 (180775690)|HEAP_ALLOCATE|[4]|Bytes:501
08:32:50.0 (180837176)|HEAP_ALLOCATE|[4]|Bytes:12
08:32:50.0 (180867227)|SYSTEM_MODE_EXIT|false
08:32:50.180 (180920287)|CUMULATIVE_LIMIT_USAGE
08:32:50.180 (180920287)|LIMIT_USAGE_FOR_NS|(default)|
  Number of SOQL queries: 1 out of 100
  Number of query rows: 2 out of 50000
  Number of SOSL queries: 0 out of 20
  Number of DML statements: 0 out of 150
  Number of DML rows: 0 out of 10000
  Maximum CPU time: 0 out of 10000
  Maximum heap size: 0 out of 6000000
  Number of callouts: 0 out of 100
  Number of Email Invocations: 0 out of 10
  Number of future calls: 0 out of 50
  Number of queueable jobs added to the queue: 0 out of 50
  Number of Mobile Apex push calls: 0 out of 10

08:32:50.180 (180920287)|CUMULATIVE_LIMIT_USAGE_END

08:32:50.0 (180971978)|CODE_UNIT_FINISHED|AccountTeamHelper.getAccountTeamList(Id)
08:32:50.0 (181214815)|CODE_UNIT_FINISHED|Aura
08:32:50.0 (181231754)|EXECUTION_FINISHED
Amnon KruviAmnon Kruvi
I meant the flow debug log, like the one you shared originally. That one looks for an opportunity ending in AAA, whereas your following screenshot shows a product with an identifier ending in AA2. If it's for the same opportunity, then obviously there's a disrepency there.
Steve ConnellySteve Connelly
Here you fo. Four screen shots...
User-added image
Steve ConnellySteve Connelly
User-added image
Steve ConnellySteve Connelly
User-added image
Steve ConnellySteve Connelly
User-added image
Steve ConnellySteve Connelly
So, where the flow fails is when it is looking for matches against the Account_and_Match_Date_ICS__c field in the ICS match object.

Note: I have queried for both 0014100000a9AMUAA2-4-15-2020 and 0014100000a9AMUAA2-2-19-2020 and the query finds both records in the ICS Match object.
Amnon KruviAmnon Kruvi
Your flow seems to be querying for 2-19-2020, whereas according to your screenshot the record is 4-15-2020. Let's take a look at the debug log (just that one step is enough) that searches for 4-15-2020.
Steve ConnellySteve Connelly
It is actually querying for both. The field in the ICS Start elementis 2-19-202 and the fioeld in the ICS End element is 4-15-2020. Both are failing.

Here is the raw log for 4-15-2020

48.0 APEX_CODE,FINEST;APEX_PROFILING,INFO;CALLOUT,INFO;DB,INFO;NBA,INFO;SYSTEM,DEBUG;VALIDATION,INFO;VISUALFORCE,INFO;WAVE,INFO;WORKFLOW,INFO
09:20:21.0 (551933)|USER_INFO|[EXTERNAL]|00541000002c4kS|sconnelly@promnetwork.com.full|(GMT-04:00) Eastern Daylight Time (America/New_York)|GMT-04:00
09:20:21.0 (3649998)|FLOW_CREATE_INTERVIEW_BEGIN|00DP0000003reYR||301P000000047KH
09:20:21.0 (11444988)|FLOW_CREATE_INTERVIEW_END|18722003f44cd205f81c3a3c25df172323777c8-48bc|Create Match Delta Record
09:20:21.11 (11953380)|USER_INFO|[EXTERNAL]|00541000002c4kS|sconnelly@promnetwork.com.full|(GMT-04:00) Eastern Daylight Time (America/New_York)|GMT-04:00
09:20:21.11 (12012268)|FLOW_START_INTERVIEW_BEGIN|18722003f44cd205f81c3a3c25df172323777c8-48bc|Create Match Delta Record
09:20:22.571 (1571278756)|EXCEPTION_THROWN|[EXTERNAL]|System.QueryException: Non-selective query against large object type (more than 200000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.
Even if a field is indexed a filter might still not be selective when:
1. The filter value includes null (for instance binding with a list that contains null)
2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times)
09:20:22.571 (1573965916)|FLOW_ELEMENT_FAULT|Fault path taken.|FlowRecordLookup|Query_ICS_Match_Start
09:20:22.571 (1903952736)|EXCEPTION_THROWN|[EXTERNAL]|System.QueryException: Non-selective query against large object type (more than 200000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.
Even if a field is indexed a filter might still not be selective when:
1. The filter value includes null (for instance binding with a list that contains null)
2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times)
09:20:22.571 (1909268917)|FLOW_ELEMENT_FAULT|Fault path taken.|FlowRecordLookup|Query_ICS_Match_End
09:20:22.996 (1996944789)|CODE_UNIT_STARTED|[EXTERNAL]|Flow:01IP0000000fh3l
09:20:22.996 (1997281889)|CODE_UNIT_FINISHED|Flow:01IP0000000fh3l
09:20:22.571 (2114803785)|FLOW_START_INTERVIEW_END|18722003f44cd205f81c3a3c25df172323777c8-48bc|Create Match Delta Record
09:20:22.571 (2114906962)|FLOW_INTERVIEW_FINISHED|18722003f44cd205f81c3a3c25df172323777c8-48bc|Create Match Delta Record
09:20:23.115 (2115636177)|USER_INFO|[EXTERNAL]|00541000002c4kS|sconnelly@promnetwork.com.full|(GMT-04:00) Eastern Daylight Time (America/New_York)|GMT-04:00
Steve ConnellySteve Connelly
This looks different from what I sent last time. This looks like there might be a problem with the number of records it has to search for ICS. That would not be a surprise as there are a lot more ICS records that CDARS. CDARS runs once a week and we keep 1 year od date. ICS runs 5 days a week or five times as much data.
Is that my problem?
 
Amnon KruviAmnon Kruvi
Queries running from any form of trigger need to run on indexed fields. If your query isn't selective (uses enough indexed fields) and your object has over 200,000 records, you'll get this error - regardless of whether any results were found.

Since you're matching by this one single field, are you able to mark it as External ID in the field definition and see if it changes things?
This was selected as the best answer
Steve ConnellySteve Connelly
Not sure how to mark it as external ID. Where do i do that?
Sc
Amnon KruviAmnon Kruvi
Ahhh, your field is a formula, and formulas can't be external IDs. That's... unfortunate.

This means you'll need to convert that formula into a regular text field, and write the value into it using a workflow rule, process, or other form of automation.
Steve ConnellySteve Connelly
OK, so I will add a text field to the ICS Match object mark it external and popoulate it and try it.

If that works, then I will see about how to auto pouluate it from the formula field.

Unless you have suggestions on a better way to match  the Match object records to the Opportunities. :)
I will let you know if a few.
Sc 
Amnon KruviAmnon Kruvi
You could always just match on the two components - the opportunity ID and the date. You could mark the opportunity ID as an external ID (unless it's already a lookup field, in which case it's already indexed).
Steve ConnellySteve Connelly
And just like that, it works.

So, I need to find the best way to populate a text field from a formula in the match objects. For all new matches I can just do it upon creation since the records never change after creation. i suppose I can use demand tools or similar to update all the existing matches.

So before I do any of that, is there any better way to approach this process or is it just a matter of it is, what it is?
Sc
Amnon KruviAmnon Kruvi
So the problem is updating all the existing records? Since you have such a large amount of data, then you'd want some proper import tool to do it. You could always write a batch class if you have the time :)
Steve ConnellySteve Connelly
Hmmm, My boss wants me to keep my hand in Apex. He might like me to do it that way LOL Anything you can point me at as tips or examples?
Sc
Amnon KruviAmnon Kruvi
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_batch_interface.htm
Steve ConnellySteve Connelly
Many thanks sir. I will let you know what comes of this. I must remember to look at the debug logs more often.
Steve