Script to find matching fields and perform an action

[miquelsit]

Hi all!

I've been reading this forum and learning from all of you for a while now (I am very new to FM), for some days now I have been trying to figure out how to fix a problem, I have read a lot and browsed through a myriad of posts and can't seem to work it out on my own, I will be very thankful if any of you could help me.

I maintain a big database of products indexed by their UPC (EAN) code, they mostly come from one provider so importing stock updates is pretty easy, but now we have a second provider and I keep their products also indexed by UPC code in a different table. The problem comes when I try to match UPC codes in provider1 (they share most of the catalog) with the codes in provider2 to see which one is cheaper so that I can transfer stock, price and SKU to my main database (replacing the values from provider1, if they have it cheaper).

 

My failed approach goes as follows:

 

Show All Records

Sort Records [Restore; No dialog]

Go to Record/Request/Page [First]

Loop

  Set Variable [$upc; Value:Provider1::upc]

  If [Provider1::upc = Provider2::upc]

     Set Field [Provider1::Test; "O"] // This is just to test if the search works

  Else

    Set Variable [$upc; Value:Provider1::upc]

  End If

End Loop

 

After I execute this script, nothing happens, so I'm not sure if the script is taking the first code and matching it with all the codes in the second table and then exits or it's just not doing anything...

 

Thank you!

 

Miquel

[bruceR]

Not sure what you mean by nothing happens.

As written it is an endless loop that will do nothing after the first time through.

 

It looks like you forgot to put a go to next record, exit after last statement just before End Loop.

[miquelsit]

Thanks for your reply Bruce!

I wrote the script to mark a field with a 0 if it's UPC matches the other list, I know for certain that they both have matching codes yet I get no 0s on my list, I added next record, exit before the End Loop but I still get no matching tables. The way I believe the script should be behaving is like this... arrange Provider1 by UPC code, take the first one, go to Provider2 and look for a matching one (going though all the records in the table), if you don't find any, do nothing, if you do find one, write a 0 on the predefined field. go to next record, rinse and repeat until you get to the end.

Did I miss the step where I tell FM to go to the next record in Providers2? How could I Script that?

 

Miquel

[miquelsit]

Ok, I see what happened, I forgot to establish the relationship... Well, now it's working!

Thank you again for your help Bruce.

 

Miquel