Using a date in a relationship

[gummy]

Hello everybody.

 

I'm designing a really simple little database to manage communication campaigns per town.

It has a clients table and campaigns table. 

 

I am trying to show the campaigns for the same town but different clients, and the catch is I do not want the expired campaigns to show up. So I created a relationship using the town and a global date field (with current date automatically inserted) to keep only the the campaigns for the same town and whose date is after today. 

 

At least that is how I thought it would work, however it does not. All of the campaigns for the town show in the external table, regardless of their date.

 

The global field and the date field both are in date format, besides that I have no clue as to why it is not working.

 

Any advice ?

 

Thanks 

[David McQueen]

Hello everybody.

 

I'm designing a really simple little database to manage communication campaigns per town.

It has a clients table and campaigns table. 

 

I am trying to show the campaigns for the same town but different clients, and the catch is I do not want the expired campaigns to show up. So I created a relationship using the town and a global date field (with current date automatically inserted) to keep only the the campaigns for the same town and whose date is after today. 

 

At least that is how I thought it would work, however it does not. All of the campaigns for the town show in the external table, regardless of their date.

 

The global field and the date field both are in date format, besides that I have no clue as to why it is not working.

 

Any advice ?

 

Thanks 

 

I'll take a stab here. It appears that the date part of the relationship is not working from your description.

 

Make sure both the town name and the date you are relating to are indexed. To be clear you appear to be relating from global fields in one table to a campaigns table with both a date of the campaign and a location. The globals, (the fields you are looking from)  cannot be indexed by definition. The campaign fields (the fields you are looking to) must be indexed for the relationship to work.

[gummy]

Ok, I knew that global fields can't be indexed but I forgot about that.

 

However what I didn't say is that the town field used in the relationship is also a global, since a client can have several campaign for several towns and I want to be able to narrow the selection to just one town at a time.

And with the town field the relationship works fine, even though it is a global.

 

Anyway, I modified the date field to a normal calculation field, and now nothing shows up at all!

 

 

For those who would like to have a look I've attached the file in its previous half-working state. Be warned it's in French.

Ok sorry to have wasted your time here.

I've found the solution, somehow using the global field on the other side of the relationship works. I'm not sure why it doesn't the other way round, it doesn't seem logical to me.

But anyway it now works, so for those who are using globals in relationships, check out which way you define them.

Distrib.zip

[Rick Whitelaw]

Match fields on the child side of the relationship must be able to be indexed.