Force update auto enter calculation

[edwardv]

Hello FmForums!

Newbie here. It's been about 6 months of learning filemaker - I've been using forums, Youtube and good ol' Google for my learning. However, I am now scratching my head and can't find a solution. So, I am making my first post 😅.

My issue: I have many records in the database and now I need to create another field. This field will calculate a product's selling price using the case formula as a pricing table. However I want the field to be an auto enter calc so I can change the field manually after if needed. The calculation uses a couple of existing fields, which have already been entered for every record in the database.

How do I force an auto enter calc for this new field? I will to also have to checked so it does not recalculate.

Why did I not have this field in the db earlier? To be honest, I had no idea of how to create a pricing table and I recently found the solution which I thought I can just add later. Also, if anyone can share of a better way to create a pricing table, I would love to hear it.

 

Regards,

Edward (a desperate newbie)

 

[OlgerDiekstra]

An auto-enter calc does not update unless any of the fields it relies on change. So if you add an auto-enter calc afterwards, it doesn't do anything for existing records. To update the auto-enter calc field you have to change one of the fields. You can do this via a script or menu by using the "replace field contents" and replacing it with its own value. That way the auto-enter calc is triggered.

This may take a while if you have lots of records. Also note that if you have a related field in your auto-enter calc that the auto-enter calc will not update when the related field changes. It only works if fields in the table that the auto-enter calc resides in change (unless you change the relationship to the related field).

[comment]

If you want to populate existing records, try changing the field to a calculation field, exiting the Manage Database dialog, and then changing it back. This has the advantage of not triggering any "last modified" fields. However, it will only work if the calculation can be stored. Otherwise you would need to use Replace Field Contents to populate the field. Make sure to have a backup before you try that, as there is no undo in case you make a mistake.

 

17 minutes ago, OlgerDiekstra said:

To update the auto-enter calc field you have to change one of the fields.

IMHO, it would be simpler to change the target field directly, by replacing its contents with a calculated result using the same formula as the auto-enter. But it may not be necessary at all, as explained above.

 

2 hours ago, edwardv said:

This field will calculate a product's selling price using the case formula as a pricing table.

Are you sure that's a good idea? You didn't say how exactly the price would be calculated. You should not hard-code a formula into the file's schema unless you are positive it can never change.

 

[edwardv]

Thank you for the replies. I appreciate your time and help.

3 hours ago, OlgerDiekstra said:

You can do this via a script or menu by using the "replace field contents" and replacing it with its own value. That way the auto-enter calc is triggered.

Interesting idea. So would I lay the script out like this Replace Field Contents(productCost;productCost)? I just did a small test and it worked  I just want to make sure I'm not missing something. Also, I do have a related field in my auto enter calc. It looks like this:

productCost * Case( IsEmpty(Brand)=0 and isEmpty (productCost)=0 and productCost <10; 1.5; ...and so on

Brand is a related field, which is present in this function as it will alter the result of the case formula output. In regards to business logic, if the item is branded, the price needs to be adjusted. My layout contains a portal with all the product variations and costs. For instance, the product is a black shirt, and the portal allows me to create the size SKU's along with the respective costs and prices.

3 hours ago, comment said:

Are you sure that's a good idea? You didn't say how exactly the price would be calculated. You should not hard-code a formula into the file's schema unless you are positive it can never change.

Agreed. Please see above in this comment for the explanation of the calc. The reason for the auto enter is more for business needs. For example the prices should be set via a calculation (which most likely will be changed in the future), but in some instances a person doing the QC of the data entry work may modify the price.

Let's say that only 20% of the products entered will have the prices modified, it would be easier for 80% of the auto entered prices to be left alone than enter all of them. That is why I am leaning away from a  calculation field as the user can not modify the contents, or as far as I understand it to be. Also, if I change the function(price table) it won't recalculate all the previous work, which is favored.

 

 

[comment]

1 hour ago, edwardv said:

So would I lay the script

I don't think you need a script for a one-time action like this.

 

1 hour ago, edwardv said:

Please see above in this comment for the explanation of the calc.

I am afraid I don't follow the calc, because a major part of it seems to be replaced by "...and so on". From the very little I can see, I would suspect that the values 10 and 1.5 are very likely to change with time. Such changes should not require a developer modifying the file's schema. There should be a pricing table and the correct values should be looked up from there. 

 

[edwardv]

7 hours ago, comment said:

I don't think you need a script for a one-time action like this.

Right, correct. I can just run in once to update past records.

7 hours ago, comment said:

I am afraid I don't follow the calc, because a major part of it seems to be replaced by "...and so on". From the very little I can see, I would suspect that the values 10 and 1.5 are very likely to change with time. Such changes should not require a developer modifying the file's schema. There should be a pricing table and the correct values should be looked up from there. 

I should have gone into greater detail - my fault. Here is a clear example of the calc:

productCost *

Case(

IsEmpty(Brand)=0 and isEmpty (productCost)=0 and productCost <10; 1.5

IsEmpty(Brand)=0 and isEmpty (productCost)=0 and productCost <20; 1.4

IsEmpty(Brand)=0 and isEmpty (productCost)=0 and productCost <50; 1.3

)

This case function would continue for a while to hit various price points. Regard a pricing table, that was my original plan, but honestly, I have no idea how to accomplish that. What needs to be used in order for a lookup value to choose the right price bracket from the table?

To better understand my perspective, this is for a personal project, so most of my decisions come from practicality given the time constraints. As I progress in my FM knowledge, I would like to change my solution to follow best practices. So any sloppy work from my end is not end coming at the expense of any client

[comment]

Your pricing table would look something like:

ProductCost	Factor
0	1.5
10	1.4
20	1.3

and you would lookup the factor from there using a relationship matching on ProductCost, with the lookup defined to copy next lower value if no exact match.

 

[edwardv]

On 10/9/2019 at 8:01 AM, comment said:

Your pricing table would look something like:

ProductCost	Factor
0	1.5
10	1.4
20	1.3

and you would lookup the factor from there using a relationship matching on ProductCost, with the lookup defined to copy next lower value if no exact match.

 

Thank you! Can you point in the right direct of what I would need to accomplish the calculation? 

[comment]

Once you have looked up the factor into a local field, it's just ProductCost * Factor.

[edwardv]

20 hours ago, comment said:

Once you have looked up the factor into a local field, it's just ProductCost * Factor.

If im using a table, which formula would I need to replace the logic of the case function?

[comment]

We seem to have a breakup in communications. A lookup is not a calculation, it does not use a formula - see:
https://fmhelp.filemaker.com/help/18/fmp/en/index.html#page/FMP_Help%2Flookups.html