Logic Statements on Contains Function

[Andrew_UK]

Hi All,

I need a formula which tells me if another table contains a row which matches my criteria (if it helps I expect either 1 match or none)

Table 1 includes a tab which shows Invoice Month

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Invoice Month[/TD]
[TD]Other Data[/TD]
[TD]Discount Entitled[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table 2 shows whether or not this customer is in

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Discount Start[/TD]
[TD]Discount End[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So for instance the discount entitlement needs to read something like True/Fals and I imagine the formula will be along the lines of...

Contains(Table2, Customer, Customer, Invoice Month >= Related(Discount Start), Invoice Month <= Related(Discount End))

But I can't seem to get it working. The two tables are linked by customer

Thanks in advance for your help!!

 

[Ozeroth]

An "Events in Progress" pattern should apply here.

You could adapt Chris Webb's pattern here.
Also discussed in this paper on SQLBI (see page 27 for the optimized pattern).