Edit Document Fields 4.1

Updated version of Edit Document Fields is now available. Some of you have used my Edit Document Fields 3.0, I have been waiting for the Sandbox to post my most recent update. But it is still not there after a couple of weeks. It seems over the years they have gotten slower and slower at posting submitted information. Here is the latest and greatest version.

The 5 Simple Steps

Select the document(s) to update.
Click your toolbar button.
Select the field to update.
Select the data type or action to be performed
Enter the new information (if prompted)

Limitations

For best results update less than 100 documents at a time, because a list of the updated documents’ UNID must be kept.
Field value is limited to 255 because of the @Prompt limitations.
When updating multiple documents only the documents that are selected and visible (expanded) in the view will be updated. Notes needs to be able to navigate to the document!

Features

New data type/actions:
- + Append Values – Appends new values to a field.
- Sort Ascending – Any type of list.
- Sort Descending – Any type of list.
- Replace – with the ability to update multiple fields at once.
- Replace Substring – with the ability to update multiple fields at once.
- Unique – Removes all duplicate elements in a list.
- Implode – Prompts for separator and converts a list to text.
- Explode – Prompts for separators and converts text to a list.
Thank you to Lefty Tsamis for the ability to update multiple documents with the same value.
Lefty Tsamis also added the ability to check the data type of the field for the selected document and have that be the default type instead of always being text.
Thank you to Vladislav Gorbunov (Creator of FieldControl) for adding the use of the Notes.ini to store the last field that was edited.
Vladislav Gorbunov also added the +Append Values data function.
Added additional checks so fields are not set to errors.
Added Status Bar notification of what happened.
Sorted the data types to find them easier.
Added confirmation of update for more than one document.

Code Break Down

Setup

First we need to determine the fields that are available. We will use the currently selected document. To clean up the code we will create a temp variable just for the window titles. Then define all of the possible data types or actions that will be allowed on a field. I just ran across an interesting tip about Formula Language’s dirty secret so I changed my data types definition to be a string and then explode it out into an array.

REM {Get a listing of all the fields on the current document};
 List := @Sort( @DocFields );
 
 REM {Reusable title.};
 PromptTitle := @DbTitle + " - " + @ViewTitle;
 
 REM {Possible data types to choose from.};
 REM {I called Number Integer because use keyboard to select what you want with keyboard quicker.};
 DataTypes := @Explode( "Text,Date,Integer,Password,Name,Common Name,Abbreviate Name,Remove Field,Text Multi Value,Date Multi Value,Integer Multi Value,Name Multi Value,Common Name Multi Value,Abbreviate Name Multi Value,Upper Case Text,Lower Case Text,Proper Case Text,Upper Case Text Multi Value,Lower Case Text Multi Value,Proper Case Text Multi Value,+ Append Values,Sort Ascending,Sort Descending,Replace,Replace Substring,Unique,Implode,Explode" ; "," );

Field Selection/Add

Display a list of all available fields with the ability to enter new ones. When a selection has been made set a local environment variable of the selected field, so the next time using Edit Document field this field will be selected. Select Field

REM {Prompt for which field needs to be updated.};
 @DoWhile( 
	EditField := @Prompt( [OkCancelEditCombo] ; PromptTitle ; "Select the field you wish to alter:" ; @Environment( "edfLastField" ) ; List );
 	EditField = "" );
 
 @Environment( "edfLastField"; EditField );

Determine Field Type

Based on the field that was selected lets look at the value of the field and determine what type it is and have that be the default field type. If there are any problems the default field type will be text. Then we can actually prompt what the new field type should be. Select Data Type

 DataType :=  	
	@If(  		
		@IsNumber( @GetField( EditField ) ) ;
			@If( @Count( @GetField( EditField ) ) &gt; 1 ;
					"Integer Multi Value";
 				"Integer" ) ;
		@IsTime( @GetField( EditField ) ) ;
			@If( @Count( @GetField( EditField ) ) &gt; 1 ;
				"Date Multi Value";
 				"Date" ) ;
		@If( @Count( @GetField( EditField ) ) &gt; 1 ;
		"Text Multi Value";
 	"Text" )
	);
 
 REM {If the data type is a type of error then select the data type of text};
 DefaultDataType := @IfError( DefaultDataType ; "Text" );
 
 REM {Prompt for which data type you would like the data to be};
 REM {This needs to be done before value prompt to determine if the Picklist or any prompting needs to be used.};
 DataType := @Prompt( [OkCancelList] ; EditField + " - " + PromptTitle; "Please select the correct data type or action for field: " + EditField + "."; DefaultDataType ; DataTypes );

