Edit Document Fields 5.1.0
Updated version of Edit Document Fields is now available.
Added some more *smarts* to the Edit Document Fields 5.0.1. When selecting categories with documents the categories are not included at all. Download Edit Document 5.1.0 or if you would like to step threw the code break down then continue.
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
- Only 500 documents can be updated at a time
- 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
Code Break Down
Constants
REM {Edit Document Fields 5.1.0 by Chad Schelfhout.}; REM {Visit http://www.chadsmiley.com/EditDocumentFields for the latest updates}; REM {Constants}; EditLastField:="edfLastField"; EditLastFieldDataType:="edfLastFieldDataType"; EditLastSeparator:="%~%"; ProfileName:="ChadSmiley Tools"; PromptTitle:=@DbTitle+" - "+@ViewTitle; MaxSearchForSelectedDocs:=5520; MaxUpdatedDocuments:=1000; ArraySeperator:=";"; NoteEntryLength:=11; PormptNewLine:=@Char(13)+@Char(13); CategoryNoteID:="NT00000000";
Data Types and Data Type Code
This defines all the possible datatypes and the formula that will be used to set the field.
REM {Data types|@Function execution}; DataTypesCombo:=@Explode( "Integer|@TextToNumber(RawValue)$"+ "Integer Multi Value|@TextToNumber(@Explode(RawValue;Separator;@True))$"+ "Date|@ToTime(RawValue)$"+ "Date Multi Value|@ToTime(@Explode(RawValue;Separator;@True))$"+ "Text|@Text(RawValue)$"+ "Text Multi Value|@Text(@Explode(RawValue;Separator;@True))$"+ "Name|RawValue$"+ "Name Multi Value|RawValue$"+ "Common Name|@Name([CN];RawValue)$"+ "CommonName Multi Value|@Name([CN];@Explode(RawValue;\":\";@True))$"+ "Upper Case Text|@UpperCase(@Implode(@Text(@GetField(EditField[ef]))))$"+ "Lower Case Text|@LowerCase(@Implode(@Text(@GetField(EditField[ef]))))$"+ "Proper Case Text|@ProperCase(@Implode(@Text(@GetField(EditField[ef]))))$"+ "Upper Case Text Multi Value|@UpperCase(@Explode(@Text(@GetField(EditField[ef]));Separator;@True))$"+ "Lower Case Text Multi Value|@LowerCase(@Explode(@Text(@GetField(EditField[ef]));Separator;@True))$"+ "Proper Case Text Multi Value|@ProperCase(@Explode(@Text(@GetField(EditField[ef]));Separator;@True))$"+ "Replace Substring|@ReplaceSubstring(@GetField(EditField[ef]);FromRawValue;RawValue)$"+ "Replace|@Explode(@Replace(@GetField(EditField[ef]);FromRawValue;RawValue);Separator;@True)$"+ "Implode|@Implode(@Text(@GetField(EditField[ef]));Separator)$"+ "Explode|@Explode(@Text(@GetField(EditField[ef]));Separator;@True)$"+ "Formula|@Eval(RawValue)$"+ "Abbreviate Name|@Name([Abbreviate];RawValue)$"+ "Abbreviate Name Multi Value|@Name([Abbreviate];@Explode(RawValue;Separator;@True))$"+ "Password Set|@Password(RawValue)$"+ "Password Convert|@Password(@GetField(EditField[ef]))$"+ "Remove Field|@DeleteField$"+ "Unique|@Unique(@GetField(EditField[ef]))$"+ "+ Append Values|@If(" + " @GetField(EditField[ef])=\"\";RawValue;" + " @Contains(DefaultDataType;\"Date\");" + " @If(@IsError(@ToTime(RawValue));" + " \"\";" + " @SetField(EditField[ef];@GetField(EditField[ef]) : @TextToTime(@Explode(RawValue;Separator))));" + " @Contains(DefaultDataType;\"Integer\");" + " @If(@IsError(@TextToNumber(@Explode(RawValue;Separator)));" + " \"\";" + " @SetField(EditField[ef];@GetField(EditField[ef]) : @TextToNumber(@Explode(RawValue;Separator))));" + " @SetField(EditField[ef];@GetField(EditField[ef]) : @Explode(RawValue;Separator)))$"+ "Sort Ascending|@Sort(@GetField(EditField[ef]);[Ascending])$"+ "Sort Descending|@Sort(@GetField(EditField[ef]);[Descending])";"$"); DataTypes:=@Word(DataTypesCombo;"|";1); DataTypesAction:=@Word(DataTypesCombo;"|";2);
Setup
First we need to determine the fields that are available, based on the currently selected document. Then get the last updated field based on the form of the currently selected document.
REM {Get a listing of all the fields on the current document}; List:=@Sort(@DocFields); REM {Look for last field modified in Profile Doc}; FieldList:=@Explode(@GetProfileField(ProfileName;EditLastField;@UserName);ArraySeperator;@True); REM {Get the list of forms and field that was updated using Edit Document Fields}; FieldListForms:=@Word(FieldList;EditLastSeparator;1); FieldListField:=@Word(FieldList;EditLastSeparator;2); FieldListLastIndex:=@Member(Form;FieldListForms); REM {If the FieldListLastIndex is greater than zero then set the last field to the what was in the profile document}; @If(FieldListLastIndex >0; @Do(LastField:=FieldListField[ FieldListLastIndex ]; FieldList:=@ReplaceSubstring(FieldList;FieldList[ FieldListLastIndex ];"")); LastField :="");
Field Selection/Add
Display a list of all available fields with the ability to enter new ones. Then set the prompt title that will be used throughout the rest of the code.
REM {Prompt for which field needs to be updated. Loop until a field is selected or 'Cancel' is selected}; @DoWhile( EditField:=@Prompt([OkCancelEditCombo];PromptTitle;"Select the field you wish to alter or enter a new field to add:";LastField;@Trim(@Unique(List : LastField))); EditField=""); EditFieldPromptTitle:=EditField+" - "+PromptTitle;
Determine Field Type
Using the form of the currently selected document and the field that was just selected to determine the last data type used and the last formula code used. If there was no previous data type try to determine the data type base on the data of the field. If there are any problems the default field type will be text.
REM {This will allow the retrieval of the data type of the field that was last selected. Data is stored like Form+Field%~%DataType.}; FormFieldList:=@Explode(@GetProfileField(ProfileName;EditLastFieldDataType;@UserName);ArraySeperator;@True); FormFieldListFormField:=@Word(FormFieldList;EditLastSeparator;1); FormFieldListDataType:=@Word(FormFieldList;EditLastSeparator;2); FormFieldListFormulaCode:=@Word(FormFieldList;EditLastSeparator;3); FormFieldListIndex:=@Member(Form+EditField;FormFieldListFormField); @If(FormFieldListIndex >0; @Do(DefaultDataType:=FormFieldListDataType[ FormFieldListIndex ]; FormFieldListFormulaCode:=FormFieldListFormulaCode[ FormFieldListIndex ]; FormFieldList:=@ReplaceSubstring(FormFieldList;FormFieldList[ FormFieldListIndex ];"")); DefaultDataType :=""); REM {If there was no data type used for the field on the form the try to determine the data type}; DefaultDataType := @If(DefaultDataType!=""; DefaultDataType; @If( @IsNumber(@GetField(EditField)); @If(@Count(@GetField(EditField)) >1; "Integer Multi Value"; "Integer"); @IsTime(@GetField(EditField)); @If(@Count(@GetField(EditField)) >1; "Date Mult iValue"; "Date"); @If(@Count(@GetField(EditField)) >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");
Select Data Type/Set Action
Once the data type is selected determine the posistion in the array of the data type and set the data type action with the action in the same position of the array.
REM {Prompt for which data type you would like the data to be. This needs to be done before value prompt to determine if the Picklist or any prompting needs to be used.}; DataType:=@Prompt([OkCancelList];EditFieldPromptTitle;"Please select the correct data type or action for field: "+EditField+".";DefaultDataType;DataTypes); REM {The DataTypeAction will contain the formula that will be executed to retrieve the new value}; DataTypeAction:=DataTypesAction[ @Member(DataType;DataTypes) ];
Get Original Value
When using the formula data type there is no reason to see the selected fields value, because you would reference the field name when writing formula. Since there is no real need of the field value why not show the last formula for that field.
REM {If formula was used on this field then use that instead of the fields value. Format the original value as text because the @Prompt command requires text.}; OriginalValue:=@If(DataType=formula & DefaultDataType=formula & FormFieldListFormulaCode!=""; FormFieldListFormulaCode; @If(@Contains(DefaultDataType;MultiValue); @Implode(@Text(@GetField(EditField));ArraySeperator); @Text(@GetField(EditField))) );
Replace & Replace Substring
The idea behind the search and replace is to update all occurances of a string, so why not search over multiple fields. This allows multiple fields to be selected.
Then determine what to search for.
REM {Prompt for additional fields and determine the string that they are searching for.}; @If(DataType=@Explode("Replace Substring;Replace";ArraySeperator); @Do(EditField:=@Unique(EditField : @Prompt([OkCancelListMult];PromptTitle;"Select any addtional fields you wish to alter:";EditField;List)); FromRawValue:=@Prompt([OkCancelEdit];EditFieldPromptTitle;"Please enter the text (Case sensitive) to search for in: "+@Implode(EditField;", ")+".";"")); @Do(EditField:=EditField; FromRawValue:="") );
Implode/Explode
When exploding multiple characters can be used to separate text. If no seperator is entered a semicolon will be used. When imploding the string entered will be used to seperate the values.
Separator:=@If(DataType=@Explode("Implode;Explode";ArraySeperator); @Prompt([OkCancelEdit];PromptTitle;"Enter the "+@If(DataType="Implode";"separator";"separators")+ArraySeperator;""); ArraySeperator);
Get New Value
Most data types don't have any promptings like Unique or Sort, the rest the prompt depends on the data type.
REM {Based on what type of data is being entered different prompts will happen if any at all.}; RawValue:=@If( @Contains(DataType;"Name Multi Value");@PickList([Name]); @Contains(DataType;"Name");@PickList([Name] : [Single]); DataType=@Explode("Remove Field;Unique;Sort Ascending;Sort Descending;Implode;Explode;Proper Case Text;Proper Case Text Multi Value;Lower Case Text;Lower Case Text Multi Value;Upper Case Text;Upper Case Text Multi Value;Password Convert";ArraySeperator);""; @Contains(DataType;"Multi Value");@Prompt([OkCancelEdit];EditFieldPromptTitle;"Please enter the new desired value for: "+@Implode(EditField;", ")+"."+PormptNewLine+"Seperated with;for each value.";OriginalValue); @Contains(DataType;"+ Append Values");@Prompt([OkCancelEdit];EditFieldPromptTitle;"Please enter values to append: "+@Implode(EditField;", ")+"."+PormptNewLine+"Seperated with;for each value.";""); DataType=@Explode("Replace Substring;Replace";ArraySeperator);@Prompt([OkCancelEdit];EditFieldPromptTitle;"Please enter the text to repalce with in: "+EditField+".";""); DataType=formula;@Do(@DoWhile( OriginalValue:=@Prompt([OkCancelEdit];EditFieldPromptTitle;"Please enter the new desired formula for: "+EditField+".";OriginalValue); tempReturnCheck:=@CheckFormulaSyntax(OriginalValue); @If(tempReturnCheck!="1";@Prompt([Ok];"Invalid Formula";@Text(tempReturnCheck));""); tempReturnCheck != "1"); OriginalValue); @Prompt([OkCancelEdit];EditFieldPromptTitle;"Please enter the new desired value for: "+EditField+".";OriginalValue) );
Update Profile
Now that the data type has been selected and the new value or forumula has been entered lets set the profile document so it will be available the next time.
REM {Store Field in Profile doc}; @SetProfileField(ProfileName;EditLastField;@Unique(@Explode(FieldList : (Form+EditLastSeparator+EditField[1]);ArraySeperator;@False));@UserName); REM {Store Data Type of Field in Profile doc}; @SetProfileField(ProfileName;EditLastFieldDataType;@Unique(@Explode(FormFieldList : (Form+EditField[1]+EditLastSeparator+DataType+EditLastSeparator+@If(DataType=formula;RawValue;FormFieldListFormulaCode));ArraySeperator;@False));@UserName);
Find all the documents to be updated
First need to make sure that the current document is one that is selected. Since there is no way to know if it is selected we need to move to the next selected document. The next block of code eleminates the blank value in the list of Note IDs lets set the NoteIDList with the newly selected document. Finally we can navigate through all the selected documents to gather all the documents that will be updated. The last step is to write to the status bar how many documents were found.
{Store all Note IDs before manipulation in case field modifications cause categorized views or sorted columns to reorganize}; NoteIDList:=@Text(@NoteID); @Command([NavNextSelected]); @UpdateFormulaContext; REM {Start Looping Selected documents}; @While((@Left(NoteIDList;NoteEntryLength)!=(@Text(@NoteID+ArraySeperator))) & (@Length(NoteIDList) <MaxSearchForSelectedDocs); NoteIDList:=NoteIDList+ArraySeperator+@Text(@NoteID); NoteIDList:=@ReplaceSubString(NoteIDList;CategoryNoteID+ArraySeperator;""); @Command([NavNextSelected]); @UpdateFormulaContext ); NoteIDList:=@ReplaceSubString(NoteIDList;CategoryNoteID;""); NoteIDList:=@Unique(@Explode(NoteIDList;ArraySeperator;@False)); @StatusBar("Found "+@Text(@Elements(NoteIDList))+" documents."); NotNoteIDList:="";
Update the field
For each document determine if it needs to be updated. Not all selected documents need to be updated becuase it could have moved in the view after it was updated. If there are any errors while updated the field then stop. If the max number of documents that can be navigated to is met then assume that some of the documents can not be found. This eliminates the possibility of an infinite loop.
REM {Loop through selected docs taking each NoteIDList out of the list as it is processed}; DocUpdateCount:=0; DocNavigationCount:=0; @While(DocUpdateCount <@Elements(NoteIDList); @If(@TextToNumber(@Text(@DocumentUniqueID))!=0; @Do( NoteIDList:=@Replace(NoteIDList;@NoteID;""); NotNoteIDList:=NotNoteIDList : @NoteID; @For(ef:=1;ef <= @Elements(EditField);ef:=ef+1; formulaResult:=@Eval(DataTypeAction); REMark:=" **REM** The values entered above will be applied to all selected doc. If data conversion doesn't work then don't set field."; @If(@IsError(formulaResult); @Return(@Prompt([Ok];"Error";@Text(FormulaResult))); @SetField(EditField[ef];formulaResult) ) ); @If(DocNavigationCount >MaxUpdatedDocuments; NoteIDList:=""; @Do( DocUpdateCount:=DocUpdateCount+1; @Command([NavNextSelected]); @UpdateFormulaContext; REMark:=" **REM** If we haven't processed all docs yet but the current doc is not in the NoteIDList list, keep looping ... if cnt exceeds MaxUpdatedDocuments assume infinite loop and stop "; @If(DocUpdateCount <@Elements(NoteIDList) & (!@Member(@NoteID;NoteIDList)) & (!@Member(@NoteID;NotNoteIDList)); @While((! @Member(@NoteID;NoteIDList) & DocNavigationCount <MaxUpdatedDocuments); @Command([NavNextSelected]); @UpdateFormulaContext; DocNavigationCount:=DocNavigationCount+1); "") ) ) ); @Do(@Command([NavNextSelected]); @UpdateFormulaContext) ) );
Display results
If there are any documents that have not been updated the write the Note ID(s) to the status bar. Also update the status bar with the number of documents that were navigated through and the number of documents updated. That's it.
@If(@Implode(@Unique(@Explode(NoteIDList;ArraySeperator;@False)))!="";@StatusBar("Unable to update the following documents: "+@Implode(@Unique(@Explode(NoteIDList;ArraySeperator;@False));", ")+".");""); @StatusBar("Navigated through "+@Text(DocUpdateCount+DocNavigationCount)+" documents."); @StatusBar("Performed '"+DataType+"' for '"+@Implode(EditField;", ")+"' field"+@If(@Elements(EditField) >1;"s ";" ")+"on "+@Text(DocUpdateCount)+" document"+@If(DocUpdateCount >1;"s";"")+".")