Hi all, I was wondering if I could pick your collective minds on brainstorming some approaches to a form I am building.
The Scenario:
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!
Work Done:
I've tried a few approaches to this starting with the responsive form template, but quickly switched to the classic form template so I could add custom JavaScript to help with functionality.
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.
The Problem(s):
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.
Cheers
