11 Replies Latest reply on Oct 27, 2009 4:56 PM by chant50

    Show day names using script and value lists

    chant50

      Title

      Show day names using script and value lists

      Post

      Hi everyone! First time posting and I'm a newbie to forums and Filemaker Pro 10. I've used FM Pro 8.5 but not very well I'm afraid. I'm using Windows XP, FM Pro Advanced 10.03 and as much grey matter as I can muster!

      Here's my question:

      I have (or want to have) 14/15 fields named something like Day1, Day2, Day3, and so on. I want to have a drop down list with the months listed by either name (january, February, February) or number (Jan = 1, Feb = 2, and so on) and a drop down list with the years (2009, 2010, 2011, 2012, and so on). These will be 2 different lists and I can create them. When I select say Jan 2010 I would like the first Day1 field to show the first Monday, Wednesday, or Friday [as TEXT not as a number] of that month whichever it happens to be (as "M", "W", "F", if possible) Then if the first field shows Monday the next would show Wednesday then the next would show Friday. The fourth field would then show Monday again and so on until all fields needed are filled in.  Checking my calendar shows that the first day (of the 3 I'm interested in) in Jan 2010 is a Friday so my fields would be F....M....W....F....M.....W....F until you run out of days named either Monday, Wednesday, or Friday. Is this even possible? Can anyone help me or show an example so I can expand upon it?

      Thanks a lot

        • 1. Re: Show day names using script and value lists
          philmodjunk
            

          "...14/15 fields named something like Day1, Day2, Day3..."

          Can't tell without knowing a lot more about how you plan to use these fields, but a restructruing of your database to place date fields in a related table may make for a much easier to work with database design.

           

          With regards to your specific question:

           

          Let's use the month number option for simpler, calculations. If you really want month names, you can set up a case function to translate month names into month numbers.

           

          Let's call your two drop down formatted fields "MonthNumb" and "SelectedYear".

          We'll use two calculation fields for each day--one returns the letter you want and one computes the date which can be referenced in the next "day" calculation to form a chain of connected calculations.

           

          First field, "Day1Date", define it as a calculation returning a date.

          Let ( [ D1 = Date ( yourtable::MonthNumb ); 1 ; Yourtable::SelectedYear ) ; DW = DayofWeek (D1) ] ;

                 Choose ( DW ; 0 ; D1 + 1 ; D1 ; D1 + 1 ; D1 ; D1 + 1 ; D1 ; D1 + 2 ) )

           

          2nd field: "Day1Label", define as a calculation returning text.

          Left ( DayName ( Day1date ) ; 1 )

           

          3rd field: Day2Date

          Let ( [ D1 = Day1Date ; DW = DayofWeek (D1) ] ;

                 Choose ( DW ; 0 ; D1 + 1 ; D1 ; D1 + 1 ; D1 ; D1 + 1 ; D1 ; D1 + 2 ) )

          /* extra space inserted between colon and "Day1Date" to avoid emoticon, remove when pasting into filemaker. */

           

          and so forth, but with the reservations noted at the beginning of this post.

          • 2. Re: Show day names using script and value lists
            chant50
              

            philmodjunk,

            Thanks for trying to help me with such little information. I have an example of what it is I'm trying to do but I can't get it to show the day names only the date. Your explaination probably makes sense to most but I'm lost. Are the field you are talking about the same as what I describe as Day1, Day2, and so on? How would these be connected to a drop down value list? Is there any way for me to send you the sample that I have and then you advise me how to change it? I don't want to post it on the web if possible but could send it via e-mail if that's okay. If not I understand. I don't think I'm good enough to acomplish this yet on my own.

            Any and all help is greatly appreciated.

            Thanks

            • 3. Re: Show day names using script and value lists
              philmodjunk
                

              In the example I posted, "Day1Label" corresponds with your "Day1" field. The Day1Label, Day2Label, Day3Label... fields are the fields you would put on your layout to get "M", "W" or "F". In that calculation, yourtable::MonthNumb and Yourtable::SelectedYear are the names of the fields you would set up with Month number and Year value lists.

               

              The Day1Date field computes the date that Day1Label uses to return an M, W or F. It is then used by the a "Day2Date" field to compute the next Monday, Wednesday or Friday date. I am leaving it up to you to continue the "chain" of fields for each of your subsequent day labels.

              • 4. Re: Show day names using script and value lists
                chant50
                  

                philmodjunk,

                Thanks! I'm not sure if I'll acomplish it totally without bothering you again but this certainly gives me a great start. I really appreciate your help.

                Thanks

                • 5. Re: Show day names using script and value lists
                  chant50
                    

                  philmodjunk,

                  Well, it didn't take long for me to get lost! I made the first fields "Day1Date" and "Day1Label" and the value lists. It works very well with every month I try. It does exactly what I want in the way I want. Yay!  Then I made the second field "Day2Date" and "Day2Label". I copied the calc from your e-mail changing only the yourtable references and I only get ? marks in both of the new fields. 

                   

                  Here's what I have in my calculation with date as a result for Day2Date:

                   

                  Let ( [ D1 = Date (Day1Date ; 1 ; Selected_Year ) ; DW = DayOfWeek (D1) ] ;

                         Choose ( DW ; 0 ; D1 + 1 ; D1 ; D1 + 1 ; D1 ; D1 + 1 ; D1 ; D1 + 2 ) )

                   

                  Then I have this calculation with Text as a result for my Day2Label :

                   

                  Left ( DayName (Day2Date) ;1)

                   

                  I also didn't understand the /* extra space inserted between colon and "Da1Date" to avoid emoticon, remove when pasting into Filemaker.*/   but when I copied the calculation into Filemaker it kept saying " There are too few parameters in this function" until I took out the right ) from behind the "Day1Date" part of the calculation. Then it seems to work for the first field "Day1Label" but no further.

                  Could you shed some light on what I did wrong, please?

                  Thanks

                   

                   

                  • 6. Re: Show day names using script and value lists
                    philmodjunk
                      

                    My mistake,

                     

                    After editing, Day2Date should look like this:

                     

                    Let ( [ D1 = Day1Date ; DW = DayofWeek (D1) ] ;

                           Choose ( DW ; 0 ; D1 + 1 ; D1 ; D1 + 1 ; D1 ; D1 + 1 ; D1 ; D1 + 2 ) )

                     

                    in each subsequent "Day_date" calculatation, you'll refer to the previous Day_Date field.

                    • 7. Re: Show day names using script and value lists
                      chant50
                        

                      I changed the calculation and instead of ? I get the same result in Day2Date as in Day1Date. Below is both field calculations in case I've made an obvious mistake in one or both. I'm starting to understand the concept of chaining calculations but I guess not well enough!

                       

                      Day1Date 

                      Let ( [ D1 = Date ( Month_Number; 1 ; Selected_Year ) ; DW = DayOfWeek (D1) ] ;

                             Choose ( DW ; 0 ; D1 + 1 ; D1 ; D1 + 1 ; D1 ; D1 + 1 ; D1 ; D1 + 2 ) )

                       

                      Day1Label

                      Left ( DayName ( Day1Date ) ;1)

                       

                      Day2Date

                      Let ( [ D1 = Day1Date ; DW = DayOfWeek (D1) ] ;

                             Choose ( DW ; 0 ; D1 + 1 ; D1 ; D1 + 1 ; D1 ; D1 + 1 ; D1 ; D1 + 2 ) )

                       

                      Day2Label 

                      Left ( DayName ( Day2Date) ;1)

                       

                      I actually hope you spot a mistake so I can fix it.

                       

                      Thanks, You've been a great help.

                      • 8. Re: Show day names using script and value lists
                        philmodjunk
                          

                        Drat it! I see my mistake. The way I wrote it, if the date falls on a Monday, Wednesday or Friday, the date won't change. Try this for Day2Date and subsequent dates:

                         

                        Day2Date

                        Let ( [ D1 = Day1Date ; DW = DayOfWeek (D1) ] ;

                               Choose ( DW ; 0 ; D1 + 1 ; D1+ 2 ; D1 + 1 ; D1 + 2 ; D1 + 1 ; D1 + 3 ; D1 + 2 ) )

                         

                        In case you haven't looked it up, DayofWeek returns a number, 1 = Sunday, 2 = Monday, ... etc. That's the value being fed into the Choose function.

                        • 9. Re: Show day names using script and value lists
                          chant50
                            

                          philmodjunk,

                          Well, it works for the first two for sure! You can tell I'm pleased, I'll bet. While I have you teaching me could you further explain the HOW it knows to pick the fist Monday, Wednesday, or Friday instead of just the first day of the month? I see the chain calculation happening and I see adding days to previous dates with the D1 + 1 but I can't wrap my mind around the whole thing. I'm thinking if I understand this better I could change it (if needed) to return other day names or sequences if in the future I need it.

                          I really appreciate your help and patience. The fact that you're also teaching me is super!

                          Thanks

                          • 10. Re: Show day names using script and value lists
                            philmodjunk
                              

                            As I already explained, DayofWeek returns a number that represents the day of the week, Sunday = 1, Monday = 2, Tuesday = 3,...Saturday = 7.

                             

                            The choose function takes the DayofWeek number (DW) and returns 0 if DW = 0, date + 1 if DW = 1 (Sunday), Date + 2 if DW = 2 (Monday) and so forth.

                             

                            You can compute new dates by adding and subtracting days because date fields actually count the number of days elapsed from an early, arbitrary date. Thus, adding one to a date like May 5, 2009 will change it to May 6, 2009.

                             

                            Make sense?

                             

                            You can also look up each of these functions in filemaker help to learn more and see more examples on how they're used.

                            • 11. Re: Show day names using script and value lists
                              chant50
                                

                              Thank you very much, sir. I made the entire month up and it works flawlessly. I really appreciate all of the time and effort you have given me. I believe you have answered everything I can think of and more. Kudos for sure!

                              Thanks!