Workflow dealing with multiple tables

Question

In a redesign of our CRM, we have a need to modularize our workflow. Due to the various activities and campaigns that we process, I have a need to create a master case with a one-to-many relationship to various case items. However, each case item needs to have its own associated data.

The current design that I have involves a set of tables:

• Cases
• Case_Calls
• Case_Emails
• etc...

We have about a dozen different items in total.

I'm trying to figure out a good way to organize my form to accommodate these varying workflows. The ideal workflow might involve adding multiple items to a case during a given interaction, so I need a sane way to handle them within the same form.

Originally, everything was text-templated and added to a single "Long Text" field. This made it very difficult to filter data or run reports, so it's not very useful to us.

The next iteration involved a Tab Control, with a different tab for each case item. However this causes behavioral problems, and it doesn't gracefully handle the number of different item types that I'm looking for; wherein the additional tabs cause a need for horizontal scrolling that just pisses everyone off.

So now I'm at a loss. How can this be best designed for ease of use?

Edit: As suggested, here are some screenshots of the current (broken) iteration:

It is broken in the sense that each tab contains a subform, but there is no guarantee that a Case is created before the subform record is created. There is also no straightforward way to enforce a create-only workflow this way.

Hospitable

Have you tried playing with the tab control's "multirow" property? That could reduce the need for horizontal scrolling. – Backrest

FYI you need to set the "Use Theme" property to No for this to work in newer versions of Access. – Backrest

Can you add a couple screenshots of the iterations you describe? It will be easier to help if we can see what you're going for. – Reiterate

@Reiterate Thanks for the suggestion, I have added screenshots. – Hospitable

Can you hide the subform/pages before a case is created, or use another form to ensure the creation of the Case before even opening this form. – Seventh

@geeFlo, I can, however as stated in the question, it doesn't fit the workflow well. "However this causes behavioral problems, and it doesn't gracefully handle the number of different item types that I'm looking for; wherein the additional tabs cause a need for horizontal scrolling that just pisses everyone off." – Hospitable

If it's the issue of too many tabs, then perhaps you can consolidate simlar tables (eg. Emails and Calls) and differentiate the logs with a "Type" field. If that's still too many, create a second or third Tab control, that allows you to have the tabs all stacked on top of one another. – Seventh

So with the tabbed design, you're ending up with lost email or call records unlinked to cases? – Reiterate

@Rominus, yes. It causes calls & emails to be assigned to Case ID '0' if I don't create the case prior to filling out the subforms. It's what triggered our desire to move away from this design, as it is also confusing. – Hospitable

Ok, I can help with that, let me write up a proper answer – Reiterate

Okay, just read below. What it sounds like is that your workflow may require that you actually start logging an instance prior to a Case Number being assigned. Create a form that opens one of the subcases with it's own form. The completion of that form's record, then triggers code to create a CaseNumber in your main table. Then whenever you subsequently open your form/subform view, the parent/child linkage now exists, and you can then update other info in your main case record. – Seventh

You haven't really described the workflow you want to support. Should a case be automatically created if an item is created? Where does the case data come from then? As for the tab control, using reasonable short titles, it shouldn't be a problem to fit 13 tabs without scrolling. i.imgur.com/AQt696s.png – Mctyre

@Mctyre - I need to be able to create a case, and attach multiple different items to the case. For example, I may need to be able to create a case with several emails and a call attached to it. – Hospitable

Then what behavioral problems are left if you disable the creation of items if no case is loaded (as suggested by geeFlo)? I honestly don't understand. – Mctyre

@Andre, maybe I need to rewrite/reword my question. I thought I was fairly clear that I'm looking for a working solution, not necessarily to fix my existing solution. There are multiple issues with my existing solution, that we want to move away from it entirely. I'm not entirely sure why this has become the focal point when I've stated that multiple times. – Hospitable

The thing is, with your data structure and the need to have all data on one form, your existing design (main form for main record, subforms in a tab control for subitems) is most probably the best one. And fixing it would be the way to go. -- Unless all items can be mingled into a single structure with a "type" field to distinguish them, then you could simply use one continuous form. – Mctyre

Either your company does not have a process-flow or you haven't understood it. how can you take notes for a case without case details? your database should not allow such configuration. if case and notes/emails or whatever do not have a mandatory parent-child but link-able, you must find when and where they come together! ask yourself, can a history exists without case record? can attachments be uploaded without case record? if you can do so, how are you going to find it again? by going through 10000 of records? – Harpy

@krishKM Ideally, there technically will be a case. Right now there certainly is a problem that I could solve by asking the question "how can I make it so that a new record in a subform ensures that a record is created on the main form" or to that like, but I'm not convinced that's my only problem here. However, it sounds like Andre might be right, and I just need to suck it up with my current design. – Hospitable

Hi, Firstly, I'm not a big fan of Access being used as a database (there are better options out their i.e. SQL database). Second, does the frontend has to using Access forms? personally I would use .NET solution. You have more options to make user journey easier and more user friendly. Lastly, with actual user journey, it sounds like there is a lot of data entry. Rather then displaying multiple tab's, consider displaying a 'page' at a time with some king of progress bar at the top of the page. It makes it simple and clear for the user – Nebo

In ALL cases the main form will automatic write out the main record WHEN you change focus to a sub form - so that main record WILL be saved and created for you. As for child tables and records, it should not matter in general if child records are created or not. (eg: you have customers - the sub form with invoices does not necessary have to create records since its possible to enter and create customers without invoices. So it not clear at all why the main record is not saved (or created) when focus moves to a sub form that is bound to a child table. – Hobo

Answer 1

Perhaps you should completely forget about your old system and think fresh.

your current scenario that i could think of:

Your employees are taking calls or notes for a case and they are currently writing it down on a paper, Excel or your app! They surely note somewhere who called and what time called? this is your key points!

As a developer you must ensure, users are following the correct process-flow or you must prepare different scenarios.

In reality, there won't be any emails regarding a "case", if the case record does not exists. By following this rule, your app should not allow sending out emails/taking notes without a valid case record.

If the case details aren't available at the time when notes/emails are received you must still create the "case" record with any available data you have. For instance who actioned? what date time? With this data, you can insert the case record and then allow your employees to continue adding notes, emails.

lets assume:

• All your sub-forms have valid linked master-child values
• you are opening your case form for adding new records:

---

docmd.OpenForm "your_case_form",acNormal,,,acFormAdd

on your "your_case_form" form_load event you can check if the form is ready for entering new details. something like this.

Private Sub Form_Load()
    if( Me.NewRecord) then
        me.txt_added_by.value = your_way_of_username
        me.txt_added_date.value = now()
    END IF

End Sub

above code will enter the username and current time-stamp which automatically generates a case record (assuming your case table does not have any "not null" fields and ID is an auto number)

your employees can then just move to any sub-form which will automatically trigger the save action for the case table and you have your case record!

Before inserting in your sub-form you could also check if the parent/Case record is available.

if (nz(me.parent!txt_case_id.value,0) =0) then
    'case id not found..
    'you can advise the user to enter anything on the case section to create a record or you can use SQL to insert a record
    'or you could cancel the insert, move/set focus to parent form. add datetime / username to create record 
end if

the point is, you need to make sure that you create parent record before allowing child records.

Harpy

Answer 2

To fix your unlinked records issue you need to set your Link Master Fields and Link Child Fields in the properties of the subform(s). In the screenshot below, my linked field is SUBSCRIBER_ID, yours would be Case ID (or whatever the actual varname is).

Edit: questioner already did the above, so that step is covered

You could put in a VBA check for null Case IDs in your New Call button (or however a new call/email record is created). Something like this:

If IsNull(Me.Case_ID) Then
    MsgBox "Before adding a call you need to create a Case ID, please do so first"
    Me.Undo
Else
    [Whatever the button really does]
End If

Depending on where you put the code, you may not need the Me.Undo. If it's On Click for the button you probably don't. If it's Before Update on the Call/Email ID you probably do.

Regarding the scrolling you could, instead of showing the whole tabbed subform box, have a button for each type of data that pops a form for just that data when the button is pressed. So for calls you'd have it pop something like what you show in the bottom part of your last screenshot, without the tabs, in a overlaying window. You'd want a Close button on the pop-up form to go back to the main form.

Reiterate

This doesn't seem to answer the question, but rather answers previously bad behavior. As indicated in the question & comments, I am wanting to move away from my existing design. Additionally, my forms are already linked like this. The issue with the old design (that again, I wish to move away from) is that creating a record in subform does not enforce a record in the main form. If I create the main form record first, the subform is properly linked. However creating the subform record first assigns a Case ID of 0. – Hospitable

Ok, I misunderstood. If you already have that the answer is different, of course. – Reiterate

Answer 3

You're not going to get around this:

there is no guarantee that a Case is created before the subform record is created

they have to create the case header row before they can create related children. Whether the related subforms are opened via a tab, button, or even a list of child types doesn't really matter, they're functionally the same.

If they really want to create children without a case, you could create an 'orphanage' case row and have them add them there, but then they'll ask for a way to move the child from the orphanage to the case they should have picked in the first place.

---

If this is your problem: creating a record in subform does not enforce a record in the main form. can you disable or hide the subforms until the parent case record is selected? Under what conditions are you getting new rows in child forms assigned to case 0? Does Case 0 exist? If so, that's acting as your orphanage.

Fourpenny