Script to move record to expired table

[Hawaii80]

Trying to find best way to do this, first looked at copy fields but that wasn't going to solve the issue, because I don't want fields on 2 different tables.

 

Table A has an end date field, when the end date is less than current system date, (month/day/year) I want the record to move to expired Table B, which has exact same field types, (other than pk) to maintain history but not still be in active Table A. Best way to do this?

[Maarten Witberg]

The best way is to not create a second table but add a field "expired" and use this in finds and (possibly as an extra predicate) in relationships.

the calc for Expired could be

 

Expired = calc, number result =

EndDate

 

the result would be boolean (null or 1)

 

How you update the current date in the field depends on various circumstances, but you could consider a) an update that runs in the startupscript for the file b) an update that runs in conjunction with find and report scripts. I'd steer away from unstored calc with get (currentdate), because that's hard to use in relationships, but it's an option as well.

Either way for an efficient update you'd always create a found set of unarchived records that should expire, not all records that have their end date before the current date.

[Hawaii80]

Thanks for responding. Only issue I still see is that Table A is Owner table and Table B is the Tenant table. The current tenant is displayed on the Owner's table currently by a portal with 1 row descending so I can hide previous tenants, I've tried with a relational field and the problem I'm having is the oldest tenant record by auto serial is the one that keeps showing up since they were the first tenant entered in the tenant table. This is why I thought if I could move the tenant once their lease expires to an inactive table then it would have to display the correct tenant because you can only have 1 at a time per Owner; however some Owner's have more than 1 property each having a tenant.

[Maarten Witberg]

You need a join table structure for this with three tables: owners, properties and tenants.

So you can store the owner ID and the current tenant ID in the properties table. If the lease expires (and supposedly if the rent has been paid), remove the tenant ID from the property record.

[Maarten Witberg]

you might consider a separate contracts table, which would be a second join table between tenants and properties. that way you can keep track of all leases, especially if you branch off payments from that one. hope I'm making sense.

[Hawaii80]

Okay, getting rid of tenant id makes sense; I can see how that could alleviate the problem right there so I'll give that a shot.

 

I do have 3 tables right now, Owner, Property, Tenant; each has their own pk, (and tenant/property have a fk) and I've linked Owner pk to Property fk because 1 owner can have many properties and I've linked tenant pk to property pk because 1 tenant at a time can only belong to 1 property. Does that relation sound correct to you? Just want to make sure because I didn't know if that was why I was having an issue to begin with. Thanks again kjoe.

[Maarten Witberg]

I've linked tenant pk to property pk

that does not make sense. you should create a tenant fk in the property table, or as I suggested in my second-thoughts post, create a contracts table and store both tenant and property IDs there.

I think you will benefit from drawing an entity-relationship diagram.

[Hawaii80]

Thanks kjoe, I'll change the keys and go from there. Thanks for your insight.

[poetnabotl]

If I have many fields that rely on today's date, whatever that may be at the time, does it make sense to make a field (maybe a global):

todaysDate = get(currentdate)

 

And then as kjoe suggested, have a script that refreshes that field on file launch? Would all calcs that use todaysDate as a reference re-evaluate also?