Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

Auto-Enter Advice please...

Discussion created by roryduffy on Mar 14, 2018
Latest reply on Mar 16, 2018 by user19752

Like • Show 0 Likes0
Comment • 20

Hi,

I have a very simple auto-enter requirement.

I have 2 fields, called "Q" and "Value".

Then I have a 3rd field called "Subtotal".

All 3 fields are number fields, in the same table.

The "Subtotal" field is set to auto-enter by calculation:

Q * Value.

I want the Subtotal to update if I change any of the values in Q or Value *but* I want the option to override the Subtotal.

e.g., If I put

Q = 2

Value = £12

Then I want it to auto-enter £24 as the Subtotal.

Then I want to have the option to change the Subtotal to £30 if I want to.
But if I then change the Q to 3, I want the Subtotal to change to £36.

If I have "Do not replace existing value of field (if any)" checked, it won't let me change £24 to £30 if I want to.

Yet, I've tried doing the following to allow the field to be driven by Q and Value if I need it to be:

- "Validate data in this field" set to Always

- Unchecked "Do not evaluate if all referenced fields are empty"

Am I missing something obvious? Basically, I'm trying to get Q and Value to drive the Subtotal, but then have the option of override the Subtotal (i.e., change the Subtotal, but then have Subtotal auto-update if 1 of the other 2 fields are changed).
Thanks
Rory

Outcomes

20 Replies

erolst Mar 14, 2018 7:17 AM
Basically, I'm trying to get Q and Value to drive the Subtotal, but then have the option of override the Subtotal (i.e., change the Subtotal, but then have Subtotal auto-update if 1 of the other 2 fields are changed).
I'm a bit surprised; if the "Do not replace …" setting for the auto-enter calc is unchecked, the field should behave exactly as you desire.
Like • Show 0 Likes0
Actions
philmodjunk Mar 14, 2018 7:31 AM
So as a test, I defined the three fields as you describe.

I then entered 4 as the value for Q and subtotal showed 0. I entered 5 for Value and got 20. I was then able to click into SubTotal and change the value to 26.

This was done using FileMaker 16.04 on Windows 7.

Note that any editing of Q or Value will update Subtotal to show a new calculated result.
1 person found this helpful
Like • Show 0 Likes0
Actions
beverly Mar 14, 2018 7:34 AM
Just for thoughts:. If you do enter the "total" shouldn't the other fields be cleared?
Beverly
Like • Show 0 Likes0
Actions
TKnTexas55 Mar 14, 2018 8:34 AM
If you have
Q=5
Value = 10
Subtotal = 50
But if you overwrite 50 with 55, do you want any action to change value to 11? Or just have the subtotal be 55 going forward?
Like • Show 1 Like1
Actions
roryduffy @ TKnTexas55 on Mar 14, 2018 8:37 AM
TKnTexas55
No, just to have the subtotal be 55 going forward.
Thanks!
Like • Show 0 Likes0
Actions
roryduffy @ erolst on Mar 14, 2018 8:39 AM
erolst
It's not though, if I have "Do not replace existing value of field (if any)" unchecked for the Subtotal, Filemaker doesn't let me change the Subtotal; it forces to whatever the calculation is.
Like • Show 0 Likes0
Actions
BruceRobertson @ roryduffy on Mar 14, 2018 9:10 AM
It's not though, if I have "Do not replace existing value of field (if any)" unchecked for the Subtotal, Filemaker doesn't let me change the Subtotal; it forces to whatever the calculation is.
Not true.
Like • Show 0 Likes0
Actions
philmodjunk Mar 14, 2018 9:12 AM
That is not the case in my experience.

Here's a demo file that I used to create the screen shot posted. The "do not replace" check box is cleared and I can modify the subtotal field as needed.

If you are using email to reply or have this discussion open in the forum's "in box", you won't see the link to download this file.
Attachments
- Auto-enterTest.fmp12.zip65.9 KB
Like • Show 0 Likes0
Actions
TKnTexas55 Mar 14, 2018 9:23 AM
If you set Q, VALUE and SUBTOTAL as number fields with SUBTOTAL an auto-enter option of Q * VALUE, it should work like you want. It might look odd to someone, 5 10 and 55 as values for the fields.
Like • Show 1 Like1
Actions
TobiasLiebhartKoschierSE Mar 15, 2018 10:55 AM
I agree, something else seems to be wrong in your solution. Can you post your file or a test file of yours?
Like • Show 0 Likes0
Actions
roryduffy @ TobiasLiebhartKoschierSE on Mar 16, 2018 9:15 AM
Hi all,
Thanks for your help and suggestions - especially the demo file, very useful, thanks!
It turned out that I needed "Do not replace existing value of field (if any)" unchecked, for it to actively re-evaluate the auto-enter.

I'm not sure why it wasn't letting me change the value before. I'll try and capture it next time that happens and work out why. But for now, it seems to be working fine.

Thank you for your help, it is much appreciated!
Rory
Like • Show 0 Likes0
Actions
philmodjunk Mar 16, 2018 9:23 AM
I would take the comments of beverly and others into careful consideration here. The fact that your Q and value fields won't always produce the result shown in the sub total field would appear confusing to your users. A script trigger on the Subtotal field might be used to clear the value and Q fields should you choose to "override" the calculated value. Another option might be to use a conditional format to "flag" the value as not being equal to Q * Value.
Like • Show 0 Likes0
Actions
TKnTexas55 Mar 16, 2018 9:24 AM
Ok, since the original question has been asked and answered, here is an alternative. Have a field that is a straight number field, that you overlay on top of SUBTOTAL. cSUBTOTAL is the calculated version and nSUBTOTAL is the override. The benefit to this would be that you could run a comparison of the two. I did this on a catering application. I need to know how much the Catering Manager was overriding the sales. Since I had overlaid the number on top of the calculation, when they changed they did not know they were filling in a separate field that I captured.
Like • Show 2 Likes2
Actions
erolst @ roryduffy on Mar 16, 2018 9:32 AM
roryduffy wrote:
It turned out that I needed "Do not replace existing value of field (if any)" unchecked, for it to actively re-evaluate the auto-enter.
Wasn't that the consensus at the start of this discussion? To wit:
I'm a bit surprised; if the "Do not replace …" setting for the auto-enter calc is unchecked, the field should behave exactly as you desire.

Like • Show 0 Likes0Reply

Actions
- roryduffy@ Oliver Stroh on Mar 14, 2018 4:39 PM
  erolst
  It's not though, if I have "Do not replace existing value of field (if any)" unchecked for the Subtotal, Filemaker doesn't let me change the Subtotal; it forces to whatever the calculation is.
Like • Show 0 Likes0
Actions
philmodjunk @ erolst on Mar 16, 2018 9:36 AM
Hey, we all make mistakes and what matters is that the issue is resolved.
Like • Show 0 Likes0
Actions
roryduffy Mar 16, 2018 9:37 AM
Yes, that was what the problem was before but not it seems to be working, oddly.
I'll keep checking - maybe it was something to do with how the field behaves when referencing data from another table vs. referencing data from its own table. Anyway my brain is fried now so will check those other suggestions as soon as I can.

Thanks!
Like • Show 0 Likes0
Actions
philmodjunk Mar 16, 2018 9:44 AM
YES, there can be a difference when data is referenced from another table.

Auto-enter calcs with "do not replace" cleared (and stored calculation fields also) will automatically re-evaluate any time that a field from the same record that is referenced in the calculation is modified. If the field comes from a related record, changes to it will not automatically cause the field to re-evaluate for auto enter calculations. (For calculation fields, this forces the field to be unstored and so the above statement remains true for auto-enter calcs and stored calculation fields.)

But note that this does the opposite of what you described---it won't cause the field to re-evaluate when you edit the field directly. It's a case where changing Q or value fields might fail to result in a new calculated value in sub total if subtotal is referring to fields from a related record.
Like • Show 0 Likes0
Actions
erolst @ philmodjunk on Mar 16, 2018 9:52 AM
Just a friendly reminder to take advice asked for and given into actual consideration.
Like • Show 0 Likes0
Actions
erolst @ philmodjunk on Mar 16, 2018 10:01 AM
To confuse matters a bit more:

If you'd have an auto-enter calc that only references related data, it will not re-evaluate when that data is changed (which is why the field would be populated using a triggered script instead).

But the field contents will change if the calc references related and “native” fields, and one of those native fields is changed.
Like • Show 0 Likes0
Actions
user19752 Mar 16, 2018 9:35 PM
There is a way to override calculated value only when user enter value in the field, using auto-enter
Case (
Get(ActiveFieldTableName) & "::" & Get(ActiveFieldName) = GetFieldName(Self) ; Self ;
Q * Value
)
But this is not so preferable. I like having another field as TKnTexas55 wrote.
Like • Show 0 Likes0
Actions

Incoming Links

Re: Auto-Enter Advice please...

Auto-Enter Advice please...

Outcomes

Attachments

Related Content

Recommended Content

Incoming Links