Script calculate next year (value list)

Hi Dario,

At first glance your function looks OK to me. Have you tested whether the function yields the correct result?
I ask because the problem may lie elsewhere in your code, e.g. in the part where you set the result as a propertyvalue.

BR, Karl

Hi Karl

Thanks for your answer.

The code is exactly in place like this (the whole script) in 2 other customer environments. 

In my own environment, there is a problem with the value list year and the identification of the correct value in the value list according to PD.EndeGJ (date -> year), so i would have to create a new value list but then also change/replace the property in each class, which should not be the idea.

without this specific function, the whole script runs well, but then we would have a problem since PD.EndeGJ would remain and it would cause a loop. Also when creating a new value list for "PD.Jahr" with values such as 2022, 2023 etc. the script works fine.

I'm not sure that I can offer to make changes to this - my VBScript is rusty as anything - but at first glance it looks like the code will only return a value if it exists in the list; there's no logic in here to add a value if it's missing.

The "IF" clause probably needs to be changed to check whether there's zero values and, if so, add a value list item using this sort of method: M-Files API - AddValueListItem Method

That is a very good point. At least you need to make sure the value list actually has items for each year that may be needed in the next few years. That can be done manually for now to avoid having to change the script at this point.

Another thing that may cause problems is if you have duplicate value lists or property definitions with the same Alias. The easiest way to check could be to show Metadata Flat View and then sort Property Definitions by Alias and skim through the list, repeat for Value Lists.

There is a value list with all values necessary, e.g. when I extract 2022 from 31.12.2022, the value 2022 is already available in the value list. So this can't be the issue. But there is maybe an issue with deleted values? Somehow the correct value ID can not be calculated. Here is the complete value list (including the deleted values:

Any idea, how I can fix it without creating a new value list and change the Property in every class? Since there are/were documents in the vault with a value in this property, it is not possible to change the value list for the specific property.

This code has several problems:

1. as Karl and Craig pointed out do not handle all cases in IF statement

2. the date is not formatted correctly in taking the year, and this leads to an incorrect calculation of the year

3. Deleted records are not filtered in the search query. The correct code should be something like this:

Function JahrWertelisteID_Ermitteln()
	Dim ListItemSearchResults
	Dim JahrID: JahrID = Vault.PropertyDefOperations.GetPropertyDefIDByAlias("PD.Jahr")
	Dim JahrValueListID: JahrValueListID = Vault.ValueListOperations.GetValueListIDByAlias("VL.Jahr")
	
	Dim oExpression: Set oExpression = CreateObject("MFilesAPI.Expression")
	Dim oSearchCondition: Set oSearchCondition = CreateObject("MFilesAPI.SearchCondition")	 
	Dim oSearchConditions: Set oSearchConditions = CreateObject("MFilesAPI.SearchConditions")
	
	Dim EndeGJ_ID: EndeGJ_ID = Vault.PropertyDefOperations.GetPropertyDefIDByAlias("PD.EndeGJ")
	Dim EndeGJ: EndeGJ = FormatDateTime(PropertyValues.SearchForProperty(EndeGJ_ID).TypedValue.DisplayValue & "-01-01",2)
	
	Dim neuesJahr: neuesJahr = YEAR(DateAdd("yyyy", 1, EndeGJ)) 'Das Jahr von (EndeGJ + 1 Jahr)

	
	oSearchCondition.ConditionType = MFilesAPI.MFConditionType.MFConditionTypeEqual
	oSearchCondition.Expression.DataTypedValueValueList = JahrID
	oExpression.SetValueListItemExpression MFValueListItemPropertyDefName, MFParentChildBehaviorNone, Nothing 
	oSearchCondition.Expression = oExpression
	oSearchCondition.TypedValue.SetValue MFilesAPI.MFDataType.MFDatatypeText, neuesJahr
	oSearchConditions.Add -1, oSearchCondition
	

	oSearchCondition.ConditionType = MFilesAPI.MFConditionType.MFConditionTypeEqual
	oExpression.SetValueListItemExpression MFValueListItemPropertyDefDeleted, MFParentChildBehaviorNone, Nothing
	oSearchCondition.TypedValue.SetValue MFDatatypeBoolean, False
	oSearchCondition.Expression = oExpression	
	oSearchConditions.Add -1, oSearchCondition
	
	
	'Set ListItemSearchResults = Vault.ValueListItemOperations.SearchForValueListItemsEx(JahrValueListID,oSearchConditions)
	Set ListItemSearchResults = Vault.ValueListItemOperations.SearchForValueListItemsEx(JahrValueListID,oSearchConditions, True, MFExternalDBRefreshTypeNone, True)
	

	IF ListItemSearchResults.Count < 1 Then
	
		'Set code to react if no values are found
	
	ELSEIF ListItemSearchResults.Count = 1 THEN
	
		JahrWertelisteID_Ermitteln = ListItemSearchResults.Item(1).ID	
		
	ELSE
		'Set code to react if more than one value found
	END IF
	
End Function

Thank you very much!

The Format Date was not necessary and caused a problem but the delete Search Condition definitively helped and it works fine now as intended!

Appreciate it!

Thank you very much!

The Format Date was not necessary and caused a problem but the delete Search Condition definitively helped and it works fine now as intended!

Appreciate it!