I need a tabular report DataPage that shows all records from table A that DON'T have a corresponding record in TableB with a date after today.

[BrianI]

I have TableA with a one to many relationship with TableB

TableB has a date field.

I need a tabular report DataPage that shows all records from table A that DON'T have a corresponding record in TableB with a date after today.

 

I thought this would be really easy but for some reason I can't see it.

 

Thanks for any hints.

[Volomeister]

Hi @BrianI

Here is what you can do:

1. Add a checkbox field to your Table A. This checkbox will indicate whether there are related records in Table B
2. Add triggered actions on Table B's insert, update, and delete events. These triggered actions will check or uncheck a checkbox on a related Table A record based on your specified condition.
3. Add an application task that will run once a day to loop through all records in Table A and check if they have related records in Table B with a date after today. 
4. Build a view based on Table A to filter out records based on the checkbox.
5. Build report DataPage based on this view

[BrianI]

Hi @Volomeister

Thanks for the reply. I implemented something very similar which is working! I'm surprised something which could be a simple sql query ends up being so convoluted but there it works.

Cheers

Brian