2

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.

  • You can try calculated column – Arulraj Sep 12 '18 at 12:33
  • I have a calculated column for the year, but here i have to check the last entry of the list and calculate it. – Lord_Pinhead Sep 12 '18 at 13:01
  • What is the expression, you used? – Arulraj Sep 12 '18 at 13:06
  • =Text(Year(Today)), "0") that is for the year. But i need a new Number for every year to start with because the endresult of the number should be year-number -> 2018-0001, 2018-0002, 2018-0003 and 2019-0001. Year and Number are to Columns. – Lord_Pinhead Sep 12 '18 at 13:17
0

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>

enter image description here

  • That is a good solution, worked like a charm. Thanks. – Lord_Pinhead Sep 17 '18 at 12:50
  • Maybe i should say i added setInterval(getMaxNumber, 5000); at the end of the function and put it in a function to run the query every 5 seconds to avoid a racing condition as best as possible. – Lord_Pinhead Sep 17 '18 at 12:57
0

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:

picture of page and web part

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

sort by ID

Set the item limit to 1.

item limit = 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

  • That is also a good solution, but the user want it directly in the UI, so i go with the JQuery Solution and change it a little bit to avoid racing conditions. Your solution is good to avoid a racing condition we could have with a slow writer. – Lord_Pinhead Sep 17 '18 at 12:53
0

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.