I have three tables, SpeakerEvent, ParishEvent and a junction table that joins SpeakerEvent to ParishEvent, JNC_Speaker_Event_Parish_Event
I need an Appian query that can tell me which SpeakerEvents have not been assigned to a Parish as well as another query that will tell me a Parish has not be assigned to a SpeakerEvent.
If I were creating a view in SQL, this would be done easily with a left join and checking to see if the ParishEventId is null.
SELECT SE.* FROM `PMSO_SPEAKER_EVENT` AS SE LEFT JOIN `PMSO_JNC_SPEAKER_EVENT_PARISH_EVENT` AS JSP ON SE.SPEAKER_EVENT_ID = JSP.SPEAKER_EVENT_ID WHERE JSP.PARISH_EVENT_ID IS NULL
In Appian, I am trying to create an Expression Rule to do the same thing. I tried several version of using a a!queryFilters as well as a!queryLogicalExpression. I'm guessing that it has to do with the fact that there are no records on the other end of the relationship (JNC_Speaker_Event_Parish_Event).
if( a!isnotNullOrEmpty(ri!speakerId), a!queryRecordType( recordType: 'recordType!PMSO Speaker Event', fields: { 'recordType!PMSO Speaker Event.fields.speakerId', 'recordType!PMSO Speaker Event.fields.speakerEventId', 'recordType!PMSO Speaker Event.relationships.event.fields.event', 'recordType!PMSO Speaker Event.relationships.jncSpeakerEventParishEvent.fields.parishEventId' }, pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 1000), filters: a!queryLogicalExpression( operator: "AND_ALL", filters: { a!queryFilter( field: 'recordType!PMSO Speaker Event.fields.speakerId', operator: "=", value: ri!speakerId ), a!queryFilter( field: 'recordType!PMSO Speaker Event.relationships.jncSpeakerEventParishEvent.fields.parishEventId', operator: "is null" ) } ) ).data, null() )
No errors are thrown, just no data and I know that there are records that satisfy this logic.
Any guidance here would be most appreciated.
Discussion posts and replies are publicly visible
Did you try just doing an "is null" on the relationship?
i.e.
Yes. The output is null and I know there are records in the DB that would satisfy this condition.
Maybe I'm misunderstanding what you are trying to do but I replicated your structure and this works for me.
Here is what I have:
I put a 1-n relationship from Speaker_Event -> Speaker_Parish_Event on the parishEventId.
This returns any speakerEvents that have no associated parishEvents.
Parish Event
Speaker Event
Speaker Parish Event
My query returns Speaker Event 6.
Thanks. Mine is working now as well, I copied your format. I was referencing the field in the junction table rather than the table itself as you show in your example.