Automatically update the name of a field in a formula


#1

I refer to my earlier topic regarding creating three-way formulas in Panorama X.

I bought Panorama X a few days ago; it is excellent and a pleasure to use.

I am able to easily create two-way and three-way formulas without having to create dummy hidden fields in a financial forecasting app that I am developing in Panorama X and which I wish to eventually market.

Neither FileMaker, Access, Excel or Quantrix Modeler allow formula fields to be edited to over-write the formula in a field with a number to recalculate a independent field in the formula.

However, if I change the name of a field the respective formula which uses that field does not automatically update with the new field name. I have to manually change the name of the field in the formula. For example, if I change the name of field # 1in a database I then have to change the name of field # 1in the formula which is in another field. This is a duplication of the process. If I don’t change the name of field # 1 in the formula to match the new name of field # 1, the formula will not calculate.

Is there a way to automatically update the name of a field in a formula if the field name is changed in the database e.g. using code?


#2

The implication seems to be that changing field names after you have written formulas that use those names is something you expect to be doing often. I don’t know why that would be, but maybe you could accomplish whatever you have in mind by leaving the field name unchanged, and changing the field title instead.


#3

Dave,

I don’t expect be to changing the name of fields often, but if I change the name of a field after I have created the formula, the formula is not updated with the new field name.

I could do what you suggested by not changing the field name and change the field title. I have done this for presentation purposes e.g. many field names include the word “percentage” and the respective field titles include the symbol “%”, but the field title is not part of the formula and the field name, which is part of the formula, may need to be changed e.g. a spelling mistake, to abbreviate the field name, to use an underscore in the field name to join to words so that the formula will calculate.


#4

I think you want to do what I did recently: put formulas in a bunch of fields which depend on other fields, and then change the names of some of the fields. I ended up doing find and replace in a text editor and then pasting the modified formulas back in the fields.

While it is possible to do what you want, a better way was discussed here recently: Put the formula in a procedure, and have the fields call that procedure as the formula. When you want to make a change, you will only need to make the change in the procedure, not in each field. I will do that when I get around to it.

I would suggest that you add notes to the procedure so you know what fields or procedures call it. That sort of thing comes in handy.


#5

Thank you Bruce. I will try that.


#6

Database field numbers can be used in many places – in automatic formulas (where you are using them), in formulas in graphic objects, in procedures … even in other databases (for example in lookup( functions.) Because of Panorama’s execute functions, they even could be “hidden” in text constants, variables, or even separate text files on disk.

With all of these options, it would be impossible for Panorama to reliably find and adjust every occurrence of a field name when that name is changed. So that is left as a manual task. Usually the best bet is to be somewhat careful and deliberate about choosing field names in the first place.

That is not necessary. You can use ANY field name in a formula, even if it contains spaces, punctuation, whatever. All you have to do is surround the name with « and » characters when you place it in the formula. For example, if you have a field named

First Name

You can reference it in a formula like this:

«First Name»

With this technique, you can even use field names that contain mathematical operators! For example:

«P/E Ratio»

I don’t think this is what you are asking for, but on the off chance it is, you can modify the formula for a field with programming code, using the setfieldproperties statement. For example, this program would modify the formula for the current field to change any occurrence of First Name to First_Name.

setfieldproperties initializedictionary(
    "FORMULA",replacewordexact(fieldformula("",""),"First Name","First_Name")

This could be extended with a loop to scan all of the fields and make this change.


#7

Jim,
Thank you very much for your advice. I have updated my app accordingly.