2 Replies Latest reply on Feb 15, 2016 8:23 AM by mcemond

    Server scheduled script - 1 email several records

    mcemond

      I haven't seen a lot (or maybe Im looking in the wrong paces) about a single email that sends a found set as a scheduled script. Currently I have individuals manually send their weekly time sheets and we can wrap them up nicely as a pdf or excel attached to an email. Automating this won't work as the server won't do pdf or excel attachments. I looked at snapshot links but the recipients of the email don't have filemaker licenses. So my question is what kind of script steps would be involved in a "records being browsed" kind of list that can be put into an email? This is what I've been dabbling in so far... Thanks. Mike

       

      Edit: Filemaker Server 13 running database.

       

      Go to Layout [ "crew" (crew)]

      Go to Record/ Request/ Page [ First ]

      // Go through each crew member and perform a search for this week's logged work hours

      Loop

           Set Variable [ $crewLogged; Value: crew::crewName ]

           Set Variable [ $crewEmail; Value: crew::email ]

      // This is the table that stores their logged hours

           Go to Layout [ "logTime" ( logTime ) ]

           Enter Find Mode [ ]

      // weekEnding is a date calculation that finds the Friday of whatever week the hours are logged during. This script would be set on the server to run weekly on Friday evenings so Get (CurrentDate) should always return any hours entered that week.

           Set Field [ logTime::weekEnding; Get ( CurrentDate ) ]

      // Should return all hours entered by the current crew member for this loop iteration

           Set Field [ logTime::crewName; $crewLogged ]

           Set Error Capture [ On ]

           Perform Find [ ]

           Set Error Capture [ Off ]

      // So now we have a found set of all time logged for the current week by an individual crew member. We would now like to email this to the payroll clerk. Next: if the individual has no time logged this week, skip them, otherwise, email the results.

           If [ Get ( FoundCount ) > 0 ]

                Send Mail [ Send via SMTP; To: "payroll@clerk.com"; CC: $crewEmail; Subject: $crewLogged & "'s hours"; Message: "This is where I am struggling. I thought of setting it up like an html table in order to control formatting but how would I get the records to show as a found set? Would I have to go to an nth record sort of thing?"]

           End If

      // Go back to the crew table to set up the loop for the next crew member.

           Go to Layout [ "crew" (crew) ]

           Go to Record/ Request/ Page [ Next; Exit after last ]

      End Loop

      Go to Layout [ original layout ]

       

      Optimal email result (sometimes multiple hours are logged on the same day on different jobs):

      Crew Name

      Week Ending

      Date 1 - Hours logged - notes about hours

      Date 1 - Hours logged - notes about hours

      Date 2 - Hours logged - notes about hours

      Date 3 - Hours logged - notes about hours

      Date 3 - Hours logged - notes about hours

      Date 4 - Hours logged - notes about hours

      Date 5 - Hours logged - notes about hours

      Total Week's Hours