Can't figure out why this calc doesn't work

[Kent Searight]

I am trying to create an auto-enter calc in a repeating field (in 1 table)that gets its values based on a logical calc that uses values from 3 foreign tables. I've attached a file that shows what I'm trying to do. Here's a quick overview of the scenario and what I'd like to accomplish:

There are 4 tables total.

Table "Components" shows the quantity of components a customer wants to buy.

Table "MachineSpecs" shows a "speed table" of quantities and the corresponding speeds that the machine will operate at to produce those quantities.

Table "PrefGlobals" has a global field "constant1" that always has the value "1", which I use for the Extend() function.

Table "MachineProcess" has a field "speedForCompQty", and that is where I wish to have an auto-enter calc that returns values from MachineSpecs::brkSpeeds based on the values in Components::quantities.

This is my calc that I can only make work if I use a "quantity" field in Table "MachineProcess". Problem is, I need the "quantity" field referenced by the calc to be in "Components":

Case (

//Check to see if the Component quantity falls within the first quantity range in MachineSpecs. Use result from repitition 1 of MachineSpecs::breakSpeeds field if true.

Extend (PrefGlobals::constant1 ) * qtyTest ≤ GetRepetition ( MachineSpecs::breakQtys ; 1 ) ; GetRepetition ( MachineSpecs::breakSpeeds ; 1 );

//Check to see if the Component quantity falls within the second quantity range in MachineSpecs. Use quantity from repitition 2 of MachineSpecs::breakSpeeds field if true.

Extend (PrefGlobals::constant1 ) * qtyTest > GetRepetition ( MachineSpecs::breakQtys ; 2 ) and Extend (PrefGlobals::constant1 ) * qtyTest ≤ GetRepetition ( MachineSpecs::breakQtys ; 3 ); GetRepetition ( MachineSpecs::breakSpeeds ; 2 );

//Check to see if the Component quantity falls within the third quantity range in MachineSpecs. Use quantity from repitition 3 of MachineSpecs::breakSpeeds field if true.

Extend (PrefGlobals::constant1 ) * qtyTest > GetRepetition ( MachineSpecs::breakQtys ; 3 ) and Extend (PrefGlobals::constant1 ) * qtyTest ≤ GetRepetition ( MachineSpecs::breakQtys ; 4 ); GetRepetition ( MachineSpecs::breakSpeeds ; 3 );

//Check to see if the Component quantity falls within the fourth quantity range in MachineSpecss. Use quantity from repitition 4 of MachineSpecs::breakSpeeds field if true.

Extend (PrefGlobals::constant1 ) * qtyTest > GetRepetition ( MachineSpecs::breakQtys ; 3 ); GetRepetition ( MachineSpecs::breakSpeeds ; 4 )

)

Anyone have any ideas??? I've been pulling my hair out for days over this one.

Thanks in advance for any help.

SpeedCalculation.zip

[-Queue-]

I think your logic is a bit off.

Try

If( not IsEmpty(qtyTest);

Case(

qtyTest ≤ MachineSpecs::breakQtys[1]; MachineSpecs::breakSpeeds[1];

qtyTest ≤ MachineSpecs::breakQtys[2]; MachineSpecs::breakSpeeds[2];

qtyTest ≤ MachineSpecs::breakQtys[3]; MachineSpecs::breakSpeeds[3];

MachineSpecs::breakSpeeds[4]

)

)

Also, your relationship to PrefGlobals (whose values are not needed for this calculation) is unnecessary. Globals can be accessed from any table, without a relationship. A relationship from a global to a global will not be very fruitful either.

[Kent Searight]

Sorry Queue, I think I did a crummy job of explaining and my sample wasn't a very good example. Please see the new attachment. Notice that I deleted the qtyTest field in Table "MachineProcess". I never had any intention of using it...I was just trying to show how I could get the calc to work if I used a quantity field in the same table as the calc. Now notice that the "quantity" field in the layout for MachineProcess is from the Table "Components". This is what I want the calc to reference but I can't seem to make it happen.

Again, sorry for such a poor explanation in the opening of this thread. I think I tried to over-explain things and made it more confusing.

