10 Replies Latest reply on Feb 4, 2009 2:35 PM by raybaudi

    Monthly/Weekly report different payment dates

    jimmelo

      Title

      Monthly/Weekly report different payment dates

      Post

      Here is my problem.

       

      I have multiple payment lines.  ie. a client can make payments on different days and we have an entry field for each payment date.  Let's say I call the fields Payment1, Payment2, Payment3,etc.  Each payment thus also has a PaymentDate1, PaymentDate2, etc field.  

       

      I want my monthly report to show only payments made this month, or this week or whatever range I choose.  Right now my report will show ALL payments made for a given client if any payments were made in the date range I am looking at.    For example Client A made payment in Dec, 2008 for Payment1, Client A then made payment in Jan, 2009 for Payment2.  If I want a January report I want only to see Payment2 for client A, not his December Payment1.


       

       

        • 1. Re: Monthly/Weekly report different payment dates
          jimmelo
            

           


           

          Here is how I am currently running my script:

          <!-- [if gte mso 9]><xml>   Normal   0           false   false   false     EN-US   X-NONE                                        MicrosoftInternetExplorer4                                      </xml><![endif] --><!-- [if gte mso 9]><xml>                                                                                                                                                                                                                                                                                   </xml><![endif] --><!-- [if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable      {mso-style-name:"Table Normal";      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-priority:99;      mso-style-qformat:yes;      mso-style-parent:"";      mso-padding-alt:0in 5.4pt 0in 5.4pt;      mso-para-margin-top:0in;      mso-para-margin-right:0in;      mso-para-margin-bottom:10.0pt;      mso-para-margin-left:0in;      line-height:115%;      mso-pagination:widow-orphan;      font-size:11.0pt;      font-family:"Calibri","sans-serif";      mso-ascii-font-family:Calibri;      mso-ascii-theme-font:minor-latin;      mso-fareast-font-family:"Times New Roman";      mso-fareast-theme-font:minor-fareast;      mso-hansi-font-family:Calibri;      mso-hansi-theme-font:minor-latin;} </style> <![endif] -->

          Enter Find Mode []

          Set Field [PaymentDate1]

          Insert Calculated Result [Select; PaymentDate1; gStartDate & ".." & gEndDate]

          Set Field [PaymentDate12]

          Insert Calculated Result [Select; PaymentDate2; gStartDate & ".." & gEndDate]

          ...

          Perform Find []

           

          Am I missing some easy If/Case arguments I could use instead?  Got an example?

           

          Thanks in advance.

          • 2. Re: Monthly/Weekly report different payment dates
            jimmelo
               Sorry the text editor said I had over 20,000 characters and I didn't figure out that it didn't like direct cut and paste from word.  I had to use the other button.  Sorry about that.
            • 3. Re: Monthly/Weekly report different payment dates
              raybaudi
                

              Hi jimmelo

               

              > Here is my problem.

               

              I have multiple payment lines.  ie. a client can make payments on different days and we have an entry field for each payment date.  Let's say I call the fields Payment1, Payment2, Payment3,etc.  Each payment thus also has a PaymentDate1, PaymentDate2, etc field.

               

              ------------------------------------------------------

               

              Sorry, but your real problem is the way you made your DB.

               

              You must have a related table with ONLY two fields ( Payment and PaymentDate ) plus a field needed by the relationship ( ClientID ), but many records.

              • 4. Re: Monthly/Weekly report different payment dates
                jimmelo
                  

                Could you be more specific please?  How does that solve my problem?  How does the script look?  How do I filter out payments from last month for a client making payments in both months? 

                 

                Thanks

                Jim

                • 5. Re: Monthly/Weekly report different payment dates
                  raybaudi
                    

                  How many tables do you have at present time ?

                   

                  How are they related ?

                  • 6. Re: Monthly/Weekly report different payment dates
                    unc12
                      

                    I run a script using the below. 

                     

                    Get(CurrentDate)&"..."&Get(CurrentDate)+45

                     

                     

                    It retrieves records with a payment date range of today plus 45 days.

                     

                     

                    • 7. Re: Monthly/Weekly report different payment dates
                      raybaudi
                        

                      unc12 wrote:

                       

                      It retrieves records with a payment date range of today plus 45 days.

                       

                       


                      ... but he has fields, not records. ( or so I understood )


                      • 8. Re: Monthly/Weekly report different payment dates
                        jimmelo
                          

                        Yes I have thousands of records each of them with multiple payment date fields, but I want to run a report that ONLY shows payments made in January.  I want to EXCLUDE payments made outside of my date range.  I have all ready created an automated date report creator.  I can run reports for ANY given date range as well as this week, last week, this month, last month, by quarter, and by year.  That part I have figured out thanks to this bb.  I just want to get more specific in my reports and exclude data on my report that I don't want to see.  The problem is the field may exist for one record while I don't want to exlude it for another.

                         

                        Imagine the table looks as follows:

                         

                        client name  pay1 paydate1  pay2  paydate2  pay3  paydate3

                        client1         $10  12/1/08   $10     12/15/08  $10    1/1/09

                        client2         $10  12/20/08  $10    1/5/09   

                        client3          $10  1/1/09     $10    1/10/09    $10    1/15/09

                         

                        So in my january report I only want to see the following:

                        for client 1 I only want to see a value in pay3

                        for client 2 pay2

                        for client 3 pay1, pay2, pay3

                         

                        I can make relational tables if I want but the person who set this up before me had not done so, so I am currently keeping it all on one table.  I can set up a relational table if needed but if you advise that can you please at the same time tell me what a script would look like to produce the results I want if you are able.

                         

                        thanks,

                        Jim

                        • 9. Re: Monthly/Weekly report different payment dates
                          jimmelo
                            

                          unc you can actually get a lot more elaborate than that if you want to without a whole lot of work. 

                           

                          for example I have an entry field for Start Date that I can manually override or I also have radio buttions for a ReportType value list that populates the entry field:

                           

                          Start date field is defined as:

                           

                          Let (d = Get (CurrentDate);

                                    Case (ReportType="This Week"; Date (Month (d); Day (d) - DayOfWeek (d) +1; Year (d));

                                             ReportType="Last Week"; Date (Month(d); Day(d) - DayOfWeek (d) -6; Year (d));

                                             ReportType="Monthly"; Date (Month (d); 1; Year (d));

                                             ReportType="Annually"; or gReportType="Q1";Date (1;1;Year(d));

                                             ReportType="Q2"; Date (4;1;Year(d));

                                             ReportType="Q3"; Date (7;1;Year(d));

                                             ReportType="Q4"; Date (10;1;Year(d))

                                   )

                          )                   

                           

                          I also have an end date entry field defined almost the same way but with some minor modifications:

                          Let (d = Get (CurrentDate);

                                    Case (ReportType="This Week"; Date (Month (d); Day (d) - DayOfWeek (d) +7; Year (d));

                                             ReportType="Last Week"; Date (Month(d); Day(d) - DayOfWeek (d); Year (d));

                                             ReportType="Monthly"; Date (Month (d)+1; 1; Year (d)-1);

                                             ReportType="Annually"; or gReportType="Q4";Date (12;31;Year(d));

                                             ReportType="Q2"; Date (4;0;Year(d));

                                             ReportType="Q3"; Date (7;0;Year(d));

                                             ReportType="Q4"; Date (10;0;Year(d))

                                   )

                          )                   

                           

                           

                          I DID NOT write this code but found it on this listserve.  Actually I dowloaded an entire mini monthly report database called call_stats that I can't seem to find the post for.  I can't seem to upload a file here but I can email it to you if you want.

                           

                          • 10. Re: Monthly/Weekly report different payment dates
                            raybaudi
                              

                            jimmelo wrote:

                             

                            I can set up a relational table if needed but if you advise that can you please at the same time tell me what a script would look like to produce the results I want if you are able.

                             


                            Jim, I'm not able to produce that result without setting up a relational table.

                             

                            That said, I think that someone else may come to help you with strange calculations that go and keep values from different fields ( or different repeatition of a field ) in a record.