Database view NULL where clause to show ALL records in a table

Vinicius Luz1 · ‎09-18-2024

Hi,

I have this setup

I want the case table to show all the records inside of it - Please note that I already have the 'left join' as true ...

How can I achieve it - If it is possible

Brad Bowman · ‎09-18-2024

I attempted to re-create this in my PDI. In a scoped app table I added a column that's a reference to the case table then setup the Left join and Where clause I suggested where case_sys_id=<<my table column>>. I was only seeing the 5 records I created in the scoped app, 4 of which had the case reference column populated - which would be a left join on the custom table. I switched the order to put case first, with the Where clause on the 200/custom table and it works as expected.

View solution in original post

Brad Bowman · ‎09-18-2024

Change the Order on case to 200, move the Where clause to the case table, and switch it so that case is to the left of the =, then the left join will show all records on the case table, even if there is not a related record on the other table.

Vinicius Luz1 · ‎09-18-2024

Hi Brad,

Thanks for the help so far, this is what I've tried

Result: Its showing all the records on the time entries table but not all the cases (not even 1 case is coming)

Just FYI - the Case table has a (1-n) Relationship with Time Entries / Time entries tables has a (1-1) relationship to Case table

Brad Bowman · ‎09-18-2024

This is the correct use of left join. Your issue with this view is likely that it is in a scope, uses a custom table in a scoped app, and/or the table in the Customer Service app. Make sure your table/app has cross scope access, and try creating the view in the Global scope.

Vinicius Luz1 · ‎09-18-2024

Yep , unfortunately no success ...

I thought Database view could do that...
Its showing ALL the time entries and NO case - Both apps have Cross Scoped Privs (Read) to each other