Hi all, I was wondering if I could pick your collective minds on brainstorming some approaches to a form I am building.
We would like to design a form that allows our Instructional Designers (IDs) to request jobs from our Media team. Our IDs work on different Programmes and so need the option to select the appropriate programme they are requesting work from. At this stage the ID now has the option to request a New Job or to update an Existing Job. If they are updating an Existing Job, they can just simply type in Job number (e.g xxxx000001) but if they are requesting a new job, then depending on the Programme they select, we need to the form to look at the existing jobs and increment the job number by 1 - yes the dreaded increment!
I have created several lists on SharePoint such as a "Programmes" List which simply lists the different Programme Types. I then created another List called "SPAssetNo", where I have added a lookup column to bring in the values from the "Programme" list and then have a column stating the highest job number value. (I even added a calculated column to +1 to the Job Number value and store it to call on later.)
Back to the form I have added Lookup fields to bring in both the Programme type from the "Programmes" list and the highest job number from the "SPAssetNo", filtering it with the value of the Programme lookup field.
So far no problems, but now I am trying to figure out the best method to use this information and control the fields.
My main concern is that as soon as the ID selects the "Programme" on the form, the lookup and filtering fires and presents them with the highest Job Number value at that stage - let's say "Jet000010". Well if they are still filling in the form and another ID comes along and requests a job for the same Programme, but the previous ID still hasn't submitted their form, the highest Job Number Value on their request will still read "Jet000010" and now we are potentially overwriting each other. - I need a method of when the save/submit button is pressed, the Highest Job Number value is refreshed, to check to see if it has incremented since they started editing and to store the new value.
The second issue I am having is the filtered lookup field to bring in the Highest Job Number based on the Programme selected, is either a drop down/choice/tick box option. I have tore most of main remaining hair out trying to find the correct code/ to tick this filed automatically when the ID select the Programme from the Programme Field.
Hopefully that gives you an idea of my conundrum and I would welcome any advice and suggestions.
Predicting the future of values in an environment where those values can be changed by other people is almost always going to troublesome. There is however one value in SharePoint that both Increments *and* is Unique. It's the ID of an item of course. Personally I would not put the effort into trying to work forwards to see into the future, and would instead just utilize List Item [ID] as a component of the Job Number and a Workflow that runs on Item creation to format it into whatever I want.
I'd do it this way because no amount of checking or rechecking can EVER guarantee that a job number incremented at the form level will actually be correct / work as two or more people can submit work at roughly the same time after those checks are finished.
Doing it this way *also* ties it back to something that is useful to anyone who needs to find information about a giving Job / Item in sharepoint (ie: you the admin), as it's a trivial thing to look at and know exactly which item to be looking for.
Is this an acceptable approach? If not, could it be refined so that it is?