DBusman Posted September 18, 2018 Share Posted September 18, 2018 I'm using a script which looks at six possible event dates and returns a text string. In this text string result there are three dates that transition from one month into another: October 30, 31 & 1 - Tuesday, Wednesday & Thursday. I need it to read: October 30, 31 & November 1 - Tuesday, Wednesday & Thursday. I could create a cascading set of arguments such as If(MonthName ( event day 2) ≠ MonthName (event day 1); within the current script below but that would be a mess. Would anyone have a cleaner solution? By the way I realize the current script below is not elegant, but it works. If(event day 2 = ""; MonthName ( event day 1 ) & " " & Day ( event day 1 ) & " - " & DayName ( event day 1 ); If(event day 3 = ""; MonthName ( event day 1 ) & " " & Day ( event day 1 ) & " & " & Day ( event day 2 ) & " - " & DayName ( event day 1 ) & " & " & DayName ( event day 2 ); If(event day 4 = ""; MonthName ( event day 1 ) & " " & Day ( event day 1 ) & ", " & Day ( event day 2 ) & " & " & Day ( event day 3 ) & " - " & DayName ( event day 1 ) & ", " & DayName ( event day 2 ) & " & " & DayName ( event day 3 ); If(event day 5 = ""; MonthName ( event day 1 ) & " " & Day ( event day 1 ) & ", " & Day ( event day 2 ) & ", " & Day ( event day 3 ) & " & " & Day ( event day 4 ) & " - " & DayName ( event day 1 ) & ", " & DayName ( event day 2 ) & ", " & DayName ( event day 3 ) & " & " & DayName ( event day 4 ); If(event day 6 = ""; MonthName ( event day 1 ) & " " & Day ( event day 1 ) & ", " & Day ( event day 2 ) & ", " & Day ( event day 3 ) & ", " & Day ( event day 4 ) & " & " & Day ( event day 5 ) & " - " & DayName ( event day 1 ) & ", " & DayName ( event day 2 ) & ", " & DayName ( event day 3 ) & ", " & DayName ( event day 4 ) & " & " & DayName ( event day 5 ); If(event day 6 ≠ ""; MonthName ( event day 1 ) & " " & Day ( event day 1 ) & ", " & Day ( event day 2 ) & ", " & Day ( event day 3 ) & ", " & Day ( event day 4 ) & ", " & Day ( event day 5 ) & " & " & Day ( event day 6 ) & " - " & DayName ( event day 1 ) & ", " & DayName ( event day 2 ) & ", " & DayName ( event day 3 ) & ", " & DayName ( event day 4 ) & ", " & DayName ( event day 5 ) & " & " & DayName ( event day 6 ) )))))) Link to comment Share on other sites More sharing options...
Steve Martino Posted September 19, 2018 Share Posted September 19, 2018 Very confusing. So to simplify, if you have 3 consecutive dates that break over a month, you want to return the 3 dates and their respective days of the week? If so, what do you want to return if they don't? I don't understand what 'event day (1-6)' represent, and you check to see if each one is empty. Some screen shots, sample data with desired result, even a sample file would help. Link to comment Share on other sites More sharing options...
DBusman Posted September 19, 2018 Author Share Posted September 19, 2018 The script functions correctly when any date combination falls within the same month. Sometimes the dates "roll over" into the next month as in this example. That's the situation I'm needing to modify the script to handle. Link to comment Share on other sites More sharing options...
DBusman Posted September 21, 2018 Author Share Posted September 21, 2018 I've managed to create a long cascading script that works for this issue. It's not elegant but it does the job. If you care to review it, the script is below followed by a screen capture of three example results. (fyi, Event Date 6 is not addressed because it's very rare to have six event days) Let ([ M1 = MonthName ( event day 1 ); M2 = MonthName ( event day 2 ); M3 = MonthName ( event day 3 ); M4 = MonthName ( event day 4 ); M5 = MonthName ( event day 5 ); M6 = MonthName ( event day 6 ); D1 = Day ( event day 1 ); D2 = Day ( event day 2 ); D3 = Day ( event day 3 ); D4 = Day ( event day 4 ); D5 = Day ( event day 5 ); D6 = Day ( event day 6 ); DN1 = DayName ( event day 1 ); DN2 = DayName ( event day 2 ); DN3 = DayName ( event day 3 ); DN4 = DayName ( event day 4 ); DN5 = DayName ( event day 5 ); DN6 = DayName ( event day 6 ) ]; If(D2 = ""; M1 & " " & D1 & " - " & DN1; /* ----- break ----- */ If(D3 = "" and M2 = M1; M1 & " " & D1 & " & " & D2 & " - " & DN1 & " & " & DN2; If(D3 = "" and M2 ≠ M1; M1 & " " & D1 & " & " & M2 & " " & D2 & " - " & DN1 & " & " & DN2; /* ----- break ----- */ If(D4 = "" and M2 = M1 and M3 = M2; M1 & " " & D1 & ", " & D2 & " & " & D3 & " - " & DN1 & ", " & DN2 & " & " & DN3; If(D4 = "" and M2 ≠ M1 and M3 = M2; M1 & " " & D1 & ", " & M2 & " " & D2 & ", " & D3 & " - " & DN1 & ", " & DN2 & " & " & DN3; If(D4 = "" and M2 = M1 and M3 ≠ M2; M1 & " " & D1 & ", " & D2 & " & " & M3 & " " & D3 & " - " & DN1 & ", " & DN2 & " & " & DN3; /* ----- break ----- */ If(D5 = "" and M2 = M1 and M3 = M2 and M4 = M3; M1 & " " & D1 & ", " & D2 & " & " & D3 & " - " & DN1 & ", " & DN2 & " & " & DN3 & " & " & DN4; If(D5 = "" and M2 ≠ M1 and M3 = M2 and M4 = M3; M1 & " " & D1 & " & " & M2 & " " & D2 & ", " & D3 & ", " & D4 & " - " & DN1 & ", " & DN2 & ", " & DN3 & " & " & DN4; If(D5 = "" and M2 = M1 and M3 ≠ M2 and M4 = M3; M1 & " " & D1 & ", " & D2 & " & " & M3 & " " & D3 & ", " & D4 & " - " & DN1 & ", " & DN2 & ", " & DN3 & " & " & DN4; If(D5 = "" and M2 = M1 and M3 = M2 and M4 ≠ M3; M1 & " " & D1 & ", " & D2 & ", " & D3 & " & " & M4 & " " & D4 & " - " & DN1 & ", " & DN2 & ", " & DN3 & " & " & DN4; /* ----- break ----- */ If(D6 = "" and M2 = M1 and M3 = M2 and M4 = M3 and M5 = M4; M1 & " " & D1 & ", " & D2 & ", " & D3 & ", " & D4 & " & " & D5 & " - " & DN1 & ", " & DN2 & ", " & DN3 & ", " & DN4 & " & " & DN5; If(D6 = "" and M2 ≠ M1 and M3 = M2 and M4 = M3 and M5 = M4; M1 & " " & D1 & " & " & M2 & " " & D2 & ", " & D3 & ", " & D4 & " & " & D5 & " - " & DN1 & ", " & DN2 & ", " & DN3 & ", " & DN4 & " & " & DN5; If(D6 = "" and M2 = M1 and M3 ≠ M2 and M4 = M3 and M5 = M4; M1 & " " & D1 & ", " & D2 & " & " & M3 & " " & D3 & ", " & D4 & ", " & D5 & " - " & DN1 & ", " & DN2 & ", " & DN3 & ", " & DN4 & " & " & DN5; If(D6 = "" and M2 = M1 and M3 = M2 and M4 ≠ M3 and M5 = M4; M1 & " " & D1 & ", " & D2 & ", " & D3 & " & " & M4 & " " & D4 & ", " & D5 & " - " & DN1 & ", " & DN2 & ", " & DN3 & ", " & DN4 & " & " & DN5; If(D6 = "" and M2 = M1 and M3 = M2 and M4 = M3 and M5 ≠ M4; M1 & " " & D1 & ", " & D2 & ", " & D3 & ", " & D4 & M5 & " " & D5 & " - " & DN1 & ", " & DN2 & ", " & DN3 & ", " & DN4 & " & " & DN5 )))))))))))))))) Link to comment Share on other sites More sharing options...
Steve Martino Posted September 22, 2018 Share Posted September 22, 2018 See next post. Link to comment Share on other sites More sharing options...
Steve Martino Posted September 22, 2018 Share Posted September 22, 2018 Ok here it is with a script. Someone who can write recursive custom functions could most likely turn the script into a field calculation. Edit: Changed file to accommodate for 1 or 0 event records. Edited: 9/23/18 @ 9:41est EventsSampleSMmod1.fmp12 Link to comment Share on other sites More sharing options...
Steve Martino Posted September 25, 2018 Share Posted September 25, 2018 Any luck with the sample file? Good/Bad/Indifferent? Link to comment Share on other sites More sharing options...
Recommended Posts