If you don't mind, I will rename your tables to:
Now, if you:
(1) Define a relationship;
Bets::team = Fixtures::team
(2) Define a value list using values from Fixtures::date_of_game, include only related values starting from Bets,
then once you have selected a team in Bets, the value list will show only dates of fixtures for that team.
The relationship described here is an auxiliary relationship created only for the purpose of the value list. The "real" relationship between the two tables should be based either on both team and date_of_game, or - preferably - a unique FixtureID (this is assuming one fixture can have many bets).
Did just that, but I got a "?" when I did the date_of_game selection. Strange!
Are both date_of_game fields defined as Date fields?
I changed the fields to Text field and it work!
But when I change it back to Date field, I get the "?" again.
Well, they should be Date fields, if they hold dates. It sounds like there may a problem with your data (hard to be sure when flying blind). Did you import the dates? I suggest you go to the Fixtures table and do a find for ? (a question mark) in the date_of_game field. Any records found have invalid dates that need to be fixed.
At this moment the data is pretty small, couple of records.
Its just a weird thing going on here, that it only works when the field is text, and not date.
You haven't added anything new. My previous suggestions still apply.
I recently encountered a similar issue where a join between two table occurrences involving a date field didn't appear to work. Substituting an indexed calculation field in the TOs either side of the join, where the formula was getasnumber ( theDateField ), result being a number and storage set to indexed, was successful. There were other elements to the join and the calculation worked; I didn't investigate further. I suspect it may be that I'm using UK date format; traditionally, FileMaker seems to be most compliant when the date format is US. Maybe that will be worth a try - and it would be interesting to know, if it works, what date format you are using...
traditionally, FileMaker seems to be most compliant when the date format is US.
No, no, no .. please don't start another rumor.
I'm not sure if your comment is supposed to be humorous. If so, fine.
If not, then what do you mean? I am stating observed behaviour to try to help a colleague who is asking a question in a closed forum. Not trying to 'start another rumor'. Table occurrence joins involving date fields do not work for me in FM11 or FM12. That's not a rumor it is a repeatable fact: And the fact that you're spelling 'rumor' in the American way suggests that you may not have experienced some of the problems that have been unearthed over the last 20 years in respect of non-US date formats.
No, I am afraid it's not supposed to be humorous at all. Over the years, I have encountered many such "helpful hints" circulating in the Filemaker community that turned out to be completely baseless. Usually they were the product of encountering a problem, bypassing it somehow, then drawing a wrong conclusion.
As you have mentioned yourself, you did not investigate the issue. I am quite sure Filemaker has no bias towards the US date format (other that the order of arguments of the Date() function) - at least I haven't observed any so far. I guess this would also be a good place to mention that the conclusion you have drawn from my spelling is also completely baseless (and quite humorous, I should add...).
Now, instead of discussing you or me, I suggest "unearthing" the real issue here: you say that "it is a repeatable fact". That's a good start; why don't you provide us with details on how to reproduce the problem. Then we can see what's the real cause of it.
Thanks for your response. I am under the hammer of getting paid work finished for the New Year at present. Watch this space and I'll get back on this thread when I have a moment.
In the meantime, it would be interesting (for me, anyway) to know if my suggestion works for the OP.
Johnbuckingham said, "Table occurrence joins involving date fields do not work for me in FM11 or FM12. That's not a rumor it is a repeatable fact:"
It may be repeatable to you, John, but it certainly is NOT fact. Date fields work perfectly in joins.
fmk4, your problem with dates should be addressed. Solve the REAL issue ... do not mask the problem ... the ? Is telling you that your dates are invalid. You can attach your file if you get stuck. :-)