SpeedCalculation2.zip

[Søren Dyhr]

Well you're on 7.0 and the need for calc's to tunnel values is a bygone issue, so when you writes:

I'd like the above repeating field to show the machine speed (as taken from the

MachineSpecs table for the component quantities).

...is it about time you either read this:

http://www.filemaker.com/downloads/pdf/techbrief_fm7_foundations.pdf

...or my choise; watch this video:

http://previews.filemakermagazine.com/videos/541/GraphRules_full.mov

Then will I leave it to Queue to explain why repeaters should be used in this case, I would have used cut up portals

--sd

[Kent Searight]

Hi Søren,

I'm afraid that my lack of providing a clear and concise explanation of my goal, combined with a poor example of the solution, has confounded everyone's best efforts to help me, so I'm going to explain again and provide an example that more closely reflects what I'm trying to accomplish.

In a Table "Components", a user can add a machine process to the component via a portal that shows records from Table "MachineProcess". I would like to have the suggested (default) operating speeds of any given machine process appear in the portal. These suggested operating speeds are in the table "MachineSpecs". I would also like the user to be able to alter those machine speeds. Now, if the user changes any of the component quantities, I'd like the MachineProcess speeds to be reset to their default settings from MachineSpecs, regadless of whether a user modified the speed or if the default speed value was being used. This is very important.

Make more sense? I hope this explanation does it! Thanks for all your patience.

SpeedCalculation3.zip

[Kent Searight]

Some further clarification:

These suggested operating speeds are in the table "MachineSpecs".

What I meant to say is, "These suggested operating speeds are values from the table "MachineSpecs"."

[Søren Dyhr]

So what you say is that the figures should be entered like default values, but if the user desides some other values for a specific component will they be stored.

This is similar to an invoice issued with a price level that fluctates from day to day, but an invoice being a historic document on a deal agreed at a the specific time.

There are several ways to accomplish this, while lookups might have been answer to this problem earlier could it instead be made by script parameters based on dynamic valuelists, but this forces the generation of new records under script control ...that ain't that bad under some circustances...

What might serve your purpose best, couldn't be established on the given info.

--sd

[Kent Searight]

So what you say is that the figures should be entered like default values, but if the user desides some other values for a specific component will they be stored.

Precisely. I've figured out another way to get around the problem by using auto-enter calcs in non-repeating fields. The problem that I kept encountering was getting an auto-enter calc to work in a repeating field in any repitition other than 1.

So, although I've addressed the issue by using a different approach, I'm still completely baffled as to why a repeating calculation field works perfectly well when in all repititions, but an auto-enter calc doesn't work with the same calc formula. I've attached a simple, 2 table solution that I've been trying to make an auto-enter calc work in a repeating field, but I can't get it to work. Just to satisfy my curiosity, I'd love to hear how to achieve this.

autoenter.zip

[Søren Dyhr]

Just to satisfy my curiosity, I'd love to hear how to achieve this.

The trigger-field should be a repeating field as well, this can be done via a some calcfields - but you found a much more obvious solution to the problem ...And have learned the lesson we all learned, why a vast number of developers don't embrace repeaters ...they're awkward to make behave.

As a rule of thumb could you say that repeaters is for utility purposes, such as relational keys when they once and a while seems easier to figure out than recursive definitions in custom functions.

But if you play around with it, is some of the functions you need to investigate: Get(CalculationRepetition) and Substitute( ....

Enjoy

--sd

[SlimJim]

The only way that I have found of achieving the aim in your sample, i.e. auto-enter the five numbers from a field in the other table. is to do an Auto-enter Lookup of the field.

[Kent Searight]

Thanks for the input, SlimJim. That's the only way that I've been able to make it work also, but unfortunately my solution won't function as intended with a lookup. I guess that I'll exercise a little more caution in the future with respect to using repeating fields in auto-enter calcs.

[Kent Searight]

Well, Søren, now I understand why so many developers (including you) have voiced opinions on this and other forums against the use of repeating fields, except in the certain circumstances where they're well suited. Thanks for your help!