7 Replies Latest reply on Mar 26, 2012 3:11 PM by philmodjunk

    Caculated field based on arrival and departure

    LisaSheard

      Title

      Caculated field based on arrival and departure

      Post

      I am trying to create a caculated field called Thursday night.  I want it to be based on the following: accomodations is a yes, they are arriving on Wednesday or Thursday AND not departing on Wednesday or Thursday - basically they need a room for Thursday night.  Here is what I have that is not working.  I am fairly new to Filemaker and am working with a database that someone else created.  Any help would be appreciated. 

       

      filemaker.jpg

        • 1. Re: Caculated field based on arrival and departure
          philmodjunk

          iS arrivedate a text field or a field of type date? What you have set up only works if that field is of type text. If it is of type date, you need to use:

          DayName ( Registration2012::arrivedate )  to get the text naming the day of the week.

          It would appear your logic needs adjustment also:

          Put this inside the first If function...

          Let ( [ adn = registration2012::arrivedate ; // or dn = dayname ( registration2012::arrivedate )
                    ddn = registration2012::departuredate ; // or ddn = dayname ( registration2012::departuredate )
                 if ( patterncount ( "WednesdayThursday" ; adn ) and not patterncount ( "wednesdaythursday" ; ddn ) ; "yes" ; "no" )
                )

          note; there's nothing wrong syntaxwise with using:

          adn = "Thursday" or adn = "wednesday"

          patterncount( "WednesdayThursday" ; adn ) just makes for a simpler expression here.

          • 2. Re: Caculated field based on arrival and departure
            LisaSheard

            Thanks PhilModJunk,

            The arrive date and the depart date are both text fields and the accomodation field is a yes/no.  I have tried what you suggested and ended up with this below.  Filemaker won't accept it - the error is that List usage is not allowed in this calculation.  Cry 

            • 3. Re: Caculated field based on arrival and departure
              philmodjunk

              Hmmm, I see a typo in my example. Make this this:

              If ( Registration2012::accomodation = "Yes" ;
                    Let ( [ adn = registration2012::arrivedate ;
                              ddn = registration2012::departuredate
                            ];
                           if ( patterncount ( "WednesdayThursday" ; adn ) and not patterncount ( "wednesdaythursday" ; ddn ) ; "yes" ; "no" )
                          ) ;
                     "No" )

              • 4. Re: Caculated field based on arrival and departure
                LisaSheard

                That did it!!  I cannot thank you enough. Since I only partially understand why it worked, I have learning ahead of me.  But at least I don't have to deal with this problem while trying to figure things out.  I am very grateful!

                • 5. Re: Caculated field based on arrival and departure
                  philmodjunk

                  Using a calculation and not understanding how it works, lays the groundwork for problems should you ever need to modify this caclulation in the future...

                  I suggest looking up any unfamiliar functions such as Let and PatternCount in FileMaker help. If you still have questions, feel free to ask them here in the forum.

                  • 6. Re: Caculated field based on arrival and departure
                    LisaSheard

                    You are right and I plan on rectifying that.  In the next month or so I plan on learning this system inside and out.  Thanks for the heads up on Let and PatternCount.  I will make sure those are in my curriculum.  I appreciate places like this forum so much and I will try not to wear out my welcome.  Thanks again!

                    • 7. Re: Caculated field based on arrival and departure
                      philmodjunk

                      Helping out the "newbies" is what this forum is all about.Your continuing questions cannot wear out your welcome.

                      I'm directing you to the help file, not as a way to "scold" you in anyway but rather as my personal effort to help you teach yourself so that you can move forward more quickly in your efforts to master FileMaker Pro database design.