Compare two fields prior to import?

[wendykdt]

I'm approaching this as a Scriptmaker issue, but it might be a Relationships issue... I want to compare data in two fields, in two Unrelated records in two different files, prior to importing from one file to another.

At the start of this process, File 1 will already have a single record in the found set. (I have scripting ensuring that.) One of the fields, "Business Unit", has data ("Consumer", "Industrial", or "Geos"). In File 2, a script imports an Excel-type file into File 2 (adds new record, then makes doubly sure only THAT record is in the found set). File 2 also has a "Business Unit" field, and it will receive data (one of the same three choices) from the Excel file.

What I want to do now is: Automatically check whether the data in Business Unit field in the current record in File 1 MATCHES the Business Unit field in the current record in File 2. If they match, I want to import the current record in File 2 into File 1, over the top of the current File 1 record (also at that time setting the foreign key field in File 1). BUT if the Business Unit fields do not match, it means the user chose the wrong Excel file, and I do NOT want to allow that import.

I have a relationship between the two files, between the Primary Key of File 2 and a Foreign Key field in File 1. (This relationship will be used later to allow File 1 to show that original imported record in File 2.) But at the point described above, the two records in the found sets are NOT related.

I tried using a simple IF script comparing the two fields, which apparently doesn't work unless the records are already related.

Do I have to create some sort of constant relationship between the two files so I can just compare two fields in the two current records? (One of my coworkers feels this could be a dangerous approach.) Would a lookup work?

Eventually, users will be running this script many times per day, and will not be viewing the Import dialog.

I'm feeling clueless. What's the best approach? Thanks...

[mr_vodka]

i guess you could set a global field with the value from the Business Unit field of file one and then use an if statment to compare the value to the value in Business Unit in File2, but I dont see why you are so opposed to creating another relationship.

[wendykdt]

Did I say I was opposed to creating another relationship? I'm not. I'm just not sure what relationship to create. (Note: File 2 WILL have more than one record with a business unit of, e.g. "Consumer". However, there would be only one record in the found set.)

Regarding the idea of creating a global field... I'm not clear what you're saying. I already have a non-global Business Unit field in File 1. Are you saying that an IF statement would work if the field in File 1 is a global? At this point, my test script in File 1 is:

If File1::Business_Unit IS NOT EQUAL TO File2::Business Unit, show custom dialog "Not equal. Cannot import".

Neither field is a global. This doesn't work, UNLESS the existing relationship (PK, FK) gives Filemaker a match--and there is no match yet!

Sorry, I'm rather a novice. Could you spell it out a bit more for me? :)

[Fenton]

I believe what mr vodka means is that if you set the value into a global field, then you can go over to the other file, and see if that global = your field. Because a global field only has 1 value per table, and is accessible from anywhere without a direct relationship (there must be a Table Occurrence of its table on the Relationship Graph of the other, but it needs no relationship). So you can set it into either, and look at it from either.

[wendykdt]

I get it! And I got it.(A night of sleep also apparently helped improve brain power.) It's working. Thank you!