SOQL Datatable - Enhance $CurrentRecord api to handle null values

[solo-1234]

I'm having trouble with a query that filters on multiple lookup fields from $CurrentRecord.

I'm trying this: SELECT fields FROM object WHERE Id IN ($CurrentRecord.Lookup_1__c,$CurrentRecord.Lookup_2__c,$CurrentRecord.Lookup_3__c)

Use case is a record that has 3 lookup fields to the same object (for business reasons this made more sense than a junction obj), and I'd like to display a read-only datatable of the (1-3) records.

The datatable shows correctly when a record has all 3 lookups populated. However, if only two are populated, it shows no records and instead says Invalid SOQL String invalid ID field: null

But if I remove the third lookup field from the filter, it shows both records as it should.

(I get the same error if I use OR instead of IN() for the filter)

Any insight? Thanks!

[tsalb]

Per discord, just jotting down some notes:

1. Only single merge is supported and tested... this is a pretty interesting use case I didn't think of (surprised it works!).
2. Would recommend you null check said fields used in $CurrentRecord API first in the WHERE clause.
3. The code does not proactively modify your soql string if a null value is found, it will merge in null.
   • In other words, what you're really asking for is (psuedo):
     • if Lookup_3__c is null, remove ,$CurrentRecord.Lookup_3__c from the SOQL String including the preceding comma.

FYI point 3 is technically challenging, specifically because there might need to be lookbehind/lookahead regex.

For now, I don't have bandwidth to tackle this (happy to take PRs!) but I might add some limitations to the CurrentRecord API wiki based on the findings in this issue.

[solo-1234]

I tried null checking fields on $CurrentRecord but it errors - I think I can only check for nulls in the object I'm querying.

However, I am testing things in dev console and it looks like if you replace NULL with an empty string ('') then the query works.

Example:
SELECT fields FROM object WHERE Id IN ('XXXXXXXXXXXXXXXXXX','','')
Returns one result, as it should.

So that might be an easy fix...?

[solo-1234]

Seems like this is not related to the IN clause / multiple criteria. When using a SOQL query that looks for records that match the id of a $CurrentRecord lookup field, the component throws an error if the lookup field is null.

Invalid SOQL String invalid ID field: null

[tsalb]

Per discord, nothing is ever that straightforward about a lib like this.

Consider the following conversation as guiderails:

[solo-1234]

I have a fix working in my org now. I tested all the recipes and they work the same as in another sandbox with a clean installation, except for the two recipes which throw errors in a clean installation (see #101 and #102).

Summary of changes to release notes would be:

• When using $CurrentRecord API to filter by a lookup field, the id of the record in the lookup field is wrapped in quotes and placed into the SOQL string. Until now if the lookup field was null, the SOQL would contain 'null' and throw an error Invalid SOQL String invalid ID field: null. This PR will change it so that if the id field is null, it'll return an empty string '' instead. When the lookup field is populated there will be no change in behavior.

What is the next step? Should I prepare a PR?

Thanks!

[tsalb]

Yep! For this one vet #101 and #102 in conjunction with whatever code you want to include as this #99 feature request.

You would open a PR and then fix one or all of the included issues. Don't worry, I can contribute code on top of your fork if I need to pull you in the right direction (or contribute to code reviews).

We will do the collaboration workflow:

1. You start PR
2. You do your fixes / adjustments forked from my master
3. You ensure that #99, #101 and #102 all work in isolation (read: scratch org based on this repo's config settings)
4. Submit the PR for review
5. I can collab and add commits on top of your PR
6. OR, I can collab and code review you and point you in the right direction per commit / feature etc

One thing to keep in mind. The smaller your commits the easier it is to see changes. So, try to keep commits granular and within "context". A good rule of thumb is anything over 200 lines of code changes (that's not XML) should be an upper cap.

So long as each commit has enough context/history within a single hash, it makes for collaboration (read: code review) much easier.

Good luck and take your time!

[solo-1234]

Following up for the future... I believe this needs to apply to text fields also.

Take the recipe Related Contacts with same Mailing State as this Account's Billing State on the Account record page.

If you create an account with no mailing state, and some related contacts with no billing state, the contacts will not display. However, if you put the text null into any of the contacts' billing state, those will display in the list.

Screenshot: