Auto enter by calculation fails with related data

[IdealData]

Here's the calculation

LineValueNett =

Sum ( QDETAILS.QLineID.QLINES.QuoteID.QUOTES::ValueNett )

This works fine when LineValueNett is a calculation but fails when LineValueNett is a number field (auto-enter by calculation)

QLINES is the parent of QDETAILS in the relationship and they are separate tables but in the same file.

I replicated the same calcs using a relationship using the same source table (ie a self join) and it works!!!

So, is this a bug? Why would it work when the related data comes from the same source table but fails when the related tables are different. More curiously why work for a calculation field but not a number field with auto enter using the same calc.

I have a workaround using scripting, but that's not the point.

Any help or suggestions welcome.

[mr_vodka]

Since it references a related TO, the calc will be unstored. It will update each time. However, with an auto-enter, there is nothing that triggers it to recalc and store the data. In order for it to recalc, you wil need to reference a field within the same table. Sometimes you can use a trigger field to make it recalc.

Case ( trigger;

Sum ( QDETAILS.QLineID.QLINES.QuoteID.QUOTES::ValueNett )

)

[Søren Dyhr]

but fails when LineValueNett is a number field (auto-enter by calculation)

Why do you need to store it in the first place??

--sd

[IdealData]

Mr Vodka. Nice try, but...

I tried this

Case (

not (IsEmpty ( QLineID ));

Sum ( QDETAILS.QLineID.QLINES.QuoteID.QUOTES::ValueNett )

)

QlineID always has a value so it should do as you suggest but no joy.

Remember also I said that it works with data from a the same source table even when it is related, but not when they are different tables.

Soren

I want the user to be able to override the calculated values. This is a quotations system, so it makes sense to permit rounding of figures.

Would you quote 197.67, when sales people would quote 199.95

[Søren Dyhr]

from the same source table but fails when the related tables are different.

Ah I didn't catch this, it's simply a design flaw here, you have the problem because of entity duplication, you can't track unstored values from other tables if they're not rendered by a layout event, as simple as that. Comment call this the filemaker condurum!

Do youself a favour and merge the two tables with two keyfields, one for the order and one for the quote.

--sd

[IdealData]

Mr vodka

You are partly right. I tested the idea using a lookup and then forced a re-lookup and the values are corect, however this still needs a script to do it.

[mr_vodka]

Ideal, I think you misunderstood my post. Just because the trigger field has value in it does not mean that it will recalc. The trigger field will have to be set again.

Case ( trigger;

Sum ( QDETAILS.QLineID.QLINES.QuoteID.QUOTES::ValueNett )

)

If you have a bunch of fields that need to recalc, you can reference one trigger field. However, this field will still need to be set.

[comment]

Comment call this the filemaker condurum!

I most certainly do NOT!

[Søren Dyhr]

No offence mate, but thats how I remember it ... await my tracking it down, then you should every opportunity to correct me or your self!

--sd

[comment]

Go ahead, make my day. I don't use that word (and if I did, I would try to spell it correctly).

[Søren Dyhr]

I don't use that word

Wrong!

http://www.fmforums.com/forum/showpost.php?post/224848/

...althoug it was the post I was tracing!

--sd

[comment]

OK, so I used it once. At least I spelled it correctly then - and it does look appropriate in the context.

Still waiting for the real thing...

[Søren Dyhr]

I think I'm getting there ...it was as if this post was an echo of it:

http://fmforums.com/forum/showpost.php?post/161053/

At least is this question very similar to this in gist:

http://www.fmforums.com/forum/showtopic.php?tid/171550/fromsearch/1/hl/freshen/tp/0/all/1/

--sd

[LaRetta]

Go ahead, make my day.

DIRTY HARRY, right? With the gun and the attitude!! I haven't seen it but I've heard about it!

[comment]

I see "Abracadabra", but no "conundrum" - and no "condurum" either.

Hmm... "Abracadabra" is a good word - I should use it more often.

[comment]

That's right. Seems like Søren's feeling lucky today...

[AudioFreak]

Now you have a new word and a song to get stuck in your head.

http://www.youtube.com/watch?v=cyrcN7IwmI0

[comment]

If that link leads to something that rhymes(?!) 'abracadabra' with 'reach out and grab ya', I'm not touching it.

[David Jondreau]

Good to see the cat is back!

[AudioFreak]

If that link leads to something that rhymes(?!) 'abracadabra' with 'reach out and grab ya', I'm not touching it.

I would never link that song you are refering to

Good to see the cat is back!

Yeah I kinda missed seeing it myself.

[comment]

Nice try, but I am not falling for it.

[Søren Dyhr]

That's right. Seems like Søren's feeling lucky today...

Well not quite, unless you have had second thoughts with your wording of things in this post, and changed it?

http://www.fmforums.com/forum/showpost.php?post/186958/

...it has made inroads to my mind, since it's one of those anglo saxon only terms which lack etymological links to other european languages, and therefor makes the ill fit of a squared jigsaw piece for a round hole in my memory.

http://dictionary.reference.com/browse/conundrum

--sd

Edited March 11, 2008 by Guest

[comment]

No, not at all - that is the original. And it's not just a matter of wording - the meaning is entirely different:

The fact that Filemaker does not monitor related records is a natural result of how Filemaker works in general. Therefore, a design that follows the "Filemaker paradigm" will not need such monitoring, and no problem will arise.

OTOH, the expression "Filemaker conundrum" implies some inherent limitation, or a contradiction in the underlying logic. That is the exact opposite of what I am saying.

IOW, "paradigm" => harmonious; "conundrum" => disharmonious.

[Søren Dyhr]

"paradigm" => harmonious; "conundrum" => disharmonious.

Great to know.... which take you have on it!

What is the etymology of canundrum?

We haven't seen that spelling of the word before, but it has existed in just about every other conceivable form: conimbrum, quonundrum, conuncrum, quadundrum, cunnundrum, and connunder, among others. The word first appears in the written record in 1596 as conundrum, with the meaning "a pedant or ninny". By 1645 it had come to refer to a pun which pivots on similarities in meaning, such as in the pair "paradise" and "pair o' dice". By the late 18th century conundrum referred to a riddle in the form of a question whose answer includes a pun, and, similarly, any enigmatic problem or question (the latter being the most common meaning of the word today). Overlapping that meaning, beginning in the early 19th century, was the use of conundrum to mean anything for which one cannot think of a name, i.e. a whatchamacallit.

So why have we used so much space to describe the evolution of conundrum's meaning instead of discussing its derivation? Unfortunately, no one knows conundrum's origins. However, there are clues which indicate that it may have originated in Oxford, perhaps as a parody word invented by students of the university there. The OED doubts that the earliest recorded use of the word ("pedant or ninny") reflects its original meaning.

From:

http://www.takeourword.com/Issue057.html

--sd

Edited March 11, 2008 by Guest

[comment]

it's one of those anglo saxon only terms which lack etymological links to other european languages

I always thought it was a word made up in jest to sound like some highbrow Latin term. And 'paradeigma' is just Greek. I hope that's enough 'etymological links' for you.

[IdealData]

I'm glad I ducked in the hail of bullets!

Can some one just answer my question, please.

[comment]

I thought your question was answered long ago. If you are referencing a related field, the calculation must be either unstored, or it will not update when the related field is modified. It doesn't matter if it's a self-join or not.

To solve your issue, you can add a local Override field, and make your calculation (unstored) =

Case ( IsEmpty ( Override ) ; Override ; Sum ( Related::Field ) )