7 Replies Latest reply on Apr 29, 2011 8:24 AM by philmodjunk

    payroll, email, and pdfs...



      payroll, email, and pdfs...



      I have a payroll report that I need to run every two weeks, based on a table Models::Events Scheduled.  When i sort the layout, i get the model's information (hours worked, etc) pay rate, and date worked.  There is a trailing summary that calculates the total $ owed to that model.  

      When i preview the page, i can cycle through and see each model's pay stub, and that works fine.

      However, i want to automate sending an email pdf of the paystub to the model.  I have created the script that builds the temporary pdf, and emails that temp pdf to the model in question.

      I am calling a script called mailpayroll that is as follows:


      Constrain Found Set
      Perform Script "Email Form"

      End Loop
      Go to Record/Request (next), exit after last

      The Email Form script is:

      Set variable [$temppdf; value:Get (TemporaryPath) & "info" & ".pdf"]
        Save Records as PDF [Restore; $temppdf; records being browsed]
      Send Mail [via client]; To: List (model scheduled::email); Subject: "blah"; "$temppdf"]
      End Loop 

      The behavior i am getting always starts at the first model, but doesn't progress from model 1 to 2. 

      I want to have the script step through all the models that would have paystubs for a given period automatically. 

      What am I missing?

      Thanks in advance...


        • 1. Re: payroll, email, and pdfs...

          It looks like you find the same set of records over and over with each loop.

          Constrain found set should likely be replaced with Perform Find. Constrain limits the existing found set to those records matching the criteria specified. You'll either get the same set each time or a progressively smaller set of records depending on the criteria you use. With Perform Find, you search the entire table each time and this seems the correct option here. You'll still need specify different criteria each time you perform the found set.

          One way to do that is to use this inside your loop:

          enter find mode[]
          Set field // specify criteria specific for one model here
          Set Error Capture [on] // disables "no records found" dialog so that it doesn't intrerrupt your script
          Perform Find []
          If [Get ( FoundCount ) > 0 ]
             Now send out your PDF
             //Do any error messages needed since no records were found here
          End IF

          • 2. Re: payroll, email, and pdfs...

            Let me go a little deeper here...

            I have built the layout to list the girls and their events, which can be sorted by date range for payroll.  When I sort the range, i get a list of say, 5 events a girl worked during a certain period, repeating for all of the girls who have worked during that time period. There is a summary field at the bottom that calculates each girl's individual salary.

            The goal is to email each girl a pdf of the various events returned by this find.  the preview mode allows me to view what the reports would look like.  When I script to create pdf and attach that pdf to an email (temporary path), i either get 1 event (regardless of how many events match the found time period) when I choose "current record" or i get a pdf of the ENTIRE report when I choose all records (which is bad, as we dont want girls seeing other's pay rates, etc.

            I am at a complete stalemate here...if i am in the layout and do the find, then right click on a model last name and then constrain the found set, i get what I want, but the numerous steps to just "go backward" to the whole list are too cumbersome.

            Thoughts? any help is greatly appreciated, especially from Phil and TS Gal....thanks.


            • 3. Re: payroll, email, and pdfs...

              Perform a find each time for just the one model for that event so that the records for the other models are not part of the found set. Then use records being browsed when you generate your PDF.

              • 4. Re: payroll, email, and pdfs...

                Thanks Phil...this is fantastic stuff. Last question...is there a way to script so that the report would move from model to model automatically? i am hoping to eliminate some steps in terms of workflow once the layout is reached. Could the perform find be scripted to move to the next model in the list alphabetically (or by their ID Number, perhaps as in the next in the series).

                • 5. Re: payroll, email, and pdfs...

                  Certainly this can be done. The details depend on the structure of your database. Do you have something like this table structure?


                  Models::ModelID = EventParticipation::ModelID
                  Events::EventID = EventParticipation::EventID

                  If so, you can perform a find on EventParticipation to find a list of all models working for one or more events in a specified date range. You can then put a list of ModelID's in a variable and step through them as your loop that finds records for your PDF and emails them.

                  Go To Layout [EventParticipation]
                  //perform find here for date range etc. of desired events
                  Set Variable [$ModelIDList ; Value: List ( Models::ModelID ) ]
                     Set Variable [ $I ; Value: $I + 1 ]
                     Exit Loop if [$I > valueCount ( $ModelIDList ) ]
                     //perform the same find, but now also use Getvalue ( $ModelIDLISt ; $I ) to specify the model's ID
                     //create and email PDF
                  End Loop

                  • 6. Re: payroll, email, and pdfs...


                    Thanks so much for working through this with me.  In your script above, you list

                    //perform the same find, but now also use Getvalue ( $ModelIDLISt ; $I ) to specify the model's ID

                    does that mean a scripted step, or is that something you have commented out?  not sure how to read that statement.

                    I am stuck there...


                    • 7. Re: payroll, email, and pdfs...

                      Since I don't know exactly what criteria you need to specify for each Model's PDF, I tried to just indicate what additional criteria you'll need each time you pull up a model's records for the report.

                      There are two basic approaches you can use. I prefer the first of the following two examples:

                      Enter Find Mode [] // clear the pause check box
                      Set Field [YourTable::ModelID ; GetValue ( $ModelIDList ; $I ) ]
                      // use additional Set Field steps to enter criteria as needed in other fields
                      Set Error Capture [on]
                      Perform Find []

                      Set Variable [$ModelID ; Value: GetValue ( $ModelIDList ; $I ) ]
                      Set Error capture [on]
                      Perform Find [Restore ] //Include $ModelID as part of the stored criteria in this find step

                      I prefer the first, even though it requires more script steps, because I can see all the criteria listed without having to open up the Find Step to look at the criteria--that saves me time any time I have to come back and make further modificiations to my scripts.