How to present a list of items to the user where some of those rows may be unavailable (i.e. -greyed out /disabled)

[Rodeo Clown]

Hello everyone. Disclaimer: this project & it's data is confidential so I cannot post any code or the DB itself.

I want to track whether or not an entity has been issued warning(s) for an infraction.

The Enforcement_T has the following fields:
1. entity_ID (autonumber, PK)
2. EnforcementDate (date/time)
3. EnforcementType_ID (number, foreign key - relates to EnforcementType_T - below)
4. 1st Warning (checkbox - Yes/No)
5. 2nd warning (checkbox - Yes/No)
3. Legal Action initiated (checkbox - Yes/No)

The EnforcementType_T has the following fields:
1. 1st warning issued
2. 1st warning resolved
3. 2nd warning issued
4. 2nd warning resolved
5. Legal Action initiated
6. Legal Action resolved

Parameters: (I hope this makes sense...)
1. There can only be one 1st warning at any given time. Likewise there can only be one 2nd warning or legal action initiated at any given time.
2. neither the 2nd warning issued, 2nd warning resolved, legal action initiated or legal action resolved can be selected if the 1st warning is false (this holds true as we progress down the list)
3. It is possible to have 1st & 2nd warning and legal action initiated all set to true, in which case the only choice presented to the user is legal action resolved

I've tried using a combo box, but apparently it is not optional to "grey-out" (i.e. disable) specific rows.

I've also tried using a list box, but apparently you cannot grey out specific rows in that control either.

So my question is this: What kind of control can I use to accomplish this feature? I thought about a bunch of check boxes, but don't really like the clutter on the form. I also thought about using a query to display the available options in a listbox to the end user.

 

[Josef P.]

In other words: is this simply a status workflow?

BTW (perhaps OT):

3. EnforcementType_ID (number, foreign key - relates to EnforcementType_T - below)

+

4. 1st Warning (checkbox - Yes/No)
5. 2nd warning (checkbox - Yes/No)
3. Legal Action initiated (checkbox - Yes/No)

That looks redundant to me.

 

[Rodeo Clown]

In other words: is this simply a status workflow?

BTW (perhaps OT):

+

That looks redundant to me.

Well, I guess it could be considered a status workflow - although the flow would stop if the entity meets the conditions where the 1st warning is resolved, which in turn would turn off that checkbox in the table. I see your point about redundancy but I wasn't sure how to incorporate that feature without creating a dependency in the table. Do you have a better solution?

 

[plog]

Your tables aren't properly set up. You shouldn't store data in field names fields 4,5,6 of Enforcement_T do this and every field of EnforcementType _T does this as well. To help you I would need more information about the real world process this is modeling. Specifically--

1. How does EnforcementDate work? Is it set at the beginning and then left? Or is it updated everytime a new status is entered for that enforcement? As it is now you don't have a timeline of the enforcements. Your either simply tracking the date of the first warning, or you lose that whenever you update to a new status.

2. Only certain orders of enforcements are valid right? Every enforcement must start at 1 right? Then 1-2-3-4-5-6 is valid, probably 1-3-5 is valid. But I bet 1-6 isn't, nor 2-3-5-6.

3. Can only one enforcement occur at a time? At all? Suppose they go 1-2 and no enforcements for 5 years and then they get another one. Does that start at new enforcement record? Or does the existing record rest to 1 or does the existing enforcement record go to 3? Or something else?

I think the real issue is you are conflating a form with data and trying to solve both problems at once. You need to figure out the data side--get your table and fields correct then build a form to support that data. You shouldn't be building your table/fields around an idealized form.

 

[Josef P.]

Just a few thoughts:

Tabel Enforcement_T
- entity_ID (autonumber, PK)
- EnforcementDate (date/time)

Table EnforcementAction_T
- idEnforcementAction (autonumber, PK)
- entity_fi (fk to Enforcement_T)
- No (int) ... ascending numbering .. allowed values: 1-3
- date_issued => also used to show "issued Yes/No"
- date_resolved => also used to show "resolved Yes/No"
* unique index: entity_fi + No .. or use this as PK

 

[Rodeo Clown]

