?
Solved

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

Posted on 2011-10-19
6
Medium Priority
?
279 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:rvfowler2
  • 3
  • 3
6 Comments
 
LVL 25

Accepted Solution

by:
Will Loving earned 2000 total points
ID: 36994948
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
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 36995106
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
 
LVL 25

Expert Comment

by:Will Loving
ID: 36995122
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Author Comment

by:rvfowler2
ID: 36995166
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
 
LVL 25

Expert Comment

by:Will Loving
ID: 36995286
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 36995299
Yes, working on it.  Have reduced from 14 down to 8 files thus far using your template.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question