Special Field Types

Some unique prompts need to occur for Replace, Implode, Explode, and Replace Substring.

Multi Value

Need to convert the array into a string separated by ; which is the character used to explode the string back into an array later.

REM {Format the original value as text because the @Prompt command requires text.};
 OriginalValue := @If( @Contains( DefaultDataType ; "Multi Value" ) ; @Implode( @Text( @GetField( EditField ) ) ; ";" ); @Text( @GetField( EditField ) ) );

Replace & Replace Substring

When Replacing or Replace Substring allow updating of multiple fields at once. Then determine what to search for. This would be really helpful changing a name because someone left the company.

EditField := @If( DataType = "Replace Substring" | DataType = "Replace" ; @Prompt( [OkCancelListMult] ; PromptTitle ; "Select any additional fields you wish to alter:" ; EditField ; List ); EditField );
 
 REM {Determine the string that they are searching for.};
 FromRawValue := @If( DataType = "Replace Substring" | DataType = "Replace" ; @Prompt(
 [OkCancelEdit] ; EditField + " - " + PromptTitle ; "Please enter the text to search for in: " + @Implode( EditField ; ", " ) + "." ; "" ) ; "" ) ;
 ReplaceFailure := @False;

Imploding and Exploding

When exploding multiple characters can be used to separate text. By default the value is a semicolon. When imploding only one string is allowed.

Separator := @If( DataType = "Implode" | DataType = "Explode" ; @Prompt( [OkCancelEdit] ; PromptTitle ; "Enter the " + @If( DataType = "Implode" ; "separator" ; "separators" ) + " : " ; "" ); ";" );

Determine New Value

We have the field and the data type now the last information to gather is new value. The name field types will use the PickList to gather the new value ( s ). Some field types like Unique and Remove Field do not require any prompting. Enter New Value.GIF

REM {Based on what type of data is being entered different prompts will happen if any at all.}; REM {Use the GetField function instead of using Abstract which always returned text and did not convert non text data.};
 
 RawValue := @If( 
 @Contains( DataType ; "Name Multi Value" ); @PickList( [Name] );
 	@Contains( DataType ; "Name" ) ; @PickList( [Name] : [Single] );
 	DataType = ( "Remove Field":"Unique":"Sort Ascending":"Sort Descending":"Implode":"Explode" ) ; "" ;
 	@Contains( DataType ; "Multi Value" ); @Prompt( [OkCancelEdit] ; EditField + " - " + PromptTitle; "Please enter the new desired value for: " + @Implode( EditField ; ", " ) + "." + @Char( 13 ) + @Char( 13 ) + "Seperated with ; for each value." ; OriginalValue ) ;
 	@Contains( DataType ; "+ Append Values" ); @Prompt( [OkCancelEdit] ; EditField + " - " + PromptTitle; "Please enter values to append: " + @Implode( EditField ; ", " ) + "." + @Char( 13 ) + @Char( 13 ) + "Seperated with ; for each value." ; "" ) ;
 	DataType = ( "Replace Substring":"Replace" ) ; @Prompt( [OkCancelEdit] ; EditField + " - " + PromptTitle ; "Please enter the text to repalce with in: " + EditField + "." ; "" ) ;
 	@Prompt( [OkCancelEdit] ; EditField + " - " + PromptTitle ; "Please enter the new desired value for: " + EditField + "." ; OriginalValue )
 	 );

Update the Field

Finally the field can be updated. The UNID variable will be used to remember which documents have been updated.

