6 Replies Latest reply on Feb 23, 2012 10:10 AM by matt.rowe

    What's the best way to do a partial cascading delete?

    matt.rowe

      FMP newbie question I'm afraid!

       

      Part of my database looks like this:

       

      Lease --< LeaseLetUnit >-- LettingUnit

       

      When I delete a Lease record I want to change a field in the LettingUnit table (LettingUnit::Status) to the string "Vacant", before the Lease record and LeaseLetUnit join record are deleted (by way of a cascading delete on the relationship between these two TOs i.e. Lease --< LeaseLetUnit).

       

      The Letting Unit's details appear on a portal on the Lease Details layout.

       

      Can I use the "Go To Portal row"/"Set Field" script step to iterate through each record in the portal to achieve this (by way of a custom menu item)? If so what would happen if there were no rows in the portal? Or should I use a more complex approach in a new window, passing paramters and switching layouts etc?

       

      Being a newbie; I'm not aware of the "gotchas" when taking the simplewr approach!

       

      Any help you can offer is much appreciated.

       

      Thanks in advance.

        • 1. Re: What's the best way to do a partial cascading delete?
          comment

          In the definition of the Lease --< LeaseLetUnit relationship, you can specify "Delete related records in this table ..." on the LeaseLetUnit side.

           

           

          matt.rowe wrote:

           

          When I delete a Lease record I want to change a field in the LettingUnit table (LettingUnit::Status) to the string "Vacant"

           

          Shouldn't that be a calculation? I am not sure why do you want to delete Lease records in the fist place; surely you don't want to erase your lease history?

          • 2. Re: What's the best way to do a partial cascading delete?
            deninger

            I concur with Michael H on this. I would take the approach of flagging a lease as "expired" or "terminated" or some such jargon, leaving the lease details for historical purposes.

             

            WIth respect to a calculation, I sometimes like to take the more physical approach. I might create a script that flags the lease as expired or terminated etc and then have the script manually find the unit(s) associated with them and mark them as vacant. Different approach with same general result.

            1 of 1 people found this helpful
            • 3. Re: What's the best way to do a partial cascading delete?
              matt.rowe

              Hi Michael, thanks for your reply.

               

              The relationship is set to delete (see above)

               

              There will be a requirement to "tidy up" old Leases.  I can either set them to "Inactive" or delete them, users can therefore choose to maintain a history or not (I will need to add another script to trigger a Letting Unit to become "Vacant" when a lease is "Deactivated"; this time without deleting anything).

               

              Either way the problem is that the "Picker Window" used for attaching Leases to Letting Units is filtered to only show "vacant" units.  This prevents users from letting a unit more than once, on a different Lease.

               

              I'm not sure what you mean when you say "Shouldn't that be a calculation?".  Sorry if this sounds confusing and thanks again for your reply.

              • 4. Re: What's the best way to do a partial cascading delete?
                matt.rowe

                Thanks deninger.

                 

                I suppose I could call a "sub-script" first to deactivate the Lease and then Delete it afterwards (or at some later time as part of a tidy up routine).  Breaking it down into "chunks" might make it less complex and easier for my poor old man's brain to work out!

                 

                Thanks for the tip.

                 

                Regards

                 

                Matt

                • 5. Re: What's the best way to do a partial cascading delete?
                  comment

                  matt.rowe wrote:

                   

                  I can either set them to "Inactive" or delete them

                   

                  I cannot see any advantage to deleting them. OTOH, I can see users coming up with "what was the thing with that lease" right after it was deleted.

                   

                   

                  matt.rowe wrote:

                   

                  Either way the problem is that the "Picker Window" used for attaching Leases to Letting Units is filtered to only show "vacant" units.  This prevents users from letting a unit more than once, on a different Lease.

                   

                  That's not an entirely accurate description: if a unit can be leased only once, why would you need a join table between Leases and Units?  Depending on what time spans you work with, a unit is available if it doesn't have any active leases at all, or if it doesn't have any active leases that overlap the desired lease period. Your "Picker Window" (whatever it is) should be able to filter the units according to that. 

                   

                   

                  matt.rowe wrote:

                   

                  I'm not sure what you mean when you say "Shouldn't that be a calculation?".

                   

                  I mean that the status of a unit is given by the leases it has - therefore it should be calculated, not stored in the Units table. As a general rule, you want to avoid storing the same information in two places.

                  • 6. Re: What's the best way to do a partial cascading delete?
                    matt.rowe

                    Very good points, well made.

                     

                    Basic Newbie design error here I think! It seems I may have inadvertantly cornered myself.  I need to rethink my approach to this part of the solution.  Many thanks for your help