3 Replies Latest reply on May 29, 2013 8:52 AM by philmodjunk

    Script step for Last (Payment Date) doesnt work.



      Script step for Last (Payment Date) doesnt work.


           Im using FMP Adv11 on a PC runnin Windows.  I have a file in which I have four payment choices Pd Date1 through Pd Date4 set up as text fields.  I need to know the lastest payment Date so I did a nested If Statement:  If(GetAsText(Pd Date1) > " " and GetAsText(Pd Date2) ="" ; Pd Date 1;  If(Pd Date1) > " " and GetAsText(Pd Date2>" " and GetAsText(Pd Date 3=""; Pd Date2; If(GetAsText(Pd Date1>" " and GetAsText(Pd Date2 >" " and Get asText(Pd Date3>" " and GetAsText(Pd Date4="";Pd Date 3; If(GetAsText(Pd Date1>" " and GetAsText(Pd Date2>" " and GetAsText(Pd Date 3>" " and GetAsText(Pd Date4>" ";Pd Date4))))

           It works for two dates, 3 dates and 4 dates filled in, but NOT for only 1 date, so, you eagle eyed smart guys, what's wrong



        • 1. Re: Script step for Last (Payment Date) doesnt work.

               Why are you using fields of type text instead of fileds of type date? That seems to unnecessarily complicate your situation.

               And have you considered using a related table for the up to 4 dates per parent records instead of 4 separate date fields?

               That would also simplify things here.

          • 2. Re: Script step for Last (Payment Date) doesnt work.


                 I used text fields instead of date because date format for user input is just too fussy. How could I set up a related table ?  I still don't see why the first If statement doesn't work. 



            • 3. Re: Script step for Last (Payment Date) doesnt work.

                   You really, really need fields of type date. "Too fussy" has me puzzled. Data entry for fields of type date seems very straight forward to me whether you use a calendar pop up or just a straight edit box.

                   Without a field of type date for each date, you risk data entered that is either ambiguous or for invalid dates without any built in traps to catch the data entry errors.

                   Plus, text fields cannot be properly evaluated to determine which date is the most recent (What I assume you mean by "last") date.

                   WIth fields of type date:

                   Max ( DateField1 ; DateField2; DateField3; DateField4 )  --if you have individual fields


                   Max ( RelatedTable::DateFIeld ) -- if you use the related table I have suggested

                   will return the most recent date in the date fields.

                   To set up a related table, you need a relationship such as:


                   OriginalTable::__pkOriginalTableID = DatesTable::_fkOriginalTableID

                   You can then use a portal to DatesTable or a set of one row portals to DatesTable to display the dates on your layout.

                   And with a related table of dates--with fields of type Date to store the dates, either a sorted relationship or a sorted portal that sorts by Date in Descending order can also be used to display the most recent date.

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained