Auto-increment or auto-populate

[darkroast]

Here's my scenario...

I'm creating a case log table: one record per case. Each case will have one or more calls associated with it.

What I'd like to do is have a button at the top of the form (or some other way) where I can choose "New Case" or "Existing Case". If you choose the New Case option, it will save the case record with an auto-generated sequential case number. However, if you choose the Existing Case option, it will bring up a box where you can enter / select the case number, and that will then lookup all the previous call records, and add a new one (for the current call) if you save the call record.

So, here's the question: how would I accomplish the "new case" versus "existing case" logic so that it either creates the new case number or gives you the lookup?

Is this possible at all?

 

[darkroast]

friendly bump...

 

[darkroast]

Bump again... Is this even possible?

 

[troester]

In the "calls" form/list add a databasejoin element pointing to the "case" table, enable "Add option in frontend" and may be "Frontend select"

 

[darkroast]

OK, that's helpful. Now, a question: the case number field (the one I created as above) is linked to the ID field of the case log table. If I save it with a blank entry, will that case number be auto-incremented?

Basically, I want to (a) use an auto-incremented case number if it's a new case, or be able to call up the case if it's an existing case. This will definitely do the second, but will it also handle the first?

 

[darkroast]

friendly bump...

 

[darkroast]

bump please...

 

[darkroast]

Bump, friendly-like y'all