12 Replies Latest reply on Dec 17, 2009 3:38 PM by tarheelme

    Deleting a record with a higher value.

    tarheelme

      Title

      Deleting a record with a higher value.

      Post

      Using FMP 9.0/ WinXp

      I import pricing from manufacturers which include a regular price and a promotional price for the same product.

      In my database I only need the highest price since I base retail pricing from it.

      I can find duplicates and sort so the highest price lists first, but I'd like to delete the second record with the lower price.

      How can I do this?

      Thanks,

       

      Dangerous newbie

      Tysdad

        • 1. Re: Deleting a record with a higher value.
          philmodjunk
            

          By hand or using a script?

          • 2. Re: Deleting a record with a higher value.
            tarheelme
              

            Over 1400 records... prefer to remove by script.

            My fields are: 

            MFR

            STYLE

            STYLE#

            PRICE

             

            I've created a MFR & STLYE# & PRICE field which allows me to find and sort into consecutive records, just can't delete the lower priced record.

             

            • 3. Re: Deleting a record with a higher value.
              philmodjunk
                

              You can create a script that loops through the records and deletes the duplicate records with a lesser value. Note that you could also "mark" these records and then simply exclude them from reports and such. This option allows you to keep this data for historical or other purposes should that be useful to you.

               

              I'm assuming that you want to delete records with the same Style# but a lesser price...

               

              Enter Find Mode [] //clear the pause check box

              Set Field [Style#; "!"]

              Set Error Capture [on]// Keeps "no records found" dialog from popping up and pausing the script if no duplicates were found.

              Perform Find[]

              If [Get ( FoundCount ) > 0 /* duplicates were found */]

                Sort[Restore; No Dialog] //Specify your sort to sort by style#, then price

                Set Variable[$StyleNo; Value: ""]

                Loop

                   IF [YourTable::Style# = $StyleNo /* previous record was duplicate with greater price */]

                       Delete Record [no dialog]

                   Else

                       Set Variable[$StyleNo; Value: Yourtable::Style#]

                   End If

                   Go To Record [next; Exit after last]

                End Loop

              Else

                Show Custom Dialog ["No duplicates where found"]

              End IF

               

              That should do it. Make a copy of your database before you try out this script so that if you find it deletes the wrong records you can try again.

              If I've assumed incorrectly somewhere let me know and I'll adjust this script example.

               

              Many new users get confused the first time they try to add Set Field to a script. See the following link for step by step instructions:

              Selecting an "All" option with checkboxes

                 

              • 4. Re: Deleting a record with a higher value.
                comment_1
                  

                PhilModJunk wrote:

                 

                That should do it.


                I don't think so.


                • 5. Re: Deleting a record with a higher value.
                  philmodjunk
                    

                  Thank you. I knew when I posted that script I should have tested it. The following example was tested via FMP 10.

                   

                  Note that I'm using different table and field names, so you'll need to substitute your own as needed here.

                   

                  Enter Find Mode [ ]

                  Set Field [ DeleteLesserDups::ID Number; "!" ]

                  Perform Find [ ]

                  Sort Records  [ Restore; No dialog ]

                  Go to Record/Request/Page [ First ]

                  Set Variable [ $id; Value:"" ]

                  Loop

                    If [ $ID = DeleteLesserDups::ID Number ]

                      Delete Record/Request [ No dialog ]

                      Go to Record/Request/Page [ Previous ]

                    End If

                    Set Variable [ $id; Value: DeleteLesserDups::ID Number ]

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

                  End Loop

                   

                  Comment, as always, if you spot any goofs, feel free to point that out.

                  • 6. Re: Deleting a record with a higher value.
                    comment_1
                      

                    PhilModJunk wrote:

                     

                    Comment, as always, if you spot any goofs, feel free to point that out.


                    No, I don't think so - I am getting awfully tired of all this.


                    • 7. Re: Deleting a record with a higher value.
                      philmodjunk
                        

                      comment wrote:
                      No, I don't think so - I am getting awfully tired of all this.

                      Yet you don't identify the error so the OP is still not helped and you set yourself up to be even "more tired":smileywink:

                       

                      Thanks just the same and I'll take a third look at the script.


                      • 8. Re: Deleting a record with a higher value.
                        comment_1
                           I am afraid you have missed my point.
                        • 9. Re: Deleting a record with a higher value.
                          philmodjunk
                             Feel free to enlighten me.
                          • 10. Re: Deleting a record with a higher value.
                            philmodjunk
                              

                            Tysdad,

                             

                            I've now tested the updated script every way I can think of while watching it step by step in the script debugger and it appears to work correctly. Let me know if you have any further questions or if it doesn't work the way you need it to.

                             

                            Community at large,

                             

                            If you spot a bug in this script or any other post I put up, feel free to point out what and where it is. I appreciate the feedback.

                            • 11. Re: Deleting a record with a higher value.
                              philmodjunk
                                

                              One small improvement would be a good idea to enable the script to run a bit smoother:

                               

                              Enter Find Mode [ ]

                              Set Field [ DeleteLesserDups::ID Number; "!" ]

                              Set Error Capture [on]

                              Perform Find [ ]

                              Sort Records  [ Restore; No dialog ]

                              Go to Record/Request/Page [ First ]

                              Set Variable [ $id; Value:"" ]

                              Loop

                                If [ $ID = DeleteLesserDups::ID Number ]

                                  Delete Record/Request [ No dialog ]

                                  Go to Record/Request/Page [ Previous ]

                                End If

                                Set Variable [ $id; Value: DeleteLesserDups::ID Number ]

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

                              End Loop

                               

                              In the event that there are no duplicates, the error capture step will keep Filemaker from interrupting the script with a "records not found" dialog.

                              • 12. Re: Deleting a record with a higher value.
                                tarheelme
                                   Thanks... runs fine!