I have a field named Groups that is populated from a checkbox with a value list. I need to change some of the group names in the value list, but I also need the records already in the database to change to the new names also.

How should I do this?

Hi

I think that the simplest way is to:

1) show all records

2) put the cursor into the field "Groups" ( even if it is formatted as checkbox, so that it becomes active )

3) make a "Replace Field Contents...>>Replace with calculated result" from the Record Menu with this type of calc:

Let([

Old = "OldValue";[color:red]//write here your real old value

New = "NewValue"[color:red]//write here your real new value

];

Substitute ( Trim ( Substitute ( Substitute ( ¶ & Grops & ¶ ; ¶ & Old & ¶ ; ¶ & New & ¶ ) ; [ " " ; "§" ] ; [ ¶ ; " " ] ) ) ; [ " " ; ¶ ] ; [ "§" ; " " ] )

)

[color:red]***

4) repeat steps from 2 to 3 for every old value that you want to change with a new one

5) IF the value list is a CUSTOM one, change now those values in the value list; no need of this step if the values of the value list are coming from the values of the field "Groups".

[color:red]*** this operation isn't undoable, so [color:red]WARNING make this on a copy of the DB

Thank you, it works well. Could you explain why it needs the nested substitute, and the § and ¶.

I don't 100% understand how it works.

Thanks

Suppose that a single value contains more than one word like:

this is the first value

this is the second one

... and now the third

or that a value contains part of another value like:

be

better

be better

The calc above take in account those possibilities.

I don't understand either.

The "¶" acts as a delimiter. By adding a ¶ to the front and back of the value list, and each substituted value, you shouldn't have to worry about multiple words in a value. You just need to clean up the leading and trailing ¶ when you're done.

I don't understand either.

The "¶" acts as a delimiter. By adding a ¶ to the front and back of the value list, and each substituted value, you shouldn't have to worry about multiple words in a value.

that's OK

You just need to clean up the leading and trailing ¶ when you're done.

Yes.. and in which way ?

I used the Trim ( ) function ( so the need to change " " with "§" )...

Don't like to count how many ¶ there are in front and how many behind }:(

How about:

Let ( 

xch = Substitute ( ¶ & listOfValues & ¶ ; ¶ & oldItem & ¶ ; ¶ & newItem & ¶ ) 

;

Middle ( xch ; 2 ; Length ( xch ) - 2 ) 

)

I used the Trim ( ) function ( so the need to change " " with "§" )...

Don't like to count how many ¶ there are in front and how many behind

I understand now.

Yes.. and in which way ?

Replace() came to mind immediately, there should be only one leading and one trailing ¶, but using Words functions is simpler (and it doesn't matter how many leading or trailing there are):

LeftWords(text; WordCount(text))

LeftWords(text; WordCount(text))

Beware: if your value list is:

Mr.

Ms.

Dr.

Prof.

it will mess up your last value.

Is there a list of what characters are ignored by Words?

Any character that is a word delimiter. With Unicode, that would be quite a list.

I'm surprised no one else said this, but you've just seen why value lists should be based on IDs and not text values. If you had done this, changing a group name would have been trivial.

Humm...

I don't know why I was thinking that the number of those ¶ may be variable by situation...

( instead it is always one trailing and another leading )

So the final simplest calc for wvdb is:

Let([

Old = "OldValue";

New = "NewValue";

Xch = Substitute ( ¶ & Grops & ¶ ; ¶ & Old & ¶ ; ¶ & New & ¶ )

];

Middle ( Xch ; 2 ; Length ( Xch ) - 2 )

)

I'm surprised no one else said this, but you've just seen why value lists should be based on IDs and not text values. If you had done this, changing a group name would have been trivial.

I, for example, didn't say becouse I didn't know, nor I can imagine how that can be achieved with values coming from a field.

I would like to learn a new thing ( at least for me ). Can you explain, please ?

Replace() came to mind immediately, there should be only one leading and one trailing ¶...

Also Replace ( ) must be nested, like:

Let([

Old = "OldValue";

New = "NewValue";

Xch = Substitute ( ¶ & Groups & ¶ ; ¶ & Old & ¶ ; ¶ & New & ¶ )

];

Replace ( Replace ( Xch ; 1 ; 1 ; "" ) ; Length ( Xch ) - 1 ; 1 ; "" )

)

... so I think that is better to use the old dear Middle ( ).

BTW: the real issue was that I didn't consider that "there should be only one leading and one trailing ¶"

I'm not speaking of tricks. The group value list should consist of GroupID and Group Name with perhaps just the second value showing. Since we don't know how Groups is used, I can only guess. I'm thinking on a Person record, he has a field Groups that is a hard-coded value list that is set to display as a checkbox. Then, the Person record is storing a text field of group names.

Rather, there should be a Person Table, Membership Table and Group table. Membership being the join.

To change a group name, just change it in the Group table. All records that display the group name see the change immediately, since its the GroupID they store.

Hi bcooney

Sorry, I didn't understand.

So let me make an example.

I have a value list ( "List" ) whith values coming from Table::fieldA; at this moment the list has 12 values.

How can I apply what you said ?

I'll jump in here if I may...

The "list" table needs 2 fields; id and value

1 ; cold

2 ; warm

3 ; hot

When the value list is used, only the ID is entered. The Value is pulled in through a relationship.

That way, if cold needs to be changed to freddo, just change the value in the table and the relationship pulls in the new value.