13 Replies Latest reply on Dec 19, 2012 7:14 AM by LaRetta

    Input selection

    fmk4

      Hi,

       

      I am trying to have a drop-down-list of the games available according to Table 1 when I create a new entry in Table 2

       

      Example,

       

      In Table 2, I will select the team, and the drop-down-list in date_of_game (linked to Table 1) would show me the date (date_of_game) that the team i have selected that is available for play (bet).

       

      Table 1

      date_of_game

      team

       

      Table 2

      team

      date_of_game

      bet

       

      I tried linking Table 2 to Table 1 using just date_of_game and also date_of_game and team. Both does not get the right result.

       

      Any advise?

       

      Thanks!

        • 1. Re: Input selection
          comment

          If you don't mind, I will rename your tables to:

           

          Fixtures:

          date_of_game

          team

           

          Bets:

          team

          date_of_game

          bet

           

          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.

           

           

          Note:

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

          • 2. Re: Input selection
            fmk4

            Hi,

             

            Did just that, but I got a "?" when I did the date_of_game selection. Strange!

            • 3. Re: Input selection
              comment

              Are both date_of_game fields defined as Date fields?

              • 4. Re: Input selection
                fmk4

                I changed the fields to Text field and it work!

                 

                But when I change it back to Date field, I get the "?" again.

                • 5. Re: Input selection
                  comment

                  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.

                  • 6. Re: Input selection
                    fmk4

                    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.

                    • 7. Re: Input selection
                      comment

                      You haven't added anything new. My previous suggestions still apply.

                      • 8. Re: Input selection
                        johnbuckingham

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

                        • 9. Re: Input selection
                          comment

                          johnbuckingham wrote:


                          traditionally, FileMaker seems to be most compliant when the date format is US.

                           

                          No, no, no .. please don't start another rumor.

                          • 10. Re: Input selection
                            johnbuckingham

                            Michael

                             

                            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.

                            • 11. Re: Input selection
                              comment

                              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.

                              • 12. Re: Input selection
                                johnbuckingham

                                Michael

                                 

                                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.

                                • 13. Re: Input selection

                                  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.  :-)