Replace field content with IF..

[Wim Rippen]

I need to update regularly my database COMPANIES field "Freight" with a certain value like:

If [Companies::Shippingport ="Rotterdam"]

Replace Field Contents [No Dialog;Companies::Freight;"1500"]

End If

If [Companies::Shippingport ="Houston"]

Replace Field Contents [No Dialog;Companies::Freight;"1000"]

End If

etc etc

with 200 Different Shippingport,Differents Freights.

HOW TO DO THIS in a script ??

Thanks for the help.

William

[comment]

It would probably be best to retrieve the value from another table via a relationship, based on matching shipping port. It's not quite clear to me what your workflow is here, though.

[LaRetta]

Hi William, welcome to FMForums!

With 200 different shipping ports and rates, another table would certainly be in your best interest but a few points just for future about the script you are attempting to construct:

Replace Field Contents[] runs through the entire record set and not one record at a time so you would have ended up with Freight field in all records having the value of your last test. So with a set of records, you would use Replace Field Contents[] only once and instead put the 'test' within a calculation (in blue) itself as:

Replace Field Contents [ Companies::Freight ;

Case (

Companies::Shippingport ="Rotterdam" ; 1500 ;

Companies::Shippingport ="Houston" ; 1000 ;

... etc

)

But using another table as Comment suggests is the most logical in this instance. It is best not to hard-code values (your freight rates) into calculations because each time the rate changes, a developer would need to open field definitions to change it. Using another table is also more efficient and flexible. Replace Field Contents[] is unsafe if Users are in the system because record locking can't be detected (on per record basis) so only use it if you are sure Users won't interfere (stops the field from receiving the changed value), otherwise use a loop and Set Field[].