Your tables aren't properly set up. You shouldn't store data in field names fields 4,5,6 of Enforcement_T do this and every field of EnforcementType _T does this as well. To help you I would need more information about the real world process this is modeling. Specifically--

1. How does EnforcementDate work? Is it set at the beginning and then left? Or is it updated everytime a new status is entered for that enforcement? As it is now you don't have a timeline of the enforcements. Your either simply tracking the date of the first warning, or you lose that whenever you update to a new status.

2. Only certain orders of enforcements are valid right? Every enforcement must start at 1 right? Then 1-2-3-4-5-6 is valid, probably 1-3-5 is valid. But I bet 1-6 isn't, nor 2-3-5-6.

3. Can only one enforcement occur at a time? At all? Suppose they go 1-2 and no enforcements for 5 years and then they get another one. Does that start at new enforcement record? Or does the existing record rest to 1 or does the existing enforcement record go to 3? Or something else?

I think the real issue is you are conflating a form with data and trying to solve both problems at once. You need to figure out the data side--get your table and fields correct then build a form to support that data. You shouldn't be building your table/fields around an idealized form.

to answer your questions:

1. the EnforcementDate is established when the user enters/records the action on a form. So if today is Tuesday and the 1st warning was sent out last Friday, the user would enter Friday's date instead of today. This date never changes or gets removed from the table as I do want to have an historical record of each and every time the entity gets the 1st warning (and so on).

