Hi

I have a frustrating problem. I've worked all afternoon to try and
figure out the issue but to no avail.

I have a contacts database which is related to a billing db.

Upon clicking a "new bill to this contact" button in the contacts db, a
script copies my contact's id, switches to the billing db and launches a
script in the billing db that simply creates a new record, pastes the
contact id into the contact id field and commits the record.

So far so good.

The billing db has a "client" text field that auto-enters all the
pertinent client data (name, company address, etc) through a calculation
based on the client id.

The problem is that for some client records the whole things works
flawlessly while for others I get nothing.

I've simplified the field from being a complex calculation (at first) to
a simpler one (second try), to a text field with a simple auto-enter
calculation (third try...) yet still to no avail.

Complex client entries (lots of info: addresses, emails, phone numbers)
seem to work fine while others with equal or less data don't.

I thought the:

If (IsEmpty (compagnies::téléphone_compagnie);"" ; "Téléphone: " &
compagnies::téléphone_compagnie & "¶")&

construct would take care of any fields without data.

You can see the whole calculation screen at
http://www.heliomedia.com/calculation.gif

[And you can tell what kind of mood I have been in at the end of the
calculation! ]

Thanks for any help or suggestions.

Eric

--
Eric Girouard
---
http://www.heliomedia.com
Canada 35mm at a time

eric_at_heliomedia_dot_com

Verify that both IDs are of type number.

Create a new billing record manually then manually enter a Client ID
that doesn't work to see what happens.

If there is no matching record for the Client ID, your calc will always
be blank because the checkbox in the bottom left corner "Do not
evaluate if all referenced fields are empty" is checked.



Just a suggestion
If(isempty(Name), "", Name & " ")

can more easily be written as

Name & If(isempty(Name), "", " ")


Good luck

Thanks for the quick reply FP,

1. Both IDs are indeed of type number.

2. Manually entering the client ID into a blank new record bill results
in the same error.

3. Turning the "Do not evaluate if all referenced fields are empty"
checkbox off resulted in the same error.

- Eric


FP wrote:

I did just make a quick look at your calculation.
It made me think to make three small remarks :
- it has been demonstrated long ago that ones, as far as they can ie in
Calculations and not in Scripts lines, should avoid the use of 'If' -
especially concatenated ones - because of risk of wrong interpretation
of its behaviour - it seems to have changed from FMP v4 to v5 (?) -. I
seem to recall that 'If' deals "wrongly" (apparently) when some fields
are empty.
Anyhow, whatever the reason, change every 'If' with a 'Case' which
behaves the way we think it should.
It does not cost much and it will avoid a potential risk of
miss-behaviour.
- doing the same job by script runs - according to my experience -
around 10 times faster than with calculations.
- sometimes one puts "&" in place of "and" and vice versa. Maybe could
you verify that carefully.
Just my 2 cents.
Remi-Noel



"Eric Girouard" <eric@[NOSPAM]heliomedia.com> a écrit dans le message de
news: 5luOe.39244$7b7.554849 (AT) weber (DOT) videotron.net...

In article <1124759710.754204.178410 (AT) f14g2000cwb (DOT) googlegroups.com>,
"FP" <a (AT) pottnerconsulting (DOT) ca> wrote:

That little checkbox option often seems to be a bit flakey, so I
usually do my own version as part of the auto-enter calculation itself.

Simply wrap your current auto-enter calculation inside an If statement
(or case if you prefer) that checks the appropriate fields - it returns
a blank if any of them are empty, and the appropriate calculation if
they all contain data.

I won't even try typing out Eric's large calculation, but here's
simpler example. If you want the auto-enter calculation to be

First Name & " " & Surname

then instead use

If [IsEmpty(First Name) or IsEmpty(Surname),
"",
First Name & " " & Surname
]

This forces the auto-enter field to remain empty until both fields have
data in them, and THEN the auto-enter calculation is performed.

In Eric's case he may be able to simply check that the related record
exists,
eg.
If [Count(Relationship::KeyField) = 0,
"",
{long calculation goes here}
]



Not really - it's almost exactly the same and even has the same number
of characters to type. )




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

Very useful suggestions. I will try them and keep you posted.

In the meantime, here are some behaviours which maybe useful as clues in
finding what is going wrong:

1. I replaced the whole long set of statements in the calculation with
one single reference [for example: calculation (in Bills) = text field X
(in Contacts)] and yet again some records work and some don't. (So the
calculation itself does not seem to be the culprit!)

2. Some records execute the calculation properly and:
a) return the proper set of info or
b) return the "else" result of the initial If statement ("kill me now")

wheras some other records return empty formatting (spaces, parenthesis,
paragraph returns) but no data whatsoever.

Any hunches?

Eric

OK. If we are talking hunches here goes, My hunch is that you have a
broken relationship, incorrect context or duplicated ID numbers. I think
you have covered all the obvious problems so these are all somewhat
unlikely.

If the lookup produces nothing then logic says there is no related
record or the first related record has empty fields. No related record
leads to "kill me now" so if you are getting that result then in that
case there is no related record. On the other hand if you do not get the
"kill me now" then you have a related record with empty fields. Maybe
there is a duplication of ID numbers with the first related record
simply having an ID and later records having the data.

From your calculation it seems hat you are using v7 so there is also
the possibility of incorrect table occurrence context. Another hunch
also does the relationship have conditions other than equality of ID
numbers?



Eric Girouard wrote:
--
Dan
Using
FMP7.03, WinXP SP2

Dan,

I think you have set me on the right track.
Since I am updating a v6 FM db to a multi-file relational v7 db, I think
I had an old contacts table in there that isn't a reference to the new
contacts file.
More disassembling and reassembling before I can tell for sure, but I
think this is it.

Thanks,

E.

Dan wrote: