Conditionally increment a case ID I have a table {cases} which has in part, upload_id, case_id case_name etc etc.
With a custom form, users, depending on role(4 custom roles), will upload a file upload_id. This  has been serialized. If it is a new case, case_name is NOT present then get max(case_id) add one and save to DB. Also users can add a file to existing cases, so the upload_id would auto-increase, and the case_id stays the same.
I've been working on this one area for 2 weeks and have come to the end of my rope.  

I have written all of this into a custom PHP web app a few years ago, but now I have to rewrite it all in Drupal 7.  Any pointers in the right direction would be appreciated. 

**********EDIT**************

I didn't think you could have 2 Serial Fields in the same table schema. I already have upload_id as a serial. I just can not figure out how to insert the case_id in Drupal.

Real scenario:

User uploads the file, upload_id =3796, case_id = 2916, case_name = 'xxx Macy'.

User forgot to include something needs to upload additional file. upload_id = 3797, case_id = 2916 where case_name = 'xxx Macy'. And still forgot a file, upload_id = 3798, case_id = 2916. 

New user uploads file, upload_id = 3799, case_id = 2917, case_name = 'Jane Deer'.
They way you describe your case_id it sounds like the primary key of your table (a number which increases by 1 for each new entry). Rather than increasing this number in the form (and spare your self the trouble of assure atomic results when more than one person are entering cases at once) you can just set the column as primary key in your schema and let the db manage it. That way you don't need the field in your form at all.

$schema['case'] = array(
  'fields' =&gt; array(
    'case_id'       =&gt; array('type' =&gt; 'serial', 'unsigned' =&gt; TRUE, 'not null' =&gt; TRUE),
   //your other fields
  ),

  'primary key' =&gt; array('case_id'),
);




That said I would consider not using a custom table to start with and instead leverage the functionality Drupal is shipped with and use a node to represent your cases.

There is a contrib module called Serial Field which allows you to add a field to your content type which increases by 1 for each case you create. There is a file field in core which allows you to upload a file to the site as well. The name field is a little tricky as Drupal doesn't allow empty titles but you can easily work around it by adding a simple text field for the name and generate the actual title with Automatic Entity Label.

In this case you need to download the two contrib modules, install them, create a case node type, add and configure the fields and you are done. The advantage is you can still leverage a great number of existing modules working with nodes/fields and entities which is much harder/impossible with custom tables. And you can display the results using Views.