If this is true expression for a report

[Scott_Hall]

I have a table - Collection Event and a table - Disposal Event. Each collection event is eventually assigned a Disposal Event.

Report is based on the Collection Event table.

I need an expression in the “If This is True” dialogue box of the Collection Event table that fires the report if Column [Timestamp] column type Date in the associated Disposal Event table is equal to today().

I’ve tried a range of expressions and no luck. Many thanks to anyone who can help. Stay safe.

[LeventK]

Are these 2 tables connected to each other somehow? Is there a reference between them?

[Scott_Hall]

Yes. In the Collection Event able the user selects the appropriate disposal event. When viewing the Disposal Events the related collection events are listed. I hope that helps.

[LeventK]

So you can easily create a LOOKUP() expression:

TODAY() = LOOKUP([_THISROW].[CollectionID],"Disposal Event","RefColumnName","Timestamp")

[Scott_Hall]

@LeventK Thank you. So the “RefColumnName” is [Disposal Event]. This is the column in the Collection Event table that is used to assign/ link it to the disposal event. When I use the expression TODAY() = LOOKUP([_THISROW].[CollectionID],“Disposal Event”,“Disposal Event”,“Timestamp”) I get the message that it cannot find column “Disposal Event”.

[LeventK]

What’s your table name? LOOKUP() expression should take below parameters:
LOOKUP(Search string, TableName, LookupColumnName, ReturnColumnName)

[Scott_Hall]

@LeventK The table names are Collection Event and Disposal Event

Disposal Event has a related collection event.

[LeventK]

@Scott_Hall
I have no any idea about your table schema and column names. I have given the LOOKUP structure above, so you can adjust the expression as per your table schema and column names I believe.

[Scott_Hall]

@LeventK Thank you. I’ve used this expression below. But am wondering if it will work since the [Timestamp] column in the Collection Event table is type DateTime. It does come up as a valid expression. This is for the “if this is true” dialogue box for table Disposal Event.

Today() = LOOKUP(Today(), “Disposal Event”, “Timestamp”, “Timestamp”)

(TODAY()) is equal to (One randomly chosen value from this list (
…The list of values of column ‘Timestamp’
…from rows of table ‘Disposal Event’
…where this condition is true: ((The value of column ‘Timestamp’) is equal to (TODAY()))))

[Steve]

Scott_Hall:

But am wondering if it will work since the [Timestamp] column in the Collection Event table is type DateTime.

Have you tested it? Beyond the syntax check done by Expression Assistant? Testing is a good way to find out if it works or not.

[Scott_Hall]

@Steve Thanks Steve. Wasn’t very familiar with the testing feature but found it and will do.