Jump to content
  • 0

List records without a specific relationship



10 answers to this question

Recommended Posts

  • 0

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 :( 

Link to comment
Share on other sites

  • 0

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:

LEFT JOIN A_LeftJoinTest B on B.UserID = A.UserID AND B.Type = 'Type_2'

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


Hope this helps!


Edited by DrSimi
Removed duplicate screenshot of "A_LeftJoinTest" Table.
Link to comment
Share on other sites

  • 0

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.




Link to comment
Share on other sites

  • 0


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.

Link to comment
Share on other sites

  • 0

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...


A shift record....


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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...