If this is true expression for a report

Scott_Hall
Participant V

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.

0 10 286
10 REPLIES 10

LeventK
Participant V

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

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.

So you can easily create a LOOKUP() expression:

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

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

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

@LeventK The table names are Collection Event and Disposal Event

Disposal Event has a related collection event.

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

@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()))))

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.

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

Top Labels in this Space