Data Evaluation & Field Update Issue

[A T Martin]

Hello gang. I'm having a problem trying to build this model. Thought it thru and maybe am blocked because I've "overthought" it. Anyway, here is the scenario:

The Environment:

1) Many records and many fields of data.

2) A Radio Button based STATUS field which shows CURRENT, RESCINDED and CANCELLED.

3) Two CSV files, one named RESC and one named CANX.

===

What I need to do:

Update the STATUS field from CURRENT to RESCINDED using data from the "RESC" CSV file, while also being able to update the same STATUS field from CURRENT *or* RESCINDED to CANCELLED using data from the "CANX" CSV file.

How:

Within either CSV file, there are three fields which may contain data that may match exactly to three fields in one of thousands of records the database.

For the "RESC" CSV File: If all three match, the STATUS field for that particular record needs to change from CURRENT to RESCINDED. (conversely, if only one or two of the three fields match, no update of the STATUS field is done.)

The same rule applies for the other CSV file, which would cause the STATUS field to change from CURRENT to CANCELLED.

===

The Question:

What's the best way to do this? I believe that importing the data from the CSV file(s) into separate tables within the database, then somehow evaluating that criteria against the criteria in the database and updating the STATUS field (all within the import script) is the way to go, but I am unsure as to how to set this up.

I've managed to create a auto-import data lookup in the STATUS field using a relationship to a table I created called "RESC_imp" and it works. Buuuuut - you can only have one auto-import lookup per field, and it just seems clunky to do it this way. And, it's fried my brain thinking about it over and over...

So, my big questions are - am I doing this right? Is there a better way? Can y'all help me figger this out?

Thank you in advance.

[SlimJim]

In effect you have resolved the problem if only one import takes place - even though it is clunky. Suggestion: make two status fields Staus1, Status2 and set them using the two imports and then define your proper status field using an auto-enter Case statement - even more clunky? Working on the assumption that Status2 is done second.

If(

Get(ActiveFieldName) = "Status2";

Case(

Status2 = "Cancelled";"Cancelled";

Status1 = "Rescinded";"Rescinded";

Status

);Status

)

[Søren Dyhr]

Since a proper designed database even an exported one usually contains a recordID, can this be imported into a dummy field, utilizing the "Update matching records in found set" ...but any field will do, as long as something to be imported exists.

....what it results in is a found set of records that can be either looped thru or scripted replaced with the desired value. It doesn't matter if the number of matching fields are one or several ...Filemakers search engine is quite up to it.

BTW is it interesting to note that it's called Import Summary in the import confirmation dialog.

--sd