Circular Definition

[noahbody]

Hi folks,

Some major egg on my face, but I have to ask.

I've created a calculated field called Service Fees (code below)

If(Total Payments <= Document Total,If(High Risk = "High Risk" and Amount Issued >= 500 , (Amount Issued - 100) * Interest Amount High, If(Amount Issued < 500 and High Risk = "High Risk", Amount Issued * Interest Amount High, If(High Risk="" and Amount Issued >= 500, (Amount Issued - 100) * Interest Amount Low, Amount Issued * Interest Amount Low))), 1 * some magical field = to Service Fees)

It's not pretty but hopefully will become functional. What I'm trying to do is when a certain criteria is reached it will stop calculating this field and display the value that it stopped at. This has so far been my solution to it, however trying to display the value would cause a circular definition error (makes sense, but I need a way around it).

Any and all help is greatly appreciated.

Sincerely

NB

[kennedy]

Not sure I followed your intent, but this might be what you're looking for:

GetField("Service Fees")

It is equal to Service Fees... but won't cause circular definition errors.

If not, you might need to do this using Lookup Fields; however, I didn't follow what's the calculation trigger, so I am not sure how to advise you on that... and that would seem to be an issue either way.

HTH.

[Ugo DI LUCA]

Not sure what's your Circular Problem.

May be a Case Statement would help.

Case(not isEmpty(Magical Field), Magical Field,

(Amount Issued - Case(Amount Issued >= 500,100,0))*

Case(not IsEmpty( "High Risk"), Interest Amount High, Interest Amount Low))

[noahbody]

Hi Kennedy,

When I replace "some magical field = Service Fees" with GetField("Service Fees")it no longer gives me the circular definition error, however it appears to creat a logic flaw in FMPro as it crashes and corrupts the db forcing me to use a back up.

Maybe I should leave the code out of it but instead try and explain what I'm trying to do in plain English. I have a calculated field called "Service Fees". When a certain criteria is met, I would like this field to stop calculating and display the last value calculated. Is that at all possible?

Thank you so much.

NB

[Ugo DI LUCA]

Well,

I think we are several users in the last days to have experienced and reported this behaviour with GetField. What happens is that it recalculate in a loop forcing you to quit the application.

I was quite stuck a few days ago with no back-up, but I succeed in stoping the loop at the opening, jumping to the Menu. Just in case it might help someone with these kind of problem.

You may want to index the calculation to see if it does it again or if it solves this issue. You should also look to the Case instead of If Statement I think.

HTH

[noahbody]

I'll definitely give the Case statement a try, if for no other reason than to beautify the calculation. Thanks Ugo your suggestion is much appreciated.

NB

[kennedy]

I don't get the preference of Case over If... If reads better to my English-speaking brain. I always use If. Now, in other languages, Case is far more interesting and very useful and makes sense to its name, like Case(expression-that-returns-a-number, option-if-number-is-1, option-if-number-is-2, and so on...). But I digress. Back to the point of this thread...

As to your real goal (thanks for the English description), it is a very common need... and has been discussed many times on the forums. Various solutions have been proposed and seem reasonable, but I honestly can't give my own recommendation yet... why? Because I haven't gotten to that part of my database and so haven't done any testing or experimentation with the various methods yet.

Anyway, I recommend that you search in the forums for things like "invoic" and "pric" and "sale" (for invoice, invoicing, prices, pricing, etc.)... which is where this scenario often shows up. For example, you have inventory items with current prices. You have current sales tax rates. You have different discounts and promotions. Then you have someone come in to your store and start adding items to their cart... the items each compute their price from the data in all those related records. And then they checkout. At that point, you want the prices of those items to freeze in place. So, if next month you raise the prices of the T-shirt and aspirin that they bought, and the tax rate increases, you don't want your historical record of what they paid to start mismatching with what they bought. Make sense?

Anyway, there have been a number of threads on how to best do this... and there are a number of example databases out there that you can look at.

Some use lookup fields. For example, you have a price calculation field that just keeps computing. Then you have a price lookup field that looks up the price from the calculation field, triggering on when they go to pay. Then you have a price display field that chooses one or the other depending upon the state of the record... completely hiding from your user the presence of the two underlying fields. Or something like that... again, I haven't started working on that part yet.

HTH.

[Ugo DI LUCA]

Ah.

Ok, that was the purpose....

Personnaly, I use lookup and a key to loxk the lookup.

Now, from what you said about Case or IF...

Hmm. When working with numbers, you should prefer Choose(field, value for 0, value for 1,....). Much quicker.

[-Queue-]

Extra benefits of Case vs. If: If() does not interpret an empty field as false, while Case() does. And for If( rel::constant, "one", "two" ). If the relationship is not valid, you'll never get "two" as a result, just a null, whereas Case will provide the desired results.

