I think a self join figures in somewhere
I think so too. But first you need to add an unstored calculation field cToday (result is Date) =
Then the self-join can be defined as:
YourTable::cToday ≥ YourTable 2::Start Date
YourTable::cToday ≤ YourTable 2::End Date
Set the value list to use values from YourTable 2, include only related values starting from YourTable.
This makes absolute total sense, but still I get no values in my dropdown box. I checked the date, it finds today's date and today's date is in the range that I'm looking for, but no targetvalue. I did make a relationship between the targetvalue in Table 2 and the dropdown field, should I do that relationship somewhere else?
I am afraid I don't quite follow. Please provide a wider context for what you are doing: which tables are involved, where is the field that is used for selecting the value, etc. Using meaningful names for the tables and fields would also help. This part I don't get at all:
I did make a relationship between the targetvalue in Table 2 and the dropdown field
Table 1 Table 2
Run Date cDate
Relationships: cDate >= Run Date AND cDate <= End Date
Source = SourceCode
What I want is for SourceCode to be a dropdown box that pulls up all of the values from Source that are in the date range. I'll then choose the correct Source to populate the SourceCode Field. Perhaps I should store the final value in another field... yes?
I forgot the Value List info: SourceCode is controled as a drop down box using the Value list Source Code, which uses values from field Source from the first table and Includes only related values starting from Table 2, as you suggested. I get no returns in my dropdown box with this set up.
If I understand correctly, you need two relationships here. One is based on matching:
Table 1::Source = Table 2::SourceCode
This relationship will function AFTER you have selected the SourceCode value in Table 2.
You need another relationship to narrow down the values available for selection. This will be the relationship described in my first post, where YourTable would be Table 1, and YourTable 2 would be a second occurrence of Table 1 (some meaningful names you have picked…).
Heh, Table 1 is tAdSchedule - where I have a list of advertising running and dates that they are active. Table 2 is tLead where we are setting a sales lead. I want tLead to tell me which ad the customer is responding to, but I only want the dropdown box to be populated with current ads, not ads that have expired or aren't running yet.
I took out the relationship between tLead::Source and tAdSchedule::Source Code so now the only relationship is the dates deal. Still no results in my dropdown box. There are three values in tAdSchedule that match the criteria as far as dates. I should see a value list of the three Source Code values that are in the current date range. Thanks for your patience :)
Hard to see from here what went wrong there. Try putting a portal to the tAdSchedule table on a layout of tLead and see if it shows the records that should be related. Also check your field types.
Good call on checking my field types... All that, and my dates were still set as text. Still couldn't have found it without you, good day to you!
New issue, when I change the value in tLead::Source Code to choose it, it changes the first related value in the tAdSchedule::Source to whatever I chose.
Ex. dropdown says "Newspaper1" "Newspaper2" "Newspaper3" - it is set to "Newspaper1" but I choose "Newspaper3". Now my dropdown box only has 2 and 3 in it, and the value in tAdSchedule::Source "Newspaper1" is Changed to "Newpaper3".
There is no relationship between tLead::Source Code and tAdSchedule::Source
It sounds like the field you are using to make the selection isn't what you think it is.
I sure appreciate your help, but I have no idea what that means... Can you be more specific? Do I have to have one field to be a drop down box then have the value go into another field for storage? I'm not sure how this "chooser" is changing the value in table tAdSchedule.
Nevermind my friend, I believe I have it licked...