List records without a specific relationship

[BrianI]

Hi

I have 2 tables. Staff and Notes. The notes table has a type field.

I am trying to create a report which shows all staff records that DO NOT have a related record in Notes with a note type of X.

[CoopperBackpack]

Hello @BrianI,

Could you provide more details about the design of the tables? Does the Notes table store the ID of the staff member?  
Does the Notes table look like this?

[BrianI]

Hi @CoopperBackpack

 

Yes that's about it.

Thanks

[NiceDuck]

Are you making a DataSource for it?

Try a view with this setup:

[NiceDuck]

If you want to send an email containing a list, this could work:

[NiceDuck]

Or this: 

[BrianI]

Hi @NiceDuck Thanks for the suggestions.

The Left join solution was the first thing I tried but it doesn't work. Adding a criteria in the left table prevents even a blank record being returned. So you can't use where type = X and expect a blank record being returned.

I do not need to send emails.

It seems the only way of doing this is to either load all the records in the datapage report and then use a select query to identify which have type=X and then hide those rows. Very inefficient

OR to create another table and use a task to populate it with the data required. No good for accurate on demand reporting and not at all clean in my mind.

It is disappointing as I thought this would be something easy  

[DrSimi]

Hi @BrianI,

@NiceDuck's suggestion is the closest to your needs, but the filter has to be in the "JOIN" for it to work, not in the WHERE. Since we cannot add hardcoded/static values in the View JOINs in Caspio, you can add a Formula field to your Staff Table with the "type" (X) value you want to use to filter hardcoded. For example, here's my users table:

 

Then when creating the View, use this to filter as well as the ID:

This does yield the expected result of NOT showing UserID 1 and 3 in the View since they already have a record with 'Type_2'.

The downside here is that the field will have to live in your Staff Table and you would have to create other fields if you have to present different reports. Another alternative is using pure SQL in Report DataPages for real-time visualization. The view above would translate into:

SELECT TOP 1 A.UserID
FROM USERLOGIN_TEST_1_1_test A
LEFT JOIN A_LeftJoinTest B on B.UserID = A.UserID AND B.Type = 'Type_2'
WHERE B.UserID IS NULL 

Though it only includes 1 result. To present multiple values in the DataPage, you can see this post: 

 

Hope this helps!

Edited July 2 by DrSimi
Removed duplicate screenshot of "A_LeftJoinTest" Table.

[BrianI]

Hi @DrSimi, Thanks, that's a neat little solution and it works for this use case.

I also have another report that uses multiple criteria where this method will become impractical. So still working on a solution there.

 

Cheers

Brian

[DrSimi]

@BrianI,

An alternative I can think of is creating a Separate search and Report. Separating Search Page and Results Page into Two Different Web Pages. In the Search DataPage, use a Calculated Value and write a SQL formula with the criteria that will output IDs with " OR " between each value, i.e. "A OR B OR F OR H OR Z"... You will have to set the receiving filter in the Report DataPage to "Contains", see Passing Multiple Values in One Parameter.

I am not sure if this will work with many results output from the formula though, but I think it should.

[BrianI]

Thanks @DrSimi

Another challenge I faced with this app is that I needed a list of Staff that do not have future shifts.

A staff record would be...

staff_id
name
...

A shift record....

shift_id
staff_fk
date
...

So I need a list of all staff that do not have a related shift record where date >= today.

Extending the ideas from above I added a field to the shift record called future and made it type yes/no

You cannot use a reference to today in a formula field on a table so I created Task that runs once a day just after midnight that sets the future field to yes if the shift date is today or later...

Then I added a field to the Staff table, I called it true_bit_filter (as I might need to use it for other filters as well). It needs to be set to Yes for all records and used in the view as a join. To do that create a formula field and set it to...

      CAST(1 AS BIT)

Then create the view and join the staff_id and future check box as left outer.....

Finally add a filter to return only records where shift date is blank.

Hope that helps somebody else.