Query List and add 1

Question

I am really new to SharePoint and have the Problem with a Form. I have a list like the following:

ID Title Year No DateFrom DateTo

When a user opens a form for a new Entry and the View should automatically query for the max No from the current Year and add 1. If there is no Entry, it should start at 1.

In SQL it would be a simple query:

SELECT isnull(sub.No, 1) FROM 
(
    SELECT MAX(No) AS No
    FROM List 
    WHERE Year = Year(GETDATE())
) Sub

But how to do it without Powershell or .Net Code? I could write a small Script, but i try to stay away from scripts in Workflows.

Answer 1

We can use the jQuery with REST API to achieve it.

Modify the "listName" in the code below and add it to a script editor web part in the newform.aspx page.

<script type="text/javascript" src="https://code.jquery.com/jquery-3.2.1.min.js"></script>
<script type="text/javascript">
$(function(){
    var listName="AutoNumber";
    $("input[title='No']").val(getMaxNo(listName));
});
function getMaxNo(listName){
    var maxNo=1;
    var currentYear=(new Date()).getFullYear();
    var url = _spPageContextInfo.webAbsoluteUrl + "/_vti_bin/listdata.svc/"+listName+"?$select=No&$filter=Year eq '"+currentYear+"'&$orderby=No desc&$top=1";
    $.ajax({
        url: url,
        type: "GET",
        async:false,
        headers: {
            "Accept": "application/json;odata=verbose",
        },
        success: function (data) {
            if(data.d.length>0){
                maxNo=data.d[0].No+1;
            }           
        },
        error: function (error) {
            //console.log(JSON.stringify(error));  
        }
    });
    return maxNo;
}
</script>

Answer 2

I had a similar situation. I have a task list with 3 types of tasks, including 'Departmental' level tasks. I edited the page, inserted a web part and configured like this:

Sort by the ID with the idea that the highest will show on the top.

Set the item limit to 1.

I edited a view of the list, but you will need to edit your new item form and add a web part to it. Configure the web part to display the same list.

LINKS

• Microsoft link on web parts (Applies to Sharepoint Online, I don't know which version you are using though...)

Answer 3

We don't have any OOB option to achieve this. So try any of the following approaches,

1. Use a custom form to insert the new item.
2. Use workflow to update the field after inserting an item.

In both the cases, we can query the max value.

Answer 4

I would use a small workflow to achieve this.

I would setup a helper list that holds the max(ID) for each year.

The workflow would try to retrieve the year in question from the helper list, get the max value and then update it to the new max value.

After that you can insert the new value into the current item.

Besides that I would challenge the question about the format of the ID'S - Will a unique ID be sufficient? if so combine year+itemID for sorting