I like to use If() in boolean situations with non-null boolean fields (e.g. If( boolflag, "yes", "no" ), Case() for fields with more than two possible results, and Choose() for any fields that produce results based on a consecutive series of numbers.

One additional interesting thing to note. If( boolflag, 1, 0 ) => Case( boolflag, 1, 0 ) => boolflag...and If( this = that, 1, 0 ) => Case( this = that, 1, 0 ) => this = that...that is, conditional statements with only two possible results (true and false) within If or Case statements are redundant. So, if you find yourself writing If(..., 1, 0 ) or Case(..., 1, 0 ), keep the conditional and remove the rest. If you want the opposite boolean result, (..., 0, 1 ), just use 'not' in front of the conditional statement. Case( this = that, 0, 1 ) => not this = that

[spb]

One big advantage of Case over If that may directly apply to the original query is that a Case function will stop as soon as it arrives at a correct condition. It will not test any following conditions (which is why you need to be careful about the order). In this example:

Case(

Value = 1, "one",

Value = 2, "two",

Value = 3, "three",

Value = 4, "four",

"many")

If the Value field contains 2, the function will stop evaluating and fill the field with "two." This is a subtle but important distinction between Case and nested Ifs. The function will not test for 3 or 4 once it arrives at 2. With nested Ifs, it always tests for all values.

So the original problem, how to make a calc field stop calculating, might be solvable this way. If the Value field will continue to climb, and you need your calc to stop, you could dump the contents into a non-calc number field when it reaches a desired point:

Case(

Value = 1, "one",

Value = 2, "two" SetField(NumberStore, CaseField),

Value = 3, "three",

Value = 4, "four",

"many")

Now, once Value has arrived at 2, the field contents are updated and placed in a number field. If value later goes to 3 or higher, NumberStore still contains the value needed from when Value was 2. You could use NumberStore in reports & further calculations instead of the main calc field.

Steve Brown

[kennedy]

BTW, Case is way better than nested If's... no question there.

But it seems some are strongly opposed to binary If... that is what

I've never understood.

[-Queue-]

spb said:

Case(

Value = 1, "one",

Value = 2, "two" SetField(NumberStore, CaseField),

Value = 3, "three",

Value = 4, "four",

"many")

Now, once Value has arrived at 2, the field contents are updated and placed in a number field.

I'm curious as to how a Case statement in a field definition can perform a Set Field as you've described here. Perhaps you were speaking of using the value of the result in a script?

[spb]

Oof! You're right. I frequently get script functions and calculation functions mixed up. Why *can't* we have a Case script function?

At any rate, the problem would seem to be to use a calculation to fill another field once a criterion has been satisfied. I wonder if it would be possible to use Replace? Something like;

Value = 2, "two" Replace(NumberStore, 1, 20, CaseField),

The 20 is an arbitrarily large number to ensure that you always replace everything.

Is there any reason this wouldn't work? The "NumberStore" field is only filled, or "replaced," when the second Case condition is satisfied. So if new data causes the Case to move on to the fourth or fifth conditions, the NumberStore data will remain as it was.

Steve Brown

[Vaughan]

" Why *can't* we have a Case script function?"

Because scripts and functions have nothing in common. hey are separate things and it's important that you differentiate between them.

A script is a process. A function is a mathematical or logical calculation.

Example: you can count the potatoes with the Count() function. You can mash the potatoes with a script. You cannot have a function that mashes.

[Lee Smith]

Hi Vaughan,

Nice analysis, but isn't a "If statement" a function too?

Lee

[kennedy]

Vaughan, I take it that you've never programmed in Lisp or Scheme;

or any functional language? Or really, even languages like C or C++?

I repectfully, but strongly, disagree with your statement... whether your goal is simply to compute an answer OR your goal is to get something accomplished, in both cases you are going to:

• call a named procedure/function/script/process/whatever
  
• pass it values to tell it what to do
  
• the computer does some stuff
  
• it returns a result - could be the answer being sought, could be a status saying whether the task was successful, could be a combination, or it could simply return null/nothing
  

The distinction between procedures and functions is arbitrary and not useful. It forces you to learn two different syntaxes for the same thing. And it leaves very useful constructs only available *some* of the time. Further, you very often want to get things done while computing an answer at the same time.

Once they remove that distinction, then the scripts you write would be able to return a result and could be called from any Calculation! That would be swweeet!

With that done, the final step towards true elegance would be to make those Calculations/Expressions/Scripts first-class. You do that by adding an "Eval" function. Its somewhat like "GetField", but instead of being limited to a field name, that text string could contain any Calculation.

Once you have that, a first-class functional language, then the final step is to leverage it everywhere: wherever a value is called for, they should allow you to specify a calculation. For example, in the field validation dialog, a string is allowed. Instead, make that a calculation... you could use it like you do now (but you'd have to put " at front and back... OR you could do something like 'Case(gLanguage="English", "[english message]", gLanguage="French", "[french message]", ...)' to solve the query in the other active thread.

[spb]

Precisely, Lee. If we can have an "If" script function, then I can see no reason not to give us Case. Case *is* a process function, as well as a logical calculation. It both counts and mashes potatoes.

Steve Brown

[BobWeaver]

To add to what Brian said, the beauty of a programming language like Lisp is that not only is there no difference between a function and a procedure, there is, in fact, no difference between the program and the data that it processes beyond what the programmer defines. If Filemaker adopted something along those lines for its scripting and calculations, it would open up a whole new world of possibilities.

[-Queue-]

spb said:

One big advantage of Case over If that may directly apply to the original query is that a Case function will stop as soon as it arrives at a correct condition. It will not test any following conditions

I've seen evidence to dispute this. Case in point:

I have two files that are exactly alike except for their names. I have calcs within 'this' file that are determined by the filename. I have one calc field (cCorp) that tests which file I am in and returns a boolean as desired: PatternCount( Status(CurrentFileName), "corp_" ). There are files that will be available when the file is called X that will not be available when the file is called corp_X and vice versa. Assuming that fileA relates to corp_X and fileB relates to X, exclusively, then, according to your statement, the following calculation should perform only the first test when the file is named corp_X: Case( cCorp, fileA::field, fileB::field ). However, the actual result is that the file complains that fileB cannot be found, even though it has already correctly evaluated the test and provided the correct result 'fileA::field'.

This leads me to believe that Case() does in fact test for all conditions, even after one is found to be true, and this causes me to require additional fields for gathering related information, i.e. field1|corp and field1 versus just field1, and ruins the cool functionality I wanted to implement with limited field definitions. Has anyone done something similar to this and noticed the same thing?

[Ugo DI LUCA]

It's hard to say....

I would be tempted by another interpretation. Before going into this calc, FM checks that it could be calculated if needed, therefore alerts you if not.

Is the result different with if or choose ?

[-Queue-]

The result is the same with all three. This sort of stinks then. I guess I can trick the system by setting a global in the non-existent related file, with Error Capture on, to force it to fail and trap the error. Then it won't try to calculate when a user changes a record afterward. What do you think?

[Ugo DI LUCA]

Back to your calc...

What result would you get with :

Case( cCorp= corp_X, fileA::field, fileB::field ) ?

AND why do you get an error that the file could not be found ? Where is that file ?

[-Queue-]

cCorp = corp_x would never be true, as cCorp is either 1 or 0 and corp_X is a filename, not a field.

At the Corporate office, the branch project file is nonexistent. At a branch office, the Corporate project file is nonexistent.

I think forcing the nonexistent files to try to open is my solution. It seems to be working well so far, and I can reduce the need for two fields per calc back to one.

[djgogi]

Some time ago there was an thread on this forum discussing case vs if.

On that occasion I was substaining (and I still do) that all factors in case function are evaluated before the result is

returned.

I've also said that I would post result of my tests here but once I finished them

(and saw the results that showed no significant difference when shifting the "true"

parameter inside an very long case statement) I was to laisy to put it on a paper.

Ugo, it doesn't matter why they were avaluated the fact is, as various

examples can prove, that they are evaluated and then the result is returned.

Now I do not pretend to know the exact implementation of case function

inside FM application, but what I know for sure is how the instruction are stored

inside FM file.

Let's take an simple example

This is an case statement as seen inside FM calculation definition dialog

Case("abb" * "cda", "goran", "aaa" > "bbb", "anna", "ccc"="ccc","michi","a"="b","c")

and the following are the stored data inside FM file.

41

0A

05

03616262

05

03636461

12

05

05676F72616E

05

03616161

05

03626262

13

05

04616E6E61

05

03636363

05

03636363

11

05

056D69636869

05

0161

05

0162

11

05

0163

20

As you might see the block starts with length byte 0x41 (hex size of calculation block) followed by case function identifier (0x0A) etc.

So the FM is instructed to load 0x41 bytes into memory and to achieve this

all neccessary evalutation/calculation/substitution will be performed regardless on

whether the true factor has already been reached.

Dj

[-Queue-]

So, along these lines, in your opinion is there any speed difference between these two calculations?

Case( one, Case( two, Case( three, Case( four, 1, 0 ), 0 ), 0 ), 0 )

and

one and two and three and four

[djgogi]

Consider that loading blocks are 0x41 bytes in first example and only 0x0F bytes for the second one.

Once the data are loaded into FM compiled code you won't be able to see any difference.

The problem is that they have to be interpreted before entering the "true" case

which, BTW returns (and exit after) on first true condition or returns default if none of params are true.

Remember that FM needs continually to read/write data from/to file,

40 bytes here 40 bytes there and in case of unstored calculations it could really make the difference.

Dj

I was refering to your examples; It is the pure coincidence that the size of case in yoyr example (0x41) is equal to size of case in example I gave