11 Replies Latest reply on Nov 12, 2008 8:22 AM by TSGal

    need to delete oldest invoice for each customer

    mikesgreenroom

      Title

      need to delete oldest invoice for each customer

      Post

      Hi all,

       

      First post, total newb, thanks in advance for your help and patience

       

      I have a file that looks like this when sorted by customer and then date:

       

      Customer  date    invoice# amount

      andy       1/1/08   333       50.00

      andy       2/1/08   453       45.00

      bob        1/1/08    152       55.00

      bob        1/3/08    256       55.00

      bob        1/15/08   301      60.00

       

      Is there a script that will delete the first record for each customer (numbers 333 and 152 in this case)? I]m supposed to show a history and total for each customer but the oldest invoice for each customer can't be included in the sum.

       

      thanks again

       

      mike 

        • 1. Re: need to delete oldest invoice for each customer
          Orlando
            

          Hi Mike,

           

          Welcome to the forum.

           

          Are you looking to physically delete the first record from the database completely, or simply omit it and not have it display it in a report of some kind? 

          • 2. Re: need to delete oldest invoice for each customer
            TSGal

            mikesgreenroom:

             

            Thank you for your post.

             

            The question by "Orlando" is definitely valid.  However, the following script will help you omit the first record from each group of the report and not delete it from the database file.  The report can be easily modified if you want to permanently remove the first record from each Customer.

             

            Go to Layout [<layout of report> ]

            Enter Browse Mode []

            ### If you want all records, include "Show All Records" here

            Sort Records [Restore;No dialog]

            Go to Record/Request/Page [First] 

            Set Variable [$cust; ""]

            Loop

               If [$cust <> Customer]

                  Set Variable [$cust; Customer] 

                  Omit Record

               End If

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

            End Loop

             

            =============

             

            This script goes to the layout of the report you want to print.  We make sure we are in Browse mode.  If you want this for the entire file, the use the script step "Show All Records".  If you have a found set of records, then leave out that step.

             

            Next, we sort the records by Customer and Invoice# (or Customer and Date), and go to the first record of the sorted order.

             

            We set a variable ($cust) to keep track of the Customer.  It is initialized to blank.  Now, we enter the loop to process all the records.

             

            If the variable $cust does not equal the Customer field, then we know this is the first record of the next set.  Therefore, we set the variable $cust to the Customer field and omit the record.  We then skip to the next record until we have gone through all the records.  When we go past the last record, we exit the loop and the script ends.  If you wish, add a Print statement after the End Loop step.

             

            If you need clarification, please let me know.

             

            TSGal

            FileMaker, Inc. 

            • 3. Re: need to delete oldest invoice for each customer
              mikesgreenroom
                

              TSGal,

               

              Thanks so much for this, and also the nice explanation. It might take me some time to test it out, but I'll certainly let you know my progress.

               

               thanks again

               

              mike 

              • 4. Re: need to delete oldest invoice for each customer
                mikesgreenroom
                  

                Hi Orlando,

                 

                Thanks for responding. It doesn't really matter if they get deleted or not, they just can't get added in to the sub-summary. This is actually some data I'm exporting from QuickBooks, so the data doesn't need to be preserved after the report is done, it will get purged and replaced with new data next time we need an updated report. I'm going to try TSGal's thing, I'll let you know how it goes. 

                 

                thanks again

                 

                Mike 

                • 5. Re: need to delete oldest invoice for each customer
                  mikesgreenroom
                    

                  Hi TSGal, 

                   

                  Actually, Orlando's post got me thinking (delayed reaction). It might be better to delete it after all. Would that mean that we would replace the step "Omit Record" and replace it with "Delete Record"? Forgive me if this guess is laughable

                   

                  thanks

                   

                  Mike 

                  • 6. Re: need to delete oldest invoice for each customer
                    mikesgreenroom
                      

                    Hey TSGal

                     

                    Wow. It works perfectly, quite amazing! I am very grateful for your help. I can't imagine how long this would have taken me, maybe never.

                     

                    thanks again!

                     

                    Mike 

                    • 7. Re: need to delete oldest invoice for each customer
                      TSGal

                      Mike:

                       

                      Glad to hear it is working for you.

                       

                      Yes, if you want to delete the record, replace the step "Omit Record" with "Delete Record".

                       

                      TSGal

                      FileMaker, Inc. 

                      • 8. Re: need to delete oldest invoice for each customer
                        mikesgreenroom
                          

                        Hi TSGal

                         

                        Well it turns out I may have celebrated prematurely. On closer inspection of lots of data, in some cases the last record (most recent date) is the one being omitted. Any thoughts? Sorry to rain on the victory parade.

                         

                        Thanks again

                         

                        Mike 

                        • 9. Re: need to delete oldest invoice for each customer
                          TSGal

                          mikesgreenroom:

                           

                          Remember, the script removes the first record of each grouping.  If there is only one record in a grouping, it will be omitted.

                           

                          TSGal

                          FileMaker, Inc. 

                          • 10. Re: need to delete oldest invoice for each customer
                            mikesgreenroom
                              

                            Hi TSGal

                             

                            that's OK, it's supposed to do that. The instances I'm worried about have more than one record and it is removing the last record. The customer names have similarities, could that mean anything? would it be better if I gave them each an ID number and used that instead?

                             

                            thanks

                             

                            mike 

                            • 11. Re: need to delete oldest invoice for each customer
                              TSGal

                              Mike:

                               

                              Thank you for your post.

                               

                              If the variable doesn't match the customer name, then the variable is set to the new customer name and the record is omitted. That is the only time this occurs. You may want to check and make sure there are no additional spaces at the end of a name, because even though the names may look the same, the contents would be different, and as a result, the record would definitely be omitted.

                               

                              TSGal

                              FileMaker, Inc.