2. correct, and I think you are right in that the valid order is 1-3-5. A 2nd warning cannot occur without a 1st warning being issued and subsequently a legal action cannot occur if both the 1st & 2nd warnings are unresolved (that's why I used Yes/No fields).

3. I would say the answer to this is yes. Lets say that the entity incurs an infraction/violation on 03/04/24 and they get warned to fix the problem on that date (1st warning issued, checkbox = true). If they fix the problem, the action is 1st warning resolved & checkbox = false. If they don't fix the problem within 30 days - by 04/03/24, they get a 2nd warning (2nd warning issued). Then the same with 2nd warning resolved. If they still haven't fixed the problem within 30 days then legal action commences. So, yes, they could have an infraction in 2024 where they were issued warnings # 1 & 2 but then resolved the situation (checkboxes = false) and again in 2026. And again, I do want to maintain the historical data.

You make some good points. Perhaps I will go back to the drawing board and review/revise my design, thank you.

 

[plog]

1. the EnforcementDate is established when the user enters/records the action on a form... So if today is Tuesday and the 1st warning was sent out last Friday, the user would enter Friday's date instead of today. This date never changes or gets removed from the table as I do want to have an historical record of each and every time the entity gets the 1st warning (and so on).

That made sense up until the '(and so on)'. So on to what? With your current structure you are not capturing when the 1st warning was resolved, or 2nd warning issued or any subsequent action.

Give Josef's proposed table stucture a look, I think that's what you should use. Then from a form the simplest solution would be a subform that shows all the status fields and would allow a user to enter a new one. They could click the check mark to mark an enforcement as resolved or add a new record to move it to the next level.

 

[Rodeo Clown]

yes, i do capture the 2nd warning issued date and the legal action initiated date as well in the Enforcement_T. So, if an entity had a 1st warning issued within the last 30 days, they cannot get another 1st warning (back to my original question about combos or listboxes). If 30 days has passed, they get the 2nd warning, but legal action is not an option.

If they resolve the issue, be it 1st or 2nd warning - or even legal action initiated - "when" they resolve it is irrelevant. So, that's what I meant by and so on.

***revision*** Although I stated that the resolution date is irrelevant, it is recorded when the user selects one of the 'Resolved' actions in the EnforcementDate field

Yes, I've been looking at Josef's suggestions. Thanks again.

 

[LarryE]

Perhaps a table and relationship structure like this:

You have:

1. Multiple Entities
2. Each entity may have multiple enforcement actions
3. Each enforcement action may have multiple enforcement results

 

[Pat Hartman]

An enforcement action only has one result so the third table is not necessary. The result fields go into the action table. A Resolved flag and Resolved date are redundant. Only the date is needed.

If you have standard resolutions that you might want to track you can use a combo to make them easy to group. Then a comments field to add details if necessary.

 

[LarryE]

An enforcement action only has one result so the third table is not necessary. The result fields go into the action table. A Resolved flag and Resolved date are redundant. Only the date is needed.

If you have standard resolutions that you might want to track you can use a combo to make them easy to group. Then a comments field to add details if necessary.

It was my understanding that there could be multiple results for each enforcement action:
1. 1st warning issued
2. 1st warning resolved
3. 2nd warning issued
4. 2nd warning resolved
5. Legal Action initiated
6. Legal Action resolved
I thought if you have a 1st warning issued on 5/31/2024 for example, you could then add a result of 2nd warning issued on 6/30/2024 for example and then check Resolved if the 2nd warning issued was resolved on 7/15/2024 for example. You would keep a history of the enforcement action results. But maybe I have it wrong. If you don't wish to keep a history of the enforcement results, then you are right. In my opinion, a history shows which results resolved the issue and on what date.

 

[MajP]

Something is kind of confusing here.
@LarryE interpreted "Entity" to be a person, and it sounds that way to me too!

Lets say that the entity incurs an infraction/violation on 03/04/24 and they get warned to fix the problem on that date (1st warning issued, checkbox = true)

But in the Enforcement_T table entity is the primary key not a foreign key. So it cannot be the person causing the infraction. I am assuming @Josef P. interpreted to mean an Enforcement

The Enforcement_T has the following fields:
1. entity_ID (autonumber, PK)
2. EnforcementDate (date/time)
3. EnforcementType_ID (number, foreign key - relates to EnforcementType_T - below)
4. 1st Warning (checkbox - Yes/No)
5. 2nd warning (checkbox - Yes/No)
3. Legal Action initiated (checkbox - Yes/No)

To me that kind of looks like it is supposed to be the infraction, and you would think there is a description of what the infraction is that is being enforced. It sounds that way

So, yes, they could have an infraction in 2024 where they were issued warnings # 1 & 2 but then resolved the situation (checkboxes = false) and again in 2026. And again, I do want to maintain the historical data.

So I would think (logical not matching OP names cause I got no idea)
TblPersons
-- personID
-- other person fields

tblInfractions
-- infractionID
-- InfractionDate ' might be different then first warning being when this problem occured not when warning issued
-- InfractionDescription ' somewhere need to know what they are being cited for
-- PersonID_FK
-- DateResolved ' You resolve infractions not warnings or legal actions

tblEnforcments
-- EnforcementID
-- EnforcementTypeID_FK
-- DateIssued
--InfractionID_FK

'Helper table
tblEnforcementTypes
-- EnforcementTypeID
-- EnforcementTypeDescription

EnforcementTypeID	EnforcementTypeDescription
1	1st Warning
2	2nd Warning
3	Legal Action

If you want to show all choices but grey out everything but the possible choice you could use a subform and add some continuous formatting. or you could simply show the possible choice if one exists. Make a subform function like a listbox. You could also do it just with controls on the form.

I believe the Logic (using my naming conventions)
1. If count of enforcementID = 0 where infractionID_FK = selected infraction then (no warnings)
show first warning, hide/disable other choices.
2. Else If max of EnforcementTypeID_FK = 1 AND Max DateIssue+ 30 < Date where InfractionID_FK = selected infraction then show 2nd Warning. If Max DateIssue+ 30 > Date then show nothing.
3. Else If max of EnforcementTypeID_FK = 2 AND Max DateIssue+ 30 < Date where InfractionID_FK = selected infraction then show Legal Action. If Max DateIssue+ 30 > Date then show nothing.
4. Else If max of EnforcementTypeID_FK = 3 show nothing.

I would probably do this with a group of controls on the form. Labels and buttons.
Example. If the Infraction has 2 warnings against it and 30 days passed. You would see the controls showing

First Warning issued 1/1/2024
Second Warning Issued 2/1/2024

Now my third label would say something like "45 days days have passed since Second Warning. Click to add Legal Action issued". Then clicking this button would add new record with enforcementtypeID_FK = 3 and default the issue date to current date. The third label then shows "Legal Action Started on x/x/xxxx"

 

[LarryE]

Something is kind of confusing here.
@LarryE interpreted "Entity" to be a person, and it sounds that way to me too!

But in the Enforcement_T table entity is the primary key not a foreign key. So it cannot be the person causing the infraction. I am assuming @Josef P. interpreted to mean an Enforcement



To me that kind of looks like it is supposed to be the infraction, and you would think there is a description of what the infraction is that is being enforced. It sounds that way


So I would think (logical not matching OP names cause I got no idea)
TblPersons
-- personID
-- other person fields

tblInfractions
-- infractionID
-- InfractionDate ' might be different then first warning being when this problem occured not when warning issued
-- InfractionDescription ' somewhere need to know what they are being cited for
-- PersonID_FK
-- DateResolved ' You resolve infractions not warnings or legal actions

tblEnforcments
-- EnforcementID
-- EnforcementTypeID_FK
-- DateIssued
--InfractionID_FK

'Helper table
tblEnforcementTypes
-- EnforcementTypeID
-- EnforcementTypeDescription

EnforcementTypeID	EnforcementTypeDescription
1	1st Warning
2	2nd Warning
3	Legal Action

If you want to show all choices but grey out everything but the possible choice you could use a subform and add some continuous formatting. or you could simply show the possible choice if one exists. Make a subform function like a listbox. You could also do it just with controls on the form.

I believe the Logic (using my naming conventions)
1. If count of enforcementID = 0 where infractionID_FK = selected infraction then (no warnings)
show first warning, hide/disable other choices.
2. Else If max of EnforcementTypeID_FK = 1 AND Max DateIssue+ 30 < Date where InfractionID_FK = selected infraction then show 2nd Warning. If Max DateIssue+ 30 > Date then show nothing.
3. Else If max of EnforcementTypeID_FK = 2 AND Max DateIssue+ 30 < Date where InfractionID_FK = selected infraction then show Legal Action. If Max DateIssue+ 30 > Date then show nothing.

No, I assumed an entity is a company or organization which is being monitored (regulated) by some other enforcement agency. But the OP didn't say and hasn't been back in the forum so we don't know for sure. But it sounds like the OP either works for some enforcement agency or is building a db for them.

My thought was that the enforcement action options (1st warning, 2nd warning etc.) could be a list box to be selected in the EnforcementResult table using the ResultDescription field. Then the Resolved field is Yes/No.

 

[Rodeo Clown]

@MajP OK, so my bad here and I really do apologize for the confusion as I was trying to mimic the actual table design and I missed a key element here (no pun intended). The Enforcement_T does have a primary key named Enforcement_ID and the Entity_ID is a foreign key to a table named Entity_T.

Please accept my sincerest apologies for the confusion.

 

[MajP]

@MajP OK, so my bad here and I really do apologize for the confusion as I was trying to mimic the actual table design and I missed a key element here (no pun intended). The Enforcement_T does have a primary key named Enforcement_ID and the Entity_ID is a foreign key to a table named Entity_T.

Please accept my sincerest apologies for the confusion.

OK that makes a lot more sense. I assume the Enforcement_T holds some description of the thing being enforced or is there another related table to the infraction?

No, I assumed an entity is a company or organization which is being monitored (regulated) by some other enforcement agency. But the OP didn't say and hasn't been back in the forum so we don't know for sure.

I guess it depends on your background on how you interpret it. I assumed it was my home owners association citing me again for having a house that needs power washing or my leaves need to get raked.

 

[Rodeo Clown]

I really dislike that I can't post the actual project/code and yes I understand that the abstraction is confusing. Let me try to put this another way.

Let's say that Starfleet Command has ordered all Starships, regardless of class, to have their Dilithium Crystals replaced every 5 light years. Every Chief Engineer, on every ship, must comply with this new mandate. If the Enterprise's crystals are due to be replaced on stardate 2024-04-30 and LaForge hasn't sent in his report by that date, then Starfleet will issue him a 1st warning (1st warning checkbox on). If, within 30 days, he then swaps out the crystals & informs Starfleet, then the 1st warning has been resolved (1st warning checkbox off). No other notation needs to be recorded other than the Enterprise was delinquent but the matter was resolved in 2024. If LaForge does NOT respond to the mandate (because they're busy fighting the Romulans), then Starfleet issues a 2nd warning (1st AND 2nd warning checkboxes on). Now, LaForge takes care of business and informs Starfleet and the 2nd warning has been resolved (1st AND 2nd warning checkboxes off). IF Laforge does not comply with the mandate he is brought before the Klingon High Council (1st, 2nd and Legal Action initiated checkboxes on). But LaForge changes the crystals prior to his court date and thus legal action has been resolved (1st, 2nd and legal checkboxes off).

In the latter scenario w/ the Klingons, there would be four entries in the Enforcement_T: 1st warning issued, 2nd warning issued, legal initiated and legal resolved - along with the corresponding dates when each of the four events occured.

So in my Enforcement_T the entity is the engineer of the starship.

 

[LarryE]

I really dislike that I can't post the actual project/code and yes I understand that the abstraction is confusing. Let me try to put this another way.

Let's say that Starfleet Command has ordered all Starships, regardless of class, to have their Dilithium Crystals replaced every 5 light years. Every Chief Engineer, on every ship, must comply with this new mandate. If the Enterprise's crystals are due to be replaced on stardate 2024-04-30 and LaForge hasn't sent in his report by that date, then Starfleet will issue him a 1st warning (1st warning checkbox on). If, within 30 days, he then swaps out the crystals & informs Starfleet, then the 1st warning has been resolved (1st warning checkbox off). No other notation needs to be recorded other than the Enterprise was delinquent but the matter was resolved in 2024. If LaForge does NOT respond to the mandate (because they're busy fighting the Romulans), then Starfleet issues a 2nd warning (1st AND 2nd warning checkboxes on). Now, LaForge takes care of business and informs Starfleet and the 2nd warning has been resolved (1st AND 2nd warning checkboxes off). IF Laforge does not comply with the mandate he is brought before the Klingon High Council (1st, 2nd and Legal Action initiated checkboxes on). But LaForge changes the crystals prior to his court date and thus legal action has been resolved (1st, 2nd and legal checkboxes off).

In the latter scenario w/ the Klingons, there would be four entries in the Enforcement_T: 1st warning issued, 2nd warning issued, legal initiated and legal resolved - along with the corresponding dates when each of the four events occured.

So in my Enforcement_T the entity is the engineer of the starship.

Makes absolutely no difference to ACCESS what an "entity" is. And that is exactly the design I presented.

 

[Rodeo Clown]

@LarryE your close with my occupational assessment

@MajP I was going to experiment with conditional formatting as you suggested, or use VBA, or a query, to restrict what rows are displayed in the listbox.

 

[MajP]

I tried to do this with a subform using comboboxes, and it became too much of a pain. This is one of those cases where designing a non traditional interface is actually easier to control the workflow. I think this works well and has several bells and whistles making it easy and intuitive.

2nd warning assigned no resolution date and more than 30 days elapsed for the first warning.

1. As you can see only the applicable button is available to select. Example, If no actions have been taken you can add a first warning, if first warning exist you can only add second warning, if a second warning you can only add legal action.

2. If there is no resolution date to the infraction and if 30 days has passed since an action was issue that action becomes red. It will turn green if there is a resolution. Red: 30 days elapsed. Green: resolved. No format: not resolved and less than 30 days.
3. If you try to add another action (in this case Legal Action) and 30 days have not elapsed since the last action it will alert you that 30 days has not elapsed.

I think this table structure and design should meet all the requirements and is visually intuitive.

Legal action assigned and a resolution date. No viable selections.

1st warning assigned, greater than 30 days, and no resolution date. Second warning is viable to assign.

To Do.
1. I would add a button to delete an action and not allow to delete from the subform. This would then only allow you to delete the last action.
2. Need to validate action dates. Should not allow the user to enter a date for an action prior to the last action date. I gave the user the option to add an action within 30 days but you could just dissallow that.
3. Next to the buttons you could add a checkbox icon to mark it as complete. Give more a workflow feel. I disabled the buttons. But instead you could show the next only. For example if you added a First warning then show First Warning disabled, Second Warning enabled, and hid legal action. Once a Second warning is selected then disable second warning and show first warning.

One thing. In post 8 you made it sound like resolution was as I have it. But in post 16 you made it sound as each action could have a resolution. I do not get the meaning behind that.

 

[MajP]

I added the Checks so it make it more "workflow" like