I'm looking to configure a filter in a!queryRecordType() that's similar to checking for null values on the outer side of a relationship that behaves like an outer join.
For example, I have a data structure as described below. The table Invoices is managed by an Appian application and we have a synced record type based on that table. The table SAP Documents is updated by an external source and we have another synced record type based on that table. There is a many-to-one relationship configured in our record type from Invoices.SAP_DOCUMENT_ID to SAP Documents.SAP_DOCUMENT_ID.
Invoices
ID (PK)
a bunch of other fields
SAP_DOCUMENT_ID (FK)
SAP Documents
SAP_DOCUMENT_ID (PK)
I need to query the Invoice records and return those where there is a value in Invoices.SAP_DOCUMENT_ID and where there is no corresponding row in SAP Documents - and I need to combine this with some other query filters, so this is not the only condition I need to check for. This is used in some logic that sends API calls to the external system that manages the SAP Documents table to tell that system which records need to be updated. There are thousands of rows in these tables, so fetching all of the records and using difference() is not an option. If I were doing this in a database view, it would be as simple as left joining Invoices to SAP Documents and checking for null values in SAP Documents.SAP_DOCUMENT_ID. However, when I try configuring this logic using a!queryRecordType (or when I try setting up a custom record field to check for this condition, as shown below), it doesn't behave like an outer join.
The custom record field expression below returns false when there is a value in Invoice.SAP_DOCUMENT_ID but no corresponding row in SAP Documents. I would have expected it to return true since there is no value in that field, "the related record doesn't exist" seems to be handled differently by Appian than "the related record exists and the value in that column is null". How can I write a custom record field expression to check for this condition where there is a value in Invoice.SAP_DOCUMENT_ID but no corresponding row in SAP Documents? At this point, I am trying to find ways to ensure that there's always a corresponding row in the SAP Documents table even if all of its other columns are empty, and I think that would solve my problem - but Product team, here's another use case to consider if you want custom record fields to replace the need for database views
a!customFieldMatch( value: 'recordType!Invoice.relationships.sapDocument.fields.documentId', whenTrue: a!customFieldCondition( field: fv!value, operator: "is null" ), then: true, default: false )
Discussion posts and replies are publicly visible
Hi John Stretton ,
Just trying to understand your requirement here, if you already have a many to one relation from invoice.SAP_DOCUMENT_ID to SAP Documents.SAP_DOCUMENT_ID, you either have a primary key or a null value, so what reference in SAP Documents you are trying to check?
Sorry if I wrongly understood your scenario.
The relationship from Invoice to SAP Document is many-to-one, not one-to-many. To be clear, I'm looking for the equivalent of this SQL query:
SELECT [Invoices].[whatever columns]
FROM [Invoices]
LEFT OUTER JOIN [SAP Documents] ON [Invoices].[SAP_DOCUMENT_ID] = [SAP_Documents].[SAP_DOCUMENT_ID]
WHERE [Invoices].[SAP_DOCUMENT_ID] is not null and [SAP Documents].[SAP_DOCUMENT_ID] is null
This would return all rows from Invoices that have a value in the SAP_DOCUMENT_ID field but where there is no corresponding row in the [SAP Documents] table because that row has not been populated by the other system yet.
Sorry John Stretton I meant many to one in my previous comment. In my understanding when you create the many to one relation you can either have the SAP_Document.SAP_DOCUMENT_ID populated or not, as SAP_DOCUMENT_ID is the primary key from the SAP_DOCUMENT table. Or do you have values in the Invoices.SAP_DOCUMENT_ID that do not relate to a primary key in the SAP_DOCUMENT?
"Or do you have values in the Invoices.SAP_DOCUMENT_ID that do not relate to a primary key in the SAP_DOCUMENT?"
Yes, it can happen that values in Invoices.SAP_DOCUMENT_ID do not relate to a primary key in [SAP Documents].SAP_DOCUMENT_ID. I know this is somewhat unusual, but the SAP Documents table is maintained by another system and I'm using this query (with some additional conditions) to tell that other system which rows in this table need to get updated.
Again, I'm just looking for a way to do the equivalent of this SQL query:
So far I have not been able to do this with filters in a!queryRecordType or with a custom record field because Appian seems to be treating "is null" and "related record does not exist" as two different things.
Similar question as Acacio,
Your query corresponds to a situation where, Invoices table will have SAP_DOCUMENT_ID, but there will be no corresponding entry in SAP_DOCUMENTS.
This is bit confusing because, SAP_DOCUMENT_ID is a primary key as per your question. How can it not have an value in the primary table (SAP_DOCUMENTS) but it will be mapped in secondary table (INVOICES)
The SAP_DOCUMENT_ID column in the Invoices table is populated by an external automation, and the SAP Documents table is maintained by a different system - we have to tell that other system when it needs to write records to this table from its own internal source. I know this sounds unusual, but that's the way it is.
I am only looking for a way to do the equivalent of this SQL query, which would be very easy to achieve in a database view but so far I have not been able to configure with synced records, again because Appian seems to treat "is null" and "related record does not exist" as two different things. I know that I can check for this condition in any individual record using a simple null check, but I'm looking for a way to find the records that meet this condition.