REM {Add each document's UNID to a growing list, check each doc against the list as };
 REM {we navigate through selected docs; Once a duplicate is found all should be processed, end loop};
 REM {The values entered above will be applied to all selected docs ...};
 REM {If data conversion doesn't work then don't set field.};
 UNID:="";
 @While( ! @Contains( UNID;@Text( @DocumentUniqueID ) + ":" );
	UNID:= UNID + ":" + @Text( @DocumentUniqueID );
 
 	@For( ef := 1;
		ef &lt;= @Elements( EditField );
 		ef := ef + 1;
 
 		FieldDataType := @If( @IsNumber( @GetField( EditField[ef] ) ) ; @If( @Count( @GetField( EditField[ef] ) ) &gt; 1 ; "Integer Multi Value"; "Integer" ) ; @IsTime( @GetField( EditField[ef] ) ) ; @If( @Count( @GetField( EditField[ef] ) ) &gt; 1 ; "Date Multi Value"; "Date" ) ; @If( @Count( @GetField( EditField[ef] ) ) &gt; 1 ; "Text Multi Value"; "Text" ) );
 		FieldDataType := @IfError( DefaultDataType ; "Text" );
 		@If( 
 		DataType = "Date" ; @If( @IsError( @ToTime( RawValue ) ) ;
			"" ;
 			@SetField( EditField[ef] ; @ToTime( RawValue ) ) );
		DataType = "Integer" ; @If( @IsError( @TextToNumber( RawValue ) ) ;
			"" ;
 			@SetField( EditField[ef] ; @TextToNumber( RawValue ) ) ) ; 		
		DataType = "Common Name" ; @SetField( EditField[ef] ; @Name( [CN]; RawValue ) ) ;
 		DataType = "Abbreviate Name" ; @SetField( EditField[ef] ; @Name( [Abbreviate]; RawValue ) ) ;
 		DataType = "Password" ; @SetField( EditField[ef] ; @Password( RawValue ) ) ;
 		DataType = "Remove Field" ; @SetField( EditField[ef] ; @DeleteField ) ;
 		DataType = "Text Multi Value" ; @SetField( EditField[ef] ; @Explode( RawValue ; ";" ) ) ;
 		DataType = "+ Append Values" ; @If(  		
			@Contains( DefaultDataType; "Date" );
			@If ( @IsError ( @ToTime ( RawValue ) ) ;
 					"" ;
 					@SetField( EditField[ef] ; @GetField( EditField[ef] ) : @TextToTime( @Explode( RawValue ; ";" ) ) ) ) ;
			@Contains( DefaultDataType; "Integer" );
			@If( @IsError( @TextToNumber( @Explode( RawValue ; ";" ) ) ) ;
				"" ;
 					@SetField( EditField[ef] ; @GetField( EditField[ef] ) : @TextToNumber( @Explode( RawValue ; ";" ) ) ) ) ;
			@SetField( EditField[ef] ; @GetField( EditField[ef] ) : @Explode( RawValue ; ";" ) ) );
					DataType = "Date Multi Value" ; @If( @IsError( @ToTime( RawValue ) ) ;
					"" ;
 			@SetField( EditField[ef] ; @TextToTime( @Explode( RawValue ; ";"
 ) ) ) ) ;
		DataType = "Integer Multi Value" ; @If( @IsError( @TextToNumber( @Explode( RawValue ; ";" ) ) ) ; 		
		"" ;
 			@SetField( EditField[ef] ; @TextToNumber( @Explode( RawValue ; ";" ) ) ) ) ;
			DataType = "Name Multi Value" ; @SetField( EditField[ef] ; @Explode( RawValue ; ":" ) ) ;
 		DataType = "Common Name Multi Value" ; @SetField( EditField[ef] ; @Name( [CN]; @Explode( RawValue ; ":" ) ) );
 		DataType = "Abbreviate Name Multi Value" ; @SetField( EditField[ef] ; @Name( [Abbreviate]; @Explode( RawValue ; ":" ) ) );
 		DataType = "Upper Case Text" ; @If( @Contains( DefaultDataType ; "Text" ) ; @SetField( EditField[ef] ; @UpperCase( RawValue ) ) ; "" );
 		DataType = "Lower Case Text" ; @If( @Contains( DefaultDataType ; "Text" ) ; @SetField( EditField[ef] ; @LowerCase( RawValue ) ); "" );
 		DataType = "Proper Case Text" ; @If( @Contains( DefaultDataType ; "Text" ) ; @SetField( EditField[ef] ; @ProperCase( RawValue ) ); "" );
 		DataType = "Upper Case Text Multi Value" ; @If( @Contains( DefaultDataType ; "Text" ) ; @SetField( EditField[ef] ; @UpperCase( @Explode( RawValue ; ";" ) ) ); "" );
 		DataType = "Lower Case Text Multi Value" ; @If( @Contains( DefaultDataType ; "Text" ) ; @SetField( EditField[ef] ; @LowerCase( @Explode( RawValue ; ";" ) ) ); "" );
 		DataType = "Proper Case Text Multi Value" ; @If( @Contains( DefaultDataType ; "Text" ) ; @SetField( EditField[ef] ; @ProperCase( @Explode( RawValue ; ";" ) ) ); "" );
 		DataType = "Unique" ; @SetField( EditField[ef] ; @Unique( @GetField( EditField[ef] ) ) );
 		DataType = "Implode" ; @SetField( EditField[ef] ; @Implode( @GetField( EditField[ef] ) ; Separator ) );
 		DataType = "Explode" ; @SetField( EditField[ef] ; @Explode( @GetField( EditField[ef] ) ; Separator ) );
 		DataType = "Sort Ascending" ; @SetField( EditField[ef] ; @Sort( @GetField( EditField[ef] ) ; [Ascending] ) );
 		DataType = "Sort Descending" ; @SetField( EditField[ef] ; @Sort( @GetField( EditField[ef] ) ; [Descending] ) );
 		DataType = "Replace Substring" ; @If( @Contains( FieldDataType ; "Text" ) ;
			@SetField( EditField[ef] ; @ReplaceSubstring( @GetField( EditField[ef] ) ; FromRawValue ; RawValue ) ) ;
 			ReplaceFailure := @True ) ;
		DataType = "Replace" ; @If( @Contains( FieldDataType ; "Text" ) ;
			@SetField( EditField[ef] ; @Explode( @Replace( @GetField( EditField[ef] ) ; FromRawValue ; RawValue ) ; ";" ) ) ;
 			ReplaceFailure := @True );
 			@SetField( EditField[ef] ; RawValue )
		)
	);
 
 	@Command( [NavNextSelected] );
 	@UpdateFormulaContext );
 	@If( ReplaceFailure ; @Prompt( [OK] ; "Unable to Replace" ; "Unable to replace some or all because the original value is not text." ) ; "" );
"";
 
@StatusBar( "Peformed '" + DataType + "' for '" + @Implode( EditField ; ", " ) + "' field" + @If( @Elements( EditField ) &gt; 1; "s " ; " " ) + "on " + @Text( @Elements( @Explode( UNID ; ";" ; @False ) ) ) + " document" + @If( @Elements( @Explode( UNID ; ";" ; @False ) ) &gt; 1 ; "s" ; "" ) + "." )

It is deffinitly amazing how something evolves over time. To think about this little bit of code started four years ago and how much time it has saved me. Thanks again to everyone who has helped add new things over the years. Download all the code

15 thoughts on “Edit Document Fields 4.1”

ChadSmiley on February 7, 2005 at 8:13 pm said:

Johan,
Check out the section titled Field Selection/Add. The field selection in previous version was a list box, but now it is combo box so new fields can be added!

Reply ↓
Johan Känngård on February 7, 2005 at 8:13 pm said:

Thanks for the comments fix, Chad! What I was aiming for, was a new choice in the field name selection dialog, that is “Add new field” (at the top of the list IMO). When selecting it, you will get to the field type dialog (perhaps exclude those that aren’t of any interest to a new field), and choos type. In the next dialog, I enter the actual value to add. Do you understand now what I want? The reason for this request is, that I often develop web-only apps, and seldom (hrmm never) take the Notes client into account. Therefore, I am unable to do any editing or resaving in the client. So, when some app has a document that is somehow corrupt (like the developer forgot to add a field in the form…), I have to create an agent everytime I need to add a field in the document.
Keep up the good work!

Reply ↓
John on February 7, 2005 at 8:13 pm said:

thanks for the update. awsome tool!

Reply ↓
Johan Känngård on February 9, 2005 at 2:20 am said:

I still miss an “Add field X with value Y” Otherwise, your formula rocks!

– Johan

Reply ↓
Janne on February 9, 2005 at 6:43 am said:

If you just write the desired fieldname in step 1 this fieldname will be added.

Reply ↓
Johan K on February 9, 2005 at 6:48 am said:

T

Reply ↓
rainer_seidel@t-online.de on February 20, 2005 at 10:14 am said:

Hallo Eknori, Dein Programm zum Editieren von Feldern ist prima. Ein Punkt ist mir jedoch in der Anwendung aufgefallen: Wenn etliche Dokumente ausgewählt sind, der Cursor aber auf einer anderen von mir nicht bewusst ausgewählten Zeile steht, wird auch dieses Dokument mit verändert. Das ist unschön, da dieses manchem User nicht bewusst ist. Gibt es die Möglichkeit dieses programmtechnisch zu ändern?

Gru

Reply ↓
eknori on February 21, 2005 at 10:47 pm said:

@Rainer Seidel: Sorry, aber das ist hier nicht meine Seite

@Chad: Rainer wrote:

The code ist great. But there seems to be a little bug. When a large ammount of documents is marked and the cursor is positioned on a non-marked document, even this documnet ist changed to the new value …

Reply ↓
Chad on February 26, 2005 at 1:51 pm said:

I was not aware of this, I will see if I can determine what is causing it.

Chad

Reply ↓
Johan Känngård on February 28, 2005 at 4:12 am said:

I get multiple field types, the list lookes like below. It doesn’t matter what document I choose…
+
Abbreviate
Abbreviate
Append
Ascending
Case
Case
Case
Case
Case
Case
Common
Common
Date
Date
Descending
Explode
Field
Implode
Integer
Integer
Lower
Lower
Multi
Multi
Multi
Multi
Multi
Multi
Multi
Multi
Multi
Name
Name
Name
Name
Name
Name
Password
Proper
Proper
Remove
Replace
Replace
Sort
Sort
Substring
Text
Text
Text
Text
Text
Text
Text
Text
Unique
Upper
Upper
Value
Value
Value
Value
Value
Value
Value
Value
Value
Values

Reply ↓
Chad on February 28, 2005 at 6:49 am said:

Thanks, The correction is bolded below.
DataTypes := @Explode( “Text,Date,Integer,Password,Name,Common Name,Abbreviate Name,Remove Field,Text Multi Value,Date Multi Value,Integer Multi Value,Name Multi Value,Common Name Multi Value,Abbreviate Name Multi Value,Upper Case Text,Lower Case Text,Proper Case Text,Upper Case Text Multi Value,Lower Case Text Multi Value,Proper Case Text Multi Value,+ Append Values,Sort Ascending,Sort Descending,Replace,Replace Substring,Unique,Implode,Explode” ; “, ” );

Reply ↓
Johan Känngård on February 28, 2005 at 7:38 am said:

Thanks, it works!

Reply ↓
Chad on April 15, 2005 at 8:42 am said:

rainer,

This should be fixed in the next release, comming very soon.
http://www.chadsmiley.com/chadsmiley/home.nsf/d6plinks/Edit_Document_Fields_4_2_to_5_0

Reply ↓
cococo69 on May 30, 2006 at 1:20 am said:

Hi Chad, your code is great!!! But sometimes, if I mark multiple values, ALL the documents in the current view are changed, not only the marked??? What’s the problem? cococo69

Reply ↓
Chad Schelfhout on May 30, 2006 at 7:15 am said:

@11 I would get the latest version of this code at http://www.chadsmiley.com/EditDocumentFields

Reply ↓

ChadSmiley Blog

Things about family, life, coding, and more