4 Replies Latest reply on Jul 28, 2011 7:09 AM by Heather

    Auto filling a date field with an alternate message

    Heather

      Title

      Auto filling a date field with an alternate message

      Post

      I have a date field that I want to auto fill with the text "no absences" when it is blank.  Is that possible?

        • 1. Re: Auto filling a date field with an alternate message
          philmodjunk

          Not if you intend to keep the field of type "date" like you should as this is text.

          You can, however, put the layout text "no absences" on your layout and use conditional formatting to make it visible only when the date field is empty.

          An expression such as:

          Not IsEmpty ( YourDateFieldHere ) can be used to change the text's font size to 500--which makes it disappear on your layout.

          • 2. Re: Auto filling a date field with an alternate message
            Heather

            I'm intrigued by your answer but I don't know how to apply conditional formatting so I'm not sure where to begin.  Can you explain further or should I consider going about this a different way?

            Here is what I want to accomplish.  I have a number of date fields where I list the dates that my piano students are absent.  At the end of a term, I send a form letter to my students showing which absences (the date fields) are eligible for a make-up lesson.  If the date field is blank, I want it to display the message "no absences".  Would it be simpler to change the date field to a text field and just be careful to enter the dates accurately?  Can you think of a better way to accomplish this?

            Secondly, can I set the field to automatically enter that text "no absences" if the field is empty?  If so, exactly how do I go about that.  I'm unfamiliar with function writing so you'll have to be quite specific.  I experimented with the Auto-Enter field option, typing in the data "no absences" and checking that box.  However, it would only apply it to new records, not existing records.

            • 3. Re: Auto filling a date field with an alternate message
              philmodjunk

              IF you set a date field to automatically enter text, you'll see a ? instead of the date.

              To set a conditional format requires a fairly recent version of Filemaker so you may need to check to see if it's an option for your version.

              Enter layout Mode and use the text tool to add "No absences" to your layout.

              Right click this text (or do the Mac equivalent to bring up the pop up menu) and select conditional formatting. This brings up a dialog where you can click add to add a conditional format and you then can enter the expression I posted earlier and you can then select either a text format or a fill color for the layout object. To specify the font size of 500, click the more formatting option and then enter a custom font size of 500.

              You can also define a calculation field that returns text to substitute text when the date field is empty. This option keeps your date a date, but you use this calculation field on your layouts where you need to display the "no absences" text.

              If ( YourDateField ; GetAstext ( YourDateField ) ; "No Absences" )

              Select "text" as the calculation's return type.

              "I have a number of date fields where I list the dates that my piano students are absent."

              You might want to consider replacing these multiple date fields with a related table of records in a portal. This can add greater flexibility both for reporting and for data entry. (You can now deal with any number of lesson date records rather than a fixed number of fields.)

              • 4. Re: Auto filling a date field with an alternate message
                Heather

                Thank you SO much!  The conditional formatting met my needs perfectly.  I wish I could take your course in "getting the most out of Filemaker"!