How to write a script to choose result

Can anyone solve this problem for me?


My FM8.5 system

1. Table:Contacts, has data fields ShiptoCompany, ShiptoStreet,
ShiptoCity etc.
2. Table:Invoice/Layout:Invoice, has data
fields ::ShiptoCompany, ::ShiptoStreet, ::ShiptoCity... (a Lookup'
from Table:Contacts)
3. Table:Invoice/Layout:Manifest, has fields yet to be defined
indicating the ShiptoCompany, ShiptoStreet etc.

How do set up my Manifest fields so that I get what I believe to be an
EITHER/OR result?

EITHER the Table:Invoice/Layout:Invoice 'ship to' data from my
Table:Contact
OR the data entered into the Layout:Invoice - this would be new data,
an entry overide. The new data would be the result of a request to
ship to an address other than the normal Shipto... etc.

The default would be the Contact Shipto data. The new over ride data
taking precedence in the event of 2 options.

I cannot do a look up to the Table:Invoice/Layout:Invoice fields
because it is already referencing the Table:Contacts.

Hope that makes sense.

Thanks to any replies.

Re: How to write a script to choose result

In article <1194673393.789620.298930@i13g2000prf.googlegroups.com>,
buck.matthew74@yahoo.com wrote:

> Can anyone solve this problem for me?
>
>
> My FM8.5 system
>
> 1. Table:Contacts, has data fields ShiptoCompany, ShiptoStreet,
> ShiptoCity etc.
> 2. Table:Invoice/Layout:Invoice, has data
> fields ::ShiptoCompany, ::ShiptoStreet, ::ShiptoCity... (a Lookup'
> from Table:Contacts)
> 3. Table:Invoice/Layout:Manifest, has fields yet to be defined
> indicating the ShiptoCompany, ShiptoStreet etc.
>
> How do set up my Manifest fields so that I get what I believe to be an
> EITHER/OR result?
>
> EITHER the Table:Invoice/Layout:Invoice 'ship to' data from my
> Table:Contact
> OR the data entered into the Layout:Invoice - this would be new data,
> an entry overide. The new data would be the result of a request to
> ship to an address other than the normal Shipto... etc.
>
> The default would be the Contact Shipto data. The new over ride data
> taking precedence in the event of 2 options.
>
> I cannot do a look up to the Table:Invoice/Layout:Invoice fields
> because it is already referencing the Table:Contacts.
>
> Hope that makes sense.
>
> Thanks to any replies.

Firstly, it's not a good idea to use non-letter and non-number
characters in the names of things (files, tables, fields, Value Lists,
etc.) - you should not really use "/" or ":" since they have specific
meaning to FileMaker. You can use spaces and "_", but otherwise stick
to numbers and letters. Also stay away from FileMaker functions /
commands as names of things. For example, don't use "Beep" as a field
name, instead use "Sound" or "MyBeep".


If I understand your problem correctly, what you have is the Manifest
table that needs to retrieve addresses from another table - usually the
Contacts table, unless the Invoice table has an address entered.

"Either / or" is exactly what the "If "statement is for. The format is:

If (Test, TestSucceedsResult, TestFailsResult)

where "Test" is any calculation that returns a "true" or "false" result.

For example, the calculation:

If (Field1 = "A", "Yes", "No")

will return "Yes" when Field1's data is the letter "A" (without quote
marks, uppercase or lowercase), and "No" when it is anything else.


This means you don't need a script, since a simple Calculation or
Auto-enter fields will do. Your new field(s) only need an If statement
where the test uses the IsEmpty function to check whether or not the
Invoice table's field contains data.
eg.
In the Manifest table:

ShiptoStreet Text Result
= If (IsEmpty(Invoice::ShiptoStreet),
Contacts::ShiptoStreet,
Invoice::ShiptoStreet
)

A Calculation field will change itself whenever the
Invoice:ShiptoStreet or Contacts:ShiptoStreet fields change. A normal
Text field using an Auto-enter by Calculation option will be set when
the record is created and won't later change when either of the other
fields is changed - this option is usually best for historic data that
you don't want to change when a company moves locations.

This assumes that however you have defined your Relationships to the
Invoice and Contracts tables is working properly.


If you prefer, you can swap "If" for "Case" and it will work the same
way. "Case" can also have optional multiple tests and multiple results,
so it's format is:

Case (Test1, Test1SucceedsResult,
Test2, Test2SucceedsResult,
Test3, Test3SucceedsResult,
...
ALLTestsFailResult)

This allows you to return different results for different criteria, but
you may need to be careful which order you have the tests - the Case
statement will always return the result from the FIRST test that
succeeds.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)