FMPro 5.5 Auto matching new records to existing "tagged" records

[Roger1]

Hi, am using a FMPro 5.5 legacy base.

It has existing records and new records are added fortnightly.

Some of the existing record entries are of particular interest and have already been manually tagged with a check box field.

When new records are added that match the client name AND account number of an old record with the check box crossed, (done manually at a previous date)....I want to have FMaker automatically enter a cross in the check box on the new record.

The end result is that as the database is updated with new records I will be able to search for the check box and located all the records of interest, both old and recent, without having to manually find the old records with the check box and then manually check the box in the new, so that all records of interest are tagged.

I'd appreciate som help.

Roger

[Vaughan]

After the import is performed the found set of records will be those that were updated. Use the replace step to "check" the checkbox field immediately after the import. I'd recommend trapping the import for errors to ensure that the replace is only done if the import proceeds without error.

set error capture on

import

If( status(current error)) <> 0

exit script

end if

set error capture off

replace

[Roger1]

Not quite with you, not all of the new records added need to be tagged.

Only the ones that match a couple of fields in the old records AND have a cross in the field that contains the check box.

I want the new records only that match those 2 fields and have check box in old records... to automatically pick up a cross in the same checkbox field. Then I can search checkbox and get old and new records of the type I need.

Thanks again for your help.

Roger

[Vaughan]

Set up a self-join based on the client name and account number (in FMP 6 you will need to make a calculated field to concatenate these value together to use as the key field). Then use this relationship to work out whether the new records relate to old ones that are checked.

[Roger1]

thanks Vaughan,

but sorry I am still a bit lost. I struggle because I have gone through and manually tagged a check box for particular clients and this is the only thing that differs them from other records.

I want to import new records every week for all the clients in the database and then instead of handpicking through the data and put a cross in the check box if one of my previously checked clients has another entry - I want filemaker to do it automatically. (That is: If I have checked these entities before, do it again).

My logic falls over because I only want to cross the ones that were done before and I cant match any of the old "crossed" entries with the new uncrossed entries because the unique difference is the cross itself and it's not on the new entries yet.

(I hope that made sense!)

The fields are

Policy

Client

Tag Box 2

[Vaughan]

Yes it makes sense. It also confirms my thoughts that you really need a relational design where there is one client record, and these new records are related Policy records.

The source that you import the data from, does it have a unique identifier for the clients that you can also use to identify them?

[Roger1]

Hi, the unique identifier at source is the policy number.

The target database has multiple commission entries for that policy (and all others)

When I import more commission entries, I want to be able to auto identify the new ones with the same policy number as those already tagged.

[comment]

Why don't you tag the policies once, in the Policies table? Then all commissions related to a tagged policy will be automatically tagged by association.

[Roger1]

Thanks for the response, I'll keep asking until I understand, so sorry if is frustrating anyone...!!

Not sure what you mean by Policies Table?

There is only one database with all the information. Client, Policy and commission.

One client may have several policies.

And there is no uniform way of expressing the client name when the data arrives. It could be F Bloggs, Bloggs, Bloggs F etc (but always the same for the same policy) with varying amounts of commission as agreed.

I have tagged old entries and want new entries to auto tag... but there is only one base, there is no relational database with a unique ID.

Is that what you were referring to or is it something esle that I may be able to use in the setup that I have (which has nearly 300,000 entiries in it BTW)

Thanks for your response

Roger

[Roger1]

Stop looking folks, I think the penny has dropped, and I think it was what you were both trying to tell me.

I couldnt get over the fact that I had only one database and the information to export each week is over 2500 entries.

I didnt have another base with an identifier to relate to...

BUT the penny has dropped!!

I feel like I cheated a bit and the solution is a bit clumsy - but it works.

Because I had gone through and manually tagged policies before, I did a find and found all the tagged entries.

These entries will repeat themselves on different dates into the future for some time and everytime a new one comes in I want a tag box checked.

So... I found them all (7251 of them!!) and exported them to a new database.

Then I created a relationship between the old database with 300,000 entries and the new small base of tagged entries using the policy number as the unique id in the relationship.

Once joined, I changed the "Tag" check box to a calc field using the if statement, if the policy and client name matches and the check box is 'yes' then put a check in the check box in the major database...

and it works.

I think that this is what you were both telling me to do anyway.

Thanks for your help

Roger

[Vaughan]

My advice is to upgrade to >FMP 6 as soon as possible. You are learning the "old" way and will have to un-learn it later when you g to FMP 7 and later.

[Lee Smith]

Automatic message

This topic has been moved from "FileMaker Product Family → FileMaker Legacy → FileMaker.fp5" to "Database Schema & Business Logic → Importing & Exporting".