locked
How to pre-populate list column with number sequence? RRS feed

  • Question

  • I am creating a Call Schedule for medical staff to track which patients are assigned to different teams using a custom list. They use a system of team numbers that repeats itself to ensure workload is divided equally amongst the teams each day. The sequence is: Team 1, 3, 5, 1, 3, 5, 1, 3, 5 etc. What I want is a column to be prepopulated with the Team number so that when a new patient name is entered it is already determined by the system which Team he is assigned to. I am restricted from using programming or SharePoint Designer for this. I was thinking of using the calculated column feature. Any ideas? Thanks.

    Monday, April 25, 2011 11:01 PM

All replies

  • Hi,

     

    As I understand, you want a patient assigned when his name entered by using the calculated column feature. But the calculated column based on other columns, so we could do some calculation with the column called "ID"

     

    You may do these with following steps:

    1.        Go to your custom list and select Create Column in the dropdown of Settings.

    2.        Typing the name as “team number” or something else and selecting the Calculated as the type of your column.

    3.        Typing your formula in the text box of Formula. EG:

    =IF(MOD(ID,3)=1,"1",IF(MOD(ID,3)=2,"3",IF(MOD(ID,3)=0,"5")))

    It means if the remainder of “ID” divided by three is 1, 1 will display under the new column. By analogy, we will get 2 if the remainder is 2 and get 3 when remainder is 0.

     

    Best Regards,

    Egbert

    Tuesday, April 26, 2011 6:34 AM
  • I tried your formula, and it works only for ID 1-3. Once ID 4 hits, the team number constantly shows 5. Which is strange, because the remainder of 4 divided by three is remainder 1, so it should show 1 and begin the cycle again..1,3,5, 1,3,5 etc. But instead now it shows 1,3,5,5,5,5,5,5 etc.
    Wednesday, April 27, 2011 5:00 PM
  • I tried your formula, and it works only for ID 1-3. Once ID 4 hits, the team number constantly shows 5. Which is strange, because the remainder of 4 divided by three is remainder 1, so it should show 1 and begin the cycle again..1,3,5, 1,3,5 etc. But instead now it shows 1,3,5,5,5,5,5,5 etc.

    I noticed something else. If add the calculated column to an existing list of items, the number sequence will display correctly. However, if I add a new item to an existing list, then it experiences the problem I described above where it shows 1,3,5 then 5, 5, 5, 5, etc.
    Wednesday, April 27, 2011 5:41 PM