Case -Replace

[Wim Rippen]

I have FM12Pro adv and FMServe 12 pro. and Win7.

------------------------------------------------------------------------------------------------------

Trying to perform a CASE and REPLACE but not working  so good.

I have a Script,which should replace (number) Field "FrUSA" in Database " Allfreights" linking "Shport'fields" both sides.

=============

Case(
T01_ACCOUNTS::Shport="Houston";"800";
T01_ACCOUNTS::Shport="New York";"1200";
)

=============

(this is just an short example of Shipping ports,total file upto 600+ destinations in same script.)

 

If I make a Trial Script with only one line : T01_ACCOUNTS::Shport="Houston";"800"; then all other destination are set to blank,empty.

 

If I run the total scrips (600+) then only a part of it is updated/replaced, but about half of its fields FrUSA are set to zero (0)

 

Where is the error ??

 

Thanks for helping me out.

Willlaim

 

 

[Wim Decorte]

With that may ports you would be better off storing those in a table of their own and picking up the correct number through a relationship, instead of a massive case statement.

 

As to why it fails: impossible to say from what you are posting.  The case statement logic seems ok.  If some records update and others do not I would suspect that the relationship from where you are to T01_ACCOUNTS does not always work.

[hbrendel]

There is no default result, which should be the original content of the field

[Lee Smith]

Automatic message

This topic has been moved from "FileMaker Pro 12" to "Calculation Engine (Define Fields)".

 

The General Topic Areas are for discussion of the new features, tools and functions that were introduced in that version, and not for asking How-To questions.

[LaRetta]

Both spot-on responses. Your calc doesn't address records which aren't Houston and aren't New York so as Henk suggests, you need to decide the default value for other cities. If they should remain unchanged then you insert the field again as:

Case(

T01_ACCOUNTS::Shport="Houston";"800";

T01_ACCOUNTS::Shport="New York";"1200";

T01_ACCOUNTS::Shport

)

But like Wim, I wonder about the relationship so if calc change doesn't resolve it then we need more information such as full script and how the tables are related or a zipped copy of your file.