FM - Deleting all but the last record in a group of Imported Records

I am trying to import records from a DOS database, but have been unable to find the "Active Flag."  Therefore, since the current tenant is always the one with the highest number in the "TenantNumber" field, how can I (after importing) search for all records except the highest number in the TenantNumber field and delete them, leaving me with only current tenants.  Thanks.
LVL 2
rvfowler2Asked:
Who is Participating?
 
Will LovingPresidentCommented:
Assuming that the import has multiple records per property and you are only wanting to keep the one for each property that has the highest number, I can think of two ways:

1. Sort the imported records by Property ascending and then by TenantNumber descending. Create a looping script that captures the current PropertyNumber to a script variable. The first time a new Property value is encountered, it's captured to the Variable and then Omitted. Check each subsequent record for the same Property value until a new one is encountered and then perform Set Variable and Omit again. Keep in mind that the Omit step will move you to the next record the same as performing Go To Record [ Next ] in your loop.

2. Create a self-join relationship between the records using the Property value. Create a calculation 'flag' field along the lines of: MaxTenantNumber = Case( max( Prop_SelfJoin::TenantNumber ) = TenantNumber ; 1 ). You then do a Constrain Found records which Omits all records having "1" in the MaxTenantNumber field and then delete the remaining imported records.
0
 
rvfowler2Author Commented:
Will, the self-join using the max formula worked like a charm.  I prefer calcs rather than moving parts as the latter will more likely "break."  Thanks.
0
 
Will LovingPresidentCommented:
Over the years a my databases have grown in size, I've come to prefer 'moving parts' such as using a find or looping script rather than clutter the relationship graph with an additional relationship just to solve one small problem.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
rvfowler2Author Commented:
Good point, I supposed depending on the example.  Just yesterday, I was asked to have a button that would indicate Insurance was part of CAM, which would insert something in a field and instead created a Yes/No field in one db and then changed the calc field in another db to included the "Yes" to calculate the amount accordingly.  I find scripts breaking mainly between dbs, so I've tried to eliminate this.
0
 
Will LovingPresidentCommented:
Sounds like you might want to do more consolidation of external files in to a primary file. Then you can avoid any issues with calling external scripts and just use Go To Layout or open a new window.
0
 
rvfowler2Author Commented:
Yes, working on it.  Have reduced from 14 down to 8 files thus far